################################################################################
DROP   TABLE IF EXISTS roster;
DROP   TABLE IF EXISTS instructor_info;
DROP   TABLE IF EXISTS student_info;
DROP   TABLE IF EXISTS course;
DROP   TABLE IF EXISTS instructor;
DROP   TABLE IF EXISTS student;

################################################################################
CREATE TABLE course
(
    course_id    CHAR(8)       NOT NULL PRIMARY KEY,
    description  VARCHAR(40)   NOT NULL,
    price        INT           NOT NULL
);

INSERT INTO course VALUES('X52-9272','SQL Programming Language',540);
INSERT INTO course VALUES('X52-9759','XML for Web Development',1095);
INSERT INTO course VALUES('X52-9740','Web Page Development with HTML',1095);
INSERT INTO course VALUES('X52-9238','Introduction to Java',1095);
INSERT INTO course VALUES('X52-9742','Intensive Web Development',3995);
INSERT INTO course VALUES('X52-9755','JavaScript',1095);
INSERT INTO course VALUES('X52-9267','Object Oriented Analysis and Design',995);
INSERT INTO course VALUES('X52-9562','Java Web Services',1095);


################################################################################
CREATE TABLE instructor
(
    inst_id INT                 NOT NULL    PRIMARY KEY AUTO_INCREMENT,
    lname         VARCHAR(20)   NOT NULL,
    fname         VARCHAR(20)   NOT NULL,
    ssn           CHAR(11)      NOT NULL    UNIQUE,
    sex           CHAR(1)       NOT NULL    CHECK (sex='M' OR sex='F')
);

CREATE INDEX inst_name_idx ON instructor (lname, fname);

INSERT INTO instructor VALUES(0,'Sultan','Sam','000-02-0001','M');
INSERT INTO instructor VALUES(0,'Pefanis','George','000-02-0002','M');
INSERT INTO instructor VALUES(0,'martin','susan','000-02-0003','F');
INSERT INTO instructor VALUES(0,'Paller','Marc','000-02-0004','M');
INSERT INTO instructor VALUES(0,'O''Brien','Mary','000-02-0005','F');
INSERT INTO instructor VALUES(0,'Katz','Eric','000-02-0006','M');


################################################################################
CREATE TABLE student
(
    stu_id INT               NOT NULL AUTO_INCREMENT,
    lname      VARCHAR(20)   NOT NULL,
    fname      VARCHAR(20)   NOT NULL,
    ssn        CHAR(11)      NOT NULL,
    sex        ENUM('F','M') NOT NULL,

    CONSTRAINT student_pk  PRIMARY KEY (stu_id), 
    CONSTRAINT stu_ssn_uq  UNIQUE      (ssn), 
    CONSTRAINT stu_sex_chk CHECK       (sex IN ('M','F'))
);

CREATE INDEX stu_name_idx ON student (lname, fname);

INSERT INTO student VALUES(0,'Burns','Barbara','000-01-0001','F');
INSERT INTO student VALUES(0,'Cambria','Vincent','000-01-0002','M');
INSERT INTO student VALUES(0,'Davidson','Duncan','000-01-0003','M');
INSERT INTO student VALUES(0,'Smith','David','000-01-0004','M');
INSERT INTO student VALUES(0,'Thomas','Eugene','000-01-0005','M');
INSERT INTO student VALUES(0,'Owens','Cynthia','000-01-0006','F');
INSERT INTO student VALUES(0,'Willis','Eileen','000-01-0007','F');
INSERT INTO student VALUES(0,'Myers','Rick','000-01-0008','M');
INSERT INTO student VALUES(0,'Ryan','Natasha','000-01-0009','F');
INSERT INTO student VALUES(0,'Stack','Patrick','000-01-0010','M');
INSERT INTO student VALUES(0,'Tobias','Wayne','000-01-0011','M');
INSERT INTO student VALUES(0,'Race','Joseph','000-01-0012','M');
INSERT INTO student VALUES(0,'Nelson','Colette','000-01-0013','F');
INSERT INTO student VALUES(0,'Brinson','Angel','000-01-0014','F');
INSERT INTO student VALUES(0,'Soley','John','000-01-0015','M');
INSERT INTO student VALUES(0,'Grace','Robert','000-01-0016','M');
INSERT INTO student VALUES(0,'Tok','Kathy','000-01-0017','F');
INSERT INTO student VALUES(0,'miller','janet','000-01-0018','F');
INSERT INTO student VALUES(0,'Austin','Maria','000-01-0019','F');
INSERT INTO student VALUES(0,'Teagan','Edward','000-01-0020','M');
INSERT INTO student VALUES(0,'Milgrom','Anya','000-01-0021','F');
INSERT INTO student VALUES(0,'Vasquez','Lillian','000-01-0022','F');
INSERT INTO student VALUES(0,'Chan','David','000-01-0023','M');
INSERT INTO student VALUES(0,'James','Phyllis','000-01-0024','F');
INSERT INTO student VALUES(0,'Sultan','Sam','000-02-0001','M');


