Reasoning Checkpoint
A design challenge, worked through in writing — no auto-grading, just a real attempt.
The Challenge
BlueTicket is building a small new product: ticketing for local football matches. John asks Anna to design its database from scratch, using everything from this Act — and to prove that no seat can ever be sold twice.
What it needs to store: stadiums (name, address), matches (which stadium, the two teams, date and time), seats for each match (section, row, number, price, state), fans (name, email), and tickets that fans buy. A fan can buy tickets for many matches, and a match has many fans.
What Your Design Needs
- List the tables, with their columns and a sensible data type for each.
- Mark every primary key and every foreign key, and say what each foreign key points to.
- Name each relationship as one-to-one, one-to-many or many-to-many, and say which table connects fans and matches.
- Show one place where your design is normalized — a fact stored once instead of copied — and one deliberate exception (denormalization), if any.
- Write the SQL steps (or plain-English steps) for selling a seat safely, using a transaction, and add one database rule that makes a double sale impossible even if the code forgets.
- Say which data, if any, you'd put in a key-value store instead of the relational database.
Stuck? A Few Hints
- Fans and matches are many-to-many — what's the junction table here?
- Should the stadium's address be copied into every match?
- Think about which column must be unique in the tickets table.
Before You Move On
If your tables, keys and transaction look like this, you've designed a real production-style database. The UNIQUE rule on tickets.seat_id is the line that would have saved Row C, Seat 14 — the database itself refuses a second ticket, whatever the code does. The Relational Databases course takes every idea here much further.