USE project;

CREATE TABLE persons (
  id      MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  fname   VARCHAR(15) NOT NULL,
  lname   VARCHAR(30) NOT NULL,
  userid  VARCHAR(40) NOT NULL,
  password VARCHAR(40) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE ctlabels (
  ctype   SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  string  VARCHAR(40) NOT NULL,
  PRIMARY KEY (ctype)
) ENGINE=INNODB;

CREATE TABLE contacts (
  ckey    MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  id      MEDIUMINT UNSIGNED NOT NULL,
  ctype   SMALLINT UNSIGNED NOT NULL,
  cstring VARCHAR(40) NOT NULL,
  FOREIGN KEY (id) REFERENCES persons(id) ON DELETE CASCADE,
  FOREIGN KEY (ctype) REFERENCES ctlabels(ctype) ON DELETE RESTRICT,
  PRIMARY KEY (ckey)
) ENGINE=INNODB;

CREATE TABLE rlabels (
  role    SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  string  VARCHAR(40) NOT NULL,
  PRIMARY KEY (role)
) ENGINE=INNODB;

CREATE TABLE projects (
  team    SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  client  MEDIUMINT UNSIGNED NOT NULL,
  string  VARCHAR(40) NOT NULL,
  FOREIGN KEY (client) REFERENCES persons(id) ON DELETE RESTRICT,
  PRIMARY KEY (team)
) ENGINE=INNODB;

CREATE TABLE students (
  id      MEDIUMINT UNSIGNED NOT NULL,
  team    SMALLINT UNSIGNED,
  mrole   SMALLINT UNSIGNED,
  FOREIGN KEY (id) REFERENCES persons(id) ON DELETE CASCADE,
  FOREIGN KEY (team) REFERENCES projects(team) ON DELETE SET NULL,
  FOREIGN KEY (mrole) REFERENCES rlabels(role) ON DELETE SET NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE iroles (
  ikey    MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  id      MEDIUMINT UNSIGNED NOT NULL,
  irole   SMALLINT UNSIGNED NOT NULL,
  FOREIGN KEY (id) REFERENCES persons(id) ON DELETE CASCADE,
  FOREIGN KEY (irole) REFERENCES rlabels(role) ON DELETE CASCADE,
  PRIMARY KEY (ikey)
) ENGINE=INNODB;

INSERT INTO rlabels
  (string) VALUES
  ('Project Manager'),
  ('System Architect'),
  ('Data Architect'),
  ('Test Designer'),
  ('PHP Programmer'),
  ('JavaScript Programmer'),
  ('Database Administrator'),
  ('XML Designer');

INSERT INTO ctlabels
  (string) VALUES
  ('primary email'),
  ('alternate email'),
  ('home phone'),
  ('cell phone'),
  ('work phone'),
  ('AOL IM'),
  ('Yahoo Chat'),
  ('MSN Messenger'),
  ('other');

INSERT INTO persons
  (fname, lname, userid, password) VALUES
  ('Sam',      'Cooper',        'coopers',   SHA('pass')),
  ('Lindsey',  'Goshen',        'goshenl',   SHA('pass')),
  ('Tommy',    'Teller',        'tellert',   SHA('pass')),
  ('Nancy',    'Lange',         'langen',    SHA('pass'));