Putting It All Together
Design a complete school database schema from scratch using everything you have learned.
You have mastered the fundamentals: databases, tables, constraints, joins, aggregation, and indexes. Now apply everything to a fresh design exercise. You will build a school database with teachers, courses, and enrollments, choosing data types, applying constraints, and verifying performance.
Definition
A database schema is the structural design of a database: the tables, columns, data types, and relationships between tables. A well-designed schema reduces redundancy and improves data integrity.
Start fresh: Click Reset in the sidebar to clear your database, then run the setup below.
CREATE DATABASE IF NOT EXISTS school;
USE school;
-- Students table (from earlier tutorials)
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100)
);
INSERT INTO students (name, email) VALUES
('Alice', '[email protected]'),
('Bob', '[email protected]'),
('Charlie', '[email protected]'),
('Diana', '[email protected]');Step 1: Design the Teachers Table
Before writing SQL, think about what a teacher record needs. A teacher has a name, email, department, hire date, salary, and an optional manager. Which columns should be NOT NULL? Which should be UNIQUE? What data type fits salary?
Data type choice:DECIMAL vs FLOAT
When choosing numeric types, precision matters. DECIMAL stores exact values. The number you store is the number you get back. FLOAT stores approximate values and can introduce rounding errors. For money, scores, or any value where exact precision matters, always use DECIMAL.
Create the teachers table
CREATE TABLE IF NOT EXISTS teachers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
department VARCHAR(50),
hire_date DATE NOT NULL,
salary DECIMAL(10,2) NOT NULL,
manager_id INT
);NOT NULL on name, email, hire_date, and salary ensures no incomplete records. UNIQUE on email prevents duplicate accounts. DECIMAL(10,2) stores exact salary values. department and manager_id support hierarchy queries in later tutorials.
Step 2: Design the Courses Table
A course has a code, name, optional description, and a teacher who teaches it. The code must be unique (no two courses share the same code). The teacher is a foreign key referencing the teachers table.
Create the courses table
CREATE TABLE IF NOT EXISTS courses (
id INT AUTO_INCREMENT PRIMARY KEY,
code VARCHAR(10) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL,
description TEXT,
teacher_id INT NOT NULL,
FOREIGN KEY (teacher_id) REFERENCES teachers(id)
ON DELETE RESTRICT ON UPDATE CASCADE
);UNIQUE on code prevents duplicate course codes. FOREIGN KEY on teacher_id links to the teachers table. ON DELETE RESTRICT prevents deleting a teacher who is assigned to courses. ON UPDATE CASCADE propagates teacher id changes.
Step 3: Design the Enrollments Table
An enrollment links a student to a course with a score. The combination of student_id and course_name must be unique (a student cannot enroll in the same course twice).
Create the enrollments table
CREATE TABLE IF NOT EXISTS enrollments (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
course_name VARCHAR(100) NOT NULL,
score DECIMAL(5,2),
enrolled_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (student_id) REFERENCES students(id)
ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE KEY unique_enrollment (student_id, course_name)
);ON DELETE CASCADE means if a student is deleted, their enrollments are removed too. The UNIQUE constraint on (student_id, course_name) prevents duplicate enrollments. DATETIME with DEFAULT CURRENT_TIMESTAMP records when the enrollment was created.
Step 4: Populate the Data
Insert teachers, courses, and enrollments
INSERT INTO teachers (name, email, department, hire_date, salary, manager_id) VALUES
('Dr. Smith', '[email protected]', 'Mathematics', '2018-08-15', 65000.00, NULL),
('Ms. Johnson', '[email protected]', 'English', '2020-01-10', 58000.00, 1),
('Prof. Lee', '[email protected]', 'Science', '2015-09-01', 72000.00, 1);
INSERT INTO courses (code, name, description, teacher_id) VALUES
('MATH101', 'Algebra', 'Equations, inequalities, and functions', 1),
('ENG101', 'English Composition', 'Essay writing and critical thinking', 2),
('SCI101', 'Biology', 'Cell biology and genetics', 3),
('HIS101', 'World History', 'Major events from ancient to modern times', 2);
INSERT INTO enrollments (student_id, course_name, score) VALUES
(1, 'Algebra', 90), (1, 'Biology', 85),
(2, 'Algebra', 78), (2, 'English Composition', 92),
(3, 'Algebra', 92), (3, 'Biology', 88),
(4, 'English Composition', 95), (4, 'World History', 87);Notice the data types in action: DECIMAL(10,2) for salary, DECIMAL(5,2) for score, DATE for hire_date, DATETIME for enrolled_at. Each INSERT respects the constraints defined in the schema.
Step 5: Query Across All Tables
Students with their courses and teachers
SELECT s.name AS student, e.course_name, t.name AS teacher, e.score
FROM enrollments e
INNER JOIN students s ON e.student_id = s.id
INNER JOIN courses c ON e.course_name = c.name
INNER JOIN teachers t ON c.teacher_id = t.id
ORDER BY s.name, e.course_name;Four-table join: enrollments to students, courses, and teachers. This query shows the full picture of who is learning what from whom.
Average score per course with teacher
SELECT c.name AS course, t.name AS teacher,
COUNT(*) AS enrolled,
ROUND(AVG(e.score), 1) AS avg_score
FROM courses c
INNER JOIN teachers t ON c.teacher_id = t.id
LEFT JOIN enrollments e ON c.name = e.course_name
GROUP BY c.name, t.name
ORDER BY avg_score DESC;LEFT JOIN ensures courses with no enrollments still appear. AVG and COUNT provide summary statistics. ROUND rounds to one decimal place.
Students above average
SELECT s.name, e.course_name, e.score
FROM enrollments e
INNER JOIN students s ON e.student_id = s.id
WHERE e.score > (
SELECT AVG(score) FROM enrollments
WHERE course_name = e.course_name
)
ORDER BY e.course_name, e.score DESC;This uses a correlated subquery: the inner SELECT AVG(score) runs once per row, computing the average for that specific course. Students above the course average are returned.
Step 6: Verify Performance
EXPLAIN the four-table join
EXPLAIN SELECT s.name, e.course_name, t.name
FROM enrollments e
INNER JOIN students s ON e.student_id = s.id
INNER JOIN courses c ON e.course_name = c.name
INNER JOIN teachers t ON c.teacher_id = t.id;Check the type and key columns. Students and teachers should show eq_ref (primary key lookup). Courses shows ref (on code). Enrollments may show ALL if there is no index yet.
Add indexes for common queries
CREATE INDEX idx_enrollment_student ON enrollments (student_id);
CREATE INDEX idx_course_teacher ON courses (teacher_id);These indexes speed up JOINs and WHERE clauses that filter by student or teacher.
Step 7: Review Your Schema
Inspect the full schema
SHOW CREATE TABLE enrollments\G
SHOW INDEX FROM courses;SHOW CREATE TABLE reveals all constraints, defaults, and foreign keys. SHOW INDEX shows primary key and custom indexes. Verify your design matches what you intended.
Why use DECIMAL for salary instead of FLOAT?
A query runs slowly. What is the first thing to check?
Key Takeaways
- Identify entities and relationships before writing CREATE TABLE.
- Use the right data types: DECIMAL for money, INT for IDs, DATE for dates, DATETIME for date-and-time values.
- Apply constraints from the start: NOT NULL, UNIQUE, FOREIGN KEY.
- Join tables to combine related data; aggregate to summarize it.
- Always run EXPLAIN on queries that will run in production.
Congratulations
You have completed the fundamentals. You now understand databases, tables, SQL commands, joins, aggregation, constraints, indexes, and schema design. The next stages cover advanced topics like schema modification, subqueries, window functions, and more. The best way to keep learning is to practice. Open the playground and build something.
Ready to test your knowledge?
Take a Quiz