AQA 7517 · Paper 2 · 4.10.4

Normalisation
UNF → 1NF → 2NF → 3NF

Removing data anomalies · dependency analysis · structuring relational tables

WHAT YOU'LL LEARN

UNF, 1NF, 2NF, 3NF · data anomalies · functional dependency · partial/transitive dependency

AQA SPEC LINK

4.10.4 — Normalisation: UNF, 1NF, 2NF, 3NF; functional dependencies

Why Normalise?

Why Normalise a Database?

Normalisation removes data redundancy and prevents update anomalies.

Insertion anomaly — cannot add a new entity without other data (e.g. can't add a course with no students)

Update anomaly — changing data in one place requires changes in many rows (inconsistency risk)

Deletion anomaly — deleting a record accidentally removes other data (e.g. deleting last student on a course removes the course)

UNF

Unnormalised Form (UNF)

Data in UNF has repeating groups — multiple values in a single field, or rows that repeat data.

OrderID	CustomerName	Products (repeating)	Total
O01	Alice	Pen, Book, Ruler	£12
O02	Ben	Pen, Stapler	£8

The Products field contains multiple values — this violates atomicity

1NF

First Normal Form (1NF)

A table is in 1NF if: all values are atomic (one value per cell), there are no repeating groups, and there is a primary key.

OrderID	CustomerName	ProductName	Price
O01	Alice	Pen	£1
O01	Alice	Book	£8
O02	Ben	Pen	£1

Composite PK: (OrderID, ProductName) — each row is now uniquely identifiable. But CustomerName is repeated — redundancy.

2NF

Second Normal Form (2NF)

A table is in 2NF if it is in 1NF AND every non-key attribute is fully functionally dependent on the whole primary key (no partial dependencies).

Only applies when there is a composite primary key

CustomerName depends only on OrderID (not on ProductName) — partial dependency — so split it out

Order

(OrderID, CustomerName)

OrderLine

(OrderID, ProductName, Price)

3NF

Third Normal Form (3NF)

A table is in 3NF if it is in 2NF AND there are no transitive dependencies — no non-key attribute depends on another non-key attribute.

Example: Order table contains CustomerPostcode → CustomerCity

CustomerCity depends on CustomerPostcode (not the PK) — transitive dependency

Order

(OrderID, CustomerID*)

Customer

(CustomerID, Name, Postcode)

Postcode

(Postcode, City, Region)

Functional Dependency

Functional Dependency Notation

A → B means "A determines B" — if you know the value of A, you can determine the value of B.

StudentID → Name, DOB (full dependency — StudentID is sole PK)

OrderID → CustomerName (partial dependency — only part of composite PK)

Postcode → City (transitive dependency — Postcode is not the PK)

In 3NF: every non-key attribute depends only on the primary key — "the whole key, nothing but the key"

Benefits

Benefits of Normalisation

Eliminates data redundancy — each fact stored once

Prevents insertion, update, and deletion anomalies

Easier to maintain and update — change data in one place only

Trade-off: more tables means more JOINs — can be slower for read-heavy queries

Denormalisation is sometimes done for performance in read-heavy systems (data warehouses)

AQA Exam Style

Practice Question

AQA 7517 — Paper 2 Style

A table OrderRecord has attributes: (OrderID, ProductID, ProductName, CustomerID, CustomerName, Quantity)

(a) Explain what is meant by a partial dependency. Identify ONE partial dependency in OrderRecord. [3]
(b) Normalise OrderRecord to 2NF, showing all resulting tables. [3]
(c) State the rule a table must satisfy to be in 3NF. [2]

[8 marks]

3 marks

(a) A partial dependency is where a non-key attribute depends on only PART of a composite primary key [1]; ProductName depends on ProductID alone [1] / CustomerName depends on CustomerID, not on the full key (OrderID, ProductID) [1]

3 marks

(b) Order(OrderID, CustomerID*, Quantity) [1]; Product(ProductID, ProductName) [1]; Customer(CustomerID, CustomerName) [1]

2 marks

(c) Must be in 2NF [1] AND have no transitive dependencies — every non-key attribute must depend directly on the primary key and nothing else [1]

Summary

Normalisation — Quick Reference

UNF — repeating groups, non-atomic values

1NF — atomic values, no repeating groups, has primary key

2NF — 1NF + no partial dependencies (non-key depends on whole PK)

3NF — 2NF + no transitive dependencies (non-key depends only on PK)

Goal: every fact stored once; updates easy; no anomalies

🎉 Lesson complete — move to the quiz!