LAB 12 / PREPARATION & TEAM PRACTICE
Harbor Market: refactor, test and tune
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.
Two databases, two purposes
Extract the kit and read README.txt. Use fresh copies of harbor_market.db for report and trigger questions. Use a separate copy of benchmark.db for timing. Run SELECT sqlite_version(); and PRAGMA foreign_keys=ON; in each connection. Use modern desktop SQLite; the full course expects version 3.25 or newer.
The browser stores your notes. Run the trigger and timing work on your computer; .timer and .read are SQLite shell commands. The timing section requires the sqlite3 terminal. Questions that deliberately cause errors should be run one statement at a time so you can inspect pending data before rollback.
Quick schema: market tables, columns & relationships
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.
Timing database: schema & experiment instructions
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.
EIGHT CONNECTED TOPICS
Read, predict, then verify
TOPIC 1 OF 8
Name the grain before naming the CTE
A CTE gives a query step a name for one statement. Chained CTEs use one WITH, commas between definitions and one final SELECT. A later statement cannot query the CTE name; a view persists a definition.
Here paid_lines means one paid receipt line; vendor_totals means one vendor with paid sales. COUNT(DISTINCT order_id) counts checkouts, not receipt lines. Multiply quantity by the historical unit_cents on the receipt, not the current product price.
Keep the paid rule in one source step. Then start the final settlement from active vendors and LEFT JOIN totals, so the new vendor remains visible with zeros. An inactive vendor’s historic paid sales still contribute to a market-wide denominator when that is the stated business rule.
Worked example · desktop SQLite / shell
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,COALESCE(t.orders,0) AS orders,COALESCE(t.revenue_cents,0) AS revenue_cents FROM vendors v LEFT JOIN vendor_totals t ON t.vendor_id=v.id WHERE v.active=1 ORDER BY v.id;
Pause and investigate: Compare baseline.sql and refactor.sql in the kit. Identify the active zero-sale vendor and the inactive vendor with historic sales. Which belongs in the report, and which belongs in the all-market total?
TOPIC 2 OF 8
A matching total is not a matching report
Establish the original query’s output columns, grain, NULL rules and ordering before rewriting. Check row count and numeric totals, then EXCEPT in both directions. One direction can miss extra rows.
EXCEPT compares distinct rows, so equal sets do not establish equal duplicate counts. If the contract allows duplicates, compare grouped full rows with their frequencies. For a one-row-per-vendor report, separately verify unique vendor ids. Check required ordering separately too.
A CTE improves how a reader follows the logic; it does not guarantee a faster plan or force materialization. Preserve correctness first, then measure a relevant workload.
Worked example · desktop SQLite / shell
WITH before_rows(id,cents) AS (VALUES(1,100),(2,200)), after_rows(id,cents) AS (VALUES(1,200),(2,100)) SELECT * FROM before_rows EXCEPT SELECT * FROM after_rows;
Pause and investigate: Both versions have two rows and total 300. Why does this comparison still return rows? Reverse EXCEPT, then construct a duplicate-count example that both set comparisons miss.
TOPIC 3 OF 8
Fill the calendar before analyzing a gap
A recursive CTE has an anchor, a recursive step and a stopping condition. This one generates January through March, independent of sales. A LEFT JOIN preserves empty calendar months.
Zero-fill only when the reporting period is complete and no matching activity means zero. Incomplete source data may need an unknown marker. This rehearsal is a complete invented three-month snapshot.
Filter paid activity inside the aggregate CTE. A final WHERE on the right-hand month or value can remove the very blank month you just added. Use ISO dates and a clear inclusive last month.
Worked example · desktop SQLite / shell
WITH RECURSIVE months(month) AS (
VALUES('2026-01') UNION ALL
SELECT STRFTIME('%Y-%m',month||'-01','+1 month')
FROM months WHERE month<'2026-03'
), paid_months AS (
SELECT STRFTIME('%Y-%m',o.order_date) AS month,
SUM(l.quantity*l.unit_cents) AS revenue_cents
FROM orders o JOIN lines l ON l.order_id=o.id
WHERE o.status='paid' GROUP BY STRFTIME('%Y-%m',o.order_date)
)
SELECT m.month,COALESCE(p.revenue_cents,0) AS revenue_cents
FROM months m LEFT JOIN paid_months p ON p.month=m.month
ORDER BY m.month;Pause and investigate: Predict February’s row and the total over all three months. How would an accidental INNER JOIN change the evidence?
TOPIC 4 OF 8
Audit actual changes, not every update
UPDATE OF price_cents selects updates mentioning that column; it does not mean the value changed. A WHEN comparison between OLD and NEW suppresses a no-op update.
AFTER performs an audit action after the row change. INSERT triggers can use NEW; DELETE triggers can use OLD; UPDATE can use both. The trigger body has its own statement semicolons and ends with END;.
Prices here are NOT NULL, so <> works for a real-change comparison. For nullable fields, use a deliberate NULL-aware comparison such as IS NOT. Test one real price change, an unchanged price and a stock-only update. The audit records should participate in rollback with the triggering change.
Worked example · desktop SQLite / shell
CREATE TRIGGER log_price_change AFTER UPDATE OF price_cents ON products WHEN OLD.price_cents <> NEW.price_cents BEGIN INSERT INTO audit_log(product_id,old_cents,new_cents) VALUES(NEW.id,OLD.price_cents,NEW.price_cents); END;
Pause and investigate: Install this on a fresh market copy. Change product 1 from 350 to 375, set it to 375 again, then add one to stock. How many audit rows should exist, and what old/new values should they record?
TOPIC 5 OF 8
Reject an oversell and verify every side effect
A BEFORE INSERT guard can reject a receipt line using RAISE(ABORT). An AFTER INSERT trigger reduces the matching product’s stock. Keep the scope predicate on product_id; without it, every product would lose stock.
A product’s CHECK prevents negative stock, and foreign keys reject missing products when enabled. The guard supplies the explicit oversell rule and message. Test a valid sale, too much stock, an unknown product and a multi-row statement where a later row fails.
ABORT rolls back the failing statement and its trigger side effects. It does not erase earlier successful statements in an explicit transaction. These teaching triggers cover inserts only: editing or deleting receipt lines and restocking refunds need separate business rules. This is not a complete inventory application.
Worked example · desktop SQLite / shell
CREATE TRIGGER block_oversell BEFORE INSERT ON lines WHEN NEW.quantity > (SELECT stock FROM products WHERE id=NEW.product_id) BEGIN SELECT RAISE(ABORT,'not enough stock'); END; CREATE TRIGGER reduce_stock AFTER INSERT ON lines BEGIN UPDATE products SET stock=stock-NEW.quantity WHERE id=NEW.product_id; END;
Pause and investigate: Begin a transaction, add a valid two-unit sale, then attempt an oversell in a separate statement. Observe line count and stock before and after ROLLBACK. Contrast with two rows in one INSERT where the second fails.
TOPIC 6 OF 8
Test the direction of a status transition
A refunded order may remain refunded, and a paid order may become refunded. The forbidden direction is refunded to a different valid status. State both OLD and NEW conditions explicitly.
A guard using only NEW.status could block legitimate refunds or all paid orders. Test the entire transition matrix, including no-op updates. The status CHECK constrains the vocabulary; the trigger constrains the transition.
Refund status changes do not automatically restock products in this fixture. Financial status and physical inventory are separate decisions. As with other changes, test on a disposable working copy.
Worked example · desktop SQLite / shell
CREATE TRIGGER keep_refunds_final BEFORE UPDATE OF status ON orders WHEN OLD.status='refunded' AND NEW.status<>'refunded' BEGIN SELECT RAISE(ABORT,'refund is final'); END;
Pause and investigate: Try paid → paid, paid → refunded, refunded → refunded and refunded → paid on fresh copies. Which should fail? Record the actual error and final stored status.
TOPIC 7 OF 8
Measure the work the screen actually needs
EXPLAIN QUERY PLAN describes access strategy, not elapsed time. SCAN can traverse a table or an entire index; SEARCH visits a subset. A covering index can supply all selected columns. Plans depend on the engine, schema, statistics and query.
Use the separate benchmark database for this topic. benchmark.sql repeats the same receipt summary three times before and after adding lines(order_id). It forces both the per-order line count and money total to be evaluated; a wrapper that only counts outer rows can skip the expensive subqueries.
Record SQLite version, row counts, exact query, plan and all timings. Compare medians with the same data and result. Do not claim a speedup when timings round to zero or noise dominates. This fixture shows a mechanism, not a forecast for your client’s production workload.
Worked example · desktop SQLite / shell
.read benchmark.sql
Pause and investigate: Run this SQLite shell command after opening a fresh benchmark.db copy in the extracted kit directory. Are order_rows, receipt_lines and total_cents identical in all six runs? What evidence supports your conclusion?
TOPIC 8 OF 8
Match the index to the predicate
An ordinary date index can usually support a half-open range on an ISO date column. Applying STRFTIME to that column often prevents a direct range search through that index. Expression indexes are a separate option; do not turn this example into a universal claim that functions can never use indexes.
A composite index on (product_id, order_id) naturally supports its leading column and both columns together. A trailing-column-only lookup may scan in this controlled fixture. SQLite can choose skip-scan in some distributions with appropriate statistics, so record what your plan actually says.
An index consumes storage and adds maintenance work to relevant INSERT, UPDATE and DELETE operations. Recommend an index with the exact query it serves, the result-equivalence evidence, measured read benefit and a write-cost limitation. Do not add every imaginable index.
Worked example · desktop SQLite / shell
.read composite.sql
Pause and investigate: Run after benchmark.sql in the same benchmark copy. Compare product-only, order-only and combined predicates. Name the index you would restore for the receipt screen and why.
PREDICT · RUN ON DESKTOP · EXPLAIN
Six team investigations
Try questions 1–2 in 15–20 minutes; allow 40–50 minutes for all six plus discussion. Rotate writer, evidence checker and explainer. Questions 1–5 use fresh harbor_market.db copies. Question 6 uses a separate benchmark.db copy. These practice numbers are separate from official Lab 12 Q1–Q15.
Opening your saved work…
Sign in to save across devices → · Download your guest work before switching accounts.
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;
2. February disappears from the trend
Generate a complete January–March calendar for this complete practice snapshot. Repair the final join in the supplied query to return month and revenue_cents, including zero-sales months. Explain where the paid filter belongs and demonstrate that the total is preserved.
WITH RECURSIVE months(month) AS (
VALUES('2026-01') UNION ALL
SELECT STRFTIME('%Y-%m',month||'-01','+1 month')
FROM months WHERE month<'2026-03'
), paid_months AS (
SELECT STRFTIME('%Y-%m',o.order_date) AS month,
SUM(l.quantity*l.unit_cents) AS revenue_cents
FROM orders o JOIN lines l ON l.order_id=o.id
WHERE o.status='paid' GROUP BY STRFTIME('%Y-%m',o.order_date)
)
SELECT m.month,COALESCE(p.revenue_cents,0) AS revenue_cents
FROM months m JOIN paid_months p ON p.month=m.month
ORDER BY m.month;3. The audit records a change that never happened
Use a fresh harbor_market.db copy. Run this candidate trigger, change product 1 to 375 cents, set it to 375 again, then increase only its stock by one. Record the audit rows. On another fresh copy, fix the trigger and repeat. Finally wrap a real price change in BEGIN/ROLLBACK and verify both product and audit restore.
CREATE TRIGGER log_price_change AFTER UPDATE OF price_cents ON products BEGIN INSERT INTO audit_log(product_id,old_cents,new_cents) VALUES(NEW.id,OLD.price_cents,NEW.price_cents); END;
4. The register refuses a line: did anything else change?
Use a fresh harbor_market.db copy and install inventory.sql. Begin a transaction; sell two units of product 1 with line id 7 on order 4. Then attempt line id 8 for 99 units. Capture the exact error, line count, product 1 stock and an unchanged product’s stock while still inside the transaction. ROLLBACK and compare again. Explain how one multi-row INSERT with a later failing row differs.
BEGIN; INSERT INTO lines VALUES(7,4,1,2,350); INSERT INTO lines VALUES(8,4,1,99,350); -- Observe counts and stock in this connection. -- Then ROLLBACK and observe them again.
5. The guard blocks the wrong direction
On a fresh harbor_market.db copy, install the candidate below. Test the four paid/refunded transitions on separate working copies. Explain which allowed transitions it blocks. Repair it so only reopening a refunded order fails, then record status and exact error after each test.
CREATE TRIGGER keep_refunds_final BEFORE UPDATE OF status ON orders WHEN NEW.status='refunded' BEGIN SELECT RAISE(ABORT,'refund is final'); END;
6. The index claim needs a fair experiment
Use a fresh benchmark.db copy in sqlite3. Run benchmark.sql, then composite.sql. Record all six receipt-summary outputs and timings, plans before/after, the three composite lookup plans and both date-filter plans. Verify equal results; write a five-sentence recommendation naming the helpful index, query change, measurement limits and write costs. If the measurements are too small, report inconclusive rather than inventing a ratio.
.read benchmark.sql .read composite.sql
Notes save on this device and appear in My Work. This page does not execute or grade desktop SQL, upload your database, or submit to Canvas.
Return to your actual Lab 12
Download duluth_market.db from Canvas and work on a copy. Q1–Q6 refactor the settlement and prove equivalence, Q7–Q10 create and test four triggers, and Q11–Q15 measure indexes in the SQLite shell. Keep the official aliases, question numbers, findings and actual plan/timing output in your individual report.
Include the Check the AI comparison, your client query and your own reflections. Milestone 4 is checked during this lab: have queries.sql and its owners ready to explain the portfolio. For your client, refactor one query, compare results, test an index and record the decision and before/after plan evidence in your README. Your client files stay in your own project folder.
Official template & submission →SQLite references: CTEs · triggers · query plans · optimizer choices. Canvas controls the official class requirements and deadlines.