Second and Third Normal Form

2NF and 3NF: "the key, the whole key, and nothing but the key"

Once data is atomic (1NF), redundancy can still hide in dependencies. The memorable rule for a well-normalized table: every non-key column depends on the key, the whole key, and nothing but the key.

2NF: the whole key

No column may depend on only part of a composite key. In an order_item(order_id, product_id, qty, product_name) table keyed on (order_id, product_id), product_name depends only on product_id, half the key. That's a partial dependency: it repeats product_name on every line the product appears in. Fix: move product_name to a products table keyed on product_id.

3NF: nothing but the key

No non-key column may depend on another non-key column. In orders(order_id, customer_id, customer_email), customer_email depends on customer_id, not on order_id. That's a transitive dependency (key → customer_id → email). Fix: move customer_email to a customers table keyed on customer_id.

Worked 2NF/3NF split

Start with one flat table:

order_line(order_id, product_id, qty, product_name, customer_id, customer_email)

Decompose:

-- 2NF: product attributes depend only on product_id
CREATE TABLE products  (product_id INTEGER PRIMARY KEY, product_name TEXT);
-- 3NF: customer attributes depend only on customer_id (transitive via orders)
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, email TEXT);
CREATE TABLE orders    (order_id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers);
CREATE TABLE order_items (
    order_id   INTEGER REFERENCES orders,
    product_id INTEGER REFERENCES products,
    qty        INTEGER,
    PRIMARY KEY (order_id, product_id)
);

Now product_name and email each live in exactly one place. Change an email once; every order reflects it. No update anomalies.

In the warehouse this differs, by intent. 3NF is the gold standard for OLTP systems (safe writes, no anomalies). Analytical warehouses often stop short of full normalization or deliberately denormalize (next lesson) because joins are the expensive part of a read. So DEs normalize the source-of-truth / staging layers and denormalize the mart layer. Same engineer, two different targets.

Common pitfall: over- or under-splitting. Under: leaving customer_email on orders (a real 3NF violation that causes update anomalies). Over: decomposing attributes that genuinely do depend only on the key into needless tables. Test each column: "does this depend on the whole key and nothing but the key?" If no, split; if yes, leave it.

Recap. 2NF removes partial dependencies (on part of a composite key); 3NF removes transitive dependencies (on another non-key column). Decompose so every fact is stored exactly once: the OLTP ideal you'll later denormalize for analytics.

Execution mode: you write a multi-statement script. It runs against a fresh in-memory SQLite DB, then hidden assertion queries check that each fact now lives in exactly one table.