Lesson 22 of 2235 min read

Practical Exercise: Design a 3-Table Student Management Database Schema

Apply everything from this module by designing and building a complete 3-table student management database schema from scratch.

Practical Exercise: Design a 3-Table Student Management Database Schema

It's time to put every concept from this module to work in one complete, realistic exercise: designing a student management database from a blank page. Instead of learning one isolated concept at a time, this lesson walks through the full design and build process for a 3-table schema — students, courses, and enrollments — using data types, primary keys, foreign keys, and constraints exactly as a real project would require.

The Practical Exercise: What We're Building

A student management database needs to track three core things: who the students are, what courses exist, and which students are enrolled in which courses, along with their grades. This naturally forms a classic many-to-many relationship — many students can enroll in many courses — resolved through a third, dedicated association table: enrollments.

What You'll Learn

  • Translate a real-world requirement into a 3-table relational schema.
  • Apply appropriate data types to every column based on its real meaning.
  • Use PRIMARY KEY, FOREIGN KEY, and composite UNIQUE constraints together correctly.
  • Validate the finished schema using DESCRIBE and SHOW CREATE TABLE.

Key Terms to Know

  • Many-to-many relationship: A relationship where many rows in one table can relate to many rows in another, resolved via a junction table.
  • Junction (association) table: A table that resolves a many-to-many relationship by storing foreign keys to both related tables.
  • Entity: A real-world object or concept represented by a table, such as a student or a course.
  • Schema design: The process of deciding tables, columns, data types, and constraints before any data is inserted.

Step 1: Identifying Entities and Relationships

Before writing any SQL, the entities are identified: students and courses are clear standalone entities, each deserving their own table. The relationship between them — a student enrolling in a course, possibly with a grade — is itself meaningful enough to deserve its own junction table, enrollments, rather than being squeezed awkwardly into either students or courses.

This mirrors the entity-relationship thinking from earlier in the course: students and courses are entities, and enrolls_in is the relationship, modeled here as the enrollments table.

Step 2: Choosing Data Types and Constraints for Each Table

students needs an AUTO_INCREMENT primary key, a NOT NULL name, a UNIQUE NOT NULL email, and a DATE for date_of_birth. courses needs an AUTO_INCREMENT primary key, a NOT NULL course_name, and possibly a UNIQUE course_code for short identifiers like 'CS101'.

enrollments needs foreign keys to both students and courses, a composite UNIQUE constraint on (student_id, course_id) to prevent duplicate enrollment, a DECIMAL grade column that allows NULL until grading happens, and a DEFAULT enrolled_on date. Every choice here directly reuses a concept from earlier in this module.

Step 3: Writing and Validating the Final Schema

With entities, columns, and constraints decided, the actual CREATE TABLE statements come together quickly, in dependency order: students and courses first (since they have no dependencies), then enrollments last (since it depends on both). After creation, DESCRIBE and SHOW CREATE TABLE confirm the schema matches the design exactly as intended, catching any typos or missed constraints before real data is inserted.

Visual Summary

Picture three connected boxes: a 'Students' box and a 'Courses' box, both standing independently, connected by a smaller 'Enrollments' box in the middle that holds a line to each — this middle box is the only place where a specific student and a specific course meet, along with details unique to that pairing, like the grade earned.

Final Schema Overview

TableKey ColumnsRole
studentsstudent_id (PK), full_name, email (UNIQUE)Stores each student's identity
coursescourse_id (PK), course_name, course_code (UNIQUE)Stores each course's identity
enrollmentsenrollment_id (PK), student_id (FK), course_id (FK)Links students to courses with grade/date

SQL Example

-- Step 1: Students table
CREATE TABLE students (
  student_id     INT          PRIMARY KEY AUTO_INCREMENT,
  full_name      VARCHAR(100) NOT NULL,
  email          VARCHAR(150) NOT NULL UNIQUE,
  date_of_birth  DATE         NOT NULL,
  enrolled_since DATE         NOT NULL DEFAULT (CURRENT_DATE)
);

-- Step 2: Courses table
CREATE TABLE courses (
  course_id   INT           PRIMARY KEY AUTO_INCREMENT,
  course_name VARCHAR(120)  NOT NULL,
  course_code VARCHAR(15)   NOT NULL UNIQUE,
  credits     INT           NOT NULL CHECK (credits BETWEEN 1 AND 6)
);

-- Step 3: Enrollments table (junction table resolving the many-to-many relationship)
CREATE TABLE enrollments (
  enrollment_id INT            PRIMARY KEY AUTO_INCREMENT,
  student_id    INT            NOT NULL,
  course_id     INT            NOT NULL,
  grade_percent DECIMAL(5, 2)  DEFAULT NULL CHECK (grade_percent IS NULL OR grade_percent BETWEEN 0 AND 100),
  enrolled_on   DATE           NOT NULL DEFAULT (CURRENT_DATE),
  CONSTRAINT uq_student_course UNIQUE (student_id, course_id),
  CONSTRAINT fk_enroll_student FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
  CONSTRAINT fk_enroll_course FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);

-- Validate the result
DESCRIBE enrollments;
SHOW CREATE TABLE enrollments;

students and courses are created first since enrollments depends on both of their primary keys. The composite UNIQUE constraint on (student_id, course_id) directly prevents a student from enrolling in the same course twice, while ON DELETE CASCADE ensures enrollment records are automatically cleaned up if a student or course is ever removed. The final DESCRIBE and SHOW CREATE TABLE calls confirm everything was built exactly as designed.

Real-World Examples

  • Every real learning management system (Coursera, Udemy-style platforms) uses this exact students-courses-enrollments pattern at its core, often with many additional columns layered on top.
  • HR systems use the same many-to-many pattern for employees and training programs, or employees and projects.
  • E-commerce platforms use the identical pattern for customers and products via an order_items junction table.
  • Social media platforms use this pattern for users and groups, or users liking posts, via a junction table linking the two entities.

