1.3.2 - Normalisation and referential integrity

Finding which fact determines another

Normalisation organises relational tables to reduce unnecessary duplication and the anomalies that duplication causes.

An update anomaly occurs when a repeated fact is changed in some rows but not others. An insertion anomaly occurs when storing one fact requires an unrelated fact, such as inventing an order to register a new customer. A deletion anomaly occurs when removing a record unintentionally removes the only copy of another needed fact, such as deleting a product's details with its last order line. Splitting tables can prevent these problems while preserving the information through keys.

The decision about which facts belong together depends on the rules relating their values.

In this teaching example, a shop's rules are:

• Each OrderNo has one CustomerNo; each CustomerNo has one current CustomerName.
• Each ProductCode has one current ProductName and Price. Price means the current catalogue price, not a historical price negotiated per order.
• A product appears at most once in an order. Quantity depends on the pair (OrderNo, ProductCode).

Thus OrderNo → CustomerNo, CustomerNo → CustomerName, ProductCode → ProductName, Price, and (OrderNo, ProductCode) → Quantity. Two customers can have the same name, so CustomerName does not determine CustomerNo.

An unnormalised order might store Products = [(P1, Lamp, 12, 2), (P2, Cable, 4, 3)] in one cell. That repeated group is the first issue to remove. After doing so, the dependencies will reveal which facts can be split further.

Making one value per field in 1NF

A table is in first normal form (1NF) when it has no repeating groups and fields contain single values in the chosen schema.

Turn the product list into one row per order–product pair. Use consistent fields and identify each row with a key. For our shop this gives:

The composite primary key is (OrderNo, ProductCode). OrderNo alone repeats within an order, and ProductCode alone repeats across orders, but their pair is unique under the stated rule. A single-valued CustomerName is appropriate; “atomic” does not mean split every word of a person's name. The choice is driven by how the field is used and what it represents.

This removes the list-in-a-cell problem, but it has not removed all redundancy. Lamp's name and price appear on both O10 and O11, and Nia appears on both O10 rows. 1NF is a starting point, not a guarantee of a good final design. If repeat product lines were allowed, this composite key would be insufficient and a line identifier or line number would be needed; the dependency analysis would have to use that different rule.

Removing partial dependencies in 2NF

A table is in second normal form (2NF) when it is in 1NF and every non-key attribute depends on the whole of each candidate key, with no partial dependency.

A partial dependency is dependence on only part of a composite candidate key. In the 1NF table, ProductName and Price depend on ProductCode alone. CustomerNo and CustomerName depend on OrderNo alone. Quantity needs the whole pair: the quantity of P1 can differ between O10 and O11, and quantities can differ between products in O10.

Move the facts determined by each key part into their own tables, and keep the pair-dependent fact in OrderLine:

OrderLine.OrderNo references OrderHeader and OrderLine.ProductCode references Product. OrderHeader rows are (O10,C1,Nia) and (O11,C2,Omar); Product rows are (P1,Lamp,12) and (P2,Cable,4). OrderLine retains (O10,P1,2), (O10,P2,3) and (O11,P1,1). Joining matching identifiers reconstructs the original facts without inventing new pairings.

A table with only single-field candidate keys cannot have a dependency on part of a composite candidate key, so a 1NF table of that kind satisfies this 2NF test automatically. It can still have a transitive dependency, which is the next issue. Adding an arbitrary record ID to a bad design is not a substitute for checking its real candidate keys and dependencies.