Secure Internet Voting with Perl

  0  #!/bin/sh

  1  /usr/local/bin/mysql -f CEA <<END

  2  DROP TABLE party;
  3  DROP TABLE office;
  4  DROP TABLE candidate;
  5  DROP TABLE registration;
  6  DROP TABLE tally;
  7  DROP TABLE writein;

  8  CREATE TABLE party (
  9          party_id        INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
 10          party_name      CHAR(100),
 11          PRIMARY KEY(party_id),
 12          UNIQUE(party_name)
 13  );

 14  CREATE TABLE office (
 15          office_id       INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
 16          office_name     CHAR(100),
 17          PRIMARY KEY(office_id),
 18          UNIQUE(office_name)
 19  );

 20  CREATE TABLE registration (
 21          registration_id    CHAR(100) NOT NULL,
 22          registration_used  TINYINT DEFAULT 0 NOT NULL,
 23          PRIMARY KEY(registration_id)
 24  );

 25  CREATE TABLE candidate (
 26          candidate_id	INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
 27          first_name	CHAR(50) NOT NULL,
 28          last_name	CHAR(50) NOT NULL,
 29          party_id	INT(4) UNSIGNED NOT NULL,
 30          office_id	INT(4) UNSIGNED NOT NULL,
 31          UNIQUE	(party_id, office_id),
 32          PRIMARY KEY(candidate_id)
 33  );

 34  CREATE TABLE tally (
 35          voter_id	CHAR(100) NOT NULL,
 36          office_id	INT(4) UNSIGNED NOT NULL,       
 37          candidate_id	INT(4) UNSIGNED NOT NULL,
 38          time_voted	TIMESTAMP(10),
 39          UNIQUE(voter_id,office_id),
 40          KEY(voter_id)
 41  );

 42  CREATE TABLE writein (
 43          voter_id	CHAR(100) NOT NULL,
 44          office_id	INT(4) UNSIGNED NOT NULL,
 45          writein	CHAR(100) NOT NULL,
 46          time_voted	TIMESTAMP(10),
 47          KEY(voter_id),
 48          KEY(office_id)
 49  );

 50  END