BCNF, 4NF and 5NF

BCNF, 4NF and 5NF

Boyce-Codd, Fourth, and Fifth Normal Forms

Boyce-Codd normal form (BCNF), from 1974, requires every determinant to be a candidate key, which differs from 3NF only when keys overlap. With unique titles stored on order lines, both (order_no, sku) and (order_no, title) are keys, and sku → title passes 3NF because title is part of a key. But sku alone is no key, so the title repeats per sale and can drift:

A table in 3NF but not in BCNF accepts two titles for one bookSQL

CREATE TABLE lines_titled (order_no INT, sku VARCHAR(12), title VARCHAR(60), qty INT,
                           PRIMARY KEY (order_no, sku), UNIQUE (order_no, title));
INSERT INTO lines_titled VALUES (1, 'BK-PHP-01', 'Modern PHP in Practice', 1),
                                (6, 'BK-PHP-01', 'Modern PHP, 2nd Edition', 2);
SELECT sku, COUNT(DISTINCT title) AS titles FROM lines_titled GROUP BY sku;

Output

+-----------+--------+
| sku       | titles |
+-----------+--------+
| BK-PHP-01 |      2 |
+-----------+--------+

The title belongs in products, where sku is a key. Fourth normal form (4NF) (Fagin, 1977) separates independent lists: a table of a book's authors and formats needs all 6 pairs for 2 authors and 3 formats, where two tables need 5 rows. Fifth normal form (5NF) splits a three-way table into pairs only when a rule guarantees the pairs rejoin exactly. Aim for BCNF.

MENU