enterprise database management

10
IS 631 101– EDM Project Voter Management System Date: 10 th December 2013 Presentation By: Tejas Omprakash Agarwal (Garodia)

Upload: tejas-garodia

Post on 09-May-2015

172 views

Category:

Technology


0 download

DESCRIPTION

Project on Voter Management System

TRANSCRIPT

Page 1: Enterprise Database Management

IS 631 101– EDM

ProjectVoter Management System

Date: 10th December 2013

Presentation By:Tejas Omprakash Agarwal (Garodia)

Page 2: Enterprise Database Management

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

Page 3: Enterprise Database Management

SCREEN SHOTS

 

ADD Record Panel EDIT Record Panel PRINT & PREVIEW Record Panel

Page 4: Enterprise Database Management

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

Page 5: Enterprise Database Management

EXTENDED ER DIAGRAM

Page 6: Enterprise Database Management

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

         

Page 7: Enterprise Database Management

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

Page 8: Enterprise Database Management

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

Page 10: Enterprise Database Management

THANK YOU