/*
 Notes: 
 * primary key must also be NOT NULL
 * ENUM type
 * foreign key, REFERENCES as below...
 * LOAD_FILE
 * setting variable @ in select statement.
*/

CREATE TABLE person (
  sid INT PRIMARY KEY NOT NULL,
  name VARCHAR(200),
  email VARCHAR(200));

CREATE TABLE notes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  date DATE,
  text TEXT);
  
CREATE TABLE student (
  id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  sid INT REFERENCES person(sid));

CREATE TABLE instructor (
  id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  sid INT REFERENCES person(sid) );

CREATE TABLE reading (
  id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  d DATE,
  reference VARCHAR(1000));

CREATE TABLE problem (
  id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  text TEXT,
  solution TEXT,
  ptsOutOf FLOAT,
  aTitle VARCHAR(100) REFERENCES assign(title),
  name VARCHAR(100));

CREATE TABLE assign (
  title VARCHAR(100) PRIMARY KEY NOT NULL,
  d DATE,
  type ENUM('hw', 'exam'));

CREATE TABLE grade (
  id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  pid INT REFERENCES problem(id),
  sid INT REFERENCES student(sid),
  pts FLOAT,
  comments VARCHAR(1000));
  

/*
# inserting notes.
INSERT INTO notes (date, text) VALUES ('2012-09-13', LOAD_FILE('sep13.txt'));

# inserting readings.
INSERT INTO reading (d, reference) VALUES ('2012-09-13', 'Ch. 7');

# inserting a person.
INSERT INTO person (name, email, sid) VALUES ('Jeff Kinne','jkinne@cs.indstate.edu', 991000000);
INSERT INTO person (name, email, sid) VALUES ('Devon Kinne','Devon.Kinne@indstate.edu', 991000001);

# add a person to students list
INSERT INTO student (sid) VALUES (991000001);

# add a person to instructors list
INSERT INTO instructor (sid) VALUES (991000000);

# create a HW assignment.
INSERT INTO assign (title, d, type) VALUES ('HW1', '2012-08-23','hw');
INSERT INTO problem (ptsOutOf, solution, aTitle, name, text) 
 VALUES (1, LOAD_FILE('hw1num1sol.txt'), 'HW1', '#1', 
 'Result should be a table with the name, population, and area of all countries with 
  population at least 100 million and area at least 1 million.');

# and put in more problems...

# creating an exam is similar.

# grading a homework...
SELECT @sid := sid FROM person WHERE name = 'Devon Kinne';
SELECT @pid := id FROM problem WHERE aTitle = 'HW1' AND name = '#1';
INSERT INTO grade (pid, sid, pts, comments) 
VALUES (@pid, @sid, 0.9, 
'Well, this is a comment.
It is a really cool comment. It\'s cool.');

# show grade for particular assignment for student.
SELECT name, pts, comments 
FROM grade INNER JOIN problem 
ON grade.pid = problem.id
WHERE sid = @sid AND aTitle = 'HW1';

# max possible grade on an assignment
SELECT sum(ptsOutOf) FROM problem 
WHERE aTitle = 'HW1';

*/