TRAIL GEAR - LAB 10 BUILD REHEARSAL
Original fictional practice, not a client dataset or official Lab 10 solution.
Version 2026-09-09

1. Extract this folder. Open a terminal in the extracted folder containing items.csv.
2. Run: sqlite3 rehearsal.db
   Use a new filename that does not exist yet. In the sqlite3 prompt enter:
   .read schema.sql
   .read load.sql
   .read verify.sql
   .quit
   Dot commands have no semicolon and belong in sqlite3, not a browser SQL editor.
3. Expected: 2 gear types; 6 staged rows; 3 loaded items; 3 exclusions.
   Missing notes = 2; empty notes = 0; foreign_key_check returns no rows;
   integrity_check returns ok. The rate sum is 18 for Trail gear, 12 for Water gear.
   These are sums of listed daily rates, not rental revenue.
4. Prove rebuilding without deleting your first copy:
   sqlite3 rehearsal-again.db
   .read schema.sql
   .read load.sql
   .read verify.sql
   .quit
   Use another new filename if rehearsal-again.db already exists. Compare ordered
   item rows, source decisions, counts and totals in both builds.

EXPERIMENTS (only on a disposable copy after the build)
Open the copy with sqlite3 and run PRAGMA foreign_keys = ON; before each session.
Keep successful changes in a transaction and use ROLLBACK when experimenting.
- Preview a one-item UPDATE with SELECT and the exact same WHERE; run the UPDATE,
  then SELECT changes();. Inspect both changed and unchanged rows; roll it back.
- Try a duplicate serial, a negative daily_rate and a type_id with no parent.
  Each should fail. Copy the exact error, not just 'it failed'.
- Try deleting a gear type that items reference. The default FK action refuses it.
  Do not delete children merely to silence an error: decide what deletion means.
- Add a nullable column on a disposable copy, inspect old rows, then rebuild.

WHY THIS LOAD IS SMALL
The CSV is deliberately fixed and tiny. Serial values are nonempty and unique.
The load rejects unknown type IDs and any rate not written as unsigned integer
digits; this is a practice rule, not a universal currency parser. Duplicate serials
would fail the target UNIQUE constraint, not be silently repaired or ignored.
Staging and audit tables are retained for evidence. The raw staging table is not
part of the application's ERD and deliberately has no primary key.
An INSERT ... SELECT uses SQLite's default ABORT behavior: a constraint failure
backs out that statement; it does not undo earlier statements in the script.
.bail on stops this CLI script after an error. Start again in a fresh database.

TRANSFER TO YOUR CLIENT
Use the official Lab 10 handout, corrected Milestone 2 ERD, data dictionary, source
counts and challenge rules. Build your own schema.sql and load.sql. Parts 1-3 are
the in-class target; Part 4 proves rebuilding before the deadline in Canvas.
Assign schema, each remaining file, reconciliation and sanity query to owners.
No client files are uploaded by this practice kit or its companion activity.

References: https://www.sqlite.org/cli.html
https://www.sqlite.org/foreignkeys.html
https://www.sqlite.org/lang_conflict.html
