Question

Design a simple database to track people and who they voted for. The database should have 3 tables:

A table of candidates

A table of registered voters

A table of votes

The candidate table should provide a listing of all candidates and information about the candidates.

The registered voter table should hold all registered voters and any pertinent information about them

The vote table should hold vote records for each candidate made by the voters

Requirements:

The system should not allow duplicate voters to be added to the registered voter table based on a voter_id number field

The system should reject duplicate votes by the same voter

Pick the relevant fields needed for each table to make the system useful

Provide:

SQL code to create the three tables

SQL code to insert test data into the database

A SQL query to retrieve the vote of record of a particular individual

Answer 1

1.Table Name :candidates
create table candidates(
candidateID int Primary key,
candidateName varchar(100),
netWorth decimal(10,2),
DOB date,
Mobile varchar(10));

/*inserting records into candidates table*/
insert into candidates values (1,'Sam Ram',2356987,'1986/05/12','8855663322');
insert into candidates values (2,'Mr.Loko',78963,'1976/09/10','4567891230');
insert into candidates values (3,'RG Sons',12345,'1982/01/30','1234567890');

/*selecting records*/
select * from candidates

Screen showing records :

***********************************************

2.Table Name :registeredVoters
create table registeredVoters(
registeredVoterID int Primary key,
registeredVoterName varchar(100),
DOB date,
Mobile varchar(10),
address varchar(500),
zipCode int(6));

/*inserting records into registeredVoters table*/
insert into registeredVoters values (1,'Voter1','1999/03/31','1245698707','NY',789456);
insert into registeredVoters values (2,'Voter2','2001/04/29','8888888888','UK',456123);
insert into registeredVoters values (3,'Voter3','1992/11/30','5566887799','US',123456);

/*selecting records*/
select * from registeredVoters;'

Screen showing records :

******************************************

3.Table Name :votes
create table votes(
registeredVoterID int ,
candidateID int,
VotingDate date,
ElectionType varchar(50),
Primary key(registeredVoterID,candidateID,VotingDate),
foreign key (registeredVoterID) references registeredVoters(registeredVoterID),
foreign key (candidateID) references candidates(candidateID));

/*inserting records into votes table*/
insert into votes values (1,1,'2020/10/03','MP');
insert into votes values (2,2,'2020/10/03','MP');
insert into votes values (3,3,'2020/10/03','MP');

/*selecting records*/
select * from votes;

Screen showing records :

***********************************

A SQL query to retrieve the vote of record of a particular individual :

select registeredVoters.registeredVoterID,registeredVoterName,
candidates.candidateID,candidateName,VotingDate
from
registeredVoters inner join votes
   on registeredVoters.registeredVoterID=votes.registeredVoterID
inner join candidates
   on votes.candidateID=candidates.candidateID;

Query result :