CREATE TABLE professors (
    professor_id int(11) NOT NULL auto_increment,
    first_name varchar(25) NOT NULL,
    last_name varchar(50) NOT NULL,
    PRIMARY KEY (professor_id)
);

INSERT INTO professors (professor_id,first_name,last_name) 
  VALUES (1,"Albert","Einstein");
INSERT INTO professors (professor_id,first_name,last_name) 
  VALUES (2,"Richard","Feynman");

CREATE TABLE emails (
    professor_id int(11) NOT NULL,
    email varchar(255) NOT NULL
);

INSERT INTO emails (professor_id,email) VALUES (3,"al@einsteinbros.com");
INSERT INTO emails (professor_id,email) VALUES (4,"richard@cal.edu");
INSERT INTO emails (professor_id,email) VALUES (4,"partyman@aol.com");

CREATE TABLE syllabus (
    syllabus_id int(11) NOT NULL auto_increment,
    abstract varchar(255) NOT NULL,
    fulltext text,
    PRIMARY KEY (syllabus_id)
);

INSERT INTO syllabus (syllabus_id,abstract,fulltext) 
  VALUES (1,"Comparing Chaucer and Shakespeare.",
  "You\'ll need the following books: Canterbury Tales, The Taming of the Shrew, and King Lear.");

CREATE TABLE university_classes (
    university_class_id int(11) NOT NULL auto_increment,
    name varchar(255) NOT NULL,
    syllabus_id int(11),
    professor_id int(11) NOT NULL,
    PRIMARY KEY (university_class_id)
);

INSERT INTO university_classes (university_class_id,name,syllabus_id,professor_id) 
  VALUES (1,"Comparative Lit",1,3);
INSERT INTO university_classes (university_class_id,name,syllabus_id,professor_id) 
  VALUES (2,"Saying Whoa!",NULL,3);
INSERT INTO university_classes (university_class_id,name,syllabus_id,professor_id) 
  VALUES (4,"Physics 101",NULL,4);
INSERT INTO university_classes (university_class_id,name,syllabus_id,professor_id)
  VALUES (5,"Beginning Ruby",NULL,4);

CREATE TABLE students (
    student_id int(11) NOT NULL auto_increment,
    firstName varchar(25) NOT NULL,
    lastName varchar(50) NOT NULL,
    PRIMARY KEY (student_id)
);

INSERT INTO students (student_id,firstName,lastName) VALUES (1,"Bob","Smith");
INSERT INTO students (student_id,firstName,lastName) VALUES (2,"Mary","Jones");

CREATE TABLE students_classes (
    student_id int(11) NOT NULL,
    university_class_id int(11) NOT NULL
);

INSERT INTO students_classes (student_id,university_class_id) VALUES (1,1);
INSERT INTO students_classes (student_id,university_class_id) VALUES (1,2);
INSERT INTO students_classes (student_id,university_class_id) VALUES (2,3);
INSERT INTO students_classes (student_id,university_class_id) VALUES (2,4);
INSERT INTO students_classes (student_id,university_class_id) VALUES (1,4);


CREATE TABLE person (
    person_id int(11) NOT NULL auto-increment,
    person_type varchar(15) NOT NULL,
    first_name varchar(25) NOT NULL,
    last_name varchar(50) NOT NULL,
    ssn varchar(11) NULL,
    degree varchar(11) NULL,
    PRIMARY KEY ('person_id')
);

INSERT INTO person (person_id,person_type,first_name,last_name,ssn,degree) 
  VALUES (1,"student","Bob","Smith","111-11-1111",NULL);
INSERT INTO person (person_id,person_type,first_name,last_name,ssn,degree) 
  VALUES (2,"student","Mary","Jones","222-22-2222",NULL);
INSERT INTO person (person_id,person_type,first_name,last_name,ssn,degree) 
  VALUES (3,"professor","Albert","Einstein","","Ph.D.");
INSERT INTO person (person_id,person_type,first_name,last_name,ssn,degree) 
  VALUES (4,"professor","Richard","Feynman","","Ph.D.");
INSERT INTO person (person_id,person_type,first_name,last_name,ssn,degree) 
  VALUES (10,"professor","Blaise","Pascal","","MBA");
