Part 5 of 5 · 3 min read
Exercises
Easy
Easy 1: Name the five tables (15 minutes)
List the five tables in the starting schema and, in one phrase each, what one row of each table represents.
Done when: all five tables are named with a correct one-phrase description of a row.
Hint
customers (a buyer), authors (a writer), books (a catalog item), orders (one purchase event), order_items (one book within one order).
Easy 2: Find the junction table (15 minutes)
State which table is the junction table in this schema, and which two tables it connects.
Done when: order_items is correctly named, connecting orders and books.
Hint
order_items sits between orders and books, holding a foreign key to each.
Easy 3: One-to-many or many-to-many (15 minutes)
Classify the relationship between authors and books, and the relationship between orders and books.
Done when: authors-to-books is correctly classified as one-to-many, and orders-to-books as many-to-many.
Hint
One author can have many books, but one book has only one author; one order can have many books, and one book can appear in many orders.
Medium
Medium 1: Write the join (25 minutes)
Write a query returning every order a specific customer (id 107) has placed, along with the titles of the books in each order.
Done when: a correct query joining orders, order_items and books, filtered by customer_id = 107, is given.
Hint
Join orders to order_items on order_id, then order_items to books on book_id, filtering orders.customer_id = 107.
Medium 2: Add a sixth table (25 minutes)
Design a reviews table where a customer can leave one review per book, including a rating. Give its columns, primary key, and foreign keys.
Done when: a table with a correct composite or surrogate primary key and two foreign keys (to customers and books) is given.
Hint
reviews (review_id PK, customer_id FK, book_id FK, rating, ...), or a composite key (customer_id, book_id) if only one review per pair is allowed.
Medium 3: Explain the composite key's purpose (25 minutes)
Explain why order_items uses PRIMARY KEY (order_id, book_id) rather than a single-column primary key, tying the answer back to what a junction table needs to guarantee.
Done when: the explanation correctly ties the composite key to preventing the same book from being added twice to the same order.
Hint
The composite key stops the same book being inserted twice into the same order as two separate rows.
Hard
Hard 1: What breaks without the junction table (30 minutes)
Describe what would have to change about orders if order_items did not exist and each order could only ever contain exactly one book.
Done when: the explanation correctly identifies that orders would need its own book_id column directly, losing the ability to hold more than one book per order.
Hint
orders would need its own book_id column directly, making it impossible to represent an order with more than one book.
Hard 2: Extending to many-to-many with attributes (35 minutes)
order_items already has an attribute of its own, quantity, that belongs to the relationship, not to either side. Explain why quantity could not correctly live on either orders or books instead.
Done when: the explanation correctly states quantity depends on the specific order-and-book pairing, not on either table alone.
Hint
quantity only makes sense in the context of one specific book inside one specific order, not as a property of the book itself or the order as a whole.
Hard 3: Plan the Level 5 addition (40 minutes)
Level 5 will add indexes to speed up common bookstore queries. Propose one index on this schema that would speed up the join query from Medium 1, and explain which column it should be built on and why.
Done when: a specific index is proposed on a column actually used in the join or filter condition, with a reason tied to that query.
Hint
An index on order_items.order_id (or the composite key, which already covers it) speeds up finding all items for one order quickly.