CS202 · Examination paper

2 hours 30 minutes · 100 marks · pass 40 · Merit 60 · Distinction 70 Open book: the module materials and the PostgreSQL 18 ALTER TABLE, EXPLAIN and constraint pages, printed. Sections A–D are answered on paper. Section E runs on a laptop against a local copy of the schema — never staging, and nothing in this paper is written to staging. No internet, no AI tooling.

Time budget. A 20 marks / 28 min · B 20 marks / 28 min · C 20 marks / 30 min · D 20 marks / 26 min · E 20 marks / 28 min · checking 10 min.

Every query in A, C and E must be one you have run on the seeded copy. A query that errors scores 0 for that item. Ten marks are prose, and the paper says which.

Section A — Write the queries (20 marks)

Schema: booking_enquiry, booking_enquiryband, bands_band, booking_enquirymessage, payments_invoice. booking_enquirymessage.band_id is nullable; booking_enquiry.date is nullable.

A1. (4) For every enquiry, its number of band rows, with childless enquiries present as 0. State the row count on the six-enquiry copy.

A2. (4) One row per band with offers, invoice count and total invoiced, including bands with no rows at all. No aggregate may be multiplied by a join.

A3. (4) Each thread’s latest message, one row per enquiryband_id, newest first by datetime then id. State where the NULL-thread rows are excluded.

A4. (4) Every band that has never been messaged. Write the NOT EXISTS form, then state what the NOT IN form returns here and why.

A5. (4) A running total of payments_invoice.amount per enquiry_id, ordered by due_date. State the frame you wrote and what the default frame returns for the first of enquiry 4’s two invoices.

Examines. Ch2, Ch3.

Section B — Schema and constraints (20 marks)

B1. (5) This table is built by joining the four FTM tables; its key is (enquiry_id, band_id).

CREATE TABLE booking_report AS
SELECT e.id AS enquiry_id, b.id AS band_id, b.name AS band_name,
       b.location AS band_location, e.date AS enquiry_date, e.state, i.amount
FROM booking_enquiry e
JOIN booking_enquiryband eb ON eb.enquiry_id = e.id
JOIN bands_band b ON b.id = eb.band_id
JOIN payments_invoice i ON i.enquiry_band_id = eb.id;

(a) (2) Give the functional dependencies. (b) (1) Name the normal form it violates. (c) (2) Write the split, naming the key of each table.

B2. (5) For booking_enquiryband, state the constraint that rejects each of these, one line each:

(a) a second live row for the same (enquiry_id, band_id); (b) a second row with is_owner true on one enquiry; (c) a band_id that names no band; (d) a NULL band_id.

(e) Then name the part of “exactly one owner per enquiry” that (b) does not enforce, and the mechanism that would.

B3. (4) Write the DDL for “a band holds at most one live offer per enquiry, but may be re-offered after a cancellation at status = -100”. State what happens to the cancelled row and the error a plain UNIQUE (enquiry_id, band_id) raises on the re-offer.

B4. (3) band_id on booking_enquiryband is integer NOT NULL UNIQUE in a proposed revision. Three rows insert with (1, NULL, NULL) in that column. State how many rows the constraint permits, what count(DISTINCT band_id) returns, and the downstream failure.

B5. (3) payments_invoice.band_fee is GENERATED ALWAYS AS (amount - booking_fee - planner_fee) STORED; bands_band.reviews_count is an application-maintained counter. Give the invariant of each and say which one can disagree with its source, and why.

Examines. Ch1, Ch4, Ch5.

Section C — Index design (20 marks)

For each, give the exact CREATE INDEX, the column order, and one sentence on what makes it usable.

C1. (5) The admin message feed:

SELECT id, band_id, "datetime"
FROM booking_enquirymessage
WHERE kind = 'IN' AND band_id IS NOT NULL
ORDER BY "datetime" DESC, id DESC
LIMIT 50;

