Student Database

Overview

A school has students and it has courses, but the interesting design question is how the two connect. A student can enroll in many courses, and a course can hold many students — that is a many-to-many relationship, and a relational database cannot represent it with a simple foreign key on either side. Trying to cram a list of course IDs into a single column on `students` would break the relational model's first rule (every column holds one atomic value, not a list), so instead you introduce a third table, `enrollments`, whose entire job is to record which student is in which course. This pattern — a "junction" or "linking" table sitting between two entities — is one of the most common shapes in real-world schema design, and this project is where you build one from scratch.

By the end of this tutorial you will have a three-table schema — `students`, `courses`, and `enrollments` — with primary keys, foreign keys enforcing referential integrity, and a composite uniqueness constraint that makes it structurally impossible to enroll the same student in the same course twice. You will also write the join queries that make the design pay off: pulling a student's full course list, a course's full roster, and aggregate counts across all three tables at once.

Prerequisites

Project Structure

Three tables make up the whole schema. `students` and `courses` are independent entities — neither needs to know the other exists. `enrollments` is the junction table that ties them together, and it is the only table that references the other two; nothing about `students` or `courses` needs to change to support the relationship.

Step 1: Design the Students Table

`student_id` is a surrogate key — an `AUTO_INCREMENT` integer that exists purely to identify the row, with no real-world meaning of its own. That is deliberate: a student's name can change and is never guaranteed unique, so it would make a poor primary key. `email` gets its own `UNIQUE` constraint on top of that, because in practice you also want to guarantee no two student accounts share the same email address, even though email is not the primary key.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,   -- Surrogate key: an ID with no real-world meaning, safe to reference elsewhere
    first_name VARCHAR(50) NOT NULL,             -- Every student must have a first name
    last_name VARCHAR(50) NOT NULL,              -- Every student must have a last name
    email VARCHAR(100) NOT NULL UNIQUE,          -- UNIQUE, not just NOT NULL: no two students may share a login email
    enrollment_date DATE NOT NULL                -- The date this student joined the school
);

Step 2: Design the Courses Table

`course_code` (like `CS101`) is what a human actually looks up, so it gets `UNIQUE` even though `course_id` remains the primary key everything else references — the same "surrogate key plus a separately-unique human-facing code" pattern used for `students.email`. The `CHECK` constraint on `credits` is a small but important habit: it stops obviously invalid data (a course worth 0 or 20 credits) from ever being inserted, no matter what application code eventually writes to this table.

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,          -- Surrogate key, referenced by enrollments.course_id
    course_code VARCHAR(10) NOT NULL UNIQUE,           -- Human-facing code, e.g. 'CS101' — must be unique, not the PK itself
    course_name VARCHAR(100) NOT NULL,                 -- Full display name, e.g. 'Introduction to Computer Science'
    credits TINYINT UNSIGNED NOT NULL DEFAULT 3,       -- Most courses are worth 3 credits unless stated otherwise
    CHECK (credits BETWEEN 1 AND 6)                    -- Rejects obviously invalid credit values at the database level
);

Step 3: Design the Enrollments Junction Table

Every column that matters here is a foreign key: `student_id` must point at a real row in `students`, and `course_id` must point at a real row in `courses` — that is what makes it impossible for an enrollment to reference a student or course that does not exist. `enrollment_id` still gets its own surrogate primary key rather than making `(student_id, course_id)` the primary key directly, because a future feature (attendance records, grade history) may need to reference one specific enrollment by a single simple ID. Instead, uniqueness of the pairing is enforced separately with `UNIQUE KEY uq_student_course (student_id, course_id)` — a composite constraint spanning two columns, which is what actually stops a student from enrolling in the same course twice.

CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,   -- Own surrogate key so one specific enrollment can be referenced later
    student_id INT NOT NULL,                        -- FK: which student this enrollment belongs to
    course_id INT NOT NULL,                         -- FK: which course this enrollment is for
    enrollment_date DATE NOT NULL,                   -- The date this particular enrollment was recorded
    grade CHAR(2) DEFAULT NULL,                      -- NULL until the course is graded, e.g. 'A', 'B+', 'C'
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,  -- Deleting a student cleans up their enrollments too
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE,     -- Deleting a course cleans up its enrollments too
    UNIQUE KEY uq_student_course (student_id, course_id)   -- Composite constraint: this exact pair can only exist once
);

