Build and change a database

READ & UNDERSTAND

Lab 10 builds your own client database. The original Trail Gear example here is only a rehearsal: gear_types is the parent, items refers to it, stg_items preserves raw file text, and load_audit records each source decision. The actual client ERD and dictionary determine your application tables.

Start with tables that have no foreign-key dependencies, then tables whose parents already exist. Create and load parents before children. A table nobody references is not necessarily a parent: read the direction of each foreign key, not just the diagram layout.

A declaration alone does not prove enforcement. In the SQLite command line, run PRAGMA foreign_keys = ON; and then PRAGMA foreign_keys; in the same connection before loading. Defaults can depend on the build or tool. You cannot turn enforcement on in the middle of a transaction. The browser exercises inspect the finished rehearsal; use the desktop kit and linked DDL workshops to execute changes.

SELECT id, name FROM gear_types ORDER BY id;

Your question

Return i.serial and g.name AS gear_type for every loaded item by joining its declared parent, ordered by i.id.

Explain your evidence

Which table must be seeded first? What invalid insert would prove the relationship is enforced in the loading connection?

Try a query to unlock the worked-answer discussion.