Build and change a database

1 / 9

Preview before changing rows

INSERT adds rows, UPDATE changes values, and DELETE removes rows. Before an UPDATE or DELETE, use a SELECT with the same WHERE condition to inspect its intended scope.

A missing WHERE can affect every row. A syntactically valid statement can therefore be a serious reasoning error. State what should remain unchanged and plan a query to check it.

This preview activity is read-only. The row-change workshops linked below use disposable exercise fixtures and check the resulting data, so you can safely practice the actual statements there.

SELECT id, name, price FROM menu_items WHERE price < 4 ORDER BY id;

Explain it in your own words

Which unchanged rows would you inspect afterward, and why is checking only the changed count insufficient?

2 / 9

Design constraints and verify the schema

CREATE TABLE defines the columns and constraints; ALTER changes a table’s structure; DROP removes the table. These affect a different level of the database from selecting or updating rows.

A primary key distinguishes rows. NOT NULL requires a value. A foreign key describes a relationship, but enforcement depends on the database configuration. Do not infer enforcement from a line drawn in a schema diagram.

Use the CREATE, ALTER, and DROP workshops below to run changes on their own independent fixtures. Inspect the live schema before and after each change.

SELECT id, name FROM shops ORDER BY id;

Explain it in your own words

What invalid row would you attempt in a disposable database to test a proposed constraint?

3 / 9

Reconcile an import and plan recovery

A reproducible import records the source, mappings, rejected rows, and validation queries. Row counts alone cannot detect duplicated amounts or incorrect identifiers.

Transactions group changes into a unit that can be committed or rolled back. Learn BEGIN, COMMIT, and ROLLBACK in the assigned desktop SQLite environment; the classroom browser runner accepts one statement at a time and manages its own read-only settings.

For a lab report, preserve the script and verification evidence. A screenshot of success is weaker than a rerunnable script that demonstrates the required invariants.

SELECT COUNT(*) AS orders, ROUND(SUM(total_amount), 2) AS total FROM orders;

Explain it in your own words

Could an import preserve the row count but change total sales? Give a concrete example.

4 / 9

Turn an ERD into an executable build order

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;

Explain it in your own words

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

5 / 9

Preserve raw text before deciding what to clean

The sqlite3 command-line .import command is not SQL. When its target does not exist, the CSV header supplies column names and SQLite creates text columns. Keep that staging copy while developing and auditing the transformation.

A blank CSV field arrives as an empty string. If blank means unknown for a field, NULLIF(TRIM(field), '') converts whitespace-only text to NULL. Apply that only where the dictionary supports it: a required identifier should not quietly become an accepted unknown.

Trail Gear keeps stg_items and load_audit for inspection. Its raw staging table deliberately has no application primary key; its rowid is used to trace this fixed import. Your client application tables still need the keys required by the ERD. The browser copy contains original fictional data and does not accept client uploads.

SELECT serial, note, typeof(note) AS storage FROM stg_items ORDER BY rowid;

Explain it in your own words

Which source rows become NULL? Why should the raw staging copy stay available until the decisions are explained?

6 / 9

Validate the text before casting it

CAST converts a value; it does not certify that the source was valid. SQLite can turn nonnumeric text into zero. A non-negative CHECK would then accept a made-up zero unless the load inspected the original text first.

This tiny practice file uses an explicit rule: daily_rate must be nonempty text containing only unsigned integer digits. That rejects negative values, decimal notation and unknown text. It is a rehearsal rule, not a universal currency parser. Use your client dictionary to define the real accepted formats and ranges.

Validate syntax and business meaning separately, and keep the source row and reason for anything excluded. Do not add OR IGNORE just to make a script finish: that can hide which constraint rejected which row. With default ABORT behavior, a constraint failure rolls back the failing INSERT statement, not earlier successful statements in the script.

SELECT serial, daily_rate, CAST(daily_rate AS INTEGER) AS converted_rate FROM stg_items ORDER BY rowid;

Explain it in your own words

Which malformed value becomes zero after CAST? Why would checking only the converted value lose evidence?

7 / 9

Find the rows a parent table cannot support

Use LEFT JOIN from staging to the parent to expose missing matches, and test the parent key for NULL. The direction matters: an INNER JOIN returns only rows that found a parent, making the very problem you need to document disappear.

The fixed Trail Gear source has a type_id of 99 that has no parent. Its audit records that row as Excluded. In a real client load, validate the identifier text before casting, and decide whether the brief requires exclusion, correction, or a nullable relationship. Those are business decisions, not interchangeable repairs.

A parent deletion is governed by its declared action and connection settings. This kit uses the default action and refuses to delete a referenced gear type with enforcement on. CASCADE, SET NULL and other actions behave differently; inspect the schema rather than assuming every foreign key has the same deletion rule.

SELECT s.serial, s.type_id, g.name FROM stg_items s LEFT JOIN gear_types g ON g.id = CAST(s.type_id AS INTEGER) ORDER BY s.rowid;

Explain it in your own words

Why would creating a fake parent merely to silence the foreign-key error be a poor default repair?

8 / 9

Account for every source row without inventing an exclusion count

For a first load into an empty one-to-one target, source rows should equal loaded rows plus excluded rows. Count all three independently. Computing excluded as source minus loaded guarantees the equation balances even when the load silently lost a valid row.

A reconciliation needs row-level evidence as well as totals. Trail Gear records one decision for every staging row. Compare accepted serials with loaded serials and inspect changed field values. Equal counts do not prove equal identities or correct amounts.

For appends, deduplication, bridge expansion or unpivoting, one source row may not map to one target row. Document the mapping and the appropriate units. The simple equation here applies to this fresh fixed-fixture load; it is not a universal import formula.

SELECT decision, COUNT(*) AS rows FROM load_audit GROUP BY decision ORDER BY decision;

Explain it in your own words

Could a balanced count hide the wrong serial or rate? Name an additional comparison that would expose it.

9 / 9

Prove the scripts can recreate the result

The desktop kit contains schema.sql, load.sql, verify.sql and a six-row CSV. From its extracted folder, open sqlite3 with a new database filename and use .read schema.sql followed by .read load.sql and .read verify.sql. The README gives the exact sequence. Repeat with another new filename so you can compare both builds without deleting the first.

The scripts enable foreign keys in their own connections and use .bail on to stop the CLI after an error. That is not a transaction rollback. A failed run may have earlier successful work; use a fresh rehearsal database to retry. The scripts intentionally target an empty database rather than silently appending on rerun.

Verify more than a file existing: schema and constraints, independent source/loaded/excluded counts, clean NULLs versus empty strings, ordered row values, a sanity join, and deliberately rejected CHECK, UNIQUE and FOREIGN KEY cases on a disposable copy. For Lab 10, Parts 1–3 are the in-class target; complete Part 4 and your own report by the current Canvas deadline. Assign every remaining client file an owner for Milestone 3.

SELECT id, serial, type_id, daily_rate, note FROM items ORDER BY id;

Explain it in your own words

Why is rate_sum not rental revenue? What evidence would you compare between two fresh builds before trusting reproducibility?

SQL reference & course materials

Use these reference chapters alongside the detailed lessons above. Each example identifies its database.

SQLite references: command-line imports, foreign-key enforcement, and constraint conflict behavior.

Put it to work in class

Lab 10: From Paper to Database →

Readiness practice and team activities complement the assigned Canvas lab. Official instructions and submission requirements stay in Canvas.