LAB 13 / PREPARATION & TEAM PRACTICE
Harbor Workshops: rescue the inherited sheet
Lab 13 preparation with eight readings and six team investigations. Find the key, explain the anomalies, migrate to seven tables and prove what was preserved.
Start with the sheet, not the finished tables
Extract the kit, read README.txt and make a copy of rehearsal.db. Start key and anomaly questions on raw_bookings. For migration questions, run clean.sql, schema.sql, migrate.sql and verify.sql in that order in desktop SQLite. Use a fresh copy when a question asks for the initial data.
The browser saves predictions and evidence; SQL runs on your computer. Keep intentional-error demonstrations in one connection, inspect the state and ROLLBACK before retrying. Run migration comparisons before the later exercises change business data.
Quick schema: original sheet & correction decisions
The inherited source
rehearsal.db starts with raw_bookings: six rows, 19 nullable columns, no declared key or constraints. Every value is invented. One row is one booked seat at one session.
session_date · TEXT
session_time · TEXT
seat_code · TEXT
zone · TEXT
room_name · TEXT
room_address · TEXT
room_capacity · INTEGER
event_title · TEXT
facilitator · TEXT
host_name · TEXT
host_email · TEXT
learner_email · TEXT
learner_name · TEXT
learner_phone · TEXT
learner_city · TEXT
booking_type · TEXT
fee_cents · INTEGER
booked_on · TEXT
payment_method · TEXT
Rules supplied by the practice client
Only one session starts at a date/time; seat codes are unique across rooms. Event titles, host names, room names and learner emails are identifiers within this program. The learner directory holds current contacts. Reconsider the model if those business rules change.
Approved corrections
Ari confirmed 555-0199 as the current phone; Bea confirmed 555-0102 for the missing entry; the host confirmed Mina Park as the facilitator spelling. clean.sql preserves raw input, creates cleaned_bookings and logs three field changes in resolution_log. These confirmations are invented exercise facts.
Amounts are integer cents. The client knows of an announced Bookbinding event and an April 5 session with no bookings; the booking sheet cannot supply those facts. Source-row numbers in the correction log are snapshot provenance, not business keys.
Quick schema: seven destination tables & relationships
The seven destination tables
Counts are immediately after the migration, before later changes. All non-id attributes listed are required (NOT NULL). Identifiers and foreign-key ids are INTEGER except the TEXT seat code; fee_cents and capacity are INTEGER, and remaining values are TEXT.
rooms · 2 rows
id PK; name UNIQUE; address; capacity CHECK > 0
hosts · 2 rows
id PK; name UNIQUE; email
events · 2 rows
id PK; title UNIQUE; facilitator; host_id FK → hosts.id
sessions · 3 rows
id PK; session_date; session_time; event_id FK → events.id; room_id FK → rooms.id; UNIQUE(session_date,session_time)
seats · 4 rows
code TEXT PK; zone; room_id FK → rooms.id
learners · 4 rows
id PK; email UNIQUE; name; phone; city
bookings · 6 rows
id PK; session_id FK → sessions.id; seat_code FK → seats.code; learner_id FK → learners.id; booking_type; fee_cents CHECK >= 0; booked_on; payment_method; UNIQUE(session_id,seat_code)
sessions uses UNIQUE(session_date, session_time). bookings uses UNIQUE(session_id, seat_code). Enable foreign keys in every connection. Separate seat and session FKs do not ensure equal rooms; verify.sql checks that business rule.
raw_bookings, cleaned_bookings and resolution_log remain as evidence alongside these seven tables. rebuilt is a view reconstructing the original 19 columns from the destination. Compare it with the corrected source, with counts and uniqueness checks as well as EXCEPT in both directions.
EIGHT CONNECTED TOPICS
Understand, redesign, prove
TOPIC 1 OF 8
Find the grain, then challenge the key
One source row records one booked seat at one session. In this fictional program, only one session starts at a given date and time, and seat codes are unique across rooms. Under those stated rules, (session_date, session_time, seat_code) identifies a booking.
Test duplicates and missing key components separately. Count a SELECT DISTINCT of the columns rather than concatenating values: concatenation can collide or hide NULLs. A unique sample supports a candidate; the business rule determines whether it remains a key tomorrow.
If two rooms can start sessions simultaneously, date/time alone is no longer a session identifier. Model that changed rule before choosing a UNIQUE constraint. Adding an arbitrary row id does not resolve dependencies or duplicate business records.
Worked SQL · run at the described desktop stage
SELECT COUNT(*) AS rows_in_sheet FROM raw_bookings; SELECT session_date,session_time,seat_code,COUNT(*) AS copies FROM raw_bookings GROUP BY session_date,session_time,seat_code HAVING COUNT(*)>1; SELECT COUNT(*) AS missing_keys FROM raw_bookings WHERE session_date IS NULL OR session_time IS NULL OR seat_code IS NULL;
Pause and investigate: Compare distinct triples with distinct date/seat pairs. Which booked seats show why time is necessary?
TOPIC 2 OF 8
Test a dependency without mistaking evidence for a rule
A functional dependency X → Y says that equal X values imply equal Y values under the model. Here event_title determines facilitator and host; learner_email identifies the current learner directory entry; seat_code determines zone and room; room_name determines address and capacity.
COUNT(DISTINCT Y) detects multiple known values but ignores NULL. Report missing values too. No conflicting rows means no observed counterexample in this sample, not proof of a universal business rule.
A current directory is the stated goal of this fixture. If the requirement were the phone used at booking time, differing phone values could be legitimate history. Resolve the intended meaning before calling data wrong.
Worked SQL · run at the described desktop stage
SELECT learner_email,COUNT(DISTINCT learner_phone) AS known_phones, SUM(CASE WHEN learner_phone IS NULL THEN 1 ELSE 0 END) AS missing_phones FROM raw_bookings GROUP BY learner_email ORDER BY learner_email;
Pause and investigate: Which learner has conflicting known numbers, and which has one known number plus a missing value? Test event_title → facilitator as well.
TOPIC 3 OF 8
Clean from an approved source and keep the evidence
Normalization cannot decide which spelling or phone is correct. The rehearsal README supplies three explicit fictional confirmations: Ari’s current phone, Bea’s missing phone and the host’s facilitator spelling. These are exercise facts, not an automatic “most common wins” policy.
clean.sql preserves raw_bookings, creates cleaned_bookings and writes a resolution_log with source row, field, old value, new value and reason. It then changes the copy in a transaction. Run it only once on a fresh rehearsal copy.
The migration must match the corrected source. Comparing it directly with raw input should reveal the approved changes; that is not evidence of migration loss. Keep both the correction audit and the migration comparison.
Worked SQL · run at the described desktop stage
SELECT source_row,field,old_value,new_value,reason FROM resolution_log ORDER BY source_row,field; SELECT event_title,COUNT(DISTINCT facilitator) AS spellings FROM cleaned_bookings GROUP BY event_title HAVING COUNT(DISTINCT facilitator)>1;
Pause and investigate: After clean.sql, explain each log row and check both known-value conflicts and missing phones again. Why is SELECT DISTINCT alone not a cleaning policy?
TOPIC 4 OF 8
Separate facts that must survive independently
An update anomaly repeats one host contact across bookings. An insertion anomaly forces an announced event or empty session to masquerade as a booking. A deletion anomaly removes the only record of a session when its final booking disappears.
raw_bookings has nullable columns, so it accepts an almost-empty “session row.” That success does not make it a valid booking. COUNT(*) and COUNT(seat_code) reveal the difference.
Use a transaction to demonstrate deletion, inspect what disappeared, then ROLLBACK. The need to retain a session without bookings is a reason to model sessions separately, not a reason to invent a fake seat.
Worked SQL · run at the described desktop stage
BEGIN;
INSERT INTO raw_bookings(session_date,session_time,event_title,room_name)
VALUES('2026-04-03','10:00','Print Lab','Studio A');
SELECT COUNT(*) AS rows,COUNT(seat_code) AS booked_seats FROM raw_bookings;
ROLLBACK;Pause and investigate: Identify the one-booking session. What other facts disappear if its only raw row is deleted? Which should have independent records?
TOPIC 5 OF 8
Use dependencies to defend the seven-table design
The source uses single-valued cells. Assess first normal form with the intended booking key; the nullable unconstrained SQL import does not enforce that key. A date/time determines event and room, while seat_code determines zone: those depend on only parts of the composite booking key.
Second normal form removes partial dependencies of non-prime attributes on candidate keys. Third normal form also rules out transitive dependencies of that kind. For this fixture, event_title → facilitator, host_name → host_email, and learner_email → current directory details identify facts to separate.
The design has rooms, hosts, events, sessions, seats, learners and bookings. The booking retains its historical fee_cents. A surrogate id does not make the remaining repeated event/session facts normalized. Retain natural-key UNIQUE constraints as well as ids.
Worked SQL · run at the described desktop stage
SELECT name,sql FROM sqlite_master
WHERE type='table' AND name IN
('rooms','hosts','events','sessions','seats','learners','bookings')
ORDER BY name;Pause and investigate: Draw all seven entities and seven declared FK relationships. Compare a design that removes sessions but adds event_id and room_id to each booking: which anomalies remain?
TOPIC 6 OF 8
Load parents first and make failure visible
Run schema.sql to create the seven empty destination tables, then migrate.sql to load from the corrected source. Parent identifiers are resolved by joins on the declared natural keys. Learner email is a key; a person’s display name is not.
The migration runs in one transaction. It checks row preservation, reconstruction and seat/session room agreement before COMMIT. NOT NULL, UNIQUE, CHECK and foreign keys reject invalid target rows. A failing statement alone does not promise that earlier statements in an open transaction were undone.
In sqlite3 use .bail on when reading scripts. If a script fails in an interactive connection, inspect the error and ROLLBACK before retrying on a fresh copy. Do not continue to COMMIT a partly completed manual migration.
Worked SQL · run at the described desktop stage
PRAGMA foreign_keys=ON; -- In the SQLite shell, with clean.sql already completed: -- .bail on -- .read schema.sql -- .read migrate.sql SELECT COUNT(*) AS migrated_bookings FROM bookings;
Pause and investigate: Why can a misspelled natural key silently drop a row in an INNER JOIN? What does the migration’s pre-COMMIT check catch that the destination row’s foreign keys would not?
TOPIC 7 OF 8
Rebuild the whole row, not just the money
Run verify.sql immediately after migration, before the payoff exercises change the normalized data. Compare seven entity counts, total historical fees and both directions of a reconstructed 19-column row comparison. Check declared foreign keys and the business rule connecting seat room to session room.
EXCEPT compares distinct rows and does not preserve duplicate multiplicity or output order. Key uniqueness plus row counts protects this one-row-per-booking contract; other tasks may require grouped row-frequency comparisons too.
Equal totals can hide a fee moved to the wrong booking, a missing zero-value row or an incorrect learner relationship. The reconstructed columns must mean the same things in the same order. A separate audit explains approved raw-to-clean changes.
Worked SQL · run at the described desktop stage
SELECT (SELECT COUNT(*) FROM (SELECT * FROM cleaned_bookings EXCEPT SELECT * FROM rebuilt)) AS source_not_rebuilt, (SELECT COUNT(*) FROM (SELECT * FROM rebuilt EXCEPT SELECT * FROM cleaned_bookings)) AS rebuilt_not_source;
Pause and investigate: Predict the comparison against cleaned_bookings, then against raw_bookings. Would matching the raw source exactly mean the phone correction worked?
TOPIC 8 OF 8
Keep historical facts and test cross-table rules
An event and session can exist before any seat is booked. A LEFT JOIN from sessions to bookings can display zero activity. Changing a host contact now updates one host record rather than every booking.
fee_cents is what was paid at booking time. It remains on bookings even if a current price could be looked up elsewhere. Historical transaction facts are not interchangeable with current reference values.
Separate valid foreign keys to a seat and a session do not prove they belong to the same room. The diagnostic below checks that rule. A production design could enforce it with an appropriate composite-key design or a trigger; normalization alone is not an enforcement mechanism.
Worked SQL · run at the described desktop stage
SELECT b.id,b.seat_code,st.room_id AS seat_room,s.room_id AS session_room FROM bookings b JOIN seats st ON st.code=b.seat_code JOIN sessions s ON s.id=b.session_id WHERE st.room_id<>s.room_id ORDER BY b.id;
Pause and investigate: In a transaction, insert a booking for seat B1 into a Studio A session with otherwise valid keys. Observe PRAGMA foreign_key_check and this diagnostic separately, then ROLLBACK. Which check catches the mismatch?
PREDICT · RUN ON DESKTOP · EXPLAIN
Six team investigations
Try questions 1–3 before the official design work; continue questions 4–6 with the migration rehearsal at home. Rotate writer, evidence checker and explainer. Use fresh rehearsal copies and follow each question’s required stage. These practice numbers are separate from official Lab 13 Q1–Q12 and Part B.
Opening your saved work…
Sign in to save across devices → · Download your guest work before switching accounts.
1. The key forgets the evening session
Use a fresh rehearsal database. The candidate calls date plus seat a key. Return rows_in_sheet, distinct_full_keys and distinct_date_seat in one row using distinct-column subqueries. Also check NULL components and duplicate triples. Explain the collision and the business assumption that would require an even wider session key.
SELECT session_date,seat_code,COUNT(*) AS bookings FROM raw_bookings GROUP BY session_date,seat_code HAVING COUNT(*)>1;
3. The design has keys but nowhere for an empty session
A teammate removes sessions and stores date, time, event_id and room_id on bookings. Sketch the missing entity and relationships. Run the query below to measure repeated session facts. Then demonstrate the insert or delete anomaly in a transaction on raw_bookings and ROLLBACK. Explain why adding a booking_id does not fix the model.
SELECT session_date,session_time,event_title,COUNT(*) AS bookings FROM raw_bookings GROUP BY session_date,session_time,event_title ORDER BY session_date,session_time;
4. A perfect match to the raw sheet is the wrong target
Starting from a fresh copy, run clean.sql, schema.sql and migrate.sql in order. Run verify.sql before any other changes. Report all seven count pairs, both fee totals and both round-trip counts against cleaned_bookings. Run the candidate against raw_bookings too. Explain every difference using resolution_log; which changes were corrections and which would be migration failures?
SELECT COUNT(*) AS raw_not_rebuilt FROM (SELECT * FROM raw_bookings EXCEPT SELECT * FROM rebuilt);
5. Every foreign key is valid, but the seat is in another room
After a clean migration, start a transaction. Insert booking 99 for session 1, seat B1, learner 1, Adult, 2500 cents, booked 2026-03-08 by Card. Run both checks below, observe the mismatch, then ROLLBACK. Explain which rule ordinary FKs prove and which extra rule needs a query, composite relationship or trigger.
PRAGMA foreign_key_check; SELECT b.id,b.seat_code,st.room_id AS seat_room,s.room_id AS session_room FROM bookings b JOIN seats st ON st.code=b.seat_code JOIN sessions s ON s.id=b.session_id WHERE st.room_id<>s.room_id ORDER BY b.id;
6. The launch report hides the new session
After migration, create a new event called Bookbinding (facilitator Lee Chen, host_id 1) and a session on 2026-04-05 at 10:00 in room_id 1. Write SQL returning session_date, session_time, event_title, bookings and revenue_cents for all sessions, ordered by date/time, including the empty one. Compare the candidate. Keep fee_cents as historical evidence and explain why future reference changes should not rewrite it.
SELECT s.session_date,s.session_time,e.title AS event_title, COUNT(b.id) AS bookings,SUM(b.fee_cents) AS revenue_cents FROM sessions s JOIN events e ON e.id=s.event_id JOIN bookings b ON b.session_id=s.id GROUP BY s.id ORDER BY s.session_date,s.session_time;
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.
Your official Lab 13 and client audit
This is a one-day teaching week. After the quiz, focus on official Q1–Q7 and Part B, reserving 10–15 minutes for your own client schema. Q8–Q12 continue at home: create seven tables, migrate, reconcile and explain the payoff. Use inherited.db from Canvas for that assigned work; this kit is original preparation.
Q7 needs your ERD image; Q8 needs the complete SQL schema; Q10 needs counts, money and the round trip. Keep Check the AI, Part B’s tests and schema fix, and your personal reflections in your own report. Use the handout’s exact column aliases and question numbers.
For Part B, name the main fact table’s grain and key, test its dependencies, list violations or defensible exceptions, and fix one issue in schema.sql before the final build. Record who ran the audit and how the revised schema rebuilds. Keep client files locally.
Official template & submission →SQLite references: aggregate and NULL behavior · foreign keys · transactions. Your class handout controls the assigned model, report and deadlines.