---
--- Demo of a registrar's system as a MySQL database.
---
--- See assignment 7 on
--- http://cs.marlboro.edu/term/fall04/web_perl/home.html
---
---   student        course        faculty
---   -------        ------        -------
---   Joe Fish       calculus      Dr. Who
---   Joe Fish       physics       A. Einstein
---   Mary Green     calculus      Dr. Who
---   Mary Green     chemistry     A. Noble
---   Ellen White    radio         Dr. Who
---   Francis Eld    radio         Dr. Who
---   Frank Micklin  chemistry     A. Noble
---
--- Here's what I came up with in amazing ASCII art,
--- where I'm using Status=(faculty, enrolled) to 
--- attach a person to a course as a faculty member or student.
---
---   +----------+    +----------+      +----------+
---   | Person   |    | Course   |      | Status   |
---   |----------|    |----------|      |----------|
---   |  id      |    |  id      |      |  id      |
---   |  name    |    |  name    |      |  name    |
---   |  phone   |    |  term    |      +----------+
---   +----------+    |  place   |         |
---            |      |  credits |         |
---            |      +----------+         |
---            |             |             |
---            |             |             |
---           /|\           /|\           /|\
---   +-------------------------------------------+
---   |  Registration                             |
---   |-------------------------------------------|
---   |   id                                      |  
---   |   status                                  |  
---   |   person                                  |  
---   |   course                                  |  
---   |   date                                    |
---   +-------------------------------------------+
---
---
------------------------------------------------------------------

-- The following prevents consistency checks between tables.

CREATE TABLE Person (
  id int(11) NOT NULL auto_increment
  , name VARCHAR(255) DEFAULT ''
  , phone VARCHAR(255) DEFAULT ''
  , PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE TABLE Course (
  id int(11) NOT NULL auto_increment
  , name VARCHAR(255) DEFAULT ''
  , time VARCHAR(255) DEFAULT ''
  , term VARCHAR(255) DEFAULT ''
  , place VARCHAR(255) DEFAULT ''
  , credits int(11) DEFAULT 0
  , PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE TABLE Status (
  id int(11) NOT NULL auto_increment
  , name VARCHAR(255) DEFAULT ''
  , PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE TABLE Registration (
  id int(11) NOT NULL auto_increment
  , status int(11) NOT NULL DEFAULT 0
  , person int(11) NOT NULL DEFAULT 0
  , course int(11) NOT NULL DEFAULT 0
  , date DATE
  , PRIMARY KEY (id)
  , KEY (status)
  , FOREIGN KEY (status) REFERENCES status(id) ON DELETE CASCADE
  , KEY (person)
  , FOREIGN KEY (person) REFERENCES person(id) ON DELETE CASCADE
  , KEY (course)
  , FOREIGN KEY (course) REFERENCES course(id) ON DELETE CASCADE
) TYPE=InnoDB;

INSERT INTO Status VALUES ( 1, 'faculty' );
INSERT INTO Status VALUES ( 2, 'student' );

INSERT INTO Person VALUES ( 1, 'Joe Fish',     '111-2223' );
INSERT INTO Person VALUES ( 2, 'Mary Green',   '111-2345' );
INSERT INTO Person VALUES ( 3, 'Ellen White',  '111-2323' );
INSERT INTO Person VALUES ( 4, 'Francis Eld',  '111-7777' );
INSERT INTO Person VALUES ( 5, 'Frank Miklin', '111-1234' );
INSERT INTO Person VALUES ( 6, 'Dr. Who',      '222-0000' );
INSERT INTO Person VALUES ( 7, 'A. Einstein',  '222-9999' );
INSERT INTO Person VALUES ( 8, 'A. Noble',     '222-1337' );

--- Probably the time and term should be entries in another table, eh?

INSERT INTO Course VALUES (1, 'calculus','MWF 10:00am','Fall 2004','Sci217', 3 );
INSERT INTO Course VALUES (2, 'physics','MWF 1:00pm','Fall 2004','Sci217', 4 );
INSERT INTO Course VALUES (3, 'chemistry','MWF 8:30am','Fall 2004','chem lab', 4 );
INSERT INTO Course VALUES (4, 'radio','Thus 10:00pm','Fall 2004','basement', 1 );

--- INSERT INTO Registration VALUES ( id, status, person , course, date );
-- faculty
INSERT INTO Registration VALUES ( 1, 1, 6, 1, '2004-09-01' );  -- Dr. Who, calculus
INSERT INTO Registration VALUES ( 2, 1, 6, 4, '2004-09-01' );  -- Dr. Who, radio
INSERT INTO Registration VALUES ( 3, 1, 7, 2, '2004-09-01' );  -- A. Eins, physics
INSERT INTO Registration VALUES ( 4, 1, 8, 3, '2004-09-01' );  -- A. Noble, chem
--- students
INSERT INTO Registration VALUES ( 5, 2, 1, 1, '2004-09-01' );  -- Fish, calc
INSERT INTO Registration VALUES ( 6, 2, 1, 2, '2004-09-01' );  -- Fish, phys
INSERT INTO Registration VALUES ( 7, 2, 2, 1, '2004-09-01' );  -- Green, calc
INSERT INTO Registration VALUES ( 8, 2, 2, 3, '2004-09-01' );  -- Green, chem
INSERT INTO Registration VALUES ( 9, 2, 3, 4, '2004-09-01' );  -- White, radio
INSERT INTO Registration VALUES (10, 2, 4, 4, '2004-09-01' );  -- Eld, radio
INSERT INTO Registration VALUES (11, 2, 5, 3, '2004-09-01' );  -- Micklin, chem

--- And now that the tables are set up, turn checking back on.

