Project: A Small School Database

This project puts together schema design, constraints, data and reporting into one database you could hand to a developer.

Design

Create four tables: students, courses, enrolments and results. Students and courses are independent. Enrolments link them. Results belong to an enrolment.

CREATE TABLE students (
    id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(100) NOT NULL,
    level VARCHAR(20) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE courses (
    id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    price DECIMAL(10, 2) NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE enrolments (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    student_id INT UNSIGNED NOT NULL,
    course_id  INT UNSIGNED NOT NULL,
    enrolled_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_enrolment (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE results (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    enrolment_id INT UNSIGNED NOT NULL,
    score       DECIMAL(5, 2) NOT NULL,
    CONSTRAINT chk_score CHECK (score BETWEEN 0 AND 100),
    FOREIGN KEY (enrolment_id) REFERENCES enrolments(id) ON DELETE CASCADE
) ENGINE=InnoDB;

Sample Data

Insert enough data to test the report: at least three students, three courses and a few results, including one student with no results.

Report

Write a query that returns each student, their level, the number of courses and their average score. Keep students with no data by using LEFT JOIN and COALESCE.

SELECT s.name,
       s.level,
       COUNT(DISTINCT e.course_id) AS courses,
       COALESCE(ROUND(AVG(r.score), 2), 0) AS average_score
FROM students s
LEFT JOIN enrolments e ON e.student_id = s.id
LEFT JOIN results r ON r.enrolment_id = e.id
GROUP BY s.id, s.name, s.level
ORDER BY average_score DESC;

Practice

1. Add a teachers table and link courses to a teacher.
2. Add a result for a student who has none.
3. Rewrite the report to show the best score as well.

Hands-on exercise

Build the four tables exactly as described.
1. Insert at least three students and three courses.
2. Enrol them and add results for some students.
3. Run the report query and confirm every student appears.

Recap

A finished database has a clear schema, enforced relationships, sample data and at least one report that proves it works.