DBMS Assignment 2
-- Use InnoDB for FK support
-- CREATE DATABASE university;
-- USE university;
CREATE TABLE department (
dept_name VARCHAR(50) PRIMARY KEY,
building VARCHAR(50),
budget DECIMAL(12,2) CHECK (budget >= 0)
) ENGINE=InnoDB;
CREATE TABLE instructor (
ID VARCHAR(10) PRIMARY KEY,
name VARCHAR(50) NOT NULL,
dept_name VARCHAR(50),
salary DECIMAL(10,2) CHECK (salary >= 0),
CONSTRAINT fk_instructor_dept
FOREIGN KEY (dept_name) REFERENCES department(dept_name)
ON UPDATE CASCADE
ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE student (
ID VARCHAR(10) PRIMARY KEY,
name VARCHAR(50) NOT NULL,
dept_name VARCHAR(50),
tot_cred INT DEFAULT 0 CHECK (tot_cred >= 0),
CONSTRAINT fk_student_dept
FOREIGN KEY (dept_name) REFERENCES department(dept_name)
ON UPDATE CASCADE
ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE course (
course_id VARCHAR(10) PRIMARY KEY,
title VARCHAR(100) NOT NULL,
dept_name VARCHAR(50),
credits INT NOT NULL CHECK (credits > 0),
CONSTRAINT fk_course_dept
FOREIGN KEY (dept_name) REFERENCES department(dept_name)
ON UPDATE CASCADE
ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE classroom (
building VARCHAR(50),
room_number VARCHAR(10),
capacity INT CHECK (capacity > 0),
PRIMARY KEY (building, room_number)
) ENGINE=InnoDB;
CREATE TABLE time_slot (
time_slot_id VARCHAR(10),
day VARCHAR(10), -- e.g., 'Mon', 'Tue'
start_time TIME,
end_time TIME,
PRIMARY KEY (time_slot_id, day, start_time)
) ENGINE=InnoDB;
CREATE TABLE section (
course_id VARCHAR(10),
sec_id VARCHAR(10),
semester VARCHAR(10), -- e.g., 'Spring', 'Fall'
year INT,
building VARCHAR(50),
room_number VARCHAR(10),
time_slot_id VARCHAR(10),
PRIMARY KEY (course_id, sec_id, semester, year),
CONSTRAINT fk_section_course
FOREIGN KEY (course_id) REFERENCES course(course_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT fk_section_classroom
FOREIGN KEY (building, room_number) REFERENCES classroom(building, room_number)
ON UPDATE CASCADE
ON DELETE SET NULL
) ENGINE=InnoDB;
-- teaches: instructor teaches a section
CREATE TABLE teaches (
ID VARCHAR(10),
course_id VARCHAR(10),
sec_id VARCHAR(10),
semester VARCHAR(10),
year INT,
PRIMARY KEY (ID, course_id, sec_id, semester, year),
CONSTRAINT fk_teaches_instructor
FOREIGN KEY (ID) REFERENCES instructor(ID)
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT fk_teaches_section
FOREIGN KEY (course_id, sec_id, semester, year)
REFERENCES section(course_id, sec_id, semester, year)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=InnoDB;
-- takes: student takes a section (with optional grade)
CREATE TABLE takes (
ID VARCHAR(10),
course_id VARCHAR(10),
sec_id VARCHAR(10),
semester VARCHAR(10),
year INT,
grade VARCHAR(5),
PRIMARY KEY (ID, course_id, sec_id, semester, year),
CONSTRAINT fk_takes_student
FOREIGN KEY (ID) REFERENCES student(ID)
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT fk_takes_section
FOREIGN KEY (course_id, sec_id, semester, year)
REFERENCES section(course_id, sec_id, semester, year)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=InnoDB;
-- advisor: each student may have an instructor advisor
CREATE TABLE advisor (
s_id VARCHAR(10) PRIMARY KEY,
i_id VARCHAR(10),
CONSTRAINT fk_advisor_student
FOREIGN KEY (s_id) REFERENCES student(ID)
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT fk_advisor_instructor
FOREIGN KEY (i_id) REFERENCES instructor(ID)
ON UPDATE CASCADE
ON DELETE SET NULL
) ENGINE=InnoDB;
-- prereq: course prerequisites
CREATE TABLE prereq (
course_id VARCHAR(10),
prereq_id VARCHAR(10),
PRIMARY KEY (course_id, prereq_id),
CONSTRAINT fk_prereq_course
FOREIGN KEY (course_id) REFERENCES course(course_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT fk_prereq_prereqcourse
FOREIGN KEY (prereq_id) REFERENCES course(course_id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=InnoDB;
Write the following queries for the University Database
1) List all students with their department name
SELECT [Link], [Link], s.dept_name
FROM student s ORDER BY s.dept_name, [Link];
2) List all instructors with department and salary
SELECT [Link], [Link], i.dept_name, [Link]
FROM instructor i ORDER BY i.dept_name, [Link];
3) Show all courses offered by a particular department (example: 'CS')
SELECT course_id, title, credits
FROM course
WHERE dept_name = 'CS' ORDER BY course_id;
4) Students who have taken a specific course (example: 'CS101')
SELECT DISTINCT [Link], [Link]
FROM takes t
JOIN student s ON [Link] = [Link]
WHERE t.course_id = 'CS101'
ORDER BY [Link];
5) Full transcript-style: Perform join operation of Student + Course title + semester/year + grade.
SELECT [Link], [Link], c.course_id, [Link], [Link], [Link], [Link]
FROM takes t
JOIN student s ON [Link] = [Link]
JOIN course c ON c.course_id = t.course_id
ORDER BY [Link], [Link], [Link], c.course_id;
6) List sections (with classroom info) of a given course (example: 'CS101')
SELECT se.course_id, se.sec_id, [Link], [Link], [Link], se.room_number, se.time_slot_id
FROM section se
WHERE se.course_id = 'CS101'
ORDER BY [Link], [Link], se.sec_id;
7) Instructor teaching schedule (instructor + course + section term)
SELECT [Link] AS instructor_id, [Link] AS instructor_name, t.course_id, [Link], t.sec_id, [Link], [Link]
FROM teaches t
JOIN instructor i ON [Link] = [Link]
JOIN course c ON c.course_id = t.course_id
ORDER BY [Link], [Link], [Link], t.course_id, t.sec_id;
8) Students advised by each instructor
SELECT [Link] AS instructor_id, [Link] AS instructor_name, [Link] AS student_id, [Link] AS student_name
FROM advisor a
JOIN instructor i ON [Link] = a.i_id
JOIN student s ON [Link] = a.s_id
ORDER BY [Link], [Link];
9) Students who do NOT have an advisor assigned
SELECT [Link], [Link]
FROM student s
LEFT JOIN advisor a ON a.s_id = [Link]
WHERE a.s_id IS NULL
ORDER BY [Link];
10) List prerequisites for each course (course title + prereq title)
SELECT c.course_id, [Link] AS course_title, p.prereq_id, [Link] AS prereq_title
FROM prereq p
JOIN course c ON c.course_id = p.course_id
JOIN course c2 ON c2.course_id = p.prereq_id
ORDER BY c.course_id, p.prereq_id;
11) Count number of students enrolled in each section
SELECT t.course_id, t.sec_id, [Link], [Link], COUNT(*) AS enrolled_students
FROM takes t
GROUP BY t.course_id, t.sec_id, [Link], [Link]
ORDER BY [Link], [Link], t.course_id, t.sec_id;
12) Department-wise total number of students
SELECT dept_name, COUNT(*) AS total_students
FROM student
GROUP BY dept_name
ORDER BY total_students DESC;
13) Department-wise total credits offered (sum of course credits)
SELECT dept_name, SUM(credits) AS total_credits_offered
FROM course
GROUP BY dept_name
ORDER BY total_credits_offered DESC;
14) Classroom utilization: list all sections held in each classroom
SELECT [Link], se.room_number, se.course_id, se.sec_id, [Link], [Link], se.time_slot_id
FROM section se
WHERE [Link] IS NOT NULL AND se.room_number IS NOT NULL ORDER BY [Link],
se.room_number, [Link], [Link];
15) Find students who have taken more than N courses (example: > 5)
SELECT [Link], [Link], COUNT(*) AS total_courses_taken FROM takes t
JOIN student s
ON [Link] = [Link]
GROUP BY [Link], [Link] HAVING COUNT(*) > 5 ORDER BY total_courses_taken DESC, [Link];