IB Computer Science Topic A3.2.5: Differences Between Normal Forms Notes

Normal Forms: The Fix for Data That Can Contradict Itself

1. Normalization is the process of splitting tables so that each fact is stored in exactly one place.
2. A normal form is a stage in that process, and each stage removes one particular kind of dependency problem.
3. The forms are cumulative, so a table in 3NF is already in 2NF and 1NF.
4. Normalization is worth doing because duplicated data does not stay identical: one copy gets updated and the other does not.

The Three Anomalies Normalization Exists to Remove

1. Update anomaly
   1. The same fact is stored in many rows, so changing it in one row leaves the others stale and the database now holds two answers to one question.
2. Insert anomaly
   1. A new fact cannot be recorded without inventing unrelated data, so a new item cannot be added to the price list until somebody buys it.
3. Delete anomaly
   1. Removing one row destroys a second, unrelated fact, so deleting the last purchase of an item also deletes the only record of its price.

The Dependency Vocabulary You Are Assessed On

Every normal form is defined in terms of dependency, so these terms have to be used exactly.

First Normal Form: Atomic Values and a Unique Identifier

1. Every attribute must hold a single atomic value, so lists and repeated columns are not allowed.
2. Every table must have a key that gives unique identification of its rows.
3. Repeating data is moved out into a new entity, while the non-repeating data stays where it is and is given a primary key.
4. The primary key of the original entity is added to the new entity as a foreign key, and the new entity is normally identified by a composite key.

Second Normal Form: No Partial-Key Dependencies

1. A table is in 2NF when it is in 1NF and every non-key attribute depends on the whole primary key.
2. Each attribute that depends on only part of a composite key is moved into its own entity.
3. That new entity is given a primary key, and the original entity keeps that key as a foreign key.
4. A table whose primary key is a single column is automatically in 2NF, because a single column has no parts.

Third Normal Form: No Transitive Dependencies

1. A table is in 3NF when it is in 2NF and no non-key attribute depends on another non-key attribute.
2. Each transitive dependency is moved into a separate entity with its own primary key.
3. That new primary key becomes a foreign key in the entity the attributes were removed from.
4. Transitive dependencies can appear in any entity created at an earlier stage, so every table has to be checked and not only the last one you made.

Comparing the Three Normal Forms

Worked Example: A School Shop's Purchase Records

Unnormalized Form: One Row per Paper Receipt

The shop currently keeps one spreadsheet row per receipt, so a receipt with two items holds two values in several cells.

First Normal Form, Step 1: Make Every Cell Atomic

Split the multi-valued cells so that each row describes one item on one receipt.

First Normal Form, Step 2: Separate the Repeating Student Data

StudentName repeats on every row for the same student, so it moves into its own entity.

Student (PK StudentID)

Purchase (PK StudentID + PurchaseDate + ItemName, FK StudentID)

Second Normal Form: Move the Partial Dependencies Out

The receipt-level data becomes its own entity with a surrogate key, PurchaseID.

Purchase (PK PurchaseID, FK StudentID)

The item-level data becomes an Item entity with its own key.

Item (PK ItemID)

What is left is the line items, which link a purchase to an item.

PurchaseLine (PK PurchaseID + ItemID, FK PurchaseID, FK ItemID)

Third Normal Form: Move the Transitive Dependency Out

The supplier attributes move into a Supplier entity, and Item keeps only a foreign key to it.

Supplier (PK SupplierID)

Item (PK ItemID, FK SupplierID)

The finished design has five tables: Student, Purchase, PurchaseLine, Item and Supplier.

The Normalization Issues in Summary

1. Data duplication
   1. Each stage moves a repeated fact into a table where it is stored once and referenced by a key.
2. Missing data
   1. Giving each fact its own table means a fact can be recorded as soon as it is known, instead of waiting for an unrelated row to exist.
3. Dependency concerns
   1. Composite key dependencies are resolved at 2NF by removing partial-key dependencies.
   2. Transitive dependencies are resolved at 3NF by giving the dependent group its own entity.
   3. Multi-valued dependencies are resolved by putting each independent list in its own table, so a student's clubs and their allergies never share a row.