(a) (2) The index. (b) (2) Explain why band_id > 0 in the query makes the same index ineligible. (c) (1) The query reads 53 buffers after the index exists: account for them.

C2. (4) With (band_id, "datetime") in place, say yes or no for each, with the reason in a phrase: (a) band_id = 4200; (b) `band_id = 4200 AND “datetime”

x; (c) ”datetime” > x; (d) band_id IN (4200, 4201) ORDER BY “datetime”`.

C3. (4) A reporting service sends WHERE band_id::text = '4200'. State the two measured times, the mechanism, and the two fixes, saying which one keeps the existing index usable by other callers.

C4. (4) Name the index type for each, and one thing it cannot do: (a) WHERE enquiryband_id = ? ORDER BY "datetime" DESC LIMIT 20; (b) WHERE attributes @> '{"genres": ["jazz"]}'; (c) an EXCLUDE overlap on band bookings; (d) WHERE lower("to") = 'yuuka@yuuka.com'.

C5. (3) A B-tree holds 7,873 leaf pages at fanout ~200. Give its height, the index page reads for one equality lookup, the measured buffer count for the whole point query, and the leaf count needed to add a third level.

Examines. Ch6, Ch7.

Section D — Read the plan (20 marks)

D1. (6) On booking_enquirymessage, 1.2M rows, stale statistics:

Aggregate  (cost=4210.55..4210.56 rows=1 width=8) (actual time=180.442..180.444 rows=1 loops=1)
  Buffers: shared hit=204881
  ->  Nested Loop  (cost=0.71..4209.32 rows=163 width=0) (actual time=0.038..172.910 rows=200150 loops=1)
        ->  Index Scan using msg_band_datetime on booking_enquirymessage m
              (cost=0.43..1712.00 rows=163 width=8) (actual time=0.031..38.201 rows=200150 loops=1)
              Index Cond: (band_id = 4200)
              Filter: (kind = 'IN'::text)
        ->  Index Only Scan using booking_enquiryband_pkey on booking_enquiryband b
              (cost=0.28..8.30 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=200150)
              Index Cond: (b.id = m.enquiryband_id)
              Heap Fetches: 0

(a) (3) Name the node that dominates the time, with the arithmetic that shows why. (b) (2) Diagnose the fault: access path, or estimate. (c) (1) The command that fixes it.

D2. (5) After that command the cost rises to 41,288 and the time falls to 90 ms with 11,744 buffers, on a parallel hash join. State which number was wrong, why the two costs are not comparable, and what SET enable_nestloop = off would have achieved instead.

D3. (4) Name the join and give the reason: (a) 50 enquiries against 400k band rows with an index on band_id; (b) 200k messages against 22k bands; (c) two inputs already sorted on the join key.

D4. (5)

Aggregate  (cost=34612.00..34612.01 rows=1 width=8) (actual time=61.884..61.886 rows=1 loops=1)
  Buffers: shared hit=1532 read=10228
  ->  Seq Scan on booking_enquirymessage  (cost=0.00..34532.00 rows=32000 width=0)
        Filter: ("datetime" > (now() - '1 day'::interval))
        Rows Removed by Filter: 1168000

(a) (3) The predicate matches 32,000 of 1.2M rows. Why is no index on datetime chosen? (b) (2) State the two changes that would make an index win.

Examines. Ch7, Ch8.

Section E — Transactions and a migration, on the laptop (20 marks)

Work against your own local copy of the schema, created in this session. Never connect to staging. Paste real output for every item.

E1. (6) Reproduce the lost update on invoice 1 at paid = 300.00: session A adds 350.00, session B adds 300.00, each reading before either writes and writing a literal. Paste the final paid, the final status, the sum of the receipt rows, and name the anomaly. State whether any session raised an error.

E2. (4) Repair the same pair twice: once with the arithmetic in the UPDATE, once with an explicit lock. Name the lock each takes and say which is the weaker mode and why it matters for foreign-key checks.

