CAIE 9618 · Paper 4 · Topic 4.5.2

Normalisation

Update Anomalies · Functional Dependency · 1NF · 2NF · 3NF

CSZone Cambridge International AS & A Level Computer Science 9618

Why Normalise? — Update Anomalies

Problems with Unnormalised Data

An unnormalised database stores the same data multiple times. This causes update anomalies — inconsistencies that arise when inserting, updating, or deleting data.

Unnormalised table: OrderLine(OrderID, CustomerName, CustomerAddress, ProductID, ProductName, Price, Quantity)

UPDATE ANOMALY

If a product's price changes, it must be updated in EVERY row that references that product. If any row is missed, the database contains inconsistent data.

INSERTION ANOMALY

Cannot add a new product to the database without also creating an order for it (ProductID is tied to OrderID). New products with no orders cannot be stored.

DELETION ANOMALY

Deleting the last order for a customer removes the customer's details entirely — even if the organisation still wants to keep the customer record.

SOLUTION: NORMALISATION

Organise data into related tables so each fact is stored exactly once. Eliminates all three anomaly types.

1NF, 2NF, 3NF

Three Normal Forms

1NF — First Normal Form

All attributes are atomic (single value, not a list or repeating group). Each row is unique. A primary key is defined.

Violation: storing multiple phone numbers in one cell like "01234, 05678"

2NF — Second Normal Form

Must be in 1NF. Every non-key attribute is fully functionally dependent on the WHOLE primary key (not just part of it). Applies when the PK is a composite key.

Violation: in OrderLine(OrderID, ProductID, ProductName, Qty) — ProductName depends only on ProductID, not the full PK. Partial dependency → extract into a Product table.

3NF — Third Normal Form

Must be in 2NF. No transitive dependencies — non-key attributes must depend ONLY on the primary key, not on other non-key attributes.

Violation: in Student(StudentID, TutorID, TutorName) — TutorName depends on TutorID (a non-key), not StudentID. Transitive dependency → extract into Tutor(TutorID, TutorName).

Exam Practice

Cambridge-style questions

Question 1

The following unnormalised table exists: Booking(BookingID, CustomerID, CustomerName, RoomNumber, RoomType, Price, CheckInDate). CustomerName depends on CustomerID. RoomType and Price depend on RoomNumber. Normalise to 3NF, showing the tables and their keys. [4]

1

All attributes are already atomic and a PK exists — passes 1NF.

1

Passes 2NF — single-column PK (BookingID), so no partial dependencies possible.

1

Fails 3NF — transitive dependencies exist: CustomerName depends on CustomerID (not BookingID); RoomType and Price depend on RoomNumber (not BookingID). Create: Customer(CustomerID, CustomerName) and Room(RoomNumber, RoomType, Price).

1

Final 3NF tables: Booking(BookingID, CustomerID FK, RoomNumber FK, CheckInDate) · Customer(CustomerID, CustomerName) · Room(RoomNumber, RoomType, Price)

Common Mistakes

Don't lose easy marks

1

Confusing partial and transitive dependencies — partial dependency: a non-key attribute depends on PART of a composite PK (2NF issue). Transitive dependency: a non-key attribute depends on ANOTHER non-key attribute (3NF issue). Get these confused and you'll misidentify the violation.

2

Forgetting to include foreign keys in the normalised tables — when you move an attribute to a new table, the original table must keep a FK pointing to the new table's PK. Missing FKs breaks the relationships between tables.

3

Thinking 2NF applies when there is a single-column PK — partial dependency can ONLY occur with a COMPOSITE primary key. If the PK is a single column, the table automatically satisfies 2NF (there is no "part of the key" to have a partial dependency on).

Topic Summary — 4.5.2

What You Need to Know

ANOMALIES

Update: same data in multiple rows → inconsistency. Insertion: can't add data without linking to unrelated entity. Deletion: deleting one fact removes unrelated data. All solved by normalisation.

1NF

Atomic attributes, no repeating groups, unique rows, PK defined.

2NF

1NF + no partial dependencies (all non-key attributes depend on the FULL composite PK). Applies only when PK is composite.

3NF

2NF + no transitive dependencies (non-key attributes must depend ONLY on the PK, not on other non-key attributes). Extract into new table with FK.

CSZone

Next Video

4.5.3

Transactions & ACID

Atomicity · Consistency · Isolation · Durability

Head to CSZone.co.uk for the complete worksheet, quiz, and interactive tools