Design a School Booking Database

Introduction

30 Second Summary

Parent-teacher meetings are a scheduling nightmare. One school, twenty teachers, hundreds of parents, and each timeslot can only hold one meeting.

In this project, you will design and build a relational database in SQLite that models a school's parent-teacher booking system. You'll create tables with proper constraints that prevent double-bookings and orphaned records at the schema level.

What You'll Build

A complete SQLite database for a single-campus school where admins create meeting time slots, and parents book those slots to meet their children's teachers.

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

• A normalized schema with 6 related tables modelling teachers, parents, children, time slots, and bookings inside a school_bookings.db file.
• Working foreign key constraints with ON DELETE CASCADE and RESTRICT behaviour that prevent invalid bookings and orphaned records.
• A set of tested multi-table JOIN queries that answer real scheduling questions like available slots, a parent's bookings, and a teacher's full schedule.
• Secret Mission: Add a cancellation and waitlist system to handle overbooked slots.

Are there any prerequisites?

This project assumes you know the basics of SQL: SELECT, INSERT, and CREATE TABLE statements. You should also have DB Browser for SQLite installed on your machine.

Before We Start

Before We Start...

Set Up Your Database and Enable Foreign Keys

Before you can design tables and relationships, you need a database file to hold everything. SQLite stores an entire database in a single file on your computer, which makes it easy to move, back up, or start over.

In this step, you will create a fresh database file and flip on a critical safety setting that SQLite leaves off by default. Without this setting, the relationship rules you define later would be silently ignored.

In this step, get ready to:

• Create a new SQLite database file.
• Enable foreign key enforcement for the session.
• Verify that the enforcement is active.

Create your database file

Your database needs a home on disk before you can add anything to it.

• In DB Browser for SQLite, click File in the menu bar.
• Click New Database.
• In the save dialog, navigate to your Desktop (or another folder you can find easily).
• Name the file school_bookings and click Save.
• DB Browser may open a Create Table dialog automatically. Click Cancel to dismiss it. You will create tables using SQL instead.

What just happened?

DB Browser created an empty file called school_bookings.db on your computer. This single file will hold every table, constraint, and row of data for your booking system.

Enable foreign key enforcement

SQLite has foreign key constraints turned off by default for backwards compatibility. Without enabling them, any FOREIGN KEY clause you write is parsed but never enforced.

• Click the Execute SQL tab in the main DB Browser window.
• Enable foreign key enforcement by typing the following command into the SQL editor and pressing Ctrl+Enter (Windows) or Cmd+Enter (macOS) to run it:

-- Enable foreign key enforcement (run at start of every session)
PRAGMA foreign_keys = ON;

What does this command do?

PRAGMA foreign_keys = ON; tells SQLite to actively check every foreign key rule whenever you insert, update, or delete data. You must run this at the start of every session in DB Browser for SQLite, because the setting resets each time you open the database.

• Verify the setting is active by running this command:

PRAGMA foreign_keys;

You should see a result of 1 in the output pane below the editor. A value of 1 means enforcement is on.

Seeing 0 instead of 1?

Make sure you ran PRAGMA foreign_keys = ON; first (with the equals sign and the semicolon). If you typed PRAGMA foreign_keys; without running the ON command beforehand, it will return 0.

Also check that you did not run the PRAGMA inside a transaction. This command is a no-op within a transaction. help me figure out why PRAGMA foreign_keys is returning 0

Why must I do this every session?

SQLite keeps foreign keys off by default to stay compatible with very old applications that relied on unenforced foreign key syntax. The setting is per-connection, so every time DB Browser opens your database file it starts a fresh connection with enforcement disabled.

If you forget this step, your FOREIGN KEY clauses will still be saved in the schema. They just will not prevent bad data from being inserted.

✔️ Awesome, I've got everything!

Great. Make sure you have saved your database file before moving on.

ⓧ I'd like to double check the full code

Here is the complete SQL you should have run so far:

-- Enable foreign key enforcement (run at start of every session)
PRAGMA foreign_keys = ON;

Your database is ready and protected by foreign key enforcement. Next up, you will create the core tables for teachers, parents, and children.