E3. (4) Two payout splits of 500.00 against 850.00 outstanding both pass a read committed check-then-act and commit. Name the anomaly, give the final band_paid_amount, and state the smallest change that prevents it — and why no CHECK constraint can.

E4. (3) The retry wrapper: state why the loop must sit outside transaction.atomic(), the two SQLSTATEs it treats as retryable, and one side effect that must move after the commit.

E5. (3) Run this, paste the error, and state the two lines the Django migration needs instead:

app=# BEGIN;
BEGIN
app=# CREATE INDEX CONCURRENTLY idx_demo ON booking_enquirymessage ("datetime" DESC);

Examines. Ch9, Ch10, Ch11.

Mark scheme

Partial credit is awarded per sub-item, never for a plausible shape. A query that runs and returns the right rows scores its item in full even if written differently from the answer below; a query that runs and returns the wrong row count scores 1 for having run. Where an item asks for a number, the mark is for the number.

Section A

A1. 4 = 2 for LEFT JOIN from booking_enquiry, 1 for count(eb.id) not count(*), 1 for six rows with enquiry 6 at 0. count(*) scores 0 on the second mark — it reports one band for a childless enquiry.

A2. 4 = 2 for pre-aggregating each child before joining (three CTEs or subqueries at band grain), 1 for LEFT JOIN from bands_band, 1 for coalesce(…, 0) on all three columns. Joining the children directly and patching with DISTINCT scores at most 1: sum(DISTINCT amount) collapses band 5’s and band 2’s separate 1000.00 invoices.

A3. 4 = 2 for row_number() OVER (PARTITION BY enquiryband_id ORDER BY "datetime" DESC, id DESC), 1 for the id tiebreak, 1 for filtering = 1 in an outer query and excluding enquiryband_id IS NULL before the window. A window in WHERE does not parse; 0.

A4. 4 = 2 for NOT EXISTS (SELECT 1 … WHERE m.band_id = b.id), 2 for the explanation: NOT IN returns 0 rows because booking_enquirymessage.band_id holds NULL, so every <> is UNKNOWN and the predicate is never true. The correct answer is 1 row (band 6). NOT IN (… WHERE band_id IS NOT NULL) scores the 2 query marks and 1 of the 2 explanation marks.

A5. 4 = 1 for the query, 1 for naming ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, 1 for saying the default RANGE frame returns 2000.00 on the first of enquiry 4’s two invoices, 1 for the due_date, id tiebreak.

Section B

B1. (a) 2 = 1 for band_id → band_name, band_location, 1 for enquiry_id → enquiry_date, state and (enquiry_id, band_id) → amount/the whole key. (b) 1 = 2NF (accept “2NF and therefore not 3NF”). (c) 2 = 1 for the three-table split, naming their keys; 1 for putting amount on payments_invoice keyed by enquiry_band_id.

B2. 5 = 1 each for (a) unique_enquiry_band, the partial unique index on (enquiry_id, band_id) WHERE status >= 0; (b) unique_enquiry_band_owner, on (enquiry_id) WHERE is_owner; (c) the foreign key to bands_band(id); (d) NOT NULL on band_id — no constraint of the other kinds rejects a NULL. (e) 1 for “at most one” is enforced and “at least one” is not: UNIQUE cannot assert existence, so zero owners stays reachable; the mechanism is a DEFERRABLE INITIALLY DEFERRED constraint trigger counting owners at commit, or a single statement that sets the new owner before clearing the old. Naming only the gap without a mechanism scores 0 on (e).

B3. 4 = 2 for CREATE UNIQUE INDEX … ON booking_enquiryband (enquiry_id, band_id) WHERE status >= 0; 1 for the cancelled row staying at -100, outside the index, preserving history; 1 for duplicate key value violates unique constraint on the plain form. A plain UNIQUE without the predicate scores 2 of 4 at most.

