Design a School Management Database

Design and build a relational MySQL database for a school from scratch.

Introduction

30 Second Summary

Think about how a school keeps track of hundreds of students, their classes, teachers, and subjects. Without a structured system, all that data becomes a tangled mess of spreadsheets that nobody can search or update reliably.

In this project, you will design and build a complete relational database for a school management system using MySQL. Starting from a blank scenario, you will identify entities, define relationships, and write SQL to create a working database from scratch.

What You'll Build

A fully structured database for Bright Future Academy, a Nigerian secondary school with over 1,000 students, going from business requirements all the way to a working MySQL implementation with 6 related tables.

By the end of this project, you'll have:

  • A business analysis document identifying all entities, attributes, and relationships for a school management system.
  • A working MySQL database called SchoolManagementDB with 6 properly related tables using primary keys, foreign keys, and constraints.
  • A professional ERD screenshot in Crow's Foot notation showing all entities, relationships, and bridge tables.
  • Secret Mission: Prove your relationships work by testing referential integrity enforcement.

Are there any prerequisites?

This project assumes you already have MySQL Server and MySQL Workbench installed on Windows. If you don't have them yet, download the MySQL Installer from the official MySQL website, which bundles both tools together.

Verify Your MySQL Environment

Before designing a database from scratch, you need to confirm your tools are working properly.

In this step, you will run a version check on your MySQL Server and confirm the modeling tool for creating ERDs is accessible.

In this step, get ready to:
  • Confirm MySQL Server is running version 8.0.
  • Verify the ERD modeling tool is accessible in MySQL Workbench.
Confirm your MySQL Server version

Running a version check confirms your server is online and that you have the correct version for this project.

  • In your query tab in MySQL Workbench, type the following query:
SELECT VERSION();
  • Click the lightning bolt icon in the toolbar (or press Ctrl+Shift+Enter) to execute the query.

What does this query do?

SELECT VERSION() is a built-in MySQL function that returns the version number of your running MySQL Server. This confirms both that the server is online and that it is the correct version for this project.

  • Check the result grid below the query editor. You should see a version number starting with 8.0 (for example, 8.0.46).

Not seeing a version number?

If you see a connection error, your MySQL Server may not be running. Open the Windows Services app (search for "Services" in the Start menu), find MySQL80 in the list, and confirm its status is Running. If it is stopped, right-click it and select Start.

help me troubleshoot why my MySQL Server is not responding to queries in MySQL Workbench

Verify the modeling tool is accessible

MySQL Workbench includes a built-in modeling tool that lets you create Entity-Relationship Diagrams (ERDs). You will use this tool later in the project to visually map out your database tables and their relationships.

  • Click File in the top menu bar.
  • Click New Model.

You should see the MySQL Model screen with an EER Diagrams panel. This is where you will build your visual diagram in Step 4.

What is an EER Diagram?

EER stands for Enhanced Entity-Relationship. It is a visual diagram that shows your database tables, their columns, and the lines connecting related tables. MySQL Workbench uses Crow's Foot notation by default to show whether relationships are one-to-many or many-to-many.

  • Close the Model tab by clicking the X on the tab. You will return to this tool in Step 4.

Don't see the EER Diagrams panel?

Make sure you clicked File then New Model (not New Query Tab). The Model screen looks different from the SQL Editor. It has a panel labeled EER Diagrams with an Add Diagram option.

help me find the EER Diagram modeling tool in MySQL Workbench

Your environment is confirmed and ready to go. Next up, you will analyze what data Bright Future Academy needs to track and identify the entities for your database.

Identify Entities and Attributes

Your MySQL environment is confirmed and ready to go. Now it's time to think like a database designer.

Before writing any SQL, you need to understand what data the school actually tracks. Bright Future Academy is a Nigerian secondary school with over 1,000 students, and right now they're managing everything in spreadsheets. Your job is to analyze their data needs and identify the building blocks of a proper relational database.

In this step, get ready to:
  • Identify the 6 entities that the school needs to track.
  • Define the purpose of each entity in one sentence.
  • List all attributes (columns) for each entity.
Think through the school's data needs

A secondary school tracks many things: which departments exist, who teaches, who studies, what classes students belong to, and what subjects are offered. Each of these "things" becomes an entity in your database.

What is an entity?

An entity is a distinct "thing" that a business needs to store information about. Each entity eventually becomes a table in your database.

