Normalisation | OCR H446

Learning Objectives

By the end of this topic you will be able to:

Explain the purpose of normalisation and what problems it solves

Identify and remove anomalies: insertion, deletion and update anomalies

Normalise a relation to First Normal Form (1NF), Second Normal Form (2NF) and Third Normal Form (3NF)

Understand functional dependency and partial/transitive dependency

Why Normalise?

The Problem: Data Anomalies

Normalisation is the process of structuring a relational database to reduce data redundancy and improve data integrity. An unnormalised table can lead to three types of anomaly:

Insertion Anomaly

You cannot insert data about a new entity without also providing data for another. E.g. cannot add a new course unless a student enrols on it.

Deletion Anomaly

Deleting one piece of information accidentally removes other related data. E.g. deleting the last student on a course deletes the course information too.

Update Anomaly

Changing one piece of data requires updating it in multiple places. If inconsistently updated, the database holds contradictory data.

1NF

First Normal Form (1NF)

A relation is in 1NF if: all attributes contain atomic (indivisible) values; there are no repeating groups of columns; all rows are unique (a primary key exists).

Not in 1NF

OrderID	Products
1	Pen, Ruler, Book
2	Pen

Products is not atomic — contains multiple values in one cell.

In 1NF

OrderID	Product
1	Pen
1	Ruler
1	Book
2	Pen

One product per row; composite PK: (OrderID, Product).

2NF

Second Normal Form (2NF)

A relation is in 2NF if it is in 1NF and every non-key attribute is fully functionally dependent on the whole primary key. This eliminates partial dependency (applicable only when the primary key is composite).

Partial Dependency (violates 2NF)

Table: (OrderID, ProductID, ProductName, Qty)

ProductName depends only on ProductID, not on (OrderID, ProductID) together — this is a partial dependency.

2NF Solution

Split into:
Order_Line(OrderID, ProductID, Qty)
Product(ProductID, ProductName)

ProductName now depends fully on ProductID.

3NF

Third Normal Form (3NF)

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

Transitive Dependency (violates 3NF)

Table: (StudentID, CourseID, TeacherName, TeacherDept)

TeacherDept depends on TeacherName, which is a non-key attribute — transitive dependency.

3NF Solution

Split into:
Enrolment(StudentID, CourseID, TeacherID [FK])
Teacher(TeacherID, TeacherName, TeacherDept)

TeacherDept now depends only on TeacherID (key).

Exam Practice

OCR H446 Style · 4 marks

Explain what is meant by a transitive dependency and why it violates third normal form. Use an example in your answer.

[4 marks]

A transitive dependency occurs when a non-key attribute depends on another non-key attribute (rather than depending directly on the primary key).

Example: in a relation (StudentID, CourseID, TeacherName, TeacherDept), TeacherDept depends on TeacherName, not on the primary key StudentID/CourseID.

This violates 3NF because 3NF requires that every non-key attribute is directly dependent only on the primary key.

To resolve it, the teacher data is moved to a separate Teacher table (TeacherID, TeacherName, TeacherDept), with TeacherID as a foreign key in the original table.

Common Mistakes

Don’t Lose Marks

Saying 2NF applies to all tables — partial dependency only exists when the primary key is composite (two or more attributes). A table with a single-column primary key cannot have a partial dependency and is automatically in 2NF if it is in 1NF.

Confusing partial and transitive dependencies: partial = non-key depends on part of composite PK (2NF issue); transitive = non-key depends on another non-key (3NF issue). These are different problems and exam questions regularly test the distinction.

Saying normalisation always improves performance — normalisation improves data integrity and reduces redundancy, but may reduce query performance because data is spread across more tables requiring JOINs. This is a deliberate trade-off.