B4. 3 = 1 for “unlimited NULLs — Postgres treats NULLs as distinct”, 1 for count(DISTINCT band_id) returning 1, 1 for the null band reaching every downstream join (the LEFT JOIN band attribution and the NOT IN trap of A4).

B5. 3 = 1 for band_fee’s invariant (equals amount - booking_fee - planner_fee, engine-recomputed, direct writes refused), 1 for reviews_count’s (equals the count of non-hidden reviews; nothing enforces it), 1 for naming the counter as the one that can disagree, with the Neon Choir example or the general “cache with an unwritten invariant”.

Section C

C1. (a) 2 = CREATE INDEX … ON booking_enquirymessage ("datetime" DESC, id DESC) WHERE kind = 'IN' AND band_id IS NOT NULL; 1 of the 2 lost for a non-partial index or for the two columns in the other order. (b) 2 = the planner proves implication syntactically at plan time; band_id > 0 does not textually imply band_id IS NOT NULL, so the index is ineligible even though every matching row is inside it. (c) 1 = three index pages (root, internal, leaf) plus fifty heap pages, one per returned row, because the LIMIT stops the scan.

C2. 4 = 1 each: (a) yes, prefix; (b) yes, equality fixes the run then the range walks it; (c) no, wrong leading column — the leaves are ordered by band_id, so matching keys are scattered one run per band and Postgres has no skip scan; (d) partly — IN becomes two ranges, each supplying order, so the result is a merge of two sorted runs and a sort may be needed.

C3. 4 = 1 for 84.6 ms against 0.76 ms; 1 for the mechanism (the cast puts a function between the column and the comparison, so index order no longer corresponds to predicate order); 1 for sending an integer from the caller; 1 for the expression index on (band_id::text) — and for saying the caller fix is the one that keeps the existing index usable, the expression index serving only that cast form at doubled write cost. Both fixes without the attribution scores 3.

C4. 4 = 1 each: (a) btree on (enquiryband_id, "datetime" DESC) — cannot serve a predicate whose leading column is absent; (b) gin on attributes — cannot serve ranges; (c) gist, EXCLUDE USING gist (band_id WITH =, daterange(start, end) WITH &&) — cannot serve equality on scalars cheaply; (d) btree on the expression lower("to") — a plain index on "to" is not usable and the expression index serves only that expression.

C5. 3 = 1 for height 2 (⌈log₂₀₀ 7873⌉), 1 for three page reads, 1 for 7 buffers and 200³ = 8,000,000 leaves for a third level. Two of the three numbers without the buffer count scores 2.

Section D

D1. (a) 3 = 2 for the inner Index Only Scan … loops=200150 at 0.001 ms each, which is ~200 ms of loop cost, and 1 for the join total of 172.9 ms against the scan’s 38.2 ms. (b) 2 = the estimate, not the plan: 163 estimated against 200,150 actual is a factor of 1,228, so the planner priced 163 lookups and the executor ran 200,150. At 163 rows a nested loop is the correct choice. (c) 1 = ANALYZE booking_enquirymessage;. “Replace the nested loop with a hash join” scores 0 on (b).

D2. 5 = 2 for “neither is wrong: the first cost was computed from a 163-row estimate, so it priced a query that did not exist, and cost is only comparable within one set of statistics”; 1 for 204,881 → 11,744 buffers as the real improvement; 2 for enable_nestloop = off forcing a plan that is slower whenever the estimate is right and leaving the estimate error to reappear as a different plan for a different parameter.

D3. 4 = 1 each for (a) nested loop — 50 index lookups beat scanning 400k rows; (b) hash join — one build over 22k bands against 200k lookups; (c) merge join — 1 for the name, 1 for “neither input needs a sort, so the sort cost that decides the other two is absent”.

