AQA A-Level Computer Science · 4.10.3

Database
Normalisation

UNF → 1NF → 2NF → 3NF with a full worked example and AQA past paper questions

UNF 1NF · Atomic data 2NF · Partial dependencies 3NF · Transitive dependencies

Motivation

Why Normalise?

Three types of anomaly

An unnormalised database stores the same fact in multiple places. This redundancy creates three types of anomaly — errors that arise from normal database operations.

Update Anomaly

Changing data creates inconsistency

The same fact is stored in multiple rows. Updating one copy and missing another leaves the database in a contradictory state.

A book's price is stored on every order line.
Change it in row 1 but not row 3 → two different prices for the same book.

Insertion Anomaly

New data can't be added on its own

An entity can only be stored if it is already linked to another entity — even when that link shouldn't be required.

A new book can only be added to the database when it appears on at least one order. There's no order → no book.

Deletion Anomaly

Deleting data loses unrelated facts

Removing one entity accidentally destroys information about a different, unrelated entity stored in the same row.

Delete the only order for a particular book → all knowledge of that book (title, price, author) is permanently lost.

💡 The goal of normalisation Normalisation eliminates redundancy so that each fact is stored in exactly one place. This means a change to any fact requires updating exactly one record — and no anomalies can occur.

Stage 1

Unnormalised Form (UNF)

Multi-value cells · Repeating groups

An online bookshop stores order data in a single flat table. Each order can contain multiple books — producing multi-value cells and repeating groups, highlighted below.

OrderID	OrderDate	CustomerID	CustomerName	CustomerEmail	BookISBN	BookTitle	AuthorID	AuthorName	Price	Qty
1001	15/01/24	C01	Alice Brown	alice@mail.com	978-0, 978-1	Python Crash, Java Pro	A01, A02	Matthes, Bloch	18.99, 29.99	2, 1
1002	16/01/24	C02	Bob Smith	bob@mail.com	978-0	Python Crash	A01	Matthes	18.99	3

UNF Notation — repeating group shown in braces

ORDERS(OrderID, OrderDate, CustomerID, CustomerName, CustomerEmail, {BookISBN, BookTitle, AuthorID, AuthorName, Price, Qty})

⚠️ Problems with UNF Non-atomic cells BookISBN, BookTitle, etc. each hold multiple values in a single cell — violating atomicity.

Repeating groups The book columns are a repeating group — the number of books per order varies and there is no fixed column count.

No unique row identifier OrderID alone cannot identify a row if an order has multiple books.

🎯 UNF → 1NF: the fix To move to 1NF, we expand each multi-value row into separate rows — one book per row. This makes all cells atomic and lets us form a composite primary key: OrderID + BookISBN.

After this, every row is uniquely identifiable — but redundancy remains.

Stage 2

First Normal Form (1NF)

Atomic · Composite PK · Still redundant

One row per book per order. All cells are atomic. The composite primary key is OrderID + BookISBN. Partial dependencies (highlighted in amber) remain.

OrderID ↑	BookISBN ↑	OrderDate	CustomerID	CustomerName	CustomerEmail	BookTitle	AuthorID	AuthorName	Price	Qty
1001	978-0	15/01/24	C01	Alice Brown	alice@mail.com	Python Crash	A01	Matthes	18.99	2
1001	978-1	15/01/24	C01	Alice Brown	alice@mail.com	Java Pro	A02	Bloch	29.99	1
1002	978-0	16/01/24	C02	Bob Smith	bob@mail.com	Python Crash	A01	Matthes	18.99	3

Primary Key (composite) Depends on OrderID only (partial) Depends on BookISBN only (partial) Depends on both keys (fully dependent)

1NF Notation

ORDER_DATA(OrderID, BookISBN, OrderDate, CustomerID, CustomerName, CustomerEmail, BookTitle, AuthorID, AuthorName, Price, Qty)

⚠️ Still a problem — partial dependencies CustomerName, CustomerEmail depend only on OrderID — not on BookISBN. If Alice appears on three order lines and we update her email address, we must change it in three places. Miss one and the database is inconsistent. 2NF removes these.

Stage 3

Second Normal Form (2NF)

Remove partial dependencies

In 2NF: every non-key attribute must depend on the whole primary key — not just part of it. We identify each partial dependency and extract it into its own relation.

Dependency analysis

Attribute(s)	Depends on…	Status
OrderDate, CustomerID, CustomerName, CustomerEmail	OrderID only	Partial ✗
BookTitle, AuthorID, AuthorName, Price	BookISBN only	Partial ✗
Qty	OrderID AND BookISBN	Fully dependent ✓

Resulting 2NF relations

Three relations after 2NF

ORDER
(OrderID, OrderDate, CustomerID,
 CustomerName, CustomerEmail)

BOOK
(BookISBN, BookTitle, AuthorID,
 AuthorName, Price)

