Relational Databases and SQL Fundamentals

Read a relational schema and declare grain

Read a relational schema and declare grain

A relational table describes one kind of entity at a declared grain. A primary key identifies a row; a foreign key references a parent key. The Haven orders table has one row per order, while order_lines has one row per item line. Repeated order_id values in lines are expected. Products supply descriptive attributes, but each line retains its own historical unit price. Changing today’s product price must not rewrite past transactions. Inspect setup.sql before querying and distinguish a declared constraint from a relationship that merely looks plausible. SQLite foreign-key enforcement is connection-specific, so the setup enables PRAGMA foreign_keys=ON.

Follow a concrete example

Follow one entity through the schema: O001 is an order, while L0011 and L0012 are its two lines. Both line records reference O001, but their line IDs differ. The uniqueness condition belongs on line_id, not on order_id in the child table. A source inventory should therefore record both 48 raw line records and 24 distinct parent orders.

SQLite example

SELECT COUNT(*) AS lines,COUNT(DISTINCT line_id) AS unique_lines,COUNT(DISTINCT order_id) AS parent_orders FROM order_lines

Further reading: SQLite window functions, SQLite query planning. The case and exercises are authored for DataCareerHub.

Your task

Draw the three-table relationship diagram. Execute inspect_keys and list each primary/foreign key and its grain.

Use learner_worksheet.txt and the provided source/code files. Save your own artifact and compare the separate solution after attempting the task.

Run an explicit SELECT in a local client

Run an explicit SELECT in a local client

SQL states the result you want; the engine chooses an execution strategy. Use a local SQLite client or the included Python runner, never your hosting administration database. SELECT order_id, order_date, channel is an explicit projection that documents the required fields. SELECT * is useful for a brief inspection but makes a saved analysis sensitive to future schema changes. ORDER BY defines presentation order; a table has no guaranteed default row order. LIMIT reduces the returned rows but does not define which rows are first without a deterministic ordering. The course uses SQLite 3.39+ syntax and supplies portable alternatives where needed.

Follow a concrete example

In a client, open the supplied fixture as a practice database and run one statement at a time. Check that the selected column names match your saved output, then add a deterministic ordering before applying LIMIT. Save the SQL alongside the result. If a client asks for hosting credentials, you are using the wrong environment for this file-based exercise.

SQLite example

SELECT order_id,order_date,channel FROM orders ORDER BY order_id LIMIT 3

Inspect the reference implementation

Language: Python 3.12. The complete downloadable file includes the named helpers and the fresh-output command-line runner. Trace the source, transformation and result against this lesson’s contract before running it locally.

def sql_lab(source,out):
 # Select-only teaching SQL stored separately per module. Local fixture opened read-only.
 connection=sqlite3.connect('file:'+str((Path(source)/'haven_practice.sqlite').resolve())+'?mode=ro',uri=True)
 connection.row_factory=sqlite3.Row
 queries=json.loads((Path(source)/'queries.json').read_text())
 result={name:[dict(r) for r in connection.execute(sql)] for name,sql in queries.items()}
 connection.close();return result

Further reading: SQLite window functions, SQLite query planning. The case and exercises are authored for DataCareerHub.

Your task

Run select_projection, then remove ORDER BY in a working query. Explain why an apparently unchanged result is not an ordering guarantee.

Use learner_worksheet.txt and the provided source/code files. Save your own artifact and compare the separate solution after attempting the task.

Validate relationships before trusting analysis

Validate relationships before trusting analysis

A relationship audit checks the actual data against the intended model. A LEFT JOIN retains every line and exposes absent parents as null parent fields. Filtering those nulls identifies orphan lines without hiding them. A successful INNER JOIN cannot demonstrate that unmatched records do not exist, because it excludes them. In SQLite, inspect the schema and connection settings as well as the query result. An empty exception list supports this fixture’s relationship check but does not prove the export is complete or its business definitions are correct. Record the query, expected population and source hash alongside the result.

Follow a concrete example

Test the negative case in a new in-memory database: insert a line referencing O999 while foreign-key enforcement is enabled. The database should reject it. Then compare this constraint test with the orphan SELECT, which inspects existing data. These checks complement one another: a constraint can protect future writes without proving that an earlier import was complete.

SQLite example

SELECT l.line_id FROM order_lines l LEFT JOIN orders o USING(order_id) WHERE o.order_id IS NULL

Further reading: SQLite window functions, SQLite query planning. The case and exercises are authored for DataCareerHub.

Your task

Run relationship_audit and draft a source contract. In a separate in-memory copy, try a line with an absent order while foreign keys are enabled.

Use learner_worksheet.txt and the provided source/code files. Save your own artifact and compare the separate solution after attempting the task.