-- Database Election
Drop Database IF EXISTS Election;
Create Database Election;
Use Election;

-- Table Member
CREATE TABLE Member
(   
    mid int primary key,
    firstName varchar(30) NOT NULL,
    lastName varchar(60) NOT NULL,
    pass char(12) NOT NULL,
    email varchar(100)
);

-- Table Office
CREATE TABLE Office
(   
    oid int NOT NULL,
    title varchar(50) NOT NULL,
    PRIMARY KEY(oid)
);

-- Table Candidate
CREATE TABLE Candidate
(   
    mid int primary key,
    oid int NOT NULL,
    FOREIGN KEY (mid) REFERENCES Member(mid) on delete cascade,
    FOREIGN KEY (oid) REFERENCES Office(oid) on delete cascade
);

-- Table Ballot
CREATE TABLE Ballot
(   
    mid int NOT NULL,
    oid int NOT NULL,
    ballotPickupTime DateTime, 
    FOREIGN KEY (mid) REFERENCES Member(mid),
    FOREIGN KEY (oid) REFERENCES Office(oid),
    PRIMARY KEY(mid,oid)
);

-- Table Vote
CREATE TABLE Vote
(   
    vid int primary key auto_increment,
    mid int NOT NULL,
    voteTime DateTime NOT NULL,
    FOREIGN KEY (mid) REFERENCES Candidate(mid)
);
-- Data
-- Table Member
Insert into Member(mid,firstName,lastName,pass,email) Values(1,'Ann','Bee','ab1234','abee@misor.org');
Insert into Member(mid,firstName,lastName,pass,email) Values(2,'Ben','Wolf','bw4321','bwolf@misor.org');
Insert into Member(mid,firstName,lastName,pass,email) Values(3,'Sue','Butterfly','sb1234','sbutterfly@misor.org');
Insert into Member(mid,firstName,lastName,pass,email) Values(4,'Don','Eagle','de4321','deagle@misor.org');
Insert into Member(mid,firstName,lastName,pass,email) Values(5,'Eve','Hawk','eh1234','ehawk@misor.org');
Insert into Member(mid,firstName,lastName,pass,email) Values(6,'Tom','Dolphin','td4321','tdolphin@misor.org');
Insert into Member(mid,firstName,lastName,pass,email) Values(7,'Gin','Whale','gw1234','gwhale@misor.org');
Insert into Member(mid,firstName,lastName,pass,email) Values(8,'Hal','Fox','hf4321','hfox@misor.org');
Insert into Member(mid,firstName,lastName,pass,email) Values(9,'Ian','Cat','ic1234','icat@misor.org');
Insert into Member(mid,firstName,lastName,pass,email) Values(10,'Ron','Dog','rd4321','rdog@misor.org');

-- Table Office
Insert into Office(oid,title) Values(1,'President');
Insert into Office(oid,title) Values(2,'Vice-President');
Insert into Office(oid,title) Values(3,'Treasurer');

-- Table Candidate
Insert into Candidate(mid,oid) Values(1,1);
Insert into Candidate(mid,oid) Values(3,1);
Insert into Candidate(mid,oid) Values(4,2);
Insert into Candidate(mid,oid) Values(5,3);
Insert into Candidate(mid,oid) Values(8,1);
Insert into Candidate(mid,oid) Values(9,3);
Insert into Candidate(mid,oid) Values(10,2);

