enterprise database management
DESCRIPTION
Project on Voter Management SystemTRANSCRIPT
IS 631 101– EDM
ProjectVoter Management System
Date: 10th December 2013
Presentation By:Tejas Omprakash Agarwal (Garodia)
SCOPEScope of Voter Management System are:
To maintain (ADD, EDIT, DELETE) data of the voters
Track voters voting records
Sort voters by Surname, Building Name
Select and Print records
Find Duplicate records using voter name
Sort voter whose voter based on year of voting
Search voter from the records
SCREEN SHOTS
ADD Record Panel EDIT Record Panel PRINT & PREVIEW Record Panel
ENTITYvoter_details
PK voter_id
voter_name voter_gender voter_age address_id polling_id email voting_status election_id
polling_station
PK polling_id
polling_name election_id polling_pincode
E2. voter_address
E3. polling_station
E4. Election
E1. voter_details
E5. election_type
EXTENDED ER DIAGRAM
DATA DICTIONARYTable/Entity Name Attribute Name Type Description Key
voter_details
voter_id int(144) Voter Id Primary Key
voter_name varchar(144)Voter Name (Last name followed by First Name)
voter_gender text Gender voter_age int(24) Age email varchar(144) Email Address
address_id int(144) Address Id of Voter Foreign Key 1
polling_id int(144) Polling station Id Foreign Key 2
voting_status varchar(144)Voting Status of voter for that year
election_id int(144)Election Id corresponding to the election Foreign Key 3
voter_address
address_id int(144) Address Id of Voter Primary Key
address_house_no varchar(144) House/shop number address_bldg_name varchar(144) House Name address_street varchar(144) Street Name
address_pincode int(144) Pincode address_state varchar(144) State
address_country varchar(144) Country
polling_station
polling_id int(144) Polling Station ID Primary Keypolling_name varchar(144) Polling Station Name
polling_pincode int(144) Polling Station Pincode
election_id int(144)Election Id corresponding to the election Foreign Key
Electionelection_id int(144) Election ID Primary Keyelection_name varchar(144) Election Name election_year int(144) Election Year
election_typeelection_id int(144)
Election Id corresponding to the election Primary/Foreign Key 1
voter_id int(144)Voter Id corresponding to the voter_details Primary/Foreign Key 2
QUERYShow all the records
SELECT a.voter_id,a.voter_name,a.voter_gender,a.voter_age,a.email,(Select b.address_house_no from voter_address as b where b.address_id = a.address_id) voter_flatno,(Select b.address_bldg_name from voter_address as b where b.address_id = a.address_id) voter_building,(Select b.address_street from voter_address as b where b.address_id = a.address_id) address_street,(Select b.address_pincode from voter_address as b where b.address_id = a.address_id) address_pincode,(Select b.address_state from voter_address as b where b.address_id = a.address_id) address_state,(Select b.address_country from voter_address as b where b.address_id = a.address_id) address_country, a.address_id,a.polling_id,a.voting_status FROM voter_details as a limit $_REQUEST[start],$_REQUEST[limit]
Search Records
SELECT * FROM voter_details where voter_name LIKE '%".$_REQUEST['query']."%'
Display Duplicate Entries
SELECT (Select b.address_house_no from voter_address as b where b.address_id = a.address_id) voter_flatno,(Select b.address_bldg_name from voter_address as b where b.address_id = a.address_id) voter_building,(Select b.address_street from voter_address as b where b.address_id = a.address_id) address_street,(Select b.address_pincode from voter_address as b where b.address_id = a.address_id) address_pincode,(Select b.address_state from voter_address as b where b.address_id = a.address_id) address_state,(Select b.address_country from voter_address as b where b.address_id = a.address_id) address_country, a.address_id,a.polling_id,a.voting_status,a.voter_id,a.voter_gender,a.voter_age,a.email,a.voter_name, COUNT(*) c FROM voter_details AS a GROUP BY voter_name HAVING c > 1
QUERY contd.Delete Records
DELETE FROM election_type where election_id='1' AND voter_id regexp '".$idstr."‘
Display Records Building-wise
Query1:
SELECT DISTINCT address_bldg_name AS voter_building FROM voter_address
Query2:
SELECT a.voter_id,a.voter_name,a.voter_gender,a.voter_age,a.email,(Select b.address_house_no from voter_address as b where b.address_id = a.address_id AND b.address_bldg_name LIKE '%".$_REQUEST['voter_building']."%') voter_flatno, (Select b.address_bldg_name from voter_address as b where b.address_id = a.address_id AND b.address_bldg_name LIKE '%".$_REQUEST['voter_building']."%') voter_building, (Select b.address_street from voter_address as b where b.address_id = a.address_id AND b.address_bldg_name LIKE '%".$_REQUEST['voter_building']."%') address_street, (Select b.address_pincode from voter_address as b where b.address_id = a.address_id AND b.address_bldg_name LIKE '%".$_REQUEST['voter_building']."%') address_pincode, (Select b.address_state from voter_address as b where b.address_id = a.address_id AND b.address_bldg_name LIKE '%".$_REQUEST['voter_building']."%') address_state, (Select b.address_country from voter_address as b where b.address_id = a.address_id AND b.address_bldg_name LIKE '%".$_REQUEST['voter_building']."%') address_country, a.address_id,a.polling_id,a.voting_status FROM voter_details as a, voter_address as b WHERE b.address_bldg_name LIKE '%".$_REQUEST['voter_building']."%' AND b.address_id = a.address_id
DEMO
Voter Management System
THANK YOU