################################################################################
CREATE TABLE instructor_info 
(
    row_pk  	  INT            NOT NULL   PRIMARY KEY AUTO_INCREMENT,
    inst_id       INT            NOT NULL,
    lname         VARCHAR(20),
    fname         VARCHAR(20),
    ssn           CHAR(11),
    sex           ENUM('F','M')  CHECK (sex IN ('M','F')),
    active_status CHAR(1),
    start_date    DATE,
    specialty     VARCHAR(99),
    addr_type     CHAR(1),
    street1       VARCHAR(50),
    street2       VARCHAR(50),
    city          VARCHAR(25),  
    state         CHAR(2),
    country       VARCHAR(20),

    CONSTRAINT inst_fk  FOREIGN KEY (inst_id)  REFERENCES instructor (inst_id)
);

INSERT INTO instructor_info VALUES(0,1,'Sultan','Sam','000-02-0001','M','A','1999-09-01','Web technology, Java, XML, SQL','H','123 Main Street',null,'Bronx','NY',null);
INSERT INTO instructor_info VALUES(0,1,'Sultan','Sam','000-02-0001','M','A','1999-09-01','Web technology, Java, XML, SQL','W','7 East 12 Street',null,'New York','NY',null);
INSERT INTO instructor_info VALUES(0,2,'Pefanis','George','000-02-0002','M','A','2000-02-15','ASP, .NET, HTML',null,null,null,null,null,null);
INSERT INTO instructor_info VALUES(0,3,'martin','susan','000-02-0003','F','A','1998-07-01',null,'H','5 West 4th Street','Apt 44','New York','NY',null);
INSERT INTO instructor_info VALUES(0,4,'Paller','Marc','000-02-0004','M','A','1995-01-01','Oracle, DB2',null,null,null,null,null,null);
INSERT INTO instructor_info VALUES(0,5,"O'Brien",'Mary','000-02-0005','F','I','2003-02-25',null,'H','121 Eleventh Avenue',null,'New York','NY',null);
INSERT INTO instructor_info VALUES(0,5,"O'Brien",'Mary','000-02-0005','F','I','2003-03-25',null,'W','48 East 42 Street',null,'New York','NY',null);
INSERT INTO instructor_info VALUES(0,6,'Katz','Eric','000-02-0006','M','A','2001-05-15','JavaScript, C, Flash',null,null,null,null,null,null);


################################################################################
CREATE TABLE student_info 
(
    row_pk  	INT            NOT NULL   PRIMARY KEY AUTO_INCREMENT,
    stu_id      INT            NOT NULL,
    lname       VARCHAR(20),
    fname       VARCHAR(20),
    ssn         CHAR(11),
    sex         ENUM('F','M')  CHECK (sex IN ('M','F')),
    email       VARCHAR(99),
    addr_type   CHAR(1),
    street1     VARCHAR(50),
    street2     VARCHAR(50),
    city        VARCHAR(25),  
    state       CHAR(2),
    country     VARCHAR(20),

    CONSTRAINT stu_fk   FOREIGN KEY (stu_id)  REFERENCES student (stu_id)
);