A good entity represents a single business object. If you find yourself describing two different things (like "teachers and their departments"), that's two entities, not one.

  • Identify the following 6 entities that Bright Future Academy needs:
  • Departments - organize the school academically.
  • Teachers - deliver instruction.
  • Students - enrolled learners.
  • Classes - group students by year and stream.
  • Subjects - courses offered.
  • Enrollments - record which students study which subjects.

Notice that each entity represents one distinct thing. Enrollments might seem unusual because it's not a physical "object" like a student or teacher. It exists to link students to subjects.

Why is Enrollments its own entity?

One student studies many subjects, and one subject is studied by many students. This is a many-to-many relationship. Databases cannot represent many-to-many directly, so you need a bridge table (Enrollments) that sits between the two entities and connects them.

Define attributes for each entity

Each entity has attributes. These are the specific pieces of information you want to store about that entity. Each attribute becomes a column in your table.

  • Write down the following attributes for each entity:

Departments:

  • department_id - unique identifier.
  • department_name - name of the department.

Teachers:

  • teacher_id - unique identifier.
  • first_name, last_name - teacher's name.
  • gender - Male or Female.
  • email - contact email (must be unique).
  • phone - phone number.
  • hire_date - when the teacher joined.
  • department_id - which department the teacher belongs to.

Students:

  • student_id - unique identifier.
  • first_name, last_name - student's name.
  • gender - Male or Female.
  • date_of_birth - student's birthday.
  • email, phone, address - contact information.
  • admission_date - when the student enrolled.
  • class_id - which class the student belongs to.

Classes:

  • class_id - unique identifier.
  • class_name - display name (e.g., "SS2 Science").
  • class_level - the year level (JSS1 through SS3).
  • section - the stream (e.g., Science, Arts).

Subjects:

  • subject_id - unique identifier.
  • subject_name - name of the subject.
  • department_id - which department offers this subject.

Enrollments:

  • enrollment_id - unique identifier.
  • student_id - which student is enrolled.
  • subject_id - which subject they're enrolled in.

Why do some entities share attribute names?

Notice that department_id appears in Departments, Teachers, and Subjects. In Departments, it's the unique identifier. In Teachers and Subjects, it's a reference that points back to the Departments table.

These references are called foreign keys. You'll define them formally in the next step.

Create your Business Analysis Document

Now you'll combine everything into a single document. This is the deliverable that proves you understand the school's data requirements before touching any SQL.

  • Open any text editor or word processor (Notepad, Google Docs, or a blank file in any editor you prefer).
  • Title the document "Business Analysis Document - Bright Future Academy".
  • For each of the 6 entities, write three things: the entity name, its purpose (one sentence), and its complete attribute list.

Your document should follow this format for each entity:

Entity: Departments
Purpose: Organize the school academically.
Attributes: department_id, department_name

Entity: Teachers
Purpose: Deliver instruction.
Attributes: teacher_id, first_name, last_name, gender, email, phone, hire_date, department_id

Entity: Students
Purpose: Enrolled learners.
Attributes: student_id, first_name, last_name, gender, date_of_birth, email, phone, address, admission_date, class_id

Entity: Classes
Purpose: Group students by year and stream.
Attributes: class_id, class_name, class_level, section

Entity: Subjects
Purpose: Courses offered.
Attributes: subject_id, subject_name, department_id

Entity: Enrollments
Purpose: Record which students study which subjects.
Attributes: enrollment_id, student_id, subject_id

What makes this a good business analysis?

Each entity represents a single business object. The purposes are clear and non-overlapping. Every attribute stores a single piece of information about that entity.

If you were tempted to add "subjects" as an attribute of Students (storing "Physics, Chemistry, Biology" in one field), that would mix concerns. The Enrollments bridge table handles that relationship cleanly.

  • Save your document.
  • Verify that you have exactly 6 entities listed, each with a purpose and a complete set of attributes matching the format above.

Not sure if your attributes are correct?

Double-check that Departments has only 2 attributes, Teachers has 8, Students has 10, Classes has 4, Subjects has 3, and Enrollments has 3. If your counts don't match, compare your list against the format above.

A common mistake is forgetting the linking attributes (department_id in Teachers, class_id in Students). These are essential for connecting tables together later.

Help me check my entity attributes are complete.

✔️ Awesome, I've got everything!

Great. Make sure you've saved your document. You'll reference it throughout the rest of this project.

ⓧ I'd like to double check the full document