Create the Core Entity Tables

Your database is created and foreign key enforcement is active. Now it's time to define the tables that represent the real-world entities in your booking system.

A school booking system revolves around three core entities: teachers who offer meeting slots, parents who book those slots, and children who connect parents to specific teachers. You'll create a table for each entity, with the children table acting as the bridge between parents and teachers.

In this step, get ready to:

• Create the teachers and parents tables with appropriate constraints.
• Create the children table with foreign key relationships.
• Verify that foreign key constraints reject invalid data.

Create the teachers table

Teachers are the first entity to create because other tables will reference them. Each teacher is responsible for one class, so the class name is marked as UNIQUE to prevent duplicates.

• In the Execute SQL tab, create the teachers table by running this SQL:

-- Core entity tables
CREATE TABLE teachers (
    teacher_id INTEGER PRIMARY KEY,
    full_name TEXT NOT NULL,
    class_name TEXT NOT NULL UNIQUE
);

What does this code do?

• INTEGER PRIMARY KEY makes teacher_id an auto-incrementing unique identifier for each teacher.
• NOT NULL on full_name and class_name means these columns can never be left empty.
• UNIQUE on class_name ensures no two teachers can be assigned the same class.

• You should see a success message in the output panel below the SQL editor.
• Click the Database Structure tab on the left. You should see teachers listed under Tables.

Don't see the table listed?

• Make sure you clicked the Execute SQL play button (or pressed Cmd+Return on macOS / Ctrl+Return on Windows) to actually run the statement.
• Check the output panel for a red error message. A typo in the SQL (missing comma, extra parenthesis) will prevent the table from being created.

Help me figure out why my CREATE TABLE statement isn't working.

Create the parents table

Parents need contact information so the school can send booking confirmations. The email column is UNIQUE because each parent account maps to one email address.

• Return to the Execute SQL tab and create the parents table by running:

CREATE TABLE parents (
    parent_id INTEGER PRIMARY KEY,
    full_name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    phone TEXT
);

What does this code do?

• email is marked NOT NULL UNIQUE so every parent must have an email and no two parents can share one.
• phone has no NOT NULL constraint, making it optional. Parents can be created without a phone number on file.

• Check the output panel for a success message.
• Switch to Database Structure to confirm parents now appears alongside teachers.

Seeing 'table parents already exists'?

This means you ran the statement more than once. The table was already created successfully on the first run. You can safely ignore this error and move on.

Help me understand this 'already exists' error in SQLite.

Create the children table and verify foreign keys

The children table is where relationships come together. Each child belongs to one parent and is enrolled in one teacher's class. Foreign keys enforce these relationships at the database level, preventing orphaned records.

• In the Execute SQL tab, create the children table by running:

CREATE TABLE children (
    child_id INTEGER PRIMARY KEY,
    full_name TEXT NOT NULL,
    parent_id INTEGER NOT NULL,
    teacher_id INTEGER NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES parents(parent_id) ON DELETE CASCADE,
    FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id) ON DELETE RESTRICT
);

What do these foreign keys do?

FOREIGN KEY (parent_id) REFERENCES parents(parent_id) means every parent_id in this table must match an existing row in the parents table. You cannot create a child without a valid parent.

ON DELETE CASCADE on the parent relationship means if a parent record is deleted, all their children records are automatically removed too.

ON DELETE RESTRICT on the teacher relationship means you cannot delete a teacher who still has students assigned. The database blocks the deletion until all children are reassigned.

• Confirm the table was created by switching to Database Structure. You should see all three tables: teachers, parents, and children.
• Now test that the foreign key constraints actually work. Switch back to the Execute SQL tab and run this INSERT that references a non-existent parent:

-- This should FAIL because parent_id 999 does not exist
INSERT INTO children (full_name, parent_id, teacher_id) VALUES ('Test Child', 999, 1);

• You should see the error FOREIGN KEY constraint failed in the output panel. This confirms your database is actively rejecting invalid data.

Why is this error a good thing?

The error proves that your foreign key enforcement is working. Without PRAGMA foreign_keys = ON from the previous step, this INSERT would have silently succeeded and created an orphaned child record pointing to a parent that does not exist.