INSERT INTO student_info VALUES(0,1,'Burns','Barbara','000-01-0001','F','barbara.burns@nyu.edu','H','121 First Avenue',null,'New York','NY',null);
INSERT INTO student_info VALUES(0,2,'Cambria','Vincent','000-01-0002','M','vcambria@yahoo.com',null,null,null,null,null,null);
INSERT INTO student_info VALUES(0,3,'Davidson','Duncan','000-01-0003','M','duncan.davidson@nyu.edu','H','222 Second Avenue',null,'Brooklyn','NY',null);
INSERT INTO student_info VALUES(0,3,'Davidson','Duncan','000-01-0003','M','duncan.davidson@nyu.edu','W','126 Madison Avenue',null,'New York','NY',null);
INSERT INTO student_info VALUES(0,3,'Davidson','Duncan','000-01-0003','M','davidson123@aol.com','H','222 Second Avenue',null,'Brooklyn','NY',null);
INSERT INTO student_info VALUES(0,3,'Davidson','Duncan','000-01-0003','M','davidson123@aol.com','W','126 Madison Avenue',null,'New York','NY',null);
INSERT INTO student_info VALUES(0,4,'Smith','David','000-01-0004','M',null,null,null,null,null,null,null);
INSERT INTO student_info VALUES(0,5,'Thomas','Eugene','000-01-0005','M','ethomas@xyz.com','W','323 Third Avenue',null,'Bronx','NY',null);
INSERT INTO student_info VALUES(0,6,'Owens','Cynthia','000-01-0006','F',null,null,null,null,null,null,null);
INSERT INTO student_info VALUES(0,7,'Willis','Eileen','000-01-0007','F',null,'H','424 Forth Avenue',null,'New York','NY',null);
INSERT INTO student_info VALUES(0,8,'Myers','Rick','000-01-0008','M','rick.myers@nyu.edu',null,null,null,null,null,null);
INSERT INTO student_info VALUES(0,8,'Myers','Rick','000-01-0008','M','rick.myers@abc.com',null,null,null,null,null,null);
INSERT INTO student_info VALUES(0,8,'Myers','Rick','000-01-0008','M','myers101@aol.com',null,null,null,null,null,null);
INSERT INTO student_info VALUES(0,9,'Ryan','Natasha','000-01-0009','F',null,'W','525 Fifth Avenue',null,'Yonkers','NY',null);
INSERT INTO student_info VALUES(0,10,'Stack','Patrick','000-01-0010','M',null,null,null,null,null,null,null);
INSERT INTO student_info VALUES(0,11,'Tobias','Wayne','000-01-0011','M',null,'W','626 Sixth Avenue',null,'Hoboken','NJ',null);
INSERT INTO student_info VALUES(0,12,'Race','Joseph','000-01-0012','M','joerace321@xyz.tv',null,null,null,null,null,null);
INSERT INTO student_info VALUES(0,13,'Nelson','Colette','000-01-0013','F',null,'H','727 Seventh Avenue',null,'New York','NY',null);
INSERT INTO student_info VALUES(0,14,'Brinson','Angel','000-01-0014','F',null,null,null,null,null,null,null);
INSERT INTO student_info VALUES(0,15,'Soley','John','000-01-0015','M','soleyj@nyu.edu','H','828 Eight Avenue',null,'New York','NY',null);
INSERT INTO student_info VALUES(0,16,'Grace','Robert','000-01-0016','M',null,null,null,null,null,null,null);
INSERT INTO student_info VALUES(0,17,'Tok','Kathy','000-01-0017','F',null,'W','929 Ninth Avenue',null,'Brooklyn','NY',null);
INSERT INTO student_info VALUES(0,18,'miller','janet','000-01-0018','F',null,null,null,null,null,null,null);
INSERT INTO student_info VALUES(0,19,'Austin','Maria','000-01-0019','F',null,'H','101 Tenth Avenue',null,'Newark','NJ',null);
INSERT INTO student_info VALUES(0,20,'Teagan','Edward','000-01-0020','M',null,null,null,null,null,null,null);
INSERT INTO student_info VALUES(0,21,'Milgrom','Anya','000-01-0021','F',null,'H','1 Ontario Drive',null,'Toronto',null,'Canada');
INSERT INTO student_info VALUES(0,21,'Milgrom','Anya','000-01-0021','F',null,'W','1155 Browdway',null,'New York','NY',null);
INSERT INTO student_info VALUES(0,22,'Vasquez','Lillian','000-01-0022','F',null,null,null,null,null,null,null);
INSERT INTO student_info VALUES(0,23,'Chan','David','000-01-0023','M',null,null,null,null,null,null,null);
INSERT INTO student_info VALUES(0,24,'James','Phyllis','000-01-0024','F',null,null,null,null,null,null,null);
INSERT INTO student_info VALUES(0,25,'Sultan','Sam','000-02-0001','M',null,null,null,null,null,null,null);


