University Database Design

Complete academic management system with many-to-many relationships.

Problem Statement

Design a database for a university system. Consider:

Tasks:

  1. Draw the ERD with all relationships
  2. Identify cardinalities (one-to-many, many-to-many)
  3. List necessary junction tables
  4. Define primary and foreign keys for each table

1. Entity & Relationship Analysis

Core Entities

  1. STUDENTS - Student records
  2. COURSES - Course catalog
  3. PROFESSORS - Faculty members
  4. DEPARTMENTS - Academic departments (added for better design)

Junction Tables (Many-to-Many)

  1. ENROLLMENTS - Students ←→ Courses (with grades, semester)
  2. TEACHING - Professors ←→ Courses (with semester, section)

Key Design Decisions

2. Entity-Relationship Diagram

erDiagram DEPARTMENTS ||--o{ STUDENTS : "has major in" DEPARTMENTS ||--o{ PROFESSORS : "employs" DEPARTMENTS ||--o{ COURSES : "offers" STUDENTS }o--o{ COURSES : "enrolls via ENROLLMENTS" PROFESSORS }o--o{ COURSES : "teaches via TEACHING" DEPARTMENTS { int department_id PK varchar department_name UK varchar building varchar phone int budget } STUDENTS { int student_id PK varchar first_name varchar last_name varchar email UK date enrollment_date int department_id FK varchar major int year decimal gpa varchar status } PROFESSORS { int professor_id PK varchar first_name varchar last_name varchar email UK int department_id FK varchar office varchar phone date hire_date varchar rank } COURSES { int course_id PK varchar course_code UK varchar course_name int credits int department_id FK text description int max_enrollment } ENROLLMENTS { int enrollment_id PK int student_id FK int course_id FK varchar semester int year varchar grade date enrollment_date varchar status } TEACHING { int teaching_id PK int professor_id FK int course_id FK varchar semester int year int section_number varchar schedule varchar classroom }

3. Relationships & Cardinalities

Relationship Type Implementation Description
Departments ←→ Students One-to-Many STUDENTS.department_id FK A department has many students, but each student belongs to one major department. Crow's Foot: ||--o{
Departments ←→ Professors One-to-Many PROFESSORS.department_id FK A department employs many professors, but each professor primarily belongs to one department. Crow's Foot: ||--o{
Departments ←→ Courses One-to-Many COURSES.department_id FK A department offers many courses, but each course belongs to one department. Crow's Foot: ||--o{
Students ←→ Courses Many-to-Many ENROLLMENTS junction table with semester, year, grade, enrollment_date, status A student enrolls in many courses, and a course has many students. Unique constraint: (student_id, course_id, semester, year). Crow's Foot: }o--o{
Professors ←→ Courses Many-to-Many TEACHING junction table with semester, year, section_number, schedule, classroom A professor teaches many courses, and a course can be taught by multiple professors (different sections). Unique constraint: (professor_id, course_id, semester, year, section_number). Crow's Foot: }o--o{

4. Detailed Entity Specifications

DEPARTMENTS

Attributes:

STUDENTS

Attributes:

PROFESSORS

Attributes:

COURSES

Attributes:

ENROLLMENTS (Junction Table)

Attributes:

TEACHING (Junction Table)

Attributes:

5. Complete SQL Schema

-- ============================================
-- UNIVERSITY DATABASE SCHEMA
-- ============================================

-- 1. DEPARTMENTS Table
CREATE TABLE DEPARTMENTS (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(100) UNIQUE NOT NULL,
    building VARCHAR(100),
    phone VARCHAR(20),
    budget INT,
    INDEX idx_department_name (department_name)
) ENGINE=InnoDB;

-- 2. STUDENTS Table
CREATE TABLE STUDENTS (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    enrollment_date DATE NOT NULL,
    department_id INT,
    major VARCHAR(100),
    year INT CHECK (year BETWEEN 1 AND 4),
    gpa DECIMAL(3,2) CHECK (gpa BETWEEN 0.00 AND 4.00),
    status VARCHAR(20) DEFAULT 'Active',
    CHECK (status IN ('Active', 'Graduated', 'Suspended', 'Withdrawn')),
    FOREIGN KEY (department_id) REFERENCES DEPARTMENTS(department_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    INDEX idx_email (email),
    INDEX idx_department (department_id),
    INDEX idx_status (status),
    INDEX idx_name (last_name, first_name)
) ENGINE=InnoDB;

-- 3. PROFESSORS Table
CREATE TABLE PROFESSORS (
    professor_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    department_id INT,
    office VARCHAR(50),
    phone VARCHAR(20),
    hire_date DATE,
    rank VARCHAR(50),
    CHECK (rank IN ('Assistant Professor', 'Associate Professor', 'Full Professor', 'Lecturer', 'Adjunct')),
    FOREIGN KEY (department_id) REFERENCES DEPARTMENTS(department_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    INDEX idx_email (email),
    INDEX idx_department (department_id),
    INDEX idx_name (last_name, first_name)
) ENGINE=InnoDB;

-- 4. COURSES Table
CREATE TABLE COURSES (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_code VARCHAR(20) UNIQUE NOT NULL,
    course_name VARCHAR(255) NOT NULL,
    credits INT NOT NULL,
    department_id INT,
    description TEXT,
    max_enrollment INT DEFAULT 30,
    CHECK (credits BETWEEN 1 AND 6),
    CHECK (max_enrollment > 0),
    FOREIGN KEY (department_id) REFERENCES DEPARTMENTS(department_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    INDEX idx_course_code (course_code),
    INDEX idx_department (department_id),
    INDEX idx_credits (credits)
) ENGINE=InnoDB;

-- 5. ENROLLMENTS Junction Table
CREATE TABLE ENROLLMENTS (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    semester VARCHAR(20) NOT NULL,
    year INT NOT NULL,
    grade VARCHAR(2),
    enrollment_date DATE DEFAULT (CURRENT_DATE),
    status VARCHAR(20) DEFAULT 'Enrolled',
    CHECK (semester IN ('Fall', 'Spring', 'Summer')),
    CHECK (year >= 2000 AND year <= 2100),
    CHECK (status IN ('Enrolled', 'Dropped', 'Completed')),
    CHECK (grade IN ('A', 'A-', 'B+', 'B', 'B-', 'C+', 'C', 'C-', 'D+', 'D', 'F', 'I', 'W', 'P')),
    UNIQUE KEY unique_enrollment (student_id, course_id, semester, year),
    FOREIGN KEY (student_id) REFERENCES STUDENTS(student_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (course_id) REFERENCES COURSES(course_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    INDEX idx_student (student_id),
    INDEX idx_course (course_id),
    INDEX idx_semester_year (semester, year),
    INDEX idx_status (status)
) ENGINE=InnoDB;

-- 6. TEACHING Junction Table
CREATE TABLE TEACHING (
    teaching_id INT AUTO_INCREMENT PRIMARY KEY,
    professor_id INT NOT NULL,
    course_id INT NOT NULL,
    semester VARCHAR(20) NOT NULL,
    year INT NOT NULL,
    section_number INT DEFAULT 1,
    schedule VARCHAR(100),
    classroom VARCHAR(50),
    CHECK (semester IN ('Fall', 'Spring', 'Summer')),
    CHECK (year >= 2000 AND year <= 2100),
    CHECK (section_number > 0),
    UNIQUE KEY unique_teaching (professor_id, course_id, semester, year, section_number),
    FOREIGN KEY (professor_id) REFERENCES PROFESSORS(professor_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (course_id) REFERENCES COURSES(course_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    INDEX idx_professor (professor_id),
    INDEX idx_course (course_id),
    INDEX idx_semester_year (semester, year)
) ENGINE=InnoDB;

-- ============================================
-- SAMPLE DATA
-- ============================================

-- Insert departments
INSERT INTO DEPARTMENTS (department_name, building, phone, budget) VALUES
('Computer Science', 'Science Hall', '555-1000', 2000000),
('Mathematics', 'Math Building', '555-1001', 1500000),
('Physics', 'Science Hall', '555-1002', 1800000),
('Business', 'Business Center', '555-1003', 2500000);

-- Insert professors
INSERT INTO PROFESSORS (first_name, last_name, email, department_id, office, phone, hire_date, rank) VALUES
('John', 'Smith', 'j.smith@university.edu', 1, 'SH-301', '555-2001', '2010-08-15', 'Full Professor'),
('Jane', 'Doe', 'j.doe@university.edu', 1, 'SH-302', '555-2002', '2015-01-10', 'Associate Professor'),
('Robert', 'Johnson', 'r.johnson@university.edu', 2, 'MB-201', '555-2003', '2012-09-01', 'Full Professor');

-- Insert courses
INSERT INTO COURSES (course_code, course_name, credits, department_id, description, max_enrollment) VALUES
('CS101', 'Introduction to Programming', 3, 1, 'Basic programming concepts', 40),
('CS201', 'Data Structures', 3, 1, 'Advanced data structures and algorithms', 35),
('CS301', 'Database Systems', 3, 1, 'Database design and SQL', 30),
('MATH101', 'Calculus I', 4, 2, 'Differential calculus', 50),
('MATH201', 'Linear Algebra', 3, 2, 'Matrices and vector spaces', 35);

-- Insert students
INSERT INTO STUDENTS (first_name, last_name, email, enrollment_date, department_id, major, year, gpa, status) VALUES
('Alice', 'Williams', 'alice.w@student.edu', '2023-08-25', 1, 'Computer Science', 2, 3.75, 'Active'),
('Bob', 'Brown', 'bob.b@student.edu', '2023-08-25', 1, 'Computer Science', 2, 3.50, 'Active'),
('Charlie', 'Davis', 'charlie.d@student.edu', '2022-08-20', 2, 'Mathematics', 3, 3.90, 'Active');

-- Insert teaching assignments (Fall 2025)
INSERT INTO TEACHING (professor_id, course_id, semester, year, section_number, schedule, classroom) VALUES
(1, 1, 'Fall', 2025, 1, 'MWF 09:00-10:00', 'SH-101'),
(1, 3, 'Fall', 2025, 1, 'TTh 14:00-15:30', 'SH-105'),
(2, 2, 'Fall', 2025, 1, 'MWF 10:00-11:00', 'SH-102'),
(3, 4, 'Fall', 2025, 1, 'TTh 09:00-10:30', 'MB-101');

-- Insert enrollments (Fall 2025)
INSERT INTO ENROLLMENTS (student_id, course_id, semester, year, grade, status) VALUES
(1, 1, 'Fall', 2025, NULL, 'Enrolled'),  -- Alice in CS101
(1, 2, 'Fall', 2025, NULL, 'Enrolled'),  -- Alice in CS201
(2, 1, 'Fall', 2025, NULL, 'Enrolled'),  -- Bob in CS101
(2, 3, 'Fall', 2025, NULL, 'Enrolled'),  -- Bob in CS301
(3, 4, 'Fall', 2025, NULL, 'Enrolled');  -- Charlie in MATH101

6. Common University Queries

-- Student's current schedule
SELECT
    c.course_code,
    c.course_name,
    c.credits,
    CONCAT(p.first_name, ' ', p.last_name) AS professor,
    t.schedule,
    t.classroom
FROM ENROLLMENTS e
JOIN COURSES c ON e.course_id = c.course_id
JOIN TEACHING t ON c.course_id = t.course_id
    AND e.semester = t.semester
    AND e.year = t.year
JOIN PROFESSORS p ON t.professor_id = p.professor_id
WHERE e.student_id = 1
    AND e.semester = 'Fall'
    AND e.year = 2025
    AND e.status = 'Enrolled';

-- Professor's teaching load
SELECT
    c.course_code,
    c.course_name,
    t.section_number,
    t.schedule,
    t.classroom,
    COUNT(e.enrollment_id) AS enrolled_students
FROM TEACHING t
JOIN COURSES c ON t.course_id = c.course_id
LEFT JOIN ENROLLMENTS e ON t.course_id = e.course_id
    AND t.semester = e.semester
    AND t.year = e.year
    AND e.status = 'Enrolled'
WHERE t.professor_id = 1
    AND t.semester = 'Fall'
    AND t.year = 2025
GROUP BY t.teaching_id;

-- Department statistics
SELECT
    d.department_name,
    COUNT(DISTINCT s.student_id) AS total_students,
    COUNT(DISTINCT p.professor_id) AS total_professors,
    COUNT(DISTINCT c.course_id) AS total_courses
FROM DEPARTMENTS d
LEFT JOIN STUDENTS s ON d.department_id = s.department_id AND s.status = 'Active'
LEFT JOIN PROFESSORS p ON d.department_id = p.department_id
LEFT JOIN COURSES c ON d.department_id = c.department_id
GROUP BY d.department_id, d.department_name;

-- Student transcript
SELECT
    e.semester,
    e.year,
    c.course_code,
    c.course_name,
    c.credits,
    e.grade
FROM ENROLLMENTS e
JOIN COURSES c ON e.course_id = c.course_id
WHERE e.student_id = 1
    AND e.status = 'Completed'
ORDER BY e.year DESC, e.semester;

Design Best Practices Implemented

Previous Exercise Back to Lecture 2 Next Exercise