
CREATE TABLE advisor (
	"s_ID" VARCHAR(5), 
	"i_ID" VARCHAR(5), 
	PRIMARY KEY ("s_ID"), 
	FOREIGN KEY("i_ID") REFERENCES instructor ("ID"), 
	FOREIGN KEY("s_ID") REFERENCES student ("ID")
)


CREATE TABLE classroom (
	building VARCHAR(15), 
	room_number VARCHAR(7), 
	capacity NUMERIC(4, 0), 
	PRIMARY KEY (building, room_number)
)


CREATE TABLE course (
	course_id VARCHAR(8), 
	title VARCHAR(50), 
	dept_name VARCHAR(20), 
	credits NUMERIC(2, 0), 
	PRIMARY KEY (course_id), 
	FOREIGN KEY(dept_name) REFERENCES department (dept_name), 
	CHECK (credits > 0)
)


CREATE TABLE department (
	dept_name VARCHAR(20), 
	building VARCHAR(15), 
	budget NUMERIC(12, 2), 
	PRIMARY KEY (dept_name), 
	CHECK (budget > 0)
)


CREATE TABLE instructor (
	"ID" VARCHAR(5), 
	name VARCHAR(20) NOT NULL, 
	dept_name VARCHAR(20), 
	salary NUMERIC(8, 2), 
	PRIMARY KEY ("ID"), 
	FOREIGN KEY(dept_name) REFERENCES department (dept_name), 
	CHECK (salary > 29000)
)


CREATE TABLE prereq (
	course_id VARCHAR(8), 
	prereq_id VARCHAR(8), 
	PRIMARY KEY (course_id, prereq_id), 
	FOREIGN KEY(course_id) REFERENCES course (course_id), 
	FOREIGN KEY(prereq_id) REFERENCES course (course_id)
)


CREATE TABLE section (
	course_id VARCHAR(8), 
	sec_id VARCHAR(8), 
	semester VARCHAR(6), 
	year NUMERIC(4, 0), 
	building VARCHAR(15), 
	room_number VARCHAR(7), 
	time_slot_id VARCHAR(4), 
	PRIMARY KEY (course_id, sec_id, semester, year), 
	FOREIGN KEY(course_id) REFERENCES course (course_id), 
	FOREIGN KEY(building, room_number) REFERENCES classroom (building, room_number), 
	CHECK (semester in ('Fall', 'Winter', 'Spring', 'Summer')), 
	CHECK (year > 1701 and year < 2100)
)


CREATE TABLE student (
	"ID" VARCHAR(5), 
	name VARCHAR(20) NOT NULL, 
	dept_name VARCHAR(20), 
	tot_cred NUMERIC(3, 0), 
	PRIMARY KEY ("ID"), 
	FOREIGN KEY(dept_name) REFERENCES department (dept_name), 
	CHECK (tot_cred >= 0)
)


CREATE TABLE takes (
	"ID" VARCHAR(5), 
	course_id VARCHAR(8), 
	sec_id VARCHAR(8), 
	semester VARCHAR(6), 
	year NUMERIC(4, 0), 
	grade VARCHAR(2), 
	PRIMARY KEY ("ID", course_id, sec_id, semester, year), 
	FOREIGN KEY(course_id, sec_id, semester, year) REFERENCES section (course_id, sec_id, semester, year), 
	FOREIGN KEY("ID") REFERENCES student ("ID")
)


CREATE TABLE teaches (
	"ID" VARCHAR(5), 
	course_id VARCHAR(8), 
	sec_id VARCHAR(8), 
	semester VARCHAR(6), 
	year NUMERIC(4, 0), 
	PRIMARY KEY ("ID", course_id, sec_id, semester, year), 
	FOREIGN KEY(course_id, sec_id, semester, year) REFERENCES section (course_id, sec_id, semester, year), 
	FOREIGN KEY("ID") REFERENCES instructor ("ID")
)


CREATE TABLE time_slot (
	time_slot_id VARCHAR(4), 
	day VARCHAR(1), 
	start_hr NUMERIC(2), 
	start_min NUMERIC(2), 
	end_hr NUMERIC(2), 
	end_min NUMERIC(2), 
	PRIMARY KEY (time_slot_id, day, start_hr, start_min), 
	CHECK (start_hr >= 0 and start_hr < 24), 
	CHECK (start_min >= 0 and start_min < 60), 
	CHECK (end_hr >= 0 and end_hr < 24), 
	CHECK (end_min >= 0 and end_min < 60)
)

CREATE TABLE advisor (
	"s_ID" VARCHAR(5), 
	"i_ID" VARCHAR(5), 
	PRIMARY KEY ("s_ID"), 
	FOREIGN KEY("i_ID") REFERENCES instructor ("ID"), 
	FOREIGN KEY("s_ID") REFERENCES student ("ID")
)

/*
2 rows from advisor table:
s_ID	i_ID
24746	19368
79352	95030
*/


CREATE TABLE classroom (
	building VARCHAR(15), 
	room_number VARCHAR(7), 
	capacity NUMERIC(4, 0), 
	PRIMARY KEY (building, room_number)
)

/*
2 rows from classroom table:
building	room_number	capacity
Lamberton	134	10
Chandler	375	10
*/


CREATE TABLE course (
	course_id VARCHAR(8), 
	title VARCHAR(50), 
	dept_name VARCHAR(20), 
	credits NUMERIC(2, 0), 
	PRIMARY KEY (course_id), 
	FOREIGN KEY(dept_name) REFERENCES department (dept_name), 
	CHECK (credits > 0)
)

/*
2 rows from course table:
course_id	title	dept_name	credits
787	C  Programming	Mech. Eng.	4
238	The Music of Donovan	Mech. Eng.	3
*/