################################################################################
CREATE TABLE roster 
(
    roster_pk  	INT            NOT NULL   PRIMARY KEY AUTO_INCREMENT,
    stu_id      INT,
    stu_lname   VARCHAR(20),
    stu_fname   VARCHAR(20),
    stu_ssn     CHAR(11),
    stu_sex     ENUM('F','M')  CHECK (stu_sex IN ('M','F')),
    session_id  INT,
    course_id   CHAR(8),
    description VARCHAR(40),
    price       INT,
    inst_id     INT,
    inst_lname  VARCHAR(20),
    inst_fname  VARCHAR(20),
    inst_ssn    CHAR(11),
    inst_sex    CHAR(1)        CHECK (stu_sex IN ('M','F')),

    CONSTRAINT stu_id_fk   FOREIGN KEY (stu_id)    REFERENCES student (stu_id),
    CONSTRAINT inst_id_fk  FOREIGN KEY (inst_id)   REFERENCES instructor (inst_id),
    CONSTRAINT course_fk   FOREIGN KEY (course_id) REFERENCES course (course_id)
);

INSERT INTO roster VALUES(0,1,'Burns','Barbara','000-01-0001','F',1,'X52-9759','XML for Web Development',1095,1,'Sultan','Sam','000-02-0001','M');
INSERT INTO roster VALUES(0,1,'Burns','Barbara','000-01-0001','F',1,'X52-9740','Web Page Development with HTML',1095,2,'Pefanis','George','000-02-0002','M');
INSERT INTO roster VALUES(0,2,'Cambria','Vincent','000-01-0002','M',1,'X52-9272','SQL Programming Language',540,1,'Sultan','Sam','000-02-0001','M');
INSERT INTO roster VALUES(0,2,'Cambria','Vincent','000-01-0002','M',1,'X52-9740','Web Page Development with HTML',1095,2,'Pefanis','George','000-02-0002','M');
INSERT INTO roster VALUES(0,3,'Davidson','Duncan','000-01-0003','M',1,'X52-9759','XML for Web Development',1095,1,'Sultan','Sam','000-02-0001','M');
INSERT INTO roster VALUES(0,3,'Davidson','Duncan','000-01-0003','M',1,'X52-9740','Web Page Development with HTML',1095,2,'Pefanis','George','000-02-0002','M');
INSERT INTO roster VALUES(0,3,'Davidson','Duncan','000-01-0003','M',1,'X52-9755','JavaScript',1095,6,'Katz','Eric','000-02-0006','M');
INSERT INTO roster VALUES(0,4,'Smith','David','000-01-0004','M',1,'X52-9272','SQL Programming Language',540,1,'Sultan','Sam','000-02-0001','M');
INSERT INTO roster VALUES(0,4,'Smith','David','000-01-0004','M',1,'X52-9740','Web Page Development with HTML',1095,2,'Pefanis','George','000-02-0002','M');
INSERT INTO roster VALUES(0,5,'Thomas','Eugene','000-01-0005','M',1,'X52-9759','XML for Web Development',1095,1,'Sultan','Sam','000-02-0001','M');
INSERT INTO roster VALUES(0,5,'Thomas','Eugene','000-01-0005','M',1,'X52-9740','Web Page Development with HTML',1095,2,'Pefanis','George','000-02-0002','M');
INSERT INTO roster VALUES(0,6,'Owens','Cynthia','000-01-0006','F',1,'X52-9272','SQL Programming Language',540,1,'Sultan','Sam','000-02-0001','M');
INSERT INTO roster VALUES(0,6,'Owens','Cynthia','000-01-0006','F',1,'X52-9740','Web Page Development with HTML',1095,2,'Pefanis','George','000-02-0002','M');
INSERT INTO roster VALUES(0,6,'Owens','Cynthia','000-01-0006','F',1,'X52-9755','JavaScript',1095,6,'Katz','Eric','000-02-0006','M');
INSERT INTO roster VALUES(0,7,'Willis','Eileen','000-01-0007','F',1,'X52-9759','XML for Web Development',1095,1,'Sultan','Sam','000-02-0001','M');
INSERT INTO roster VALUES(0,7,'Willis','Eileen','000-01-0007','F',1,'X52-9740','Web Page Development with HTML',1095,2,'Pefanis','George','000-02-0002','M');
INSERT INTO roster VALUES(0,8,'Myers','Rick','000-01-0008','M',1,'X52-9272','SQL Programming Language',540,1,'Sultan','Sam','000-02-0001','M');
INSERT INTO roster VALUES(0,8,'Myers','Rick','000-01-0008','M',1,'X52-9759','XML for Web Development',1095,1,'Sultan','Sam','000-02-0001','M');
INSERT INTO roster VALUES(0,8,'Myers','Rick','000-01-0008','M',1,'X52-9740','Web Page Development with HTML',1095,2,'Pefanis','George','000-02-0002','M');
INSERT INTO roster VALUES(0,9,'Ryan','Natasha','000-01-0009','F',1,'X52-9740','Web Page Development with HTML',1095,2,'Pefanis','George','000-02-0002','M');
INSERT INTO roster VALUES(0,9,'Ryan','Natasha','000-01-0009','F',1,'X52-9755','JavaScript',1095,6,'Katz','Eric','000-02-0006','M');
INSERT INTO roster VALUES(0,10,'Stack','Patrick','000-01-0010','M',1,'X52-9272','SQL Programming Language',540,1,'Sultan','Sam','000-02-0001','M');
INSERT INTO roster VALUES(0,10,'Stack','Patrick','000-01-0010','M',1,'X52-9759','XML for Web Development',1095,1,'Sultan','Sam','000-02-0001','M');
INSERT INTO roster VALUES(0,10,'Stack','Patrick','000-01-0010','M',1,'X52-9740','Web Page Development with HTML',1095,2,'Pefanis','George','000-02-0002','M');
INSERT INTO roster VALUES(0,11,'Tobias','Wayne','000-01-0011','M',2,'X52-9740','Web Page Development with HTML',1095,3,'martin','susan','000-02-0003','F');
INSERT INTO roster VALUES(0,12,'Race','Joseph','000-01-0012','M',1,'X52-9272','SQL Programming Language',540,1,'Sultan','Sam','000-02-0001','M');
INSERT INTO roster VALUES(0,12,'Race','Joseph','000-01-0012','M',2,'X52-9740','Web Page Development with HTML',1095,3,'martin','susan','000-02-0003','F');
INSERT INTO roster VALUES(0,12,'Race','Joseph','000-01-0012','M',1,'X52-9755','JavaScript',1095,6,'Katz','Eric','000-02-0006','M');
INSERT INTO roster VALUES(0,13,'Nelson','Colette','000-01-0013','F',1,'X52-9759','XML for Web Development',1095,1,'Sultan','Sam','000-02-0001','M');
INSERT INTO roster VALUES(0,13,'Nelson','Colette','000-01-0013','F',2,'X52-9740','Web Page Development with HTML',1095,3,'martin','susan','000-02-0003','F');
INSERT INTO roster VALUES(0,14,'Brinson','Angel','000-01-0014','F',1,'X52-9272','SQL Programming Language',540,1,'Sultan','Sam','000-02-0001','M');
INSERT INTO roster VALUES(0,14,'Brinson','Angel','000-01-0014','F',2,'X52-9740','Web Page Development with HTML',1095,3,'martin','susan','000-02-0003','F');
INSERT INTO roster VALUES(0,15,'Soley','John','000-01-0015','M',1,'X52-9759','XML for Web Development',1095,1,'Sultan','Sam','000-02-0001','M');
INSERT INTO roster VALUES(0,15,'Soley','John','000-01-0015','M',2,'X52-9740','Web Page Development with HTML',1095,3,'martin','susan','000-02-0003','F');
INSERT INTO roster VALUES(0,15,'Soley','John','000-01-0015','M',1,'X52-9755','JavaScript',1095,6,'Katz','Eric','000-02-0006','M');
INSERT INTO roster VALUES(0,16,'Grace','Robert','000-01-0016','M',1,'X52-9272','SQL Programming Language',540,1,'Sultan','Sam','000-02-0001','M');
INSERT INTO roster VALUES(0,16,'Grace','Robert','000-01-0016','M',2,'X52-9740','Web Page Development with HTML',1095,3,'martin','susan','000-02-0003','F');
INSERT INTO roster VALUES(0,17,'Tok','Kathy','000-01-0017','F',1,'X52-9759','XML for Web Development',1095,1,'Sultan','Sam','000-02-0001','M');
INSERT INTO roster VALUES(0,17,'Tok','Kathy','000-01-0017','F',2,'X52-9740','Web Page Development with HTML',1095,3,'martin','susan','000-02-0003','F');
INSERT INTO roster VALUES(0,18,'miller','janet','000-01-0018','F',1,'X52-9272','SQL Programming Language',540,1,'Sultan','Sam','000-02-0001','M');
INSERT INTO roster VALUES(0,18,'miller','janet','000-01-0018','F',2,'X52-9740','Web Page Development with HTML',1095,3,'martin','susan','000-02-0003','F');
INSERT INTO roster VALUES(0,18,'miller','janet','000-01-0018','F',1,'X52-9755','JavaScript',1095,6,'Katz','Eric','000-02-0006','M');
INSERT INTO roster VALUES(0,19,'Austin','Maria','000-01-0019','F',1,'X52-9759','XML for Web Development',1095,1,'Sultan','Sam','000-02-0001','M');
INSERT INTO roster VALUES(0,19,'Austin','Maria','000-01-0019','F',2,'X52-9740','Web Page Development with HTML',1095,3,'martin','susan','000-02-0003','F');
INSERT INTO roster VALUES(0,20,'Teagan','Edward','000-01-0020','M',1,'X52-9272','SQL Programming Language',540,1,'Sultan','Sam','000-02-0001','M');
INSERT INTO roster VALUES(0,20,'Teagan','Edward','000-01-0020','M',2,'X52-9740','Web Page Development with HTML',1095,3,'martin','susan','000-02-0003','F');
INSERT INTO roster VALUES(0,21,'Milgrom','Anya','000-01-0021','F',2,'X52-9740','Web Page Development with HTML',1095,3,'martin','susan','000-02-0003','F');
INSERT INTO roster VALUES(0,21,'Milgrom','Anya','000-01-0021','F',1,'X52-9755','JavaScript',1095,6,'Katz','Eric','000-02-0006','M');
INSERT INTO roster VALUES(0,22,'Vasquez','Lillian','000-01-0022','F',1,'X52-9272','SQL Programming Language',540,1,'Sultan','Sam','000-02-0001','M');
INSERT INTO roster VALUES(0,22,'Vasquez','Lillian','000-01-0022','F',2,'X52-9740','Web Page Development with HTML',1095,3,'martin','susan','000-02-0003','F');
INSERT INTO roster VALUES(0,23,'Chan','David','000-01-0023','M',null,null,null,null,null,null,null,null,null);
INSERT INTO roster VALUES(0,24,'James','Phyllis','000-01-0024','F',null,null,null,null,null,null,null,null,null);
INSERT INTO roster VALUES(0,25,'Sultan','Sam','000-02-0001','M',null,null,null,null,null,null,null,null,null);
INSERT INTO roster VALUES(0,null,null,null,null,null,null,null,null,null,4,'Paller','Marc','000-02-0004','M');
INSERT INTO roster VALUES(0,null,null,null,null,null,null,null,null,null,5,"O'Brien",'Mary','000-02-0005','F');
INSERT INTO roster VALUES(0,null,null,null,null,null,null,'X52-9238','Introduction to Java',1095,null,null,null,null,null);
INSERT INTO roster VALUES(0,null,null,null,null,null,null,'X52-9267','Object Oriented Analysis and Design',995,null,null,null,null,null);
INSERT INTO roster VALUES(0,null,null,null,null,null,null,'X52-9562','Java Web Services',1095,null,null,null,null,null);
INSERT INTO roster VALUES(0,null,null,null,null,null,null,'X52-9742','Intensive Web Development',3995,null,null,null,null,null);