t7KVTQeA

1#
2# this SQL file creates the schema for the evote system database
3#
4
5# remove the existing tables
6#
7DROP TABLE IF EXISTS user;
8DROP TABLE IF EXISTS district;
9DROP TABLE IF EXISTS ballot;
10DROP TABLE IF EXISTS ballot_item;
11DROP TABLE IF EXISTS vote_record;
12DROP TABLE IF EXISTS candidate;
13DROP TABLE IF EXISTS political_party;
14
15#
16# Table definition for table 'user', which represents the 'User', 'Voter', and 'Election Officer' classes
17# 'age' and 'districtId' are NULL if the row represents an 'Election Officer'
18#
19CREATE TABLE user (
20    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
21    firstName VARCHAR(255) NOT NULL,
22    lastName VARCHAR(255) NOT NULL, 
23    userName VARCHAR(255) NOT NULL UNIQUE,
24    password VARCHAR(255) NOT NULL UNIQUE,
25    email VARCHAR(255) NOT NULL UNIQUE,
26    address VARCHAR(255) NOT NULL,
27    age INT UNSIGNED,
28    districtId INT UNSIGNED,
29
30    FOREIGN KEY (districtId) REFERENCES district(id)
31) ENGINE=InnoDB;
32
33#
34# Table definition for table 'district', which represents the 'Electoral District' class
35#
36CREATE TABLE district (
37    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
38    name VARCHAR(255) NOT NULL UNIQUE
39) ENGINE=InnoDB;
40
41#
42# Table definition for table 'ballot', which represents the 'Ballot' class
43#
44CREATE TABLE ballot (
45    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
46    openDate DATETIME NOT NULL,
47    closeDate DATETIME NOT NULL,
48    approved BOOLEAN NOT NULL,
49    districtId INT UNSIGNED NOT NULL,
50
51    FOREIGN KEY (districtId) REFERENCES district(id)
52) ENGINE=InnoDB;
53
54#
55# Table definition for table 'vote_record', which represents the 'Vote Record' association class
56#
57CREATE TABLE vote_record (
58    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
59    voterId INT UNSIGNED NOT NULL,
60    ballotId INT UNSIGNED NOT NULL,
61    date DATETIME NOT NULL,
62
63    FOREIGN KEY (voterId) REFERENCES user(id),
64    FOREIGN KEY (ballotId) REFERENCES ballot(id)
65) ENGINE=InnoDB;
66
67#
68# Table definition for table 'ballot_item', which represents the 'Ballot Item', 'Issue', and 'Election' classes
69# 'question' and 'yesCount' are NULL if the row represents an 'Election'.
70# 'office' and 'isPartisan' are NULL if the row represents an 'Issue'.
71#
72CREATE TABLE ballot_item (
73    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
74    voteCount INT UNSIGNED NOT NULL,
75    ballotId INT UNSIGNED NOT NULL,
76    question VARCHAR(255),
77    yesCount INT UNSIGNED,
78    office VARCHAR(255),
79    isPartisan BOOLEAN,
80    
81    FOREIGN KEY (ballotId) REFERENCES ballot(id)
82) ENGINE=InnoDB;
83
84#
85# Table definition for table 'candidate', which represents the 'Candidate' class
86#
87CREATE TABLE candidate (
88    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
89    name VARCHAR(255) NOT NULL UNIQUE,
90    voteCount INT UNSIGNED NOT NULL,
91    electionId INT UNSIGNED NOT NULL,
92    partyId INT UNSIGNED NOT NULL,
93
94    FOREIGN KEY (electionId) REFERENCES ballot_item(id),
95    FOREIGN KEY (partyId) REFERENCES political_party(id)
96) ENGINE=InnoDB;
97
98#
99# Table definition for table 'political_party', which represents the 'Political Party' class
100#
101CREATE TABLE political_party (
102    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
103    name VARCHAR(255) NOT NULL UNIQUE
104) ENGINE=InnoDB;