Answer Happy

department: This table records information about
departments.
dept_name varchar(20) NOT NULL,
building varchar(15) DEFAULT NULL,
budget decimal(12,2) DEFAULT NULL,
PRIMARY KEY (dept_name)
student: This table records information about students.
ID varchar(5) NOT NULL,
name varchar(20) NOT NULL,
dept_name varchar(20),
tot_cred int,
PRIMARY KEY (ID),
FOREIGN KEY (dept_name) references department (dept_name) on delete
RESTRICT
);
instructor: This table records information about instructors.
ID char(5) NOT NULL,
name varchar(20) NOT NULL,
dept_name varchar(20) NOT NULL,
salary decimal(8,2) DEFAULT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (dept_name) references department (dept_name) on delete
RESTRICT
);
course: This table records information about courses.
course_id varchar(8) NOT NULL,
title varchar(50) DEFAULT NULL,
dept_name varchar(20) NOT NULL,
credits int DEFAULT NULL,
PRIMARY KEY (course_id),
FOREIGN KEY (dept_name) references department (dept_name) on delete
RESTRICT
);
section: This table records the course offerings of each term and
year.
course_id varchar(8) NOT NULL,
sec_id varchar(8) NOT NULL,
semester varchar(6) NOT NULL,
year int NOT NULL,
building varchar(15) NOT NULL,
room_number varchar(7) NOT NULL,
time_slot_id varchar(4) NOT NULL,
PRIMARY KEY (course_id, sec_id, semester, year),
FOREIGN KEY (course_id) references course (course_id) on delete
RESTRICT,
FOREIGN KEY (building, room_number) references classroom (building,
room_number) on delete RESTRICT,
FOREIGN KEY (time_slot_id) references time_slot (time_slot_id) on
delete RESTRICT
);
time_slot: This table records information about lecture time
slots.
time_slot_id varchar(4) NOT NULL,
day varchar(2) NOT NULL,
start_hr decimal(2,0) NOT NULL,
start_min decimal(2,0) NOT NULL,
end_hr decimal(2,0) NOT NULL,
end_min decimal(2,0) NOT NULL,
PRIMARY KEY (time_slot_id, day, start_hr, start_min)
);
classroom: This table records information about class rooms.
building varchar(15) NOT NULL,
room_number varchar(7) NOT NULL,
capacity int DEFAULT NULL,
PRIMARY KEY (building, room_number)
);
teaches: This table records information about instructors teaching
sections.
ID varchar(5) NOT NULL,
course_id varchar(8) NOT NULL,
sec_id varchar(8) NOT NULL,
semester varchar(6) NOT NULL,
year int NOT NULL,
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) on delete RESTRICT,
FOREIGN KEY (ID) references instructor (ID) on delete
RESTRICT
);
takes: This table records information about students enrolment into
sections.
ID varchar(5) NOT NULL,
course_id varchar(8) NOT NULL,
sec_id varchar(8) NOT NULL,
semester varchar(6) NOT NULL,
year int NOT NULL,
grade varchar(2) DEFAULT NULL,
PRIMARY KEY (ID, course_id, sec_id, semester, year),
FOREIGN KEY (ID) REFERENCES student (ID) on DELETE RESTRICT,
FOREIGN KEY (course_id, sec_id, semester, year) references section
(course_id, sec_id, semester, year) on delete RESTRICT
);
advisor: This table records advisor relationships of students and
instructors.
s_ID varchar(5) NOT NULL,
i_ID varchar(5) NOT NULL,
PRIMARY KEY (s_ID),
FOREIGN KEY (i_ID) REFERENCES instructor (ID) on DELETE
RESTRICT,
FOREIGN KEY (s_ID) REFERENCES student (ID) on DELETE RESTRICT
);
prereq: This table records the prerequisite information of
courses.
course_id varchar(8) NOT NULL,
prereq_id varchar(8) NOT NULL,
PRIMARY KEY (course_id, prereq_id),
FOREIGN KEY (course_id) REFERENCES course (course_id) on DELETE
RESTRICT,
FOREIGN KEY (prereq_id) REFERENCES course (course_id) on DELETE
RESTRICT
);
Use the nested query structure and the UNION operation to
retrieve either the instructors (show ID, name and dept_name) who
taught at least two courses in 2019 or the students (show ID, name
and dept_name) who have taken two or more courses in 2019.