INSERT succeeded without an error?

If the INSERT went through without failing, foreign key enforcement is not active. This happens when you close and reopen the database without re-running the PRAGMA.

• Run PRAGMA foreign_keys; to check. If it returns 0, run PRAGMA foreign_keys = ON; again.
• Delete the test row with DELETE FROM children WHERE full_name = 'Test Child'; and retry the INSERT.

Help me figure out why my foreign key constraint is not being enforced.

✔️ Awesome, I've got everything!

Great. Make sure you click Write Changes in the toolbar to save the database to disk.

ⓧ I'd like to double check the full code

Here is the complete SQL you should have run across Steps 1 and 2. Compare it against what you have in your Execute SQL history:

-- Enable foreign key enforcement (run at start of every session)
PRAGMA foreign_keys = ON;

-- Core entity tables
CREATE TABLE teachers (
    teacher_id INTEGER PRIMARY KEY,
    full_name TEXT NOT NULL,
    class_name TEXT NOT NULL UNIQUE
);

CREATE TABLE parents (
    parent_id INTEGER PRIMARY KEY,
    full_name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    phone TEXT
);

CREATE TABLE children (
    child_id INTEGER PRIMARY KEY,
    full_name TEXT NOT NULL,
    parent_id INTEGER NOT NULL,
    teacher_id INTEGER NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES parents(parent_id) ON DELETE CASCADE,
    FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id) ON DELETE RESTRICT
);

Your core entity tables are in place with proper constraints guarding data integrity. Next up, you'll create the slots and bookings tables that let parents actually reserve meeting times with teachers.

Create the Slots and Bookings Tables

Your database now knows about teachers, parents, and children. These tables represent who is involved in the booking system.

Now you need to model what they actually do. Teachers offer time slots. Parents book those slots. In this step, you will create the scheduling tables and use CHECK constraints to guarantee that only valid dates and times can enter the database.

In this step, get ready to:

• Create a slots table with date and time validation built into the schema.
• Create a bookings table that prevents double-bookings using a UNIQUE constraint.
• Test that the CHECK constraints reject invalid data.

Create the slots table

Each slot represents a 15-minute window that an admin has opened up for a specific teacher. The table needs to validate that dates and times are in the correct format, and that a slot's end time always comes after its start time.

• In the Execute SQL tab of DB Browser for SQLite, run the following SQL to create the slots table:

-- Scheduling tables
CREATE TABLE slots (
    slot_id INTEGER PRIMARY KEY,
    teacher_id INTEGER NOT NULL,
    slot_date TEXT NOT NULL CHECK (date(slot_date, '+0 days') IS slot_date),
    start_time TEXT NOT NULL CHECK (time(start_time) IS start_time),
    end_time TEXT NOT NULL CHECK (time(end_time) IS end_time),
    CHECK (end_time > start_time),
    FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id) ON DELETE CASCADE
);

What does this code do?

This table stores time slots that admins create for each teacher. Each column has a specific guard:

• CHECK (date(slot_date, '+0 days') IS slot_date) validates that slot_date contains a real date in YYYY-MM-DD format. If you pass an invalid string, SQLite's date() function returns NULL, and NULL IS 'bad-value' evaluates to false, triggering the constraint.
• CHECK (time(start_time) IS start_time) and the matching end_time check validate HH:MM or HH:MM:SS format the same way.
• CHECK (end_time > start_time) prevents someone from creating a slot where the meeting ends before it starts.
• ON DELETE CASCADE on teacher_id means if a teacher is removed from the system, their slots are automatically deleted too.

• Click the Run button (or press Cmd+Return on macOS, Ctrl+Return on Windows).
• Switch to the Database Structure tab. You should see slots listed alongside your three existing tables.

Table not appearing?

• Make sure you highlighted just the CREATE TABLE statement before clicking Run. If previous SQL is still in the editor, DB Browser may try to re-run it.
• Check for a typo in the CHECK expressions. A missing closing parenthesis will cause a syntax error.

Why isn't my slots table being created in DB Browser for SQLite?

Create the bookings table

Each booking connects a parent to a specific time slot for one of their children. The critical design decision here is that a slot can only be booked once.