ORDER_LINE
(OrderID, BookISBN, Qty)
 FK: OrderID → ORDER
 FK: BookISBN → BOOK

⚠️ Still a problem — transitive dependencies Look at ORDER: OrderID → CustomerID → CustomerName, CustomerEmail. CustomerName doesn't depend directly on OrderID — it depends on CustomerID, which depends on OrderID. This chain is a transitive dependency. Similarly in BOOK: BookISBN → AuthorID → AuthorName. 3NF removes these.

💡 Partial dependency only matters when there is a composite key If a relation has a single-attribute primary key, it is automatically in 2NF — there is no part of the key to partially depend on. The 2NF step only does work when the PK is composite.

Stage 4

Third Normal Form (3NF)

Remove transitive dependencies

In 3NF: every non-key attribute must depend on the key, the whole key, and nothing but the key. A transitive dependency is when A → B → C (C depends on A only via B).

Transitive dependencies in 2NF tables

Relation	Dependency chain	Status
ORDER	OrderID → CustomerID CustomerID → CustomerName, CustomerEmail	Transitive ✗
BOOK	BookISBN → AuthorID AuthorID → AuthorName	Transitive ✗
ORDER_LINE	Qty depends only on (OrderID, BookISBN)	In 3NF ✓

💡 The fix Each transitive dependency is removed by extracting the dependent attributes into their own relation, with the intermediate determinant (CustomerID, AuthorID) becoming a foreign key in the original table.

Resulting 3NF relations

Five relations after 3NF

CUSTOMER
(CustomerID, CustomerName,
 CustomerEmail)

ORDER
(OrderID, OrderDate, CustomerID)
 FK: CustomerID → CUSTOMER

AUTHOR
(AuthorID, AuthorName)

BOOK
(BookISBN, BookTitle, AuthorID, Price)
 FK: AuthorID → AUTHOR

ORDER_LINE
(OrderID, BookISBN, Qty)
 FK: OrderID → ORDER
 FK: BookISBN → BOOK

Summary

UNF → 3NF: The Full Journey

Four stages

UNF

Unnormalised Form

Non-atomic cells. Repeating groups of columns. No unique row identifier. All data in one flat table.

ORDERS(OrderID, ..., {BookISBN, ...})

→

1NF

First Normal Form

All cells atomic. One row per unique combination. Composite primary key established. Partial dependencies remain.

ORDER_DATA(OrderID, BookISBN, ...)

→

2NF

Second Normal Form

No partial dependencies. Each non-key attribute depends on the whole primary key. Relations split by what each attribute actually determines.

ORDER · BOOK · ORDER_LINE

→

3NF

Third Normal Form

No transitive dependencies. Every non-key attribute depends on the key and nothing but the key. No redundancy remains.

CUSTOMER · ORDER · AUTHOR · BOOK · ORDER_LINE

Final 3NF Schema — Entity Descriptions

AQA notation — underline = primary key, italic = foreign key

CUSTOMER(CustomerID, CustomerName, CustomerEmail)
ORDER(OrderID, OrderDate, CustomerID)
AUTHOR(AuthorID, AuthorName)
BOOK(BookISBN, BookTitle, AuthorID, Price)
ORDER_LINE(OrderID, BookISBN, Qty)

⚠️ The most common student mistake with entity notation Forgetting to underline the primary key(s). For ORDER_LINE both OrderID AND BookISBN must be underlined — it is a composite primary key. Missing either costs a mark.

Exam Technique

What AQA Expects

Based on examiner reports

✅ What scores marks

✓

Cover all three normal forms when asked to "define fully normalised." Atomic data alone is only 1NF — one mark, not two.

✓

Describe anomalies concretely. Say what goes wrong in practice: "if a price is stored on multiple rows and one is missed during an update, the database will contain two different prices for the same product."

✓

Use the correct terminology: partial dependency, transitive dependency, atomic, composite primary key.

✓

Underline all primary key attributes in entity notation — including all parts of a composite key.

✗ What loses marks

✗

Naming a problem without describing it. "Data inconsistency" or "redundancy" alone scores zero on a describe question — the examiner needs to see you understand the consequence.

✗

Stating what a normalised database looks like when the question asks about an unnormalised one. Saying "all attributes don't depend on the primary key" doesn't explain a problem — it describes a property.

✗

Defining only 1NF when asked to define 3NF or "fully normalised" — a very common partial-mark response.

🎯 The AQA mark scheme for "fully normalised" [2 marks] Any two of: data is atomic / no repeating groups (1NF) · no partial key dependencies (2NF) · no transitive dependencies (3NF) · every non-key attribute depends on the key, the whole key, and nothing but the key · every determinant is a candidate key.

Past Paper · Definition Question

Define "Fully Normalised"

Sample Set 1 · Q9.2

The following question is from an AQA specimen paper. Read it carefully before answering — note the command word.

Database Context