Best Practices and Pro Tips

  • Always build dependent tables in the correct order — parent tables (students, courses) before any table with foreign keys referencing them (enrollments).
  • Add the composite UNIQUE constraint on the junction table's foreign key pair from the very start; forgetting it is one of the most common real-world bugs in many-to-many schemas, leading to silent duplicate relationships.
  • Run DESCRIBE and SHOW CREATE TABLE immediately after creating any new schema to catch typos in column names or missing constraints before any application code or real data depends on them.

Common Mistakes to Avoid

  • Trying to store multiple course IDs directly inside the students table (e.g., a comma-separated list) instead of properly using a junction table — this breaks basic relational design and makes querying painful.
  • Forgetting the composite UNIQUE constraint on the junction table, allowing the same student to be enrolled in the same course multiple times by accident.
  • Creating the enrollments table before students or courses exist, which fails because the foreign keys can't reference tables that don't exist yet.
  • Using VARCHAR for grade_percent instead of DECIMAL, making numeric comparisons and averaging unreliable later.

Interview Questions

Q1. Why does a many-to-many relationship between students and courses require a third table?

Because neither students nor courses alone can represent the relationship without duplicating data or losing the ability to track per-enrollment details like a grade. A junction table like enrollments stores one row per (student, course) pairing, cleanly resolving the many-to-many relationship.

Q2. What constraint prevents a student from enrolling in the same course twice in this schema?

A composite UNIQUE constraint on (student_id, course_id) in the enrollments table, which ensures no two rows can share the same combination of student and course, even though either column individually can repeat across many rows.

Q3. In what order must these three tables be created, and why?

students and courses must be created first, since they have no dependencies. enrollments must be created last, since its foreign keys reference the primary keys of both students and courses, which must already exist.

Q4. Why is grade_percent allowed to be NULL in the enrollments table?

Because a student may be enrolled in a course before being graded — the grade simply doesn't exist yet at enrollment time. Allowing NULL models this real-world state accurately, rather than forcing a placeholder value like 0 that would be misleading.

Practice MCQs

1. What is the purpose of the enrollments table in this schema?

  1. To store course descriptions
  2. To resolve the many-to-many relationship between students and courses
  3. To store student passwords
  4. To replace the students table

Answer: B. To resolve the many-to-many relationship between students and courses

Explanation: enrollments is a junction table that links students and courses, allowing many students to enroll in many courses while tracking per-enrollment details like grade.

2. Which constraint stops the same student from being enrolled in the same course more than once?

  1. NOT NULL on student_id
  2. FOREIGN KEY on course_id
  3. Composite UNIQUE on (student_id, course_id)
  4. DEFAULT on enrolled_on

Answer: C. Composite UNIQUE on (student_id, course_id)

Explanation: Only a composite UNIQUE constraint across both columns together can prevent the exact same student-course pairing from being inserted twice.

3. Why must students and courses be created before enrollments?

  1. Alphabetical order is required by MySQL
  2. enrollments' foreign keys reference their primary keys, which must already exist
  3. MySQL requires tables to be created in groups of two
  4. It's only a stylistic convention, not a requirement

Answer: B. enrollments' foreign keys reference their primary keys, which must already exist

Explanation: A FOREIGN KEY constraint requires the referenced parent table and column to already exist, so dependent tables must always be created after their parents.

Quick Revision Points

  • Many-to-many relationships are resolved using a junction/association table, never by storing lists inside a single column.
  • A composite UNIQUE constraint on the junction table's foreign key pair prevents duplicate relationships.
  • Parent tables must be created before child tables that hold foreign keys referencing them.
  • Nullable columns (like grade_percent) should be allowed NULL when a real-world value genuinely doesn't exist yet.

Conclusion

  • This exercise combines data types, PRIMARY KEY, AUTO_INCREMENT, FOREIGN KEY, CHECK, DEFAULT, and UNIQUE into one cohesive, realistic schema.
  • The students-courses-enrollments pattern generalizes to virtually any many-to-many relationship in real software systems.
  • Validating a finished schema with DESCRIBE and SHOW CREATE TABLE should be a standard final step, not an afterthought.

This practical exercise pulled together every concept from Module 2 into one realistic build: choosing the right data types, defining single and composite primary keys, linking tables with foreign keys and appropriate ON DELETE behavior, and applying CHECK, DEFAULT, and UNIQUE constraints exactly where they matter. The students-courses-enrollments pattern isn't just a classroom example — it's the same shape used by real learning platforms, HR systems, and e-commerce sites every day. With table creation and structure fully mastered, the next module moves on to actually populating and querying this data with SQL's DML commands.

Frequently Asked Questions

Yes, absolutely. A real student management system would likely add an instructors table linked to courses, and possibly a departments table linked to both courses and instructors, each following the same foreign key and constraint patterns covered in this module.

course_id is the internal surrogate key used for relationships and joins, while course_code (like 'CS101') is a human-readable natural identifier that users and instructors actually recognize. Making it UNIQUE prevents two different courses from accidentally sharing the same code.

Because enrollments.student_id uses ON DELETE CASCADE, deleting a student automatically deletes all of their enrollment records too, since an enrollment record referencing a non-existent student would be meaningless.

Not separately — MySQL automatically creates indexes for foreign key columns when the FOREIGN KEY constraint is defined, and the composite UNIQUE constraint already provides an index covering both columns together for that combination.

A query like SELECT course_id, AVG(grade_percent) FROM enrollments GROUP BY course_id; would calculate it directly, joining with courses if course names are needed in the output. This kind of querying is the focus of the upcoming DML and SELECT-focused modules.