• In the Execute SQL tab, run the following SQL to create the bookings table:

CREATE TABLE bookings (
    booking_id INTEGER PRIMARY KEY,
    slot_id INTEGER NOT NULL UNIQUE,
    parent_id INTEGER NOT NULL,
    child_id INTEGER NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (slot_id) REFERENCES slots(slot_id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES parents(parent_id) ON DELETE CASCADE,
    FOREIGN KEY (child_id) REFERENCES children(child_id) ON DELETE CASCADE
);

What does this code do?

This table records which parent booked which slot for which child. A few key design choices:

• UNIQUE on slot_id is the simplest way to enforce one-booking-per-slot. If a slot already has a booking, any second INSERT for that same slot_id is rejected by the database.
• DEFAULT (datetime('now')) on created_at automatically records the UTC timestamp when a booking is made. The parentheses are required for expression defaults in SQLite.
• ON DELETE CASCADE on all three foreign keys means that if a slot, parent, or child is deleted, their associated bookings are cleaned up automatically.

• Click Run to execute the statement.
• Switch to the Database Structure tab. You should now see five tables listed: teachers, parents, children, slots, and bookings.

Seeing an error about a missing table?

If you see an error mentioning slots not existing, the slots table from the previous substep was not created successfully. Switch to the Database Structure tab to confirm slots is there before running this statement.

Help me debug why my bookings table isn't being created in DB Browser for SQLite.

Test the CHECK constraints

A schema is only as good as what it rejects. You will now verify that your CHECK constraints actually block invalid data by trying two inserts that should fail.

• In the Execute SQL tab, try inserting a slot with an invalid date by running:

INSERT INTO slots VALUES (1, 1, 'bad-date', '09:00', '09:15');

• You should see an error message containing CHECK constraint failed. This confirms that the date validation CHECK is working.
• Now try inserting a slot where the end time is before the start time:

INSERT INTO slots VALUES (1, 1, '2026-07-10', '09:30', '09:00');

• This should also fail with CHECK constraint failed because 09:00 is not greater than 09:30.

Why use IS instead of = in CHECK constraints?

SQLite's date() function returns NULL when given an invalid string. With the = operator, NULL = 'bad-date' evaluates to NULL (not false), which does not trigger the constraint. Invalid data would slip through.

The IS operator handles NULL properly. NULL IS 'bad-date' evaluates to false, which triggers the constraint and blocks the insert.

Insert succeeded instead of failing?

If an invalid insert went through without an error, check that you created the slots table with the CHECK constraints. Run DROP TABLE slots; and re-create it using the exact SQL from the first substep above.

Why did my invalid INSERT succeed instead of being rejected by the CHECK constraint?

✔️ Awesome, I've got everything!

Great. Make sure you click Write Changes in the toolbar to save the database file to disk.

ⓧ I'd like to double check the full code

Here is the complete schema including all five tables. Compare this against what you see in your Database Structure tab:

-- Enable foreign key enforcement (run at start of every session)
PRAGMA foreign_keys = ON;

-- Core entity tables
CREATE TABLE teachers (
    teacher_id INTEGER PRIMARY KEY,
    full_name TEXT NOT NULL,
    class_name TEXT NOT NULL UNIQUE
);

CREATE TABLE parents (
    parent_id INTEGER PRIMARY KEY,
    full_name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    phone TEXT
);

CREATE TABLE children (
    child_id INTEGER PRIMARY KEY,
    full_name TEXT NOT NULL,
    parent_id INTEGER NOT NULL,
    teacher_id INTEGER NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES parents(parent_id) ON DELETE CASCADE,
    FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id) ON DELETE RESTRICT
);

-- Scheduling tables
CREATE TABLE slots (
    slot_id INTEGER PRIMARY KEY,
    teacher_id INTEGER NOT NULL,
    slot_date TEXT NOT NULL CHECK (date(slot_date, '+0 days') IS slot_date),
    start_time TEXT NOT NULL CHECK (time(start_time) IS start_time),
    end_time TEXT NOT NULL CHECK (time(end_time) IS end_time),
    CHECK (end_time > start_time),
    FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id) ON DELETE CASCADE
);

