Lecture 6: The Capstone Project

Building a Student Management System from Scratch

🎓 Concept Overview

End-to-End: From requirements gathering to final reporting.
Normalization in Practice: Implementing 1:N and N:M relationships.
Data Integrity: Using Constraints and Transactions to keep data clean.
Reporting: Turning raw data into business intelligence.

6.1 Phase 1: Requirements Gathering

We have been hired by a university to build a database.

🧠 Mental Model: The Translator

Client says: "Instructors teach courses. Students sign up for many courses."

DBA hears: "Instructor -> Course (One-to-Many). Student <-> Course (Many-to-Many)."

6.1.1 The Entities

Students: The people learning (primary entity).
Instructors: The people teaching.
Courses: The subject matter (taught by one instructor).
Enrollments: The record of a student taking a course (with a grade).

6.1.2 The ER Diagram

erDiagram INSTRUCTORS ||--o{ COURSES : teaches STUDENTS ||--o{ ENROLLMENTS : "signs up" COURSES ||--o{ ENROLLMENTS : "has student" INSTRUCTORS { int id PK string name string department } COURSES { int id PK string title int credits int instructor_id FK } STUDENTS { int id PK string name string email int enrollment_year } ENROLLMENTS { int student_id PK, FK int course_id PK, FK char grade date enrollment_date }

6.2 Phase 2: Schema Creation (DDL)

We build the tables in order of dependency (Parents first, Children last).

Step 1: The Independent Tables

-- 1. Instructors (Parents)
CREATE TABLE instructors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50) DEFAULT 'General'
);

-- 2. Students (Parents)
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE, -- prevent duplicates
    enrollment_year YEAR DEFAULT (YEAR(CURRENT_DATE))
);

Step 2: The Dependent Tables

-- 3. Courses (Child of Instructor)
CREATE TABLE courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    credits INT CHECK (credits > 0),
    instructor_id INT,
    FOREIGN KEY (instructor_id) REFERENCES instructors(id)
);

-- 4. Enrollments (Child of Student AND Course)
CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    grade CHAR(1), -- A, B, C, D, F
    enrollment_date DATE DEFAULT (CURRENT_DATE),
    PRIMARY KEY (student_id, course_id), -- Composite PK
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

6.3 Phase 3: Populating Data (DML)

Let's add some life to our system.

-- 1. Add Faculty
INSERT INTO instructors (name, department) VALUES 
('Dr. Smith', 'Computer Science'),
('Prof. Jones', 'Mathematics');

-- 2. Add Students
INSERT INTO students (name, email) VALUES 
('Alice', 'alice@uni.edu'),
('Bob', 'bob@uni.edu'),
('Charlie', 'charlie@uni.edu');

-- 3. Add Courses (Linked to Faculty)
INSERT INTO courses (title, credits, instructor_id) VALUES 
('Intro to SQL', 3, 1), -- Taught by Dr. Smith
('Advanced Calculus', 4, 2), -- Taught by Prof. Jones
('Data Structures', 4, 1); -- Taught by Dr. Smith

6.4 Phase 4: Business Logic (Transactions)

Scenario: Enrollment is a sensitive action. We want to ensure it completes fully.

-- Alice registers for SQL
START TRANSACTION;

-- Check if course exists (Conceptual step)
-- Insert the record
INSERT INTO enrollments (student_id, course_id, grade) 
VALUES (1, 1, NULL); -- No grade yet

COMMIT;

💡 Pro Tip: Soft Deletes

In production, never run DELETE FROM students. Instead, add a column is_active BOOLEAN and set it to false. Data is gold; don't throw it away.

6.5 Phase 5: Reporting & Analysis

Now the client wants answers.

Report 1: Student Transcripts

Join all 4 tables to see who took what.

SELECT s.name AS student, c.title AS course, i.name AS professor, e.grade
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.course_id
JOIN instructors i ON c.instructor_id = i.id;

Report 2: Instructor Workload

Who is teaching the most students?

SELECT i.name, COUNT(e.student_id) as total_students
FROM instructors i
JOIN courses c ON i.id = c.instructor_id
LEFT JOIN enrollments e ON c.id = e.course_id
GROUP BY i.name;

6.6 Teaching Notes

🎓 Instructor Guide

Grading Rubric Tips:

Composite Keys: Ensure students used PRIMARY KEY (student_id, course_id) in Enrollments. If they used a separate `id`, ask them how they prevent duplicate enrollments (they would need a UNIQUE constraint).
Soft Deletes: Ask them "What happens if a student drops out?". If they say DELETE, explain that we lose their historical record. Prefer is_active = false.
The "Architect" Mindset: Emphasize that 80% of the work is in the design (ER Diagram). Coding the CREATE TABLE is just typing; the thinking happened earlier.

6.7 Debugging Workshop

1. Double Booking

Error: Duplicate entry '1-1' for key 'PRIMARY'.

INSERT INTO enrollments (student_id, course_id) VALUES (1, 1);
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1); -- Again?

2. The Ghost Professor

Error: Cannot add or update a child row: a foreign key constraint fails.

INSERT INTO courses (title, credits, instructor_id) 
VALUES ('Ghost Hunting', 3, 99);

6.8 Practice Tasks

Task 1: The Prerequisite

Scenario: You want to ensure students take "Intro to SQL" before "Advanced SQL".

Activity: Add a column prereq_id to the courses table. It should point to the courses table itself (Self-Referencing Foreign Key).

Show Answer

ALTER TABLE courses 
ADD COLUMN prereq_id INT,
ADD FOREIGN KEY (prereq_id) REFERENCES courses(id);

Now a course can "require" another course.

Task 2: The Dean's List (GPA)

Scenario: Calculate the GPA for every student. (Assume A=4, B=3, etc. For simplicity, just count 'A's as 4.0 points).

Activity: Write a query to find students with an average grade of 'A'.