Second and Third Normal Forms

Second normal form (2NF) forbids partial dependencies and third normal form (3NF) transitive ones; Codd defined both in 1971. In William Kent's words, every non-key column states a fact about "the key, the whole key, and nothing but the key." The fix: move the dependent columns into a table keyed by their determinant, and leave the determinant behind as a foreign key.

Decomposing the sheet into 2NF, then 3NF, and checking that nothing was lostSQL

CREATE TABLE products_2nf (PRIMARY KEY (sku)) AS
  SELECT DISTINCT sku, title, category FROM sheet;
CREATE TABLE lines_2nf (PRIMARY KEY (order_no, sku)) AS
  SELECT order_no, sku, price, qty FROM sheet;
CREATE TABLE orders_2nf (PRIMARY KEY (order_no)) AS
  SELECT DISTINCT order_no, status, email, cust_name, country FROM sheet;
CREATE TABLE customers_3nf (PRIMARY KEY (email)) AS
  SELECT DISTINCT email, cust_name, country FROM orders_2nf;
ALTER TABLE orders_2nf DROP COLUMN cust_name, DROP COLUMN country;
SELECT COUNT(*) AS lost FROM (TABLE sheet EXCEPT
  SELECT order_no, status, email, cust_name, country, sku, title, category, price, qty
  FROM lines_2nf JOIN orders_2nf USING (order_no) JOIN customers_3nf USING (email)
  JOIN products_2nf USING (sku)) AS d;

Output

+------+
| lost |
+------+
|    0 |
+------+

The join of 16 lines, 9 orders, 6 customers and 8 products rebuilds every sheet row, and joins along keys cannot invent rows, so the split is lossless. To reach shop, categories get a table and surrogate id columns replace natural keys (Surrogate Versus Natural Keys). The anomalies are gone:

The three anomalies are gone in the normalized schemaSQL

START TRANSACTION;
UPDATE customers SET name = 'Benjamin Carter' WHERE email = 'ben@example.com';
INSERT INTO products (sku, title, category_id, price) VALUES ('BK-API-01', 'REST APIs', 2, 36);
DELETE FROM order_items WHERE order_id = 5;
DELETE FROM orders WHERE id = 5;
SELECT (SELECT COUNT(DISTINCT name) FROM customers WHERE email = 'ben@example.com') AS ben,
       (SELECT COUNT(*) FROM products WHERE sku IN ('BK-API-01', 'BK-UX-01')) AS books,
       (SELECT COUNT(*) FROM customers WHERE email = 'dara@example.com') AS dara;
ROLLBACK;

Output

+------+-------+------+
| ben  | books | dara |
+------+-------+------+
|    1 |     2 |    1 |
+------+-------+------+

Ben's name lives in one row, a book can exist unsold, and Dara and her book outlive the order.

MENU

Second and Third Normal Forms