Here's the complete Business Analysis Document for comparison:

Business Analysis Document - Bright Future Academy
===================================================

Entity: Departments
Purpose: Organize the school academically.
Attributes: department_id, department_name

Entity: Teachers
Purpose: Deliver instruction.
Attributes: teacher_id, first_name, last_name, gender, email, phone, hire_date, department_id

Entity: Students
Purpose: Enrolled learners.
Attributes: student_id, first_name, last_name, gender, date_of_birth, email, phone, address, admission_date, class_id

Entity: Classes
Purpose: Group students by year and stream.
Attributes: class_id, class_name, class_level, section

Entity: Subjects
Purpose: Courses offered.
Attributes: subject_id, subject_name, department_id

Entity: Enrollments
Purpose: Record which students study which subjects.
Attributes: enrollment_id, student_id, subject_id

You now have a complete picture of what data the school needs to store. Next up, you'll define the keys and relationships that connect these entities together, and verify your design follows normalization rules.

Design the Database and Analyze Normalization

Your Business Analysis Document gives you a clear picture of what data the school tracks. Now you need to define how that data connects together.

In this step, you will identify every key and relationship in your schema, then prove your design follows proper normalization rules. This is the blueprint that ensures your SQL code in the next steps will actually work.

In this step, get ready to:
  • Identify primary keys, candidate keys, and foreign keys for all 6 tables.
  • Determine the type of each relationship and identify the bridge table.
  • Write a normalization analysis explaining how your design satisfies First Normal Form.
Identify keys for each table

