Relational Data Models

Relational thinking begins before SQL. You decide what one row represents, which facts belong together, and how rows refer to one another. In this path, a tiny shop dataset stays constant: customers contains one row per customer, orders one row per order, and order_items one row per product line on an order.

Prerequisites

You should be comfortable with tabular data and basic Python-style values. No database server or prior SQL experience is required; every exercise runs locally in DuckDB.

Learning goals

• State the grain of a relation: what exactly one row represents.
• Distinguish a primary key from a foreign key.
• Explain why a relation is a set of named attributes over rows, while a SQL table may contain duplicate rows unless constraints prevent them.
• Follow a one-to-many relationship without confusing customer facts with order facts.

Relations, keys, and grain

customers.customer_id identifies a customer. orders.order_id identifies an order, while orders.customer_id refers back to a customer. That reference represents a one-to-many relationship: a customer may have zero, one, or many orders, but each order has one stored customer identifier.

A declared key is more than documentation. A PRIMARY KEY rejects duplicate and missing identifiers. A FOREIGN KEY can protect references when it is declared and enforced, but analytical files frequently arrive without such constraints. You must still test uniqueness and referential integrity.

Keep attributes at their natural grain. Customer region belongs in customers; order status belongs in orders; quantity and unit price belong in order_items. Copying order amounts onto every line item would make summing after a join unsafe.

Worked example

This is portable, Standard SQL syntax. Projection chooses columns; selection chooses rows.

SELECT customer_id, customer_name, region
FROM customers
WHERE customer_id <= 2
ORDER BY customer_id;

customer_id	customer_name	region
1	Ada	North
2	Ben	South

Following the foreign-key value does not merge grains; it simply selects the two order rows associated with customer 1.

SELECT order_id, customer_id, status
FROM orders
WHERE customer_id = 1
ORDER BY order_id;

order_id	customer_id	status
101	1	shipped
102	1	shipped

Limitations and common mistakes

• A name is not a durable identifier: two people can share it, and names can change.
• A primary key proves row identity, not that the business definition of the row is correct.
• A foreign-key-shaped column is not proof of referential integrity unless the data is checked or a constraint is enforced.
• SQL tables use bag semantics in many operations, so duplicate rows can exist and repeated matching rows matter.

Practice

Write down the grain and candidate key for each table. Then ask what should happen if an order contains the same SKU twice: is (order_id, sku) truly unique, or does the model need a line number?

Next steps

Next, use SELECT, filtering, and sorting to retrieve deterministic slices. Later, joins will show how relationships change row counts.

Primary sources

Checked 2026-10-14: ISO/IEC 9075-1:2023 SQL framework, DuckDB CREATE TABLE, and DuckDB constraints.