Database Normalisation

Normalisation is the process of organising a database to eliminate redundancy (data stored more than once) and improve integrity (data stays consistent).

Problems caused by un-normalised data:

Anomaly	What goes wrong
Update anomaly	The same data is stored in multiple rows; updating one but not the others causes inconsistency
Insertion anomaly	Cannot add data without also providing unrelated data (e.g. can't add a course unless at least one student is enrolled)
Deletion anomaly	Deleting one record accidentally deletes other useful data (e.g. deleting the last student in a course also deletes the course itself)

Normalisation solves all three by ensuring each fact is stored in exactly one place.

A table is in 1NF if:

1. Each cell contains a single (atomic) value — no repeating groups or multi-valued cells
2. Each column contains values of the same type
3. Each row is unique (there is a primary key)

Un-normalised table:

OrderID	Customer	Products
1	Alice	Pen, Notebook
2	Bob	Ruler

Products contains multiple values — violates 1NF.

1NF version:

OrderID	Customer	Product
1	Alice	Pen
1	Alice	Notebook
2	Bob	Ruler

Each cell now contains one value. The primary key is now (OrderID, Product) — a composite key.

A table is in 2NF if:

1. It is in 1NF, and
2. Every non-key attribute depends on the whole primary key — no partial dependencies

Partial dependency occurs when a non-key attribute depends on only part of a composite primary key.

1NF table (from above) — checking for partial dependencies:

OrderID	Product	Customer	ProductPrice
1	Pen	Alice	£0.50
1	Notebook	Alice	£2.00
2	Ruler	Bob	£1.50

• Customer depends only on OrderID (not on Product) — partial dependency
• ProductPrice depends only on Product (not on OrderID) — partial dependency

2NF: remove partial dependencies into separate tables:

Order(<u>OrderID</u>, Customer)
Product(<u>Product</u>, ProductPrice)
OrderLine(<u>OrderID*</u>, <u>Product*</u>)

Each non-key attribute now depends on the whole primary key of its table.

A table is in 3NF if:

1. It is in 2NF, and
2. No non-key attribute depends on another non-key attribute — no transitive dependencies

Transitive dependency: A → B → C, where A is the primary key, B and C are non-key attributes, and C depends on B rather than directly on A.

Example — 2NF table with transitive dependency:

EmployeeID	DeptCode	DeptName	Salary
101	IT	IT Department	£40000
102	HR	HR Department	£35000
103	IT	IT Department	£42000

• DeptName depends on DeptCode (non-key), not directly on EmployeeID — transitive dependency

3NF: remove transitive dependencies:

Employee(<u>EmployeeID</u>, DeptCode*, Salary)
Department(<u>DeptCode</u>, DeptName)

Now DeptName is stored once in Department. Changing a department's name requires updating one row.

Normal Form	Requirement	Resolves
1NF	Atomic values; unique rows; primary key	Repeating groups and multi-valued cells
2NF	1NF + no partial dependencies	Non-key attributes depending on part of composite key
3NF	2NF + no transitive dependencies	Non-key attributes depending on other non-key attributes

The process:

1. Start with the un-normalised data
2. Apply 1NF: split out multi-valued cells; identify primary key
3. Apply 2NF: identify partial dependencies; move them to new tables
4. Apply 3NF: identify transitive dependencies; move them to new tables

1. Skipping 2NF for tables with a single-column primary key

Partial dependencies only exist when there is a composite primary key. A table with a single-column primary key is automatically in 2NF (you cannot have a partial dependency on a single column). The 2NF step matters only when there is a composite key.

2. Confusing partial and transitive dependencies

Partial dependency (2NF issue): non-key attribute depends on part of the composite primary key. Transitive dependency (3NF issue): non-key attribute depends on another non-key attribute. Draw the dependency chain: if the chain goes key → non-key → non-key, it is transitive.

3. Applying normalisation without identifying the primary key first

You cannot check for 2NF or 3NF without knowing the primary key. The first step in normalisation is always to identify the key. All subsequent dependency analysis is relative to the key.

4. Forgetting that normalisation creates more tables

Normalising a table produces more, smaller tables. Queries on a normalised database require JOINs. This is a deliberate trade-off: more tables, less redundancy, better integrity. Denormalisation (deliberately introducing some redundancy) is sometimes done for performance — but that is beyond the AQA spec.