Every table needs a way to uniquely identify each row. That's the job of a primary key. Your schema also has columns that could serve as unique identifiers (candidate keys) and columns that link tables together (foreign keys).

  • Open your Database Design Document (a new text file or document you'll write alongside your Business Analysis).
  • Document the Primary Keys for all 6 tables: department_id, teacher_id, student_id, class_id, subject_id, enrollment_id. Note that all are AUTO_INCREMENT integers.
  • Document the Candidate Keys (columns that could also uniquely identify rows): departments(department_name), teachers(email), students(email), classes(class_name), subjects(subject_name).
  • Document the Foreign Keys (columns that reference another table's primary key): teachers.department_id references departments(department_id), students.class_id references classes(class_id), subjects.department_id references departments(department_id), enrollments.student_id references students(student_id), enrollments.subject_id references subjects(subject_id).

What is AUTO_INCREMENT?

AUTO_INCREMENT tells MySQL to automatically assign the next number in the sequence whenever a new row is inserted. Sequences begin with 1.

This means you never have to manually assign IDs. MySQL handles it for you, guaranteeing every row gets a unique primary key.

What is the difference between a primary key and a candidate key?

A candidate key is any column (or set of columns) that could uniquely identify a row. For example, no two teachers can share the same email address, so email is a candidate key.

The primary key is the one candidate key you choose as the official row identifier. You chose integer IDs because they are short, fast to index, and never change (unlike an email that a teacher might update).

Determine relationships between tables

A foreign key creates a relationship between two tables. The table with the foreign key is the child and the table it references is the parent. Now you need to classify each relationship by type.

  • In your Database Design Document, add a Relationships section.
  • Document these One-to-Many (1:M) relationships: Department to Teachers (one department has many teachers), Department to Subjects (one department offers many subjects), Class to Students (one class contains many students).
  • Document the Many-to-Many (M:M) relationship: Students to Subjects (one student studies many subjects, and one subject is studied by many students).
  • Note that the M:M relationship requires a bridge table called enrollments to connect the two tables.
  • List the parent tables: departments, classes, students, subjects. List the child tables: teachers, students, subjects, enrollments.

Why does a Many-to-Many relationship need a bridge table?

A relational database cannot directly store a Many-to-Many relationship. If you tried to put multiple subject IDs inside the students table (like "Physics, Chemistry, Biology" in one column), you could never query or update individual subjects cleanly.

The enrollments bridge table solves this by storing one row per student-subject pairing. Each row has a foreign key to students and a foreign key to subjects. This turns one M:M into two clean 1:M relationships.

Why can students appear in both the parent and child lists?

The students table is a child of classes (each student belongs to a class) and a parent of enrollments (each enrollment references a student). A table can play both roles depending on which relationship you are looking at.

Write your normalization analysis

Normalization is a set of rules that keep your database free of redundancy and inconsistency. Your design needs to satisfy First Normal Form (1NF) at minimum. This means three conditions must be true for every table.

  • Create a Normalization Analysis Document (a new section in your design notes or a separate file).
  • Explain the three requirements of 1NF: each column contains only atomic (indivisible) values, each row is unique (has a primary key), and there are no repeating groups.
  • Prove your design satisfies each requirement. Every attribute stores a single value (not lists). Every table has an AUTO_INCREMENT primary key ensuring uniqueness. There are no repeating groups because subjects are stored in the separate enrollments table, not as a comma-separated list in the students table.
  • Add a note about redundancy reduction. Your design separates departments from teachers so that department information is stored once, not repeated for every teacher in that department.

What would happen without normalization?

Imagine storing subjects directly inside the students table as "Physics, Chemistry, Biology" in one column. You could never search for all students studying Physics without parsing every row's text. Updating a subject name would mean editing hundreds of rows instead of one.

Normalization forces you to move repeated data into its own table and link it with keys. This makes queries fast, updates safe, and your data consistent.

Review your three documents. Your Business Analysis identifies the entities, your Database Design Document defines all keys and relationships, and your Normalization Analysis proves the design follows 1NF rules.

Unsure if your documents are complete?

Double-check that your Database Design Document lists all 6 primary keys, 5 candidate keys, and 5 foreign keys. Your Normalization Analysis should explicitly mention atomic values, primary keys for uniqueness, and the enrollments bridge table as proof against repeating groups.

Help me check my database design document for completeness.

Your schema design is locked in. You know exactly which keys link your tables and why the structure is sound. Next up, you will turn this blueprint into a visual ERD using MySQL Workbench's modeling tool.

Create the ERD in MySQL Workbench

Your design documents define the structure of your database in words. The next challenge is turning that structure into a visual diagram that shows exactly how your 6 tables relate to each other.

An Entity-Relationship Diagram (ERD) gives you a bird's-eye view of your entire schema. In this step, you'll build one using MySQL Workbench's built-in modeling tool and Crow's Foot notation to show relationships.

In this step, get ready to:
  • Open a new EER Diagram canvas in MySQL Workbench.
  • Add all 6 tables with their columns, data types, and constraints.
  • Draw relationships between tables and save the model.
Open a new EER Diagram

The EER Diagram canvas is where you'll visually arrange your tables and draw relationship lines between them.

  • In MySQL Workbench, click File in the top menu bar.
  • Click New Model.
  • On the MySQL Model screen, double-click Add Diagram in the EER Diagrams panel.
  • A blank white canvas opens. This is your EER Diagram workspace.
  • Verify the relationship notation is set to Crow's Foot by clicking the Model menu, then Relationship Notation.
  • Confirm that Crow's Foot (IE) is selected. It is the default, so it should already have a checkmark.

What is Crow's Foot notation?

Crow's Foot notation uses symbols at the ends of relationship lines to show cardinality. A "crow's foot" (three-pronged fork) means "many" and a single line means "one." This makes it easy to see at a glance whether a relationship is one-to-many or many-to-many.

Add tables to the canvas

Each table from your Business Analysis Document becomes a visual object on the canvas. You'll place all 6 tables and configure their columns using the table editor.

  • Select the table tool in the vertical toolbar on the left side of the canvas. It looks like a rectangular grid icon. Hovering over it shows the tooltip Place a New Table.
  • Click anywhere on the canvas to place your first table.
  • Double-click the new table on the canvas to open the table editor at the bottom of the screen.
  • In the Name field of the table editor, change the default name (table1) to departments.

Now add the columns for the departments table using the Columns tab in the table editor.

  • Click the Column Name field in an empty row and type department_id.
  • Set the Datatype to INT.
  • Check the PK (Primary Key), NN (Not Null), and AI (Auto Increment) checkboxes for this column.
  • Click the next empty row and type department_name.
  • Set the Datatype to VARCHAR(100).
  • Check the NN and UQ (Unique) checkboxes for this column.

What do the checkboxes mean?

PK marks the column as the primary key. NN means the column cannot be left empty. UQ prevents duplicate values. AI automatically generates a unique number for each new row.

These match the constraints you defined in your Database Design Document. The table editor applies them visually so you can see them on the ERD.

Repeat this process for the remaining 5 tables. Use the table tool to place each new table on the canvas, then double-click it to configure its columns. Use the reference below for all column details.

Complete column reference for all 6 tables

departments: department_id INT (PK, NN, AI), department_name VARCHAR(100) (NN, UQ).

classes: class_id INT (PK, NN, AI), class_name VARCHAR(50) (NN, UQ), class_level ENUM('JSS1','JSS2','JSS3','SS1','SS2','SS3') (NN), section VARCHAR(20).

teachers: teacher_id INT (PK, NN, AI), first_name VARCHAR(50) (NN), last_name VARCHAR(50) (NN), gender ENUM('Male','Female') (NN), email VARCHAR(100) (NN, UQ), phone VARCHAR(15), hire_date DATE (NN), department_id INT (NN).

students: student_id INT (PK, NN, AI), first_name VARCHAR(50) (NN), last_name VARCHAR(50) (NN), gender ENUM('Male','Female') (NN), date_of_birth DATE (NN), email VARCHAR(100) (UQ), phone VARCHAR(15), address VARCHAR(255), admission_date DATE (NN), class_id INT (NN).

subjects: subject_id INT (PK, NN, AI), subject_name VARCHAR(100) (NN, UQ), department_id INT (NN).

enrollments: enrollment_id INT (PK, NN, AI), student_id INT (NN), subject_id INT (NN).

Spread out your tables on the canvas

Place parent tables (departments, classes) toward the top and child tables (teachers, students, subjects, enrollments) toward the bottom. This makes the relationship lines easier to read later.

  • After placing and configuring all 6 tables, verify that each table on the canvas shows its columns listed beneath the table name.

Table not showing columns on the canvas?

Make sure you pressed Enter or clicked away after typing each column name. If a column is missing, double-click the table on the canvas to reopen the editor and add it.

Also confirm you are adding columns in the Columns tab of the table editor, not the Indexes tab.

Help me add columns to a table in MySQL Workbench's EER diagram.

Draw relationships and save the model

With all tables on the canvas, you can now draw the foreign key relationships that connect child tables to their parent tables.

  • In the vertical toolbar on the left side of the canvas, find the relationship tools (they look like lines connecting two shapes).
  • Select the 1:n Non-Identifying Relationship tool. Hovering over it shows its tooltip.
  • To draw a relationship, click the child table first (the "many" side), then click the parent table (the "one" side).

Draw the following 5 relationships one at a time:

  • Click teachers, then click departments. This creates the "one department has many teachers" relationship.
  • Click students, then click classes. This creates the "one class has many students" relationship.
  • Click subjects, then click departments. This creates the "one department has many subjects" relationship.
  • Click enrollments, then click students. This creates the "one student has many enrollments" relationship.
  • Click enrollments, then click subjects. This creates the "one subject has many enrollments" relationship.

Why click the child table first?

The child table is the one that holds the foreign key column. When you click the child first, MySQL Workbench automatically creates a foreign key in that table pointing to the parent's primary key.

After drawing each line, you'll see a dashed line with a Crow's Foot symbol at the "many" end. Dashed lines indicate non-identifying relationships, meaning the foreign key is not part of the child's primary key.

  • After drawing all 5 relationships, verify that each relationship line shows the Crow's Foot (fork) symbol at the child table end.
  • Save your model by pressing Ctrl+S.
  • In the save dialog, name the file SchoolManagementDB and save it to a location you can find later. The file will be saved as SchoolManagementDB.mwb.
  • Take a screenshot of the completed ERD showing all 6 tables, their columns, primary keys, foreign keys, and the Crow's Foot relationship lines.

Relationship line not appearing?

Make sure you selected the 1:n Non-Identifying Relationship tool before clicking the tables. The tool must be active (highlighted) for each new relationship you draw.

If you accidentally clicked the wrong table first, press Ctrl+Z to undo and try again. Remember: child table first, parent table second.

Help me draw relationships between tables in MySQL Workbench's EER Diagram.

Your ERD is complete and saved. Next up, you'll turn this visual design into a real working database by writing the SQL to create all 6 tables.

Create the Database and Tables in SQL

Your ERD now shows exactly how your 6 tables connect to each other. The visual blueprint is complete.

Now it's time to turn that blueprint into a real, working database. In this step, you'll write SQL statements that create the SchoolManagementDB database and all 6 tables with their constraints.

In this step, get ready to:
  • Create the SchoolManagementDB database and select it for use.
  • Write and execute CREATE TABLE statements for all 6 tables with proper constraints.
  • Verify the tables exist and their structure matches your design.
Create the database and select it

Before you can create tables, you need a database to hold them. The CREATE DATABASE statement makes a new empty database, and USE tells MySQL that all following statements should run inside it.

  • In MySQL Workbench, open a new query tab by clicking the **+** icon next to any existing tab (or press Ctrl+T).
  • Type the following SQL into the query editor:
-- Create the school management database
CREATE DATABASE SchoolManagementDB;

-- Select it for use in this session
USE SchoolManagementDB;
  • Select all the text and click the lightning bolt icon in the toolbar (or press Ctrl+Shift+Enter) to execute both statements.

What does this code do?

  • CREATE DATABASE SchoolManagementDB creates a new empty database on your MySQL Server.
  • USE SchoolManagementDB switches your session to that database so all subsequent CREATE TABLE statements are placed inside it.
  • Check the Action Output panel at the bottom of the screen. You should see a green checkmark next to each statement, confirming both executed successfully.

Seeing an error instead of a green checkmark?

  • If you see an error mentioning the database already exists, a previous attempt already created it. Run DROP DATABASE SchoolManagementDB; first, then re-execute both statements.
  • If you see an access denied error, confirm you connected with the root user in Step 1.

Why can't I create my database?

Write CREATE TABLE statements for all 6 tables

Table creation order matters when you use foreign keys. A child table cannot reference a parent table that doesn't exist yet. You'll create parent tables first, then child tables, then the bridge table.

Why does the order matter?

A FOREIGN KEY constraint tells MySQL that a column's value must match an existing value in another table. If you tried to create the teachers table before the departments table, MySQL would reject it because the referenced departments table wouldn't exist yet.

The correct order for this schema is: departments, classes (no foreign key dependencies), then teachers, students, subjects (each references a parent), then enrollments (references both students and subjects).

  • Clear the query editor, then type the following SQL to create the two parent tables that have no foreign key dependencies:
-- Parent Table 1: Departments
CREATE TABLE departments (
    department_id INT NOT NULL AUTO_INCREMENT,
    department_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (department_id),
    UNIQUE (department_name)
) ENGINE=InnoDB;

-- Parent Table 2: Classes
CREATE TABLE classes (
    class_id INT NOT NULL AUTO_INCREMENT,
    class_name VARCHAR(50) NOT NULL,
    class_level ENUM('JSS1', 'JSS2', 'JSS3', 'SS1', 'SS2', 'SS3') NOT NULL,
    section VARCHAR(20),
    PRIMARY KEY (class_id),
    UNIQUE (class_name)
) ENGINE=InnoDB;

What does this code do?

  • INT NOT NULL AUTO_INCREMENT creates an integer column that automatically assigns the next number for each new row. It can never be empty.
  • PRIMARY KEY marks the column that uniquely identifies each row.
  • UNIQUE prevents duplicate values. No two departments can share the same name.
  • ENUM restricts the column to a predefined list of values. The class_level column only accepts valid Nigerian secondary school levels (JSS1 through SS3).
  • ENGINE=InnoDB explicitly sets the storage engine to InnoDB, which is the only engine that enforces foreign key constraints.
  • Select both CREATE TABLE statements and execute them (Ctrl+Shift+Enter).
  • Confirm you see two green checkmarks in the Action Output panel.

Seeing a syntax error?

  • Check for missing commas between column definitions. Each column line ends with a comma except the last constraint line.
  • Make sure the ENUM values are enclosed in single quotes, not double quotes.

Help me fix my CREATE TABLE syntax error.

  • Below the parent tables, add the following SQL to create the three child tables that reference the parents:
-- Child Table 1: Teachers (references departments)
CREATE TABLE teachers (
    teacher_id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender ENUM('Male', 'Female') NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(15),
    hire_date DATE NOT NULL,
    department_id INT NOT NULL,
    PRIMARY KEY (teacher_id),
    UNIQUE (email),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
) ENGINE=InnoDB;

-- Child Table 2: Students (references classes)
CREATE TABLE students (
    student_id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender ENUM('Male', 'Female') NOT NULL,
    date_of_birth DATE NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(15),
    address VARCHAR(255),
    admission_date DATE NOT NULL,
    class_id INT NOT NULL,
    PRIMARY KEY (student_id),
    UNIQUE (email),
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
) ENGINE=InnoDB;

-- Child Table 3: Subjects (references departments)
CREATE TABLE subjects (
    subject_id INT NOT NULL AUTO_INCREMENT,
    subject_name VARCHAR(100) NOT NULL,
    department_id INT NOT NULL,
    PRIMARY KEY (subject_id),
    UNIQUE (subject_name),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
) ENGINE=InnoDB;

What does this code do?

  • FOREIGN KEY (department_id) REFERENCES departments(department_id) creates a link between the child table and the parent table. Every teacher must belong to a department that already exists in the departments table.
  • Columns without NOT NULL (like phone, address, and email in students) are optional fields that can be left empty.
  • The DATE data type stores dates in YYYY-MM-DD format.
  • Select only the three new CREATE TABLE statements and execute them.
  • Confirm three green checkmarks appear in the Action Output panel.

Foreign key constraint error?

  • If you see an error about a referenced table not existing, make sure you already executed the parent tables (departments and classes) in the previous chunk.
  • Check that the column name in REFERENCES matches exactly. For example, REFERENCES departments(department_id) must match the column name in the departments table.

Help me fix my foreign key error.

  • Finally, add the bridge table that resolves the many-to-many relationship between students and subjects:
-- Bridge Table: Enrollments (references students and subjects)
CREATE TABLE enrollments (
    enrollment_id INT NOT NULL AUTO_INCREMENT,
    student_id INT NOT NULL,
    subject_id INT NOT NULL,
    PRIMARY KEY (enrollment_id),
    UNIQUE (student_id, subject_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (subject_id) REFERENCES subjects(subject_id)
) ENGINE=InnoDB;

What does this code do?

The enrollments table is a bridge table that connects students to subjects. It has two foreign keys pointing to different parent tables.

  • UNIQUE (student_id, subject_id) prevents a student from being enrolled in the same subject twice. The combination must be unique, not each column individually.
  • This single table replaces what would otherwise be a messy comma-separated list of subjects inside the students table.
  • Select the enrollments CREATE TABLE statement and execute it.
  • Confirm one green checkmark appears in the Action Output panel.

Enrollments table won't create?

  • This table depends on both the students and subjects tables. If either doesn't exist yet, you'll get a foreign key error. Run SHOW TABLES; to confirm all 5 previous tables exist.
  • Make sure you spelled students and subjects in the REFERENCES clause exactly as you named the tables above.

Help me debug my enrollments table creation.

  • Save the complete SQL script by clicking File, then Save Script As. Name it create_school_database.sql and save it to your Desktop.

✔️ Awesome, I've got everything!

Great. Make sure the file is saved. You now have a permanent record of your database creation script.

ⓧ I'd like to double check the full code

Here is the complete create_school_database.sql file. Compare your script against this:

-- =============================================
-- TASK 5: CREATE THE DATABASE
-- =============================================

CREATE DATABASE SchoolManagementDB;

USE SchoolManagementDB;

-- =============================================
-- TASK 6: CREATE ALL TABLES
-- =============================================

-- Parent Table 1: Departments
CREATE TABLE departments (
    department_id INT NOT NULL AUTO_INCREMENT,
    department_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (department_id),
    UNIQUE (department_name)
) ENGINE=InnoDB;

-- Parent Table 2: Classes
CREATE TABLE classes (
    class_id INT NOT NULL AUTO_INCREMENT,
    class_name VARCHAR(50) NOT NULL,
    class_level ENUM('JSS1', 'JSS2', 'JSS3', 'SS1', 'SS2', 'SS3') NOT NULL,
    section VARCHAR(20),
    PRIMARY KEY (class_id),
    UNIQUE (class_name)
) ENGINE=InnoDB;

-- Child Table 1: Teachers (references departments)
CREATE TABLE teachers (
    teacher_id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender ENUM('Male', 'Female') NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(15),
    hire_date DATE NOT NULL,
    department_id INT NOT NULL,
    PRIMARY KEY (teacher_id),
    UNIQUE (email),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
) ENGINE=InnoDB;

-- Child Table 2: Students (references classes)
CREATE TABLE students (
    student_id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender ENUM('Male', 'Female') NOT NULL,
    date_of_birth DATE NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(15),
    address VARCHAR(255),
    admission_date DATE NOT NULL,
    class_id INT NOT NULL,
    PRIMARY KEY (student_id),
    UNIQUE (email),
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
) ENGINE=InnoDB;

-- Child Table 3: Subjects (references departments)
CREATE TABLE subjects (
    subject_id INT NOT NULL AUTO_INCREMENT,
    subject_name VARCHAR(100) NOT NULL,
    department_id INT NOT NULL,
    PRIMARY KEY (subject_id),
    UNIQUE (subject_name),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
) ENGINE=InnoDB;

-- Bridge Table: Enrollments (references students and subjects)
CREATE TABLE enrollments (
    enrollment_id INT NOT NULL AUTO_INCREMENT,
    student_id INT NOT NULL,
    subject_id INT NOT NULL,
    PRIMARY KEY (enrollment_id),
    UNIQUE (student_id, subject_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (subject_id) REFERENCES subjects(subject_id)
) ENGINE=InnoDB;
Verify all tables were created successfully
  • Confirm all 6 tables exist by running this query:
SHOW TABLES;
  • You should see 6 rows in the result grid: classes, departments, enrollments, students, subjects, teachers (listed alphabetically).
  • Now verify the structure of a parent table and the bridge table by running these queries one at a time:
DESCRIBE departments;

DESCRIBE enrollments;

What should I see?

DESCRIBE departments should show 2 columns: department_id (int, PRI key, auto_increment) and department_name (varchar(100), UNI key).

DESCRIBE enrollments should show 3 columns: enrollment_id (int, PRI key, auto_increment), student_id (int, MUL key), and subject_id (int, MUL key). MUL means the column is part of a non-unique index, which MySQL creates automatically for foreign key columns.

SHOW TABLES returns fewer than 6 rows?

  • Make sure you ran USE SchoolManagementDB; before running SHOW TABLES. Without it, you may be looking at a different database.
  • If a table is missing, scroll back through the Action Output panel to find which CREATE TABLE statement failed. Fix the error and re-execute only that statement.

Help me figure out why my tables are missing.

Secret mission

Establish and Verify Relationships (Task 7)

Your tables have foreign key constraints defined, but do they actually work? In this secret mission, you will prove that MySQL enforces referential integrity by attempting invalid inserts that should fail. You will also reverse engineer a live ERD from your populated database.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally on MySQL Server, so there are no ongoing costs.

Resources you used:

  • SchoolManagementDB database (in MySQL Server).
  • SchoolManagementDB.mwb model file.
  • SQL script files (create_school_database.sql, verify_relationships.sql).
  • Documentation files (Business Analysis, Database Design, Normalization Analysis).

Keep everything running

No action needed. Choose this if you are still actively building on your database or plan to extend it with more tables and queries.

  • Your SchoolManagementDB database stays in MySQL Server, ready for new queries any time you connect.
  • Your .mwb model file and SQL scripts remain available for reference or future modifications.
  • MySQL Server continues running as a background service on Windows. It uses minimal resources when idle.

Pause - I'll come back to this later

Stop MySQL Server to free up system memory while keeping all your data intact for later.

  • Press the Windows key and type Services, then press Enter.
  • Scroll down to find MySQL80 in the services list.
  • Right-click it and select Stop.
  • When you are ready to resume, return to the same screen, right-click MySQL80, and select Start.

Delete - I don't want to use this again

Remove the database and all project files completely. This cannot be undone.

  • Drop the database by opening MySQL Workbench, connecting to your server, and executing the following SQL:
DROP DATABASE SchoolManagementDB;
  • Delete the model file. Open File Explorer and navigate to wherever you saved SchoolManagementDB.mwb. Right-click and select Delete.
  • Delete your SQL script files (create_school_database.sql and verify_relationships.sql) the same way.
  • Delete your documentation files (Business Analysis, Database Design, and Normalization Analysis documents).

Nice Work!

Nice Work!

You just designed and built a complete relational database from scratch. Starting from a blank scenario, you thought like a database designer to identify entities, define relationships, and implement a working schema in MySQL.

You've learned how to:

  • Design a relational schema from business requirements, identifying entities, attributes, primary keys, candidate keys, foreign keys, and a bridge table to resolve a many-to-many relationship.
  • Create a professional ERD in MySQL Workbench using Crow's Foot notation to visually communicate database structure.
  • Implement a working database with 6 properly related tables using CREATE DATABASE, CREATE TABLE, and constraints like NOT NULL, UNIQUE, AUTO_INCREMENT, and FOREIGN KEY in MySQL.
  • Secret Mission: Verified referential integrity enforcement by testing constraint violations and reverse-engineering a live ERD from your database.

Ready to quiz yourself?