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

-- department (dept_name, building, budget) PK=dept_name
CREATE TABLE department (
    dept_name VARCHAR(20),
    building VARCHAR(15),
    budget NUMERIC(12,2) CHECK (budget > 0),
    PRIMARY KEY (dept_name)
    -- FOREIGN KEY (building) REFERENCES classroom(building)
);

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

-- instructor (ID, name, dept_name, salary) PK=ID
CREATE TABLE instructor (
    ID VARCHAR(5),
    name VARCHAR(20),
    dept_name VARCHAR(20),
    salary NUMERIC(8,2) CHECK (salary > 0),
    PRIMARY KEY (ID),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name)
);

-- time_slot (time_slot_id, day, start_time, end_time) PK=time_slot_id,day,start_time
CREATE TABLE time_slot (
    time_slot_id VARCHAR(4),
    day VARCHAR(1),
    start_time TIME,
    end_time TIME,
    PRIMARY KEY (time_slot_id, day, start_time)
);

-- section (course_id, sec_id, semester, year, building, room_number, time_slot_id)
CREATE TABLE section (
    course_id VARCHAR(8),
    sec_id VARCHAR(8),
    semester VARCHAR(6) CHECK (semester IN ('Fall','Winter','Spring','Summer')),
    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)
    -- FOREIGN KEY (time_slot_id) REFERENCES time_slot(time_slot_id)
);

-- teaches (ID, course_id, sec_id, semester, year)
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 (ID) REFERENCES instructor(ID),
    FOREIGN KEY (course_id, sec_id, semester, year) REFERENCES section(course_id, sec_id, semester, year)
);

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

-- takes (ID, course_id, sec_id, semester, year, grade)
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 (ID) REFERENCES student(ID),
    FOREIGN KEY (course_id, sec_id, semester, year) REFERENCES section(course_id, sec_id, semester, year)
);

-- advisor (s_ID, i_ID) PK=s_ID
CREATE TABLE advisor (
    s_ID VARCHAR(5),
    i_ID VARCHAR(5),
    PRIMARY KEY (s_ID),
    FOREIGN KEY (s_ID) REFERENCES student(ID),
    FOREIGN KEY (i_ID) REFERENCES instructor(ID)
);

-- prereq (course_id, prereq_id) PK=(course_id, prereq_id)
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)
);

-- example data
-- 1. classroom
INSERT INTO classroom (building, room_number, capacity) VALUES
('Watson', '101', 50),
('Watson', '102', 40),
('Packard', '100', 30),
('Packard', '101', 35),
('Taylor', '312', 80);

-- 2. department
INSERT INTO department (dept_name, building, budget) VALUES
('Comp. Sci.', 'Watson', 100000.00),
('Physics', 'Packard', 70000.00),
('Math', 'Taylor', 50000.00),
('Biology', 'Watson', 65000.00),
('Elec. Eng.', 'Packard', 85000.00);

-- 3. course
INSERT INTO course (course_id, title, dept_name, credits) VALUES
('CS-101', 'Intro. to Computer Science', 'Comp. Sci.', 3),
('CS-201', 'Data Structures', 'Comp. Sci.', 4),
('CS-301', 'Database Systems', 'Comp. Sci.', 3),
('PHY-101', 'Physics I', 'Physics', 4),
('MATH-101', 'Calculus', 'Math', 4),
('BIO-101', 'Biology I', 'Biology', 3),
('EE-201', 'Circuits', 'Elec. Eng.', 3);

-- 4. instructor
INSERT INTO instructor (ID, name, dept_name, salary) VALUES
('10101', 'Srinivasan', 'Comp. Sci.', 65000.00),
('12121', 'Wu', 'Physics', 72000.00),
('15151', 'Mozart', 'Math', 40000.00),
('22222', 'Einstein', 'Physics', 95000.00),
('33333', 'Gold', 'Biology', 78000.00),
('45678', 'Katz', 'Comp. Sci.', 75000.00);

-- 5. time_slot
INSERT INTO time_slot (time_slot_id, day, start_time, end_time) VALUES
('A', 'M', '08:00', '09:00'),
('A', 'W', '08:00', '09:00'),
('A', 'F', '08:00', '09:00'),
('B', 'T', '10:00', '11:30'),
('B', 'R', '10:00', '11:30'),
('C', 'M', '14:00', '15:00');

-- 6. section
INSERT INTO section (course_id, sec_id, semester, year, building, room_number, time_slot_id) VALUES
('CS-101', '1', 'Fall', 2024, 'Watson', '101', 'A'),
('CS-101', '2', 'Spring', 2025, 'Watson', '102', 'B'),
('CS-201', '1', 'Fall', 2024, 'Watson', '101', 'C'),
('PHY-101', '1', 'Fall', 2024, 'Packard', '100', 'A'),
('MATH-101', '1', 'Spring', 2024, 'Taylor', '312', 'B'),
('BIO-101', '1', 'Fall', 2024, 'Watson', '102', 'C');

-- 7. teaches
INSERT INTO teaches (ID, course_id, sec_id, semester, year) VALUES
('10101', 'CS-101', '1', 'Fall', 2024),
('10101', 'CS-201', '1', 'Fall', 2024),
('12121', 'PHY-101', '1', 'Fall', 2024),
('15151', 'MATH-101', '1', 'Spring', 2024),
('33333', 'BIO-101', '1', 'Fall', 2024),
('45678', 'CS-101', '2', 'Spring', 2025);

-- 8. student
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES
('00128', 'Zhang', 'Comp. Sci.', 10),
('00231', 'Shankar', 'Comp. Sci.', 12),
('00345', 'Williams', 'Physics', 8),
('00456', 'Brown', 'Math', 6),
('00543', 'Aoi', 'Biology', 9),
('00678', 'Brook', 'Elec. Eng.', 0); -- tot_cred=0，还没选课

-- 9. takes
INSERT INTO takes (ID, course_id, sec_id, semester, year, grade) VALUES
('00128', 'CS-101', '1', 'Fall', 2024, 'A'),
('00128', 'CS-201', '1', 'Fall', 2024, 'B+'),
('00231', 'CS-101', '1', 'Fall', 2024, 'A-'),
('00345', 'PHY-101', '1', 'Fall', 2024, 'B'),
('00456', 'MATH-101', '1', 'Spring', 2024, 'C'),
('00543', 'BIO-101', '1', 'Fall', 2024, 'A');

-- 10. advisor
INSERT INTO advisor (s_ID, i_ID) VALUES
('00128', '10101'),
('00231', '45678'),
('00345', '12121'),
('00456', '15151'),
('00543', '33333');

-- 11. prereq
INSERT INTO prereq (course_id, prereq_id) VALUES
('CS-201', 'CS-101'),
('CS-301', 'CS-201'),
('PHY-101', 'MATH-101');



--  2.1a. Find the titles of courses in the Comp. Sci. department that have 3 credits. 
SELECT '2.1a' AS output_message;
SELECT title from course where credits = 3 and dept_name in('Comp. Sci.');

-- b. Find the IDs of all students who were taught by an instructor named Einstein; 
-- make sure there are no duplicates in the result.