LEARN WITH ABDOU / DATABASE TEAM LAB
Trail Gear: prove the build and load
An original Lab 10 rehearsal: inspect a staged import in the browser, then build the same small fictional database with the downloadable SQLite kit. Your official lab uses your own client files.
Team: __________________________ Date: ______________
Dataset: Trail Gear: build rehearsal · Worksheet version: 2026-09-09
Open this exact lab: https://learnwithabdou.com/courses/mis3220/labs/import-rehearsal
Practice companion. Confirm official assignment and submission instructions in Canvas. Rotate query writer, evidence checker, and explainer. Record SQL, evidence, and reasoning.
Before class
P1. Get the right tool ready
Open a terminal and confirm sqlite3 starts. Read the official pre-lab and complete its Canvas check. Which commands belong to the SQLite shell rather than the browser SQL editor?
P2. Bring the build contract
Have the corrected client ERD, dictionary, CSVs and challenge rules ready. Write the parent-first build order and identify the first file to load. Which required fields and two business rules need constraints?
After the investigation
Use the desktop kit to build two fresh rehearsal databases and compare evidence. For your actual Lab 10 report, complete the official scratch warm-up, schema and first file in class; finish the rebuild by the Canvas deadline. Record owners for schema, each remaining file, reconciliation and sanity query. Export these practice notes separately from your official report.
Database schema
gear_types · 2 rows
| Column | Type | Key / required |
|---|---|---|
| id | INTEGER | PK |
| name | TEXT | NOT NULL |
items · 3 rows
| Column | Type | Key / required |
|---|---|---|
| id | INTEGER | PK |
| serial | TEXT | NOT NULL |
| type_id | INTEGER | NOT NULL |
| daily_rate | INTEGER | NOT NULL |
| note | TEXT |
type_id → gear_types.id
load_audit · 6 rows
| Column | Type | Key / required |
|---|---|---|
| source_row | INTEGER | PK |
| serial | TEXT | NOT NULL |
| decision | TEXT | NOT NULL |
| reason | TEXT | NOT NULL |
stg_items · 6 rows
| Column | Type | Key / required |
|---|---|---|
| serial | TEXT | |
| type_id | TEXT | |
| daily_rate | TEXT | |
| note | TEXT |
Original fictional rehearsal, not your client schema. items.type_id references gear_types.id. stg_items preserves all six raw TEXT rows; its implicit rowid matches load_audit.source_row by convention, not a declared foreign key. Three rows load, three are excluded. Additional target rules: gear_types.name and items.serial are UNIQUE; serial must be nonblank; daily_rate must be a nonnegative integer. The raw staging table deliberately has no application primary key. Missing item notes are NULL after trimming blanks. Use the desktop kit for mutations and .import.
Types and declared keys reflect the original teaching database. Check business meanings and logical relationships in the lecture.
Team investigation
1. The preview would update every type
A teammate intends to raise only Trail gear item rates below 10. Return id, serial and daily_rate for exactly that scope, ordered by id. Repair the preview; write the matching UPDATE in your notes and plan a changed-row and unchanged-row check. Execute changes only in a disposable desktop copy.
SELECT id, serial, daily_rate FROM items ORDER BY id;
SQL / evidence / explanation:
2. A blank still becomes a word of spaces
Return serial and clean_note for all staged rows, ordered by rowid. A blank or whitespace-only note means unknown. Repair the transformation and identify the row the initial query mishandles. Explain why a required identifier needs a different acceptance rule.
SELECT serial, NULLIF(note, '') AS clean_note FROM stg_items ORDER BY rowid;
SQL / evidence / explanation:
3. The invalid rate hides behind zero
List serial and daily_rate for every row violating the unsigned-whole-number rate rule, ordered by rowid. Repair this rejection query, then compare the original text with CAST output. Why can a nonnegative target CHECK fail to catch the original problem?
SELECT serial, daily_rate FROM stg_items WHERE CAST(daily_rate AS INTEGER) < 0 ORDER BY rowid;
SQL / evidence / explanation:
4. The orphan hunt loses the orphan
Return staged serial and type_id for rows with no gear_types parent, ordered by source row. Repair the query and compare it with the audit decision. Explain why inventing a type 99 is not an automatic fix.
SELECT s.serial, s.type_id FROM stg_items s JOIN gear_types g ON g.id = CAST(s.type_id AS INTEGER) WHERE g.id IS NULL ORDER BY s.rowid;
SQL / evidence / explanation:
5. The audit calls every row excluded
Return source_rows, loaded_rows and excluded_rows in one row, counting the three populations independently. Repair the excluded count. Then explain why calculating exclusions as source minus loaded would hide an unexplained missing row.
SELECT (SELECT COUNT(*) FROM stg_items) AS source_rows, (SELECT COUNT(*) FROM items) AS loaded_rows, (SELECT COUNT(CASE WHEN decision = 'Excluded' THEN 1 ELSE 0 END) FROM load_audit) AS excluded_rows;
SQL / evidence / explanation:
6. A sanity query doubles the rate total
Return gear_type and rate_sum for each type with loaded items, ordered by gear_types.id. Repair the join, then compare the grouped totals with the ordered item rows. Would matching this total prove a rebuild is correct? Explain what else to compare.
SELECT g.name AS gear_type, SUM(i.daily_rate) AS rate_sum FROM gear_types g JOIN items i ON 1 = 1 GROUP BY g.id, g.name ORDER BY g.id;
SQL / evidence / explanation:
Your Lab 10 client build plan
Use your own corrected ERD, dictionary, CSVs and official handout. Complete the scratch warm-up, schema and first file in class; prove the rebuild before the Canvas deadline.
Desktop rehearsal kit: learnwithabdou.com/labs/lab10-build-rehearsal.zip
Client: __________________________ Build order (parents first):
Files and owners
| Source file / target table | Owner | Cleaning rule / exclusion reason |
|---|---|---|
| ____________________ | ____________ | ________________________ |
| ____________________ | ____________ | ________________________ |
| ____________________ | ____________ | ________________________ |
Reconcile each target
| Table | Source | Loaded | Excluded / expanded and why |
|---|---|---|---|
| ____________ | ______ | ______ | ________________________ |
| ____________ | ______ | ______ | ________________________ |
| ____________ | ______ | ______ | ________________________ |
Schema owner: __________ Reconciliation owner: __________ Sanity query owner: __________
Proof to capture
Foreign keys enabled in the loading connection; expected keys and constraints; exact CHECK, UNIQUE and FK rejection errors; cleaned NULL and empty-string counts; source decision list; a sanity join; two scripts that rebuild from a fresh file. Keep SQL and output as text.
Fresh-build comparison and remaining work:
Use Q1–Q15 from Canvas in your individual report. These practice question numbers do not replace the official ones.