AS · Practice questions · Normalisation to third normal form

Split until every fact is single.

Six original Cambridge-style questions on normalisation, with worked mark points and full 3NF designs.

Original questions All questions on this page are original work, written in the Cambridge AS & A Level style. They are not from past papers. They test the same concepts and skills the syllabus rewards.

Keep these straight

Three checks, in order.

1NF: remove repeating groups; every field atomic; choose the (often composite) primary key.
2NF: move attributes that depend on only part of a composite key into their own table.
3NF: move attributes that depend on another non-key attribute into their own table, leaving a foreign key.
Write tables as NAME(Key, Attribute, ...) and say which attributes are foreign keys.

Recall

[3 marks]

State the conditions for a table to be in (a) first normal form, (b) second normal form, (c) third normal form.

(a) No repeating groups of attributes; all attributes atomic; a primary key identifies each tuple. ✓
(b) In 1NF and no partial dependencies: every non-key attribute depends on the whole primary key. ✓
(c) In 2NF and no non-key dependencies: no non-key attribute depends on another non-key attribute. ✓

Analysis

[2 marks]

The table BOOKING(BookingID, CustomerID, CustomerEmail, EventDate) has the primary key BookingID. Explain why it is not in 3NF.

CustomerEmail depends on CustomerID, not on BookingID. ✓
CustomerID is a non-key attribute, so this is a non-key (transitive) dependency. ✓

Apply

[5 marks]

A garage records repairs in one unnormalised table:

REPAIR(RepairID, RepairDate, CarReg, CarModel, OwnerID, OwnerName, PartID, PartName, PartPrice, Quantity)

Each repair is on one car and can use several parts. Each car has one owner. Produce a design in 3NF.

REPAIR(RepairID, RepairDate, CarReg). ✓
CAR(CarReg, CarModel, OwnerID). ✓
OWNER(OwnerID, OwnerName). ✓
PART(PartID, PartName, PartPrice). ✓
REPAIRPART(RepairID, PartID, Quantity), with foreign keys identified: CarReg in REPAIR, OwnerID in CAR, RepairID and PartID in REPAIRPART. ✓

Explain

[3 marks]

Using the 1NF table STUDENTCOURSE from this lesson, explain one problem caused by data that is not normalised.

Mr Ade's name is stored in every row for students S1 and S3 (redundancy). ✓
If his name changes, every one of those rows must be updated (update problem). ✓
If one row is missed, the data becomes inconsistent; similarly a new course cannot be added until a student enrols (insertion problem). ✓

Analysis

[3 marks]

PLAYER(PlayerID, MatchID, Goals, MatchDate, PlayerName) has the composite primary key PlayerID + MatchID. Identify every partial dependency and give the 2NF tables.

PlayerName depends on PlayerID only; MatchDate depends on MatchID only. ✓
Goals depends on the whole key. ✓
2NF: PLAYER(PlayerID, PlayerName), MATCH(MatchID, MatchDate), PERFORMANCE(PlayerID, MatchID, Goals). ✓

Explain

[2 marks]

A table is in 2NF and has a single-attribute primary key. Explain why it may still not be in 3NF.

A single-attribute key rules out partial dependencies only. ✓
A non-key attribute may still depend on another non-key attribute, which breaks 3NF. ✓

Mark this once you have attempted all six and checked your working. It records a Practiced badge on the topic and adds a one-time bonus. Revealing the solutions alone does not count.