Step 4: Insert Sample Data

With all three tables and their constraints in place, sample data can go in. Notice the insert order matters: `students` and `courses` must be populated before `enrollments`, because every row in `enrollments` needs a `student_id` and `course_id` that already exist — that is the foreign keys doing their job, rejecting any enrollment that points at a student or course that has not been created yet.

INSERT INTO students (first_name, last_name, email, enrollment_date) VALUES
('Priya', 'Nair', 'priya.nair@example.com', '2025-08-01'),
('Rahul', 'Verma', 'rahul.verma@example.com', '2025-08-01'),
('Aditi', 'Sharma', 'aditi.sharma@example.com', '2025-08-02'),
('Karan', 'Mehta', 'karan.mehta@example.com', '2025-08-02');

INSERT INTO courses (course_code, course_name, credits) VALUES
('CS101', 'Introduction to Computer Science', 4),
('CS102', 'Data Structures', 4),
('MATH201', 'Discrete Mathematics', 3);

-- Each row here is one student in one course; the UNIQUE constraint from Step 3
-- would reject a fifth INSERT that repeated an existing (student_id, course_id) pair.
INSERT INTO enrollments (student_id, course_id, enrollment_date, grade) VALUES
(1, 1, '2025-08-10', 'A'),
(1, 2, '2025-08-10', NULL),      -- Priya is taking CS102 but has not been graded yet
(2, 1, '2025-08-11', 'B+'),
(3, 1, '2025-08-11', 'A-'),
(3, 3, '2025-08-12', NULL),
(4, 2, '2025-08-12', NULL);

Step 5: Join Across All Three Tables

This is the query that shows why the junction table exists at all: it starts from `enrollments`, then joins to `students` to resolve `student_id` into an actual name, and joins to `courses` to resolve `course_id` into an actual course name — turning three tables of raw IDs into one human-readable roster.

SELECT
    s.first_name,
    s.last_name,
    c.course_code,
    c.course_name,
    e.grade
FROM enrollments e
JOIN students s ON e.student_id = s.student_id   -- Resolve student_id into the student's actual name
JOIN courses c ON e.course_id = c.course_id      -- Resolve course_id into the course's actual name
ORDER BY s.last_name, c.course_code;

Complete Schema

Here is the full schema assembled from the three steps above, ready to run top to bottom.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    enrollment_date DATE NOT NULL
);

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_code VARCHAR(10) NOT NULL UNIQUE,
    course_name VARCHAR(100) NOT NULL,
    credits TINYINT UNSIGNED NOT NULL DEFAULT 3,
    CHECK (credits BETWEEN 1 AND 6)
);

CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    grade CHAR(2) DEFAULT NULL,
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE,
    UNIQUE KEY uq_student_course (student_id, course_id)
);

Sample Queries

Two more queries that a real registrar's office would actually run against this schema: finding one course's full roster, and reporting enrollment counts per course.

-- Full roster for one specific course, looked up by its human-facing code
SELECT s.first_name, s.last_name, e.enrollment_date
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses c ON e.course_id = c.course_id
WHERE c.course_code = 'CS101'
ORDER BY s.last_name;

-- How many students are enrolled in each course, including courses with zero enrollments
SELECT c.course_code, c.course_name, COUNT(e.enrollment_id) AS total_students
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id   -- LEFT JOIN keeps courses even if no one has enrolled yet
GROUP BY c.course_id, c.course_code, c.course_name
ORDER BY total_students DESC;

Extend This Project

Summary

You designed a three-table schema that correctly models a many-to-many relationship using a junction table, backed by real foreign keys and a composite `UNIQUE` constraint rather than trusting application code to prevent duplicate enrollments. The join queries you wrote — resolving `enrollments` into a readable roster, filtering by course, and aggregating with `GROUP BY` — are the same query shapes you will reuse in almost any schema with a many-to-many relationship, not just this student database.