CREATE TABLE department (
	dept_name VARCHAR(20), 
	building VARCHAR(15), 
	budget NUMERIC(12, 2), 
	PRIMARY KEY (dept_name), 
	CHECK (budget > 0)
)

/*
2 rows from department table:
dept_name	building	budget
Civil Eng.	Chandler	255041.46
Biology	Candlestick	647610.55
*/


CREATE TABLE instructor (
	"ID" VARCHAR(5), 
	name VARCHAR(20) NOT NULL, 
	dept_name VARCHAR(20), 
	salary NUMERIC(8, 2), 
	PRIMARY KEY ("ID"), 
	FOREIGN KEY(dept_name) REFERENCES department (dept_name), 
	CHECK (salary > 29000)
)

/*
2 rows from instructor table:
ID	name	dept_name	salary
63395	McKinnon	Cybernetics	94333.99
78699	Pingr	Statistics	59303.62
*/


CREATE TABLE prereq (
	course_id VARCHAR(8), 
	prereq_id VARCHAR(8), 
	PRIMARY KEY (course_id, prereq_id), 
	FOREIGN KEY(course_id) REFERENCES course (course_id), 
	FOREIGN KEY(prereq_id) REFERENCES course (course_id)
)

/*
2 rows from prereq table:
course_id	prereq_id
376	130
760	169
*/


CREATE TABLE section (
	course_id VARCHAR(8), 
	sec_id VARCHAR(8), 
	semester VARCHAR(6), 
	year NUMERIC(4, 0), 
	building VARCHAR(15), 
	room_number VARCHAR(7), 
	time_slot_id VARCHAR(4), 
	PRIMARY KEY (course_id, sec_id, semester, year), 
	FOREIGN KEY(course_id) REFERENCES course (course_id), 
	FOREIGN KEY(building, room_number) REFERENCES classroom (building, room_number), 
	CHECK (semester in ('Fall', 'Winter', 'Spring', 'Summer')), 
	CHECK (year > 1701 and year < 2100)
)

/*
2 rows from section table:
course_id	sec_id	semester	year	building	room_number	time_slot_id
313	1	Fall	2010	Chandler	804	N
747	1	Spring	2004	Gates	314	K
*/


CREATE TABLE student (
	"ID" VARCHAR(5), 
	name VARCHAR(20) NOT NULL, 
	dept_name VARCHAR(20), 
	tot_cred NUMERIC(3, 0), 
	PRIMARY KEY ("ID"), 
	FOREIGN KEY(dept_name) REFERENCES department (dept_name), 
	CHECK (tot_cred >= 0)
)

/*
2 rows from student table:
ID	name	dept_name	tot_cred
24746	Schrefl	History	4
79352	Rumat	Finance	100
*/


CREATE TABLE takes (
	"ID" VARCHAR(5), 
	course_id VARCHAR(8), 
	sec_id VARCHAR(8), 
	semester VARCHAR(6), 
	year NUMERIC(4, 0), 
	grade VARCHAR(2), 
	PRIMARY KEY ("ID", course_id, sec_id, semester, year), 
	FOREIGN KEY(course_id, sec_id, semester, year) REFERENCES section (course_id, sec_id, semester, year), 
	FOREIGN KEY("ID") REFERENCES student ("ID")
)

/*
2 rows from takes table:
ID	course_id	sec_id	semester	year	grade
65901	401	1	Fall	2003	C-
24932	802	1	Spring	2003	B-
*/


CREATE TABLE teaches (
	"ID" VARCHAR(5), 
	course_id VARCHAR(8), 
	sec_id VARCHAR(8), 
	semester VARCHAR(6), 
	year NUMERIC(4, 0), 
	PRIMARY KEY ("ID", course_id, sec_id, semester, year), 
	FOREIGN KEY(course_id, sec_id, semester, year) REFERENCES section (course_id, sec_id, semester, year), 
	FOREIGN KEY("ID") REFERENCES instructor ("ID")
)

/*
2 rows from teaches table:
ID	course_id	sec_id	semester	year
34175	747	1	Spring	2004
3199	169	1	Spring	2007
*/


CREATE TABLE time_slot (
	time_slot_id VARCHAR(4), 
	day VARCHAR(1), 
	start_hr NUMERIC(2), 
	start_min NUMERIC(2), 
	end_hr NUMERIC(2), 
	end_min NUMERIC(2), 
	PRIMARY KEY (time_slot_id, day, start_hr, start_min), 
	CHECK (start_hr >= 0 and start_hr < 24), 
	CHECK (start_min >= 0 and start_min < 60), 
	CHECK (end_hr >= 0 and end_hr < 24), 
	CHECK (end_min >= 0 and end_min < 60)
)

/*
2 rows from time_slot table:
time_slot_id	day	start_hr	start_min	end_hr	end_min
A	M	8.0000000000	0E-10	8.0000000000	50.0000000000
A	W	8.0000000000	0E-10	8.0000000000	50.0000000000
*/
Find the id of instructors who didn't teach any courses?
SELECT id FROM instructor EXCEPT SELECT id FROM teaches
[{'ID': '16807'}, {'ID': '31955'}, {'ID': '35579'}, {'ID': '37687'}, {'ID': '4034'}, {'ID': '50885'}, {'ID': '52647'}, {'ID': '57180'}, {'ID': '58558'}, {'ID': '59795'}, {'ID': '63395'}, {'ID': '64871'}, {'ID': '72553'}, {'ID': '74426'}, {'ID': '78699'}, {'ID': '79653'}, {'ID': '95030'}, {'ID': '96895'}, {'ID': '97302'}]