LEARN WITH ABDOU / LAB 13 PRACTICE / VERSION 2026-09-09
Harbor Workshops: rescue the inherited sheet
Team: __________________________ Date: ______________
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.
Workshop and kit: learnwithabdou.com/courses/mis3220/labs/schema-rescue
Prepare and pace the activity
Read the official Pre-Lab 13 and complete its Canvas check. The class target after the quiz is official Q1–Q7 plus Part B, including 10–15 minutes on your client schema. Official Q8–Q12 continue at home.
Use this original rehearsal for preparation: questions 1–3 cover the sheet and design; questions 4–6 accompany the desktop migration. Extract the kit and make fresh copies of rehearsal.db. Rotate writer, evidence checker and explainer.
Reading map
1. Find the grain, then challenge the key
2. Test a dependency without mistaking evidence for a rule
3. Clean from an approved source and keep the evidence
4. Separate facts that must survive independently
5. Use dependencies to defend the seven-table design
6. Load parents first and make failure visible
7. Rebuild the whole row, not just the money
8. Keep historical facts and test cross-table rules
Keep the stages separate
raw_bookings → clean.sql → cleaned_bookings + correction log → schema.sql → migrate.sql → verify.sql. Seven normalized tables retain the facts; the raw and corrected source remain as evidence. Run comparisons before later business changes.
Keep SQL as text, actual output or errors, and reasoning. The website saves notes; SQL runs in desktop SQLite. Inspect failed changes in the same connection and ROLLBACK before retrying. Do not submit these practice numbers as official answers.
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.
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.
See the seven relationships
Explain the decomposition
A repeated fact moved to its own entity:
An announced event or empty session can now exist because:
A historical value deliberately retained on the booking:
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;
Prediction / SQL or design / observed evidence / explanation:
2. The phone check overlooks a missing value
Run this candidate. Repair it to show learner_email, known_phones and missing_phones for every learner. Identify both a known-value conflict and an incomplete directory entry. Read the kit’s correction decisions, run clean.sql once, and repeat the checks on cleaned_bookings. Explain why one current phone is a business assumption, not a rule proved by the query.
SELECT learner_email,COUNT(DISTINCT learner_phone) AS known_phones FROM raw_bookings GROUP BY learner_email HAVING COUNT(DISTINCT learner_phone)>1;
Prediction / SQL or design / observed evidence / explanation:
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;
Prediction / SQL or design / observed evidence / explanation:
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);
Prediction / SQL or design / observed evidence / explanation:
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;
Prediction / SQL or design / observed evidence / explanation:
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;
Prediction / SQL or design / observed evidence / explanation:
Part B: audit your own client schema
Return to learnwithabdou.com/courses/mis3220/labs/lab-13. Use inherited.db and the official handout for Part A. Your client files stay in your own project folder.
Client / auditor: _________________________________________________
Main fact table, one-row meaning and candidate key:
Dependency tested, including duplicate and missing-value checks:
Observed violation or defensible exception, with evidence:
One schema.sql fix, its owner and the rebuild/reconciliation test:
Report checklist
Official Q1–Q12; Q7 ERD image; Q8 complete SQL; Q10 counts, fees and round trip; Check the AI; Part B audit and fix; your own reflections. Use the Canvas template and submit Lab13_LastName.docx by the official deadline.
The practice diagram uses arrows to explain dependencies. Your assigned ERD must mark columns, PKs, FKs and the required crow’s-foot relationship symbols. Explain what the constraints enforce and which additional business rule needs checking.