CREATE TABLE bookings (
    booking_id INTEGER PRIMARY KEY,
    slot_id INTEGER NOT NULL UNIQUE,
    parent_id INTEGER NOT NULL,
    child_id INTEGER NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (slot_id) REFERENCES slots(slot_id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES parents(parent_id) ON DELETE CASCADE,
    FOREIGN KEY (child_id) REFERENCES children(child_id) ON DELETE CASCADE
);

Your schema is now fully built. You have five tables with foreign keys, CHECK constraints, and a UNIQUE rule that prevents double-bookings. Next up, you will populate these tables with sample data and prove that the integrity rules hold up under real inserts.

Populate Sample Data and Test Integrity

Your tables are built, but they are empty. A schema only proves itself when real data flows through it.

In this step you will insert teachers, parents, children, slots, and bookings. Then you will deliberately try to break the rules to confirm that your constraints actually reject bad data.

In this step, get ready to:

• Insert sample rows into all five tables.
• Verify parent-child-teacher relationships with foreign keys.
• Attempt a double-booking and confirm the UNIQUE constraint rejects it.

Insert the sample teachers

Teachers are the first table to populate because other tables (children, slots) reference them via foreign keys.

• In the Execute SQL tab of DB Browser for SQLite, paste and run the following INSERT statement:

INSERT INTO teachers (full_name, class_name) VALUES
    ('Ms. Johnson', 'Year 1A'),
    ('Mr. Patel', 'Year 2B'),
    ('Mrs. Smith', 'Year 3C'),
    ('Mr. Nguyen', 'Year 4D'),
    ('Ms. Garcia', 'Year 5E');

What does this code do?

This inserts five teacher records, each with a unique class name. Because class_name has a UNIQUE constraint, no two teachers can share a class.

SQLite auto-assigns teacher_id values 1 through 5 because the column is an INTEGER PRIMARY KEY.

• Click the Browse Data tab and select teachers from the table dropdown.
• You should see five rows with teacher IDs 1 through 5.

Not seeing any rows?

Make sure you clicked the Execute button (or pressed Cmd+Return on macOS / Ctrl+Return on Windows) after pasting the SQL. Just pasting does not run it.

If you see a duplicate error, you may have run the INSERT twice. Run DELETE FROM teachers; to clear the table, then re-run the INSERT.

Help me debug this

Insert parents and children

Parents must exist before children because the children table has a foreign key pointing to parent_id. Inserting in the wrong order triggers a constraint violation.

• Return to the Execute SQL tab and run this statement to insert three parents:

INSERT INTO parents (full_name, email, phone) VALUES
    ('David Wilson', 'david.wilson@email.com', '07700100001'),
    ('Sarah Chen', 'sarah.chen@email.com', '07700100002'),
    ('James Murphy', 'james.murphy@email.com', '07700100003');

What does this code do?

Each parent gets a unique email (enforced by the UNIQUE constraint on email). The phone column allows NULL, so it is optional.

• Now insert four children by running this statement:

-- Children (David Wilson has two children in different classes)
INSERT INTO children (full_name, parent_id, teacher_id) VALUES
    ('Emma Wilson', 1, 1),
    ('Liam Wilson', 1, 3),
    ('Olivia Chen', 2, 2),
    ('Noah Murphy', 3, 4);

What does this code do?

Notice that Emma Wilson and Liam Wilson both have parent_id = 1 (David Wilson), but different teacher_id values. This models a parent with two children in different classes.

The foreign keys guarantee that every parent_id and teacher_id value exists in the referenced table. If you tried parent_id = 99, the INSERT would fail.

• Switch to the Browse Data tab and select children from the table dropdown.
• You should see four rows. Confirm that Emma and Liam both have parent_id of 1.

Seeing a foreign key constraint error?

If you see an error mentioning a foreign key violation, confirm that you ran the parents INSERT before the children INSERT. The children table references parent_id in the parents table, so parents must exist first.

Also check that PRAGMA foreign_keys is still ON. Run PRAGMA foreign_keys; to verify it returns 1.

Help me figure out what went wrong

Insert slots and bookings, then test the double-booking constraint

Now for the real test. You will insert time slots for booking day, create bookings against those slots, and then deliberately attempt a double-booking to see the UNIQUE constraint in action.

• In the Execute SQL tab, insert four time slots and three bookings by running:

-- Slots for booking day
INSERT INTO slots (teacher_id, slot_date, start_time, end_time) VALUES
    (1, '2026-07-15', '09:00', '09:15'),
    (1, '2026-07-15', '09:15', '09:30'),
    (2, '2026-07-15', '09:00', '09:15'),
    (3, '2026-07-15', '09:00', '09:15');

-- Bookings
INSERT INTO bookings (slot_id, parent_id, child_id) VALUES
    (1, 1, 1),
    (3, 2, 3),
    (4, 1, 2);

What does this code do?

The slots give Ms. Johnson (teacher_id 1) two 15-minute windows, Mr. Patel (teacher_id 2) one slot, and Mrs. Smith (teacher_id 3) one slot.

The bookings assign slot 1 to David Wilson for Emma, slot 3 to Sarah Chen for Olivia, and slot 4 to David Wilson for Liam. David has two bookings because he has two children in different classes.

• Check the Browse Data tab for the bookings table. You should see three rows.
• Now try to double-book slot 1 by running this statement:

-- This should FAIL (slot 1 is already booked):
INSERT INTO bookings (slot_id, parent_id, child_id) VALUES (1, 3, 4);

You should see an error: UNIQUE constraint failed: bookings.slot_id. This proves that your schema prevents two parents from booking the same slot.

Why did this fail?

The bookings table has slot_id INTEGER NOT NULL UNIQUE. Since slot 1 already has a booking, any second INSERT with slot_id = 1 violates that UNIQUE constraint.

This is how you enforce one-booking-per-slot at the database level. No application code needed.

The double-booking INSERT succeeded?

If the failing INSERT went through without an error, check that your bookings table was created with slot_id INTEGER NOT NULL UNIQUE. You can verify by clicking the Database Structure tab and expanding the bookings table.

If the UNIQUE constraint is missing, you will need to drop and recreate the bookings table with the correct definition from the previous step.

Help me figure out why my constraint is not working

✔️ Awesome, I've got everything!

Great. Make sure you click Write Changes in the toolbar to save your data to the school_bookings.db file.

ⓧ I'd like to double check the full code

Here is the complete sample_data.sql with all INSERT statements from this step:

PRAGMA foreign_keys = ON;

-- Teachers
INSERT INTO teachers (full_name, class_name) VALUES
    ('Ms. Johnson', 'Year 1A'),
    ('Mr. Patel', 'Year 2B'),
    ('Mrs. Smith', 'Year 3C'),
    ('Mr. Nguyen', 'Year 4D'),
    ('Ms. Garcia', 'Year 5E');

-- Parents
INSERT INTO parents (full_name, email, phone) VALUES
    ('David Wilson', 'david.wilson@email.com', '07700100001'),
    ('Sarah Chen', 'sarah.chen@email.com', '07700100002'),
    ('James Murphy', 'james.murphy@email.com', '07700100003');

-- Children (David Wilson has two children in different classes)
INSERT INTO children (full_name, parent_id, teacher_id) VALUES
    ('Emma Wilson', 1, 1),
    ('Liam Wilson', 1, 3),
    ('Olivia Chen', 2, 2),
    ('Noah Murphy', 3, 4);

-- Slots for booking day
INSERT INTO slots (teacher_id, slot_date, start_time, end_time) VALUES
    (1, '2026-07-15', '09:00', '09:15'),
    (1, '2026-07-15', '09:15', '09:30'),
    (2, '2026-07-15', '09:00', '09:15'),
    (3, '2026-07-15', '09:00', '09:15');

-- Bookings
INSERT INTO bookings (slot_id, parent_id, child_id) VALUES
    (1, 1, 1),
    (3, 2, 3),
    (4, 1, 2);

Your database now has realistic data flowing through it, and you have proven that the constraints catch invalid operations. Next up, you will write queries that answer real scheduling questions using this data.

Write Real-World Queries

Your database is fully populated with teachers, parents, children, slots, and bookings. The constraints you built are actively preventing bad data from sneaking in.

Now it's time to put that data to work. In this step, you'll write queries that answer the kinds of questions a real booking system needs to handle every day.

In this step, get ready to:

• Find all available slots for a specific date.
• Look up a parent's complete booking history.
• Generate a teacher's full schedule showing booked and open slots.

Find all available slots for a specific date

The most common question in any booking system is "what's still open?" To answer this, you need to find slots that have no matching row in the bookings table.

• In the Execute SQL tab, run the following query to find unbooked slots on 2026-07-15:

-- 1. Find all available (unbooked) slots for a specific date
SELECT s.slot_id, t.full_name AS teacher, s.slot_date, s.start_time, s.end_time
FROM slots s
JOIN teachers t ON s.teacher_id = t.teacher_id
LEFT JOIN bookings b ON s.slot_id = b.slot_id
WHERE b.booking_id IS NULL
  AND s.slot_date = '2026-07-15';

What does this query do?

The LEFT JOIN connects every slot to its booking, but keeps slots even if no booking exists. For unbooked slots, the booking columns come back as NULL.

The WHERE b.booking_id IS NULL filter then keeps only those unmatched rows. This is called an "anti-join" pattern. It finds rows in one table that have no partner in another.

You should see one row in the results: slot_id 2, which is Ms. Johnson's 09:15-09:30 slot.

Seeing zero rows or all four slots?

Zero rows usually means you used JOIN instead of LEFT JOIN. A regular JOIN drops unbooked slots entirely.

All four rows means you may have forgotten the WHERE b.booking_id IS NULL condition. Without it, the query returns every slot regardless of booking status.

Why is my available-slots query returning the wrong number of rows?

Find all bookings for a specific parent

Parents need to see all their upcoming meetings at a glance. This query pulls together data from four tables to show which child, which teacher, and what time.

• Run this query to find all bookings for David Wilson (parent_id = 1):

-- 2. Find all bookings for a specific parent
SELECT b.booking_id, c.full_name AS child, t.full_name AS teacher,
       s.slot_date, s.start_time, s.end_time
FROM bookings b
JOIN children c ON b.child_id = c.child_id
JOIN slots s ON b.slot_id = s.slot_id
JOIN teachers t ON s.teacher_id = t.teacher_id
WHERE b.parent_id = 1;

What does this query do?

This query chains four JOINs starting from bookings. Each JOIN adds a piece of information: the child's name from children, the time from slots, and the teacher's name from teachers.

Notice how the path goes bookings to slots to teachers. The teacher name is not stored on the booking directly. You follow the relationships your foreign keys defined.

You should see two rows. One for Emma Wilson meeting Ms. Johnson, and one for Liam Wilson meeting Mrs. Smith. David has two children in different classes, so he has two bookings.

Seeing only one row?

Check that you inserted both of David Wilson's children in Step 4. Run SELECT * FROM children WHERE parent_id = 1; to confirm two rows exist.

Also confirm both bookings exist by running SELECT * FROM bookings WHERE parent_id = 1;. You should see two rows here as well.

Why is my parent bookings query returning fewer rows than expected?

Find a teacher's full schedule for a day

Teachers need to see their entire day at once. Booked slots should show the parent's name. Open slots should say "Available" so teachers know where gaps exist.

• Run this query to see Ms. Johnson's full schedule for 2026-07-15:

-- 3. Find a teacher's full schedule for a day
SELECT s.start_time, s.end_time,
       CASE WHEN b.booking_id IS NOT NULL THEN p.full_name ELSE 'Available' END AS status
FROM slots s
LEFT JOIN bookings b ON s.slot_id = b.slot_id
LEFT JOIN parents p ON b.parent_id = p.parent_id
WHERE s.teacher_id = 1 AND s.slot_date = '2026-07-15'
ORDER BY s.start_time;

What does this query do?

The CASE expression checks whether a booking exists for each slot. If b.booking_id IS NOT NULL, the slot is booked and we show the parent's name. Otherwise, we display 'Available'.

Both JOINs are LEFT JOIN because we want to keep every slot in the results regardless of whether it has a booking or a parent attached. The ORDER BY s.start_time ensures the schedule reads chronologically.

You should see two rows. The 09:00-09:15 slot shows "David Wilson" as the status. The 09:15-09:30 slot shows "Available".

Seeing NULL instead of Available?

If the status column shows NULL instead of "Available", you may have used a plain SELECT p.full_name without the CASE expression. The CASE is what converts the NULL into the readable "Available" text.

Why is my teacher schedule query showing NULL instead of a name or Available?

✔️ Awesome, I've got everything!

Great. All three queries are working and returning the expected results.

ⓧ I'd like to double check the full code

Here is the complete queries.sql file with all three queries:

PRAGMA foreign_keys = ON;

-- 1. Find all available (unbooked) slots for a specific date
SELECT s.slot_id, t.full_name AS teacher, s.slot_date, s.start_time, s.end_time
FROM slots s
JOIN teachers t ON s.teacher_id = t.teacher_id
LEFT JOIN bookings b ON s.slot_id = b.slot_id
WHERE b.booking_id IS NULL
  AND s.slot_date = '2026-07-15';

-- 2. Find all bookings for a specific parent
SELECT b.booking_id, c.full_name AS child, t.full_name AS teacher,
       s.slot_date, s.start_time, s.end_time
FROM bookings b
JOIN children c ON b.child_id = c.child_id
JOIN slots s ON b.slot_id = s.slot_id
JOIN teachers t ON s.teacher_id = t.teacher_id
WHERE b.parent_id = 1;

-- 3. Find a teacher's full schedule for a day
SELECT s.start_time, s.end_time,
       CASE WHEN b.booking_id IS NOT NULL THEN p.full_name ELSE 'Available' END AS status
FROM slots s
LEFT JOIN bookings b ON s.slot_id = b.slot_id
LEFT JOIN parents p ON b.parent_id = p.parent_id
WHERE s.teacher_id = 1 AND s.slot_date = '2026-07-15'
ORDER BY s.start_time;

Secret mission

Add a Cancellation and Waitlist System

Real booking systems need to handle cancellations gracefully. What happens when a parent cancels and another parent is waiting for that exact slot? In this mission, you'll extend your schema with status tracking and a waitlist table, then write the SQL that promotes waitlisted parents automatically.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your database file for future reference or delete it entirely.

Resources you used:

• school_bookings.db file (local SQLite database with 6 tables).

Keep everything running

No action needed. Choose this if you want to keep experimenting with your database schema, adding more tables, or practising queries.

• Your school_bookings.db file stays wherever you saved it.
• DB Browser for SQLite remains installed on your machine.
• Remember to run PRAGMA foreign_keys = ON; each time you reopen the database in a new session.

Pause - I'll come back to this later

Close DB Browser for SQLite to free up memory. Your database file stays safely on disk for when you return.

• In DB Browser for SQLite, click File, then Close Database.
• Quit DB Browser for SQLite.
• Your school_bookings.db file remains on your computer. To resume, open DB Browser and use File > Open Database to load it again.

Delete - I don't want to use this again

Remove the database file from your computer entirely.

• In DB Browser for SQLite, click File, then Close Database.
• Quit DB Browser for SQLite.
• On macOS, open Finder, navigate to the folder where you saved school_bookings.db, and move the file to the Trash.
• On Windows, open File Explorer, navigate to the folder where you saved school_bookings.db, right-click the file, and select Delete.

Nice Work!

Nice Work!

Nice work! You just designed and built a fully normalized relational database from scratch that models real scheduling complexity.

You've learned how to:

• Designed a normalized relational schema with proper PRIMARY KEY, FOREIGN KEY, CHECK, and UNIQUE constraints that prevent bad data at the database level.
• Enforced referential integrity using ON DELETE CASCADE and RESTRICT to control how deletions ripple through related tables.
• Wrote multi-table JOIN queries that answer real scheduling questions like finding available slots, viewing a parent's bookings, and checking a teacher's daily schedule.
• Secret Mission: Extended the schema with cancellation status tracking and a waitlist promotion system that automatically fills cancelled slots.

Ready to quiz yourself?