Checkpoint: Building a Schema

You've learned data types, CREATE / ALTER TABLE , constraints, and DROP / TRUNCATE / DELETE — let's design a real schema. This checkpoint pulls every recent lesson together into one hands-on build: a small, properly normalized bookshop database, populated and queried end to end.

Learn Checkpoint: Building a Schema in our free SQL course — a beginner-friendly interactive lesson with runnable queries, a practice exercise and a quick…

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

No new syntax here — just the satisfying part where it all clicks. Work through the three build steps, reveal the solutions to check yourself, then test your understanding on the quiz.

Everything in this checkpoint draws on the lessons you just finished. Here's the toolkit you're bringing to the build:

We'll model a tiny bookshop with two one-to-many relationships: one author writes many books, and one book has many sales.

Notice each fact lives in exactly one place — an author's name is stored once, and books point to it by id. That's normalization.

Fill in the blanks to define all three tables. Use NOT NULL for required fields, DECIMAL(8,2) for the price, a CHECK so quantities are at least 1, and FOREIGN KEY s to wire the tables together.

Key points: price is DECIMAL(8,2) (never FLOAT), each child table carries the foreign key, and the CHECK (qty >= 1) blocks zero or negative sales.

Foreign keys mean order is everything: insert authors first, then books (which reference authors), then sales (which reference books). Try it, then reveal to confirm.

The payoff: a single query that joins all three tables, multiplies price by quantity, and totals revenue per author. This is exactly the kind of report a well-designed schema makes easy.

A teammate scrambled the build script. Put the statements in an order that runs without a single foreign-key error:

1) Which table carries the author_id foreign key — authors or books?

books . The FK lives on the "many" side: one author has many books, so each book points back to its author.

DECIMAL stores exact base-10 money; FLOAT is approximate and accumulates rounding errors that corrupt revenue totals.

Inserting a sale with a quantity of 0 or a negative number — nonsensical data the database now refuses.

Six questions covering the whole DDL block. Think first, then reveal.

1) You try to drop the books table but it errors. Why?

The sales table has a foreign key referencing books . Drop sales first, or use DROP TABLE books CASCADE (carefully).

2) You want to empty sales for a fresh test run and reset its ids. DELETE or TRUNCATE?

TRUNCATE TABLE sales — it's fast and resets the auto-increment counter, which DELETE does not.

3) You need to add a genre column to books without recreating it. Which command?

ALTER TABLE books ADD COLUMN genre VARCHAR(50); — existing rows get NULL unless you add a DEFAULT.

4) Inserting a book with author_id = 99 fails. What constraint caught it?

The FOREIGN KEY on books.author_id . No author 99 exists, so referential integrity rejects the insert.

5) Two authors accidentally share the same id in your INSERT. What stops it?

The PRIMARY KEY on authors.id — it's unique and NOT NULL, so the duplicate is rejected.

6) Why store authors in a separate table instead of repeating the author name on every book row?

Normalization: storing each author once avoids duplication and inconsistency, and lets you update a name in a single place. Books reference the author by id.

Practice quiz

In the bookshop schema, which table carries the 'author_id' foreign key?

  • authors
  • sales
  • books
  • A separate join table

Answer: books. The foreign key lives on the 'many' side: one author has many books, so each book row carries author_id pointing back to its author.

Why is 'price DECIMAL(8,2)' the right choice instead of FLOAT for money?

  • DECIMAL stores exact base-10 values; FLOAT is approximate and accumulates rounding errors
  • DECIMAL is faster to query
  • FLOAT cannot store decimals at all
  • DECIMAL uses less storage than FLOAT

Answer: DECIMAL stores exact base-10 values; FLOAT is approximate and accumulates rounding errors. DECIMAL stores exact base-10 money, while FLOAT is approximate and accumulates rounding errors that corrupt revenue totals.

What does 'CHECK (qty >= 1)' on the sales table prevent?

  • Inserting more than one sale per book
  • Selling a book that does not exist
  • Duplicate sale ids
  • Inserting a sale with a quantity of 0 or a negative number

Answer: Inserting a sale with a quantity of 0 or a negative number. A CHECK constraint rejects rows that violate the condition, so qty >= 1 blocks zero or negative quantities.

Why must you insert authors before books, and books before sales?

  • Alphabetical convenience
  • A foreign key requires the referenced parent row to already exist
  • Inserts are faster in that order
  • It does not matter; order is irrelevant

Answer: A foreign key requires the referenced parent row to already exist. A foreign key requires its referenced parent to exist first, so you insert parents (authors), then children (books), then grandchildren (sales).

You try to DROP the 'books' table and it errors. Why?

  • The sales table has a foreign key referencing books
  • books has too many rows
  • You cannot drop any table that has a primary key
  • books is currently open in another query

Answer: The sales table has a foreign key referencing books. sales references books via a foreign key, so you must drop sales first or use DROP TABLE books CASCADE (carefully).

Inserting a book with 'author_id = 99' fails. Which constraint caught it?

  • The PRIMARY KEY on books.id
  • A CHECK constraint
  • The FOREIGN KEY on books.author_id
  • The NOT NULL on title

Answer: The FOREIGN KEY on books.author_id. No author with id 99 exists, so the FOREIGN KEY on books.author_id rejects the insert to preserve referential integrity.

Two authors are given the same 'id' in your INSERT. What stops it?

  • The NOT NULL constraint
  • The PRIMARY KEY on authors.id, which is unique and NOT NULL
  • A FOREIGN KEY
  • Nothing stops it

Answer: The PRIMARY KEY on authors.id, which is unique and NOT NULL. A PRIMARY KEY is unique and NOT NULL, so it rejects the duplicate id.

Which command adds a 'genre' column to books without recreating the table?

  • CREATE TABLE books ADD genre VARCHAR(50);
  • UPDATE books SET genre VARCHAR(50);
  • INSERT COLUMN genre INTO books;
  • ALTER TABLE books ADD COLUMN genre VARCHAR(50);

Answer: ALTER TABLE books ADD COLUMN genre VARCHAR(50);. ALTER TABLE books ADD COLUMN genre VARCHAR(50); adds the column; existing rows get NULL unless you supply a DEFAULT.

What does it mean for the bookshop schema to be 'normalized'?

  • Every table is sorted alphabetically
  • Each fact is stored once, e.g. an author's name lives in one place and books reference it by id
  • All columns use the same data type
  • There are no foreign keys

Answer: Each fact is stored once, e.g. an author's name lives in one place and books reference it by id. Normalization stores each fact once, avoiding duplication and inconsistency; books reference the single author row by id rather than repeating the name.

To total revenue per author from price * qty across all three tables, which clauses does the report query need?

  • Only a WHERE clause
  • A single SELECT with no joins
  • JOINs across the tables, plus SUM, GROUP BY, and ORDER BY
  • DISTINCT and LIMIT only

Answer: JOINs across the tables, plus SUM, GROUP BY, and ORDER BY. You JOIN authors to books to sales, multiply price * qty, aggregate with SUM, GROUP BY author, and ORDER BY revenue to rank them.