Interview Question
Country: United States
Table 1 for candidates: Attributes {candidate_id(primary key), candidate_name, email, contact_no.,roll_no}
Table 2 for voters: attributes{voter_id/roll_no,name, email, contact_no, candidate_id_of_whom_he/she_is_voting, vote_id (auto incerment to count the number of votes)}
Table 3 for votes: attributes{roll_no_of_voter, candidate_id}
I am a newbie. Please correct me if any error is in the above answer.
CREATE TABLE `user` (
`user_id` int(11) NOT NULL auto_increment,
`user_name` varchar(64) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `post` (
`post_id` int(11) NOT NULL auto_increment,
`post_name` varchar(64) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`post_id`),
CONSTRAINT `user_post` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `vote` (
`vote_id` int(11) NOT NULL auto_increment,
`user_id` int(11) NOT NULL,
`post_id` int(11) NOT NULL,
PRIMARY KEY (`vote_id`),
CONSTRAINT `user_vote` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`),
CONSTRAINT `post_vote` FOREIGN KEY (`post_id`) REFERENCES `post` (`post_id`),
UNIQUE KEY `unq_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
create procedure capture_vote(in_user_id int, in_post_id int) $$
begin
declare l_current_vote int default 0;
select vote_id from vote where user_id = in_user_id and post_id = in_post_id into l_current_vote;
if (l_current_vote = 0) then
insert into vote (user_id, post_id) values (in_user_id, in_post_id);
end if;
end
$$
CREATE TABLE `user` (
`user_id` int(11) NOT NULL auto_increment,
`user_name` varchar(64) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `post` (
`post_id` int(11) NOT NULL auto_increment,
`post_name` varchar(64) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`post_id`),
CONSTRAINT `user_post` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `vote` (
`vote_id` int(11) NOT NULL auto_increment,
`user_id` int(11) NOT NULL,
`post_id` int(11) NOT NULL,
PRIMARY KEY (`vote_id`),
CONSTRAINT `user_vote` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`),
CONSTRAINT `post_vote` FOREIGN KEY (`post_id`) REFERENCES `post` (`post_id`),
UNIQUE KEY `unq_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
create procedure capture_vote(in_user_id int, in_post_id int) $$
begin
declare l_current_vote int default 0;
select vote_id from vote where user_id = in_user_id and post_id = in_post_id into l_current_vote;
if (l_current_vote = 0) then
insert into vote (user_id, post_id) values (in_user_id, in_post_id);
end if;
end
$$
attributes{roll_no_of_voter{PK}, candidate_id}
- Gaurav Khurana January 05, 2014There should be only one primary key in vote tables i.e roll numer (or voter_id) of the voter so that one voter can have only one entry in the votes tables.
If we make both (voter_id & candidate_id ) together as primary key , then there will be multiple votes
voter1 candidate2
voter1 candidate1
so voter_id should be primary key.