Practice: five levels

Assignments

Five levels. Do them in order. Answers are in "Solutions" at the end, so try first.

Level 1: Think (no code)

A. Which database type fits best? One line each.

1. A bank's account balances and transfers.
2. Shopping carts that are read and written by user ID, millions of times a minute.
3. A catalogue where books, e-books, audiobooks and gift cards all have different fields.
4. Every page view on the site, about 600 a second, kept for a year.
5. "Find friends of friends who bought the same book."

B. Normalise or keep a copy? One line each.

1. The author's name on every book.
2. The price the customer paid, inside the order.
3. The customer's delivery address, inside the order.

Level 2: Calculate

1. The bookstore has 1 million orders with 2 items each. Embedding a full book snapshot takes about 2 KB per item; a reference (a book ID) takes 16 bytes. How much space for each approach?
2. An author's name is embedded in 200,000 order documents. The database handles 2,000 writes a second for this job. How long does renaming the author take? And in a normalised SQL design?
3. The site records 50 million page views a day. How many writes a second on average? The most popular book gets 1 million views a day; at about 100 bytes each, how big is its partition for one day if you partition by (book, day)?

Level 3: Build

Using Python's built-in sqlite3:

1. Create the bookstore tables (authors, books, users, orders, order_items) with primary keys, foreign keys and checks (price >= 0, stock >= 0, qty > 0, unique email).
2. Write place_order(db, user_id, items) that saves the order, its items and reduces stock in one transaction.
3. Write order_total(db, order_id) using a join.

Check that:

• A normal order works and reduces stock.
• An order including an out-of-stock book fails entirely: no order row, no stock change for the other book.
• A duplicate email and an order for a missing user are rejected.
• Renaming an author changes one row; doing the same in 10,000 denormalised order documents changes 10,000.

Level 4: Debug

Sometimes the warehouse ships books the system says are still in stock, and stock counts drift upward over time. The order code:

1. Find the bug. Explain step by step how stock ends up wrong.
2. Fix it.

Level 5: Design

Choose the storage for the bookstore's main features and justify each:

1. Users, orders, payments and inventory.
2. Shopping carts.
3. The product catalogue (books, e-books, audiobooks, gift cards).
4. Clickstream events (every view and click).
5. "Customers who bought this also bought."

For each: the database type, the main access pattern, and one risk you'd watch.