Show understanding of the normalisation process

8.1 Database Concepts – Normalisation

Learning Objective

Show an understanding of the normalisation process and how it is used to design efficient, anomaly‑free relational databases.

Why Normalise?

• Eliminate redundant data
• Reduce update, insertion and deletion anomalies
• Improve data integrity and consistency
• Facilitate easier maintenance and extension of the database

Key Concepts

Normalisation is based on the idea of organising data into tables (relations) so that each table represents a single entity or relationship and satisfies a series of normal forms. Each normal form imposes stricter rules on the structure of the table.

Functional Dependency

A functional dependency (FD) is written as \$X \rightarrow Y\$, meaning that the value of attribute set \$X\$ uniquely determines the value of attribute set \$Y\$.

Normal Forms Overview

1. First Normal Form (1NF)

   • All attribute values are atomic (indivisible).
   • No repeating groups or arrays.

2. Second Normal Form (2NF)

   • Table must be in 1NF.
   • Every non‑prime attribute is fully functionally dependent on the whole primary key (no partial dependencies).

3. Third Normal Form (3NF)

   • Table must be in 2NF.
   • No transitive dependencies: non‑prime attributes must depend directly on the primary key, not on other non‑prime attributes.

4. Boyce‑Codd Normal Form (BCNF)

   • Every determinant is a candidate key.
   • Stricter than 3NF; resolves certain anomalies that 3NF does not.

Step‑by‑Step Normalisation Example

Consider a university course enrolment table:

1. Check 1NF

The table already stores atomic values, so it satisfies 1NF.

2. Identify Functional Dependencies

• \$\text{StudentID} \rightarrow \text{StudentName}\$
• \$\text{CourseCode} \rightarrow \text{CourseTitle, Lecturer}\$
• \$(\text{StudentID},\text{CourseCode}) \rightarrow \text{Semester}\$

3. Convert to 2NF (remove partial dependencies)

Separate tables for Student and Course information.

4. Convert to 3NF (remove transitive dependencies)

All non‑key attributes now depend directly on the primary key of their respective tables, so the design is in 3NF.

5. Check BCNF

Each determinant (StudentID, CourseCode) is a candidate key in its table, so the schema also satisfies BCNF.

Summary of the Normalisation Process

1. Ensure the relation is in 1NF (atomic values).
2. Identify all functional dependencies.
3. Apply 2NF rules to eliminate partial dependencies.
4. Apply 3NF rules to eliminate transitive dependencies.
5. Optionally, verify BCNF for stricter control.
6. Result: a set of well‑structured tables that minimise redundancy and prevent anomalies.

Key Take‑aways

• Normalisation is a systematic method, not an arbitrary redesign.
• Each normal form builds on the previous one, adding stricter constraints.
• Understanding functional dependencies is essential for successful normalisation.
• Well‑normalised databases are easier to maintain, extend, and query efficiently.