-- Table Ballot
Insert into Ballot(mid,oid,ballotPickupTime) Values(9,1,'2009-10-02 20:08');
Insert into Ballot(mid,oid,ballotPickupTime) Values(9,2,'2009-10-02 20:08');
Insert into Ballot(mid,oid,ballotPickupTime) Values(9,3,'2009-10-02 20:08');
Insert into Ballot(mid,oid,ballotPickupTime) Values(8,1,'2009-10-02 23:05');
Insert into Ballot(mid,oid,ballotPickupTime) Values(8,2,'2009-10-02 23:05');
Insert into Ballot(mid,oid,ballotPickupTime) Values(8,3,'2009-10-02 23:05');
Insert into Ballot(mid,oid,ballotPickupTime) Values(2,1,'2009-10-02 23:41');
Insert into Ballot(mid,oid,ballotPickupTime) Values(2,2,'2009-10-02 23:41');
Insert into Ballot(mid,oid,ballotPickupTime) Values(2,3,'2009-10-02 23:41');
Insert into Ballot(mid,oid,ballotPickupTime) Values(6,1,'2009-10-02 23:45');
Insert into Ballot(mid,oid,ballotPickupTime) Values(6,2,'2009-10-02 23:45');
Insert into Ballot(mid,oid,ballotPickupTime) Values(6,3,'2009-10-02 23:45');
Insert into Ballot(mid,oid,ballotPickupTime) Values(7,1,'2009-10-03 04:02');
Insert into Ballot(mid,oid,ballotPickupTime) Values(7,2,'2009-10-03 04:02');
Insert into Ballot(mid,oid,ballotPickupTime) Values(7,3,'2009-10-03 04:02');
Insert into Ballot(mid,oid,ballotPickupTime) Values(4,1,'2009-10-03 06:32');
Insert into Ballot(mid,oid,ballotPickupTime) Values(4,2,'2009-10-03 06:32');
Insert into Ballot(mid,oid,ballotPickupTime) Values(4,3,'2009-10-03 06:32');
Insert into Ballot(mid,oid,ballotPickupTime) Values(10,1,'2009-10-03 08:39');
Insert into Ballot(mid,oid,ballotPickupTime) Values(10,2,'2009-10-03 08:39');
Insert into Ballot(mid,oid,ballotPickupTime) Values(10,3,'2009-10-03 08:39');
Insert into Ballot(mid,oid,ballotPickupTime) Values(3,1,'2009-10-03 14:53');
Insert into Ballot(mid,oid,ballotPickupTime) Values(3,2,'2009-10-03 14:53');
Insert into Ballot(mid,oid,ballotPickupTime) Values(3,3,'2009-10-03 14:53');
Insert into Ballot(mid,oid,ballotPickupTime) Values(1,1,'2009-10-03 18:32');
Insert into Ballot(mid,oid,ballotPickupTime) Values(1,2,'2009-10-03 18:32');
Insert into Ballot(mid,oid,ballotPickupTime) Values(1,3,'2009-10-03 18:32');

-- Table Vote
Insert into Vote(vid,mid,voteTime) Values(1,8,'2009-10-02 20:09');
Insert into Vote(vid,mid,voteTime) Values(2,10,'2009-10-02 20:10');
Insert into Vote(vid,mid,voteTime) Values(3,1,'2009-10-02 20:10');
Insert into Vote(vid,mid,voteTime) Values(4,4,'2009-10-02 23:07');
Insert into Vote(vid,mid,voteTime) Values(5,1,'2009-10-02 23:07');
Insert into Vote(vid,mid,voteTime) Values(6,8,'2009-10-02 23:06');
Insert into Vote(vid,mid,voteTime) Values(7,1,'2009-10-02 23:43');
Insert into Vote(vid,mid,voteTime) Values(8,10,'2009-10-02 23:44');
Insert into Vote(vid,mid,voteTime) Values(9,8,'2009-10-02 23:43');
Insert into Vote(vid,mid,voteTime) Values(10,5,'2009-10-02 23:46');
Insert into Vote(vid,mid,voteTime) Values(11,10,'2009-10-02 23:46');
Insert into Vote(vid,mid,voteTime) Values(12,10,'2009-10-02 23:46');
Insert into Vote(vid,mid,voteTime) Values(13,5,'2009-10-03 04:04');
Insert into Vote(vid,mid,voteTime) Values(14,1,'2009-10-03 04:05');
Insert into Vote(vid,mid,voteTime) Values(15,4,'2009-10-03 04:04');
Insert into Vote(vid,mid,voteTime) Values(16,5,'2009-10-03 06:34');
Insert into Vote(vid,mid,voteTime) Values(17,1,'2009-10-03 06:33');
Insert into Vote(vid,mid,voteTime) Values(18,3,'2009-10-03 06:34');
Insert into Vote(vid,mid,voteTime) Values(19,4,'2009-10-03 08:41');
Insert into Vote(vid,mid,voteTime) Values(20,8,'2009-10-03 08:41');
Insert into Vote(vid,mid,voteTime) Values(21,4,'2009-10-03 08:42');
Insert into Vote(vid,mid,voteTime) Values(22,10,'2009-10-03 14:56');
Insert into Vote(vid,mid,voteTime) Values(23,5,'2009-10-03 14:55');
Insert into Vote(vid,mid,voteTime) Values(24,1,'2009-10-03 14:54');
Insert into Vote(vid,mid,voteTime) Values(25,1,'2009-10-03 18:35');
Insert into Vote(vid,mid,voteTime) Values(26,9,'2009-10-03 18:34');
Insert into Vote(vid,mid,voteTime) Values(27,8,'2009-10-03 18:35');