Athlete(AthleteNumber, Forename, Surname, DateOfBirth, Nationality)
Race(RaceNumber, RaceDate, Distance, Venue)
RaceEntryAndResult(RaceNumber, AthleteNumber, TimeSet)

Question

AQA A-Level Computer Science · Sample Set 1 · Paper 2 · Question 9.2

Relations in a database should usually be fully normalised.

Define what it means for a database to be fully normalised.

[2 marks] · AO1 (knowledge)

⚠️ Exam tip — don't just write "no redundancy" That's too vague to score. The mark scheme wants specific technical conditions — think about what property each normal form requires.

Work through your answer, then advance to the next slide to see the mark scheme.

Past Paper · Answer

Define "Fully Normalised" — Answer

Sample Set 1 · Q9.2

Mark Scheme — any 2 from the following

1

Data is atomic — no multi-value cells, no repeating groups of attributes (1NF condition)

2

No partial dependencies — no non-key attribute depends on only part of a composite primary key (2NF condition)

3

No transitive dependencies — no non-key attribute depends on another non-key attribute (3NF condition)

4

Every non-key attribute depends on the key, the whole key, and nothing but the key

⚠️ What students did wrong — examiner commentary Both examiner commentaries on this question show students who only defined 1NF (atomicity) and scored 1 mark. One student wrote: "all of the data in it is atomic, i.e. it cannot be broken up further." Correct for one mark — but 3NF requires two additional conditions beyond atomicity. Stopping at 1NF is the single most common way to lose a mark here.

💡 How to write a full 2-mark answer Aim to name conditions at two different levels — e.g. "no repeating groups" (1NF) AND "no partial dependencies" (2NF), or "no partial dependencies" AND "no transitive dependencies" (3NF). Any two distinct conditions from the list above will earn both marks.

Past Paper · Application Question

Problems with Unnormalised Data

June 2024 · Q8.5

Database Context — an online shop

Supplier(SupplierID, SupplierName, ContactEmail)
Product(ProductID, ProductName, Price, SupplierID, SupplierName)

Question

AQA A-Level Computer Science · June 2024 · Paper 2 · Question 8.5

Describe two problems that might occur if a database was not fully normalised.

[2 marks] · 1 mark per correctly described problem · AO1 (knowledge)

⚠️ Read the command word carefully — describe, not state "State" requires just a name. "Describe" requires an explanation of what actually happens. Look at the schema above — SupplierName appears in both Supplier and Product. Use that redundancy to make your answer concrete.

Write two separate problems in your own words, then advance for the mark scheme.

Past Paper · Answer

Problems with Unnormalised Data — Answer

June 2024 · Q8.5

1 mark each — describe any two of the following

1

Update anomaly — SupplierName appears in both Supplier and Product. If a supplier changes its name, every row in Product must be updated. If any row is missed, the database will contain two different supplier names for the same supplier, creating inconsistency.

2

Insertion anomaly — a new supplier cannot be added to the database until it supplies at least one product. There is no way to store a supplier's details without an associated product record.

3

Deletion anomaly — if all products from a particular supplier are deleted, all information about that supplier (name, contact email) is permanently lost — even though the supplier still exists.

⚠️ What the 2024 examiner report said Under half of students scored marks on this question. The two most common failures were: (1) writing just "data inconsistency" without describing what that means in practice — this scored zero; (2) describing a property of normalised data ("attributes don't all depend on the primary key") instead of a problem caused by the lack of normalisation. The examiner explicitly noted both.

💡 Formula for a full-mark answer Name the anomaly type → give a concrete example using the schema in the question → explain the consequence. E.g. "If a supplier renames itself [example], every Product row must be updated [action]. If even one row is missed, the database will contain two different supplier names for the same supplier [consequence]."

Reference

Normalisation — Quick Reference

All four stages

UNF — Unnormalised Form

Problems:
  Non-atomic cells (multi-value)
  Repeating groups of columns
  No unique row identifier

Notation:
  Entity({RepeatingGroup})

1NF — First Normal Form

Fix: expand to one value per cell
     one row per combination
     establish composite PK

Test: all cells atomic?
      every row uniquely identifiable?

Remaining issue: partial dependencies

2NF — Second Normal Form

Fix: extract attributes that depend
     on only PART of the PK into
     their own relation

Test: does every non-key attribute
      depend on the WHOLE PK?

Only relevant with a composite PK
Remaining issue: transitive deps

3NF — Third Normal Form

Fix: extract A→B→C chains
     B becomes PK of new relation
     B stays as FK in original

Test: does every non-key attribute
      depend ONLY on the key?
      (nothing but the key)

No redundancy remains in 3NF

🔑 Three rules for the exam (1) "Define fully normalised" needs conditions from at least two normal forms — atomicity alone is 1NF only. · (2) "Describe a problem" means name + concrete example + consequence, not just a label. · (3) In entity notation, underline every attribute in a composite primary key — not just one of them.

DatabaseNormalisation