1Show the Level 1 solution
2**A.**
31. **Relational.** Transfers change two balances atomically; correctness is everything.
42. **Key-value** (like Redis or DynamoDB). Always by user ID, very high rate.
53. **Document.** Each product type has different fields and is read as a whole. (Or relational with a JSON column.)
64. **Wide-column** (or a log or analytics store). Huge, steady writes, read by partition and time.
75. **Graph.** Several hops of relationships.
8
9**B.**
101. **Normalise.** Store once in authors; a rename should change it everywhere.
112. **Keep a copy (snapshot).** The price paid must not change if the book's price changes later.
123. **Keep a copy (snapshot).** The order must show where it was delivered, even if the user moves.
Show the Level 2 solution
1. Embedded: 1,000,000 × 2 × 2 KB = **4 GB**. References: 1,000,000 × 2 × 16 bytes = **32 MB**, about 125 times smaller.
2. 200,000 ÷ 2,000 = **100 seconds** of writes (more if some fail and need retries), during which documents disagree. In normalised SQL: **one** row update.
3. 50,000,000 ÷ 86,400 ≈ **579 writes a second**. The top book's partition for one day: 1,000,000 × 100 bytes = **100 MB**. Fine, but if it grew much larger you'd split the partition further (for example by hour) to avoid one huge, hot partition (Chapter 15).
1Show the Level 3 solution
2Tested solution (Python 3, `sqlite3`), the key parts:
3
4```python
5SCHEMA = """
6CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
7CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT NOT NULL,
8 author_id INTEGER NOT NULL REFERENCES authors(id),
9 price INTEGER NOT NULL CHECK (price >= 0),
10 stock INTEGER NOT NULL CHECK (stock >= 0));
11CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE);
12CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id));
13CREATE TABLE order_items (order_id INTEGER NOT NULL REFERENCES orders(id),
14 book_id INTEGER NOT NULL REFERENCES books(id),
15 qty INTEGER NOT NULL CHECK (qty > 0),
16 price INTEGER NOT NULL,
17 PRIMARY KEY (order_id, book_id));
18"""
19
20
21def place_order(db, user_id, items):
22 with db: # one transaction: commit or roll back together
23 order_id = db.execute("INSERT INTO orders (user_id) VALUES (?)", (user_id,)).lastrowid
24 for book_id, qty in items:
25 price = db.execute("SELECT price FROM books WHERE id = ?", (book_id,)).fetchone()[0]
26 db.execute("INSERT INTO order_items VALUES (?, ?, ?, ?)", (order_id, book_id, qty, price))
27 db.execute("UPDATE books SET stock = stock - ? WHERE id = ?", (qty, book_id))
28 return order_id
29
30
31def order_total(db, order_id):
32 return db.execute("""
33 SELECT SUM(oi.qty * oi.price)
34 FROM order_items oi JOIN orders o ON o.id = oi.order_id
35 WHERE o.id = ?""", (order_id,)).fetchone()[0]
36```
37
38(Foreign keys need `PRAGMA foreign_keys = ON` in SQLite.) All checks pass. The full file is in `code/ch08_sql_nosql.py`.
1Show the Level 4 solution
2**The bug:** the order and the stock change are **two separate transactions** (two commits). They aren't atomic.
3
41. The order and its item are inserted and **committed**. They're now permanent.
52. Before the stock update runs, the server crashes, the request times out, or the update fails.
63. The order exists, but **stock was never reduced**. The system thinks there's more stock than the shelf holds, so it keeps selling books it doesn't have.
7
8The tested demo: after a crash, 1 order is saved and stock is still 10.
9
10**The fix:** one transaction, one commit, and let the database's check stop overselling:
11
12```python
13def place_order(db, user_id, book_id, qty):
14 with db: # commits only if everything succeeds; rolls back on any error
15 order_id = db.execute("INSERT INTO orders (user_id) VALUES (?)", (user_id,)).lastrowid
16 price = db.execute("SELECT price FROM books WHERE id = ?", (book_id,)).fetchone()[0]
17 db.execute("INSERT INTO order_items VALUES (?, ?, ?, ?)", (order_id, book_id, qty, price))
18 db.execute("UPDATE books SET stock = stock - ? WHERE id = ?", (qty, book_id))
19 return order_id
20```
21
22(Two buyers racing for the last copy is a separate problem, isolation, covered in Chapter 17.)
1Show the Level 5 (sketch) solution
21. **Users, orders, payments, inventory → relational** (PostgreSQL). Transactions across orders, items and stock; many different queries. **Risk:** the primary becomes the write bottleneck at sale peaks; watch it and plan replicas and partitioning (Chapters 14 and 15).
32. **Carts → key-value** (Redis or DynamoDB), keyed by user ID. **Risk:** losing carts if the store isn't durable; decide whether a lost cart is acceptable or persist it.
43. **Catalogue → document store** (or PostgreSQL with JSON), one document per product, read whole. **Risk:** copies of author data drifting; keep the author as a reference and update display copies through events.
54. **Clickstream → wide-column** (or an append-only log feeding an analytics store), partitioned by book and day. **Risk:** hot partitions for viral books; split by hour if needed.
65. **"Also bought" → graph** store, or a precomputed table updated nightly. **Risk:** running a second database for one feature; a nightly precompute in SQL may be enough at first.