D4. (a) 3 = 2 for the random-heap argument: 32,000 rows spread across every heap page means ~11,700 scattered ctid fetches, nearly the whole table, unordered; 1 for the planner’s random_page_cost making the sequential scan cheaper. (b) 2 = 1 for a selective predicate (a few thousand rows, clustered), 1 for an index-only scan where the index holds every needed column so the heap is never visited.

Section E

E1. 6 = 2 for a transcript in which both sessions read 300.00 before either writes and B blocks on A’s row lock, 2 for the final paid of 600.00 (not 950.00), 1 for status still 'deposit_paid' with receipts summing to 950.00, 1 for “no error was raised — the lost update is silent”.

E2. 4 = 2 for UPDATE payments_invoice SET paid = paid + 350.00 WHERE id = 1 (evaluated against the locked version), 1 for SELECT … FOR NO KEY UPDATE (or FOR UPDATE) before computing, 1 for naming FOR NO KEY UPDATE as the weaker mode, because FOR UPDATE also blocks the FOR KEY SHARE lock a child insert takes to validate its foreign key. The update form alone scores 2.

E3. 4 = 2 for write skew and the final band_paid_amount of 1000.00, 150.00 over band_net_fee; 1 for SERIALIZABLE, which aborts the second with 40001; 1 for why no CHECK suffices — both transactions write different rows and each row is individually legal, and a generated column cannot compare against band_net_fee across rows. “Repeatable read” scores 0 on the third mark: disjoint rows are no write-write conflict.

E4. 3 = 1 for “a 40001 aborts the whole transaction, so the retry must start a new one, and an exception caught inside transaction.atomic() marks it for rollback and refuses further queries”; 1 for 40001 and 40P01; 1 for one side effect named (Stripe charge, deposit email, Discord post) with the reason that it would fire once per attempt and is not idempotent.

E5. 3 = 1 for the pasted ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block; 1 for atomic = False; 1 for IF NOT EXISTS on the SQL and state_operations so makemigrations does not recreate the index.

Grade descriptors

Band	The script
Distinction (70+)	A1–A5 run and return the stated counts; A2 pre-aggregates at band grain rather than patching with `DISTINCT`; B1 names 2NF and the full split; B2(e) gives both the gap and a mechanism; C1 is partial with the predicate restated, and C1(c) is the 3 + 50 buffer arithmetic; D1 blames the estimate and says a nested loop was correct at 163 rows; E1 reproduces 600.00 with the receipts at 950.00 and names it a lost update, and E3 distinguishes snapshot isolation from SSI.
Merit (60–69)	One section’s worth of arithmetic slips: C5 gives the height but not the buffer count; D2 knows the time improved but calls the 41,288 cost wrong; B3 writes the partial index but cannot say what a plain `UNIQUE` raises; E2 fixes the race with `paid = paid + …` and thinks that is a lock upgrade rather than arithmetic moving into the statement. E3 right, E4 right.
Pass (40–59)	Queries mostly right, defects predictable: A1 uses `count(*)` and reports one band for childless enquiries; A4 uses `NOT IN` and gets 0 rows without noticing; B2 names the two indexes but not `NOT NULL`; C2 says yes to `"datetime" > x`; D1 identifies the nested loop as “the problem” rather than the estimate; E1 gets 600.00 but cannot explain why the lock did not help.
Fail (<40)	Cannot write a join that preserves childless parents; B1 calls `booking_report` 3NF; C1 indexes the predicate columns but not the sort; D reads the plan top-down and blames the outer scan; E does not run, or the transcript was not produced on a local copy.

Section floors. A must score ≥ 8/20 and C ≥ 8/20. A candidate who cannot write a query that returns the intended rows, or cannot say which index a predicate can use, has not met the module’s outcome whatever the aggregate.

Boundaries. Pass 40 · Merit 60 · Distinction 70, on the aggregate, subject to both floors. A script at 72 with A at 7 is recorded as 39 and a fail.