################################################################################ 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);