LEARN WITH ABDOU / LAB 12 PRACTICE / VERSION 2026-09-09

Harbor Market: refactor, test and tune

Team: __________________________ Date: ______________

Lab 12 preparation with eight readings and six evidence-based team investigations. Use the small market database for correctness and the separate benchmark for timing.

Workshop and kit: learnwithabdou.com/courses/mis3220/labs/refactor-tune

Before you begin

Read the official Pre-Lab 12 and complete its Canvas check. Extract the kit and read README.txt. Questions 1–5 use fresh harbor_market.db copies; question 6 uses a separate benchmark.db copy. Check SELECT sqlite_version(); and enable PRAGMA foreign_keys=ON; in every connection.

Your investigation routine

Rotate writer, evidence checker and explainer. Predict before running. Record SQL as text, observed rows or exact errors, and reasoning. Use questions 1–2 for 15–20 minutes; allow 40–50 minutes for all six plus discussion. The website saves notes; SQL runs on your computer.

Reading map

1. Name the grain before naming the CTE

2. A matching total is not a matching report

3. Fill the calendar before analyzing a gap

4. Audit actual changes, not every update

5. Reject an oversell and verify every side effect

6. Test the direction of a status transition

7. Measure the work the screen actually needs

8. Match the index to the predicate

What counts as evidence?

For refactoring: row count, totals, EXCEPT both ways, key uniqueness and required order. For triggers: successful and rejected changes, side effects, no-ops and rollback. For timing: equal results, exact query, engine version, plans and repeated measurements.

Use the SQLite shell for .timer and .read. Run intentional errors one statement at a time in the same connection; inspect pending data, ROLLBACK, then inspect again. Never substitute an expected error or timing for what your own run showed.

Harbor Market schema

Original small practice database: harbor_market.db. Counts are before installing triggers or changing rows. The actual class lab uses duluth_market.db from Canvas.

vendors · 3 rows

id INTEGER PK; name TEXT NOT NULL; active INTEGER NOT NULL CHECK 0 or 1

One vendor. New Sprouts is active with no sales; Old Orchard is inactive with historic paid sales.

products · 4 rows

id INTEGER PK; vendor_id INTEGER NOT NULL FK → vendors.id; name TEXT NOT NULL; price_cents INTEGER NOT NULL CHECK > 0; stock INTEGER NOT NULL CHECK >= 0

Current shelf price and stock. Use historical line unit_cents to value a receipt.

orders · 4 rows

id INTEGER PK; order_date TEXT NOT NULL; status TEXT NOT NULL CHECK paid or refunded

One checkout. ISO dates. One order is refunded; only paid orders contribute to the settlement.

lines · 6 rows

id INTEGER PK; order_id INTEGER NOT NULL FK → orders.id; product_id INTEGER NOT NULL FK → products.id; quantity INTEGER NOT NULL CHECK > 0; unit_cents INTEGER NOT NULL CHECK > 0

One receipt line. Revenue in cents is quantity × historical unit_cents. Several lines can belong to one order.

audit_log · 0 rows

id INTEGER PK; product_id INTEGER NOT NULL FK → products.id; old_cents INTEGER NOT NULL; new_cents INTEGER NOT NULL

Starts empty. The price-change trigger fills it; a rollback also removes its pending audit rows.

All money is integer cents. This is a complete invented January–March snapshot. Stock is the current shelf count; historical lines were loaded before triggers existed. Enable foreign keys in each connection. setup.sql contains the complete definitions.

The separate timing database

benchmark.db is a generated workload with 1,200 orders and 7,200 lines. It is not the small market and has no vendor, refund or inventory rules.

orders · 1,200 rows

id INTEGER PK; order_date TEXT NOT NULL

lines · 7,200 rows

id INTEGER PK; order_id INTEGER NOT NULL FK → orders.id; product_id INTEGER NOT NULL; quantity INTEGER NOT NULL; unit_cents INTEGER NOT NULL

product_id is a synthetic lookup attribute, not a declared foreign key in this benchmark. Six lines belong to each order. No extra indexes are installed initially.

Run the experiment

In a terminal, go to the extracted kit folder. Make a working copy of benchmark.db and open it with sqlite3. Run .read benchmark.sql, then .read composite.sql in that order. These scripts change indexes; use a fresh copy for a new experiment.

benchmark.sql prints plans and three timed runs before and after adding lines(order_id). Record every output and timing. Compare medians only after confirming identical results. composite.sql replaces that index to test product-only, order-only and combined predicates.

Record the SQLite version, sample sizes and query. Plans and timings vary by engine, statistics, cache and hardware. A result below timer resolution is inconclusive. This exercise does not establish production performance or quantify the index’s write cost.

Your measurement log

SQLite version: ______________ Hardware/tool: ________________________

Phase	Run 1	Run 2	Run 3	Median / units
Before	_______	_______	_______	____________
After	_______	_______	_______	____________

Equal order_rows / receipt_lines / total_cents: __________________________

Plan change, noise or timer-resolution limitation:

1. The money matches, but a vendor vanished

On a fresh harbor_market.db copy, compare this candidate with baseline.sql. Repair it to return id, name, orders and revenue_cents for every active vendor, ordered by id. Record counts and sums, EXCEPT both ways, and the vendor that exposes the error. Explain why the all-market total includes money absent from active settlement rows.

WITH paid_lines AS (
 SELECT l.order_id,p.vendor_id,l.product_id,l.quantity*l.unit_cents AS line_cents FROM lines l JOIN orders o ON o.id=l.order_id JOIN products p ON p.id=l.product_id WHERE o.status='paid'
), vendor_totals AS (
 SELECT vendor_id,COUNT(DISTINCT order_id) AS orders,SUM(line_cents) AS revenue_cents
 FROM paid_lines GROUP BY vendor_id
)
SELECT v.id,v.name,t.orders,t.revenue_cents
FROM vendor_totals t JOIN vendors v ON v.id=t.vendor_id
WHERE v.active=1 ORDER BY v.id;