Study Guide

Normalisation

Computer ScienceΒ· 30 min read

1. Purpose of Normalisation and Data Anomaliesβ˜…β˜…β˜†β˜†β˜†β± 10 min

Normalisation is a systematic process of organising data in a relational database to reduce redundant data storage and eliminate undesirable data anomalies. It splits large unstructured tables into smaller, well-connected tables that preserve data consistency.

πŸ“˜ Definition

Data Anomalies

Unintended inconsistencies that arise from redundant data when inserting, updating or deleting records. Three main types are insertion, deletion and update anomalies.

Example:

Storing student name and course details in the same table causes repeated student name entries for multiple courses.

    • Update anomaly: Changing a student's name requires updating every row for that student, leading to inconsistency if any row is missed.
    • Insertion anomaly: You cannot add a new course to the database until at least one student is enrolled in it.
    • Deletion anomaly: Deleting the last student enrolled in a course accidentally deletes all records of the course itself.
πŸ“ Worked Example

Identify the type of anomaly in this scenario: A table stores (OrderID, CustomerID, CustomerName, CustomerAddress, ProductID, ProductName). If all orders for a customer are deleted, the customer's contact details are also lost. Name the anomaly and explain why it occurs.

  1. 1

    This is a deletion anomaly, caused by redundant storage of customer data.

  2. 2

    Customer details are functionally dependent on CustomerID (the unique identifier for customers), not OrderID (the unique identifier for orders). This means customer data is repeated across every order placed by the same customer.

  3. 3

    When all orders for a customer are deleted, there are no remaining rows in the table that contain the customer's details, so the data is lost unintentionally.

Exam tip:

CIE examiners often ask you to name and explain an anomaly in a given scenario, not just recall definitions. Always link the anomaly to redundant data storage.

2. First and Second Normal Formβ˜…β˜…β˜…β˜†β˜†β± 15 min

Normalisation is applied incrementally, with each normal form adding stricter rules than the previous. Most CIE questions require conversion up to 3NF, so it is important to follow the steps in order.

πŸ“˜ Definition

First Normal Form (1NF)

A relation is in 1NF if all attributes contain only atomic (indivisible) values, and there are no repeating groups of data.

Example:

A table storing multiple phone numbers in a single cell for a student is not in 1NF.

πŸ“˜ Definition

Second Normal Form (2NF)

A relation is in 2NF if it is in 1NF, and all non-key attributes are fully functionally dependent on the entire primary key (no partial dependencies). 2NF only applies to relations with composite primary keys.

Example:

If the primary key is (StudentID, CourseID), StudentName depends only on StudentID (part of the PK), which is a partial dependency that violates 2NF.

πŸ“ Worked Example

Convert the following unnormalised relation to 1NF, then 2NF: One student can take multiple courses. Unnormalised relation: Student(StudentID, StudentName, StudentEmail, CourseID, CourseName).

  1. 1

    First, resolve repeating groups of course data for each student to get 1NF, with a composite primary key:

  2. 2
    \text{Student_1NF (StudentID, StudentName, StudentEmail, CourseID, CourseName)}
  3. 3

    Next, identify partial dependencies: StudentName, StudentEmail depend only on StudentID, CourseName depends only on CourseID. Both are partial dependencies on the composite PK (StudentID, CourseID).

  4. 4

    Split into three relations to eliminate partial dependencies, resulting in 2NF:

  5. 5
    Student (StudentID, StudentName, StudentEmail)Course (CourseID, CourseName)Enrolment (StudentID, CourseID)\begin{aligned} \text{Student (StudentID, StudentName, StudentEmail)} \\ \text{Course (CourseID, CourseName)} \\ \text{Enrolment (StudentID, CourseID)} \end{aligned}
βœ“ Quick check

Check your understanding of 2NF rules:

  1. Which of the following statements about 2NF is correct?

    • 2NF applies to all relations regardless of primary key type

    • A relation with a single-attribute primary key is automatically in 2NF

    • 2NF eliminates transitive dependencies

    • 2NF allows repeating groups

    Reveal answer
    1 β€”

    Correct. Partial dependencies can only exist if the primary key is composite, so a single-attribute PK means all non-key attributes are fully dependent on the entire PK.

3. Third Normal Form and BCNFβ˜…β˜…β˜…β˜…β˜†β± 20 min

After 2NF, the next step is 3NF, which is the most commonly required end point for normalisation in CIE exams. Boyce-Codd Normal Form (BCNF) is a stricter extension of 3NF that handles more complex dependency cases.

πŸ“˜ Definition

Third Normal Form (3NF)

A relation is in 3NF if it is in 2NF, and no non-key attribute is transitively dependent on the primary key. All non-key attributes must depend only on the primary key, not on other non-key attributes.

πŸ“˜ Definition

Boyce-Codd Normal Form (BCNF)

A relation is in BCNF if it is in 3NF, and for every non-trivial functional dependency , is a superkey for the relation. It resolves dependencies where a non-key attribute determines part of the primary key.

πŸ“ Worked Example

Convert the following 2NF relation to 3NF: Course(CourseID, CourseName, TutorID, TutorName, Department). Primary key = CourseID.

  1. 1

    First, identify functional dependencies: TutorID \rightarrow TutorName, Department. This is a transitive dependency, because TutorID is a non-key attribute dependent on the primary key CourseID, so TutorName and Department depend on CourseID via TutorID.

  2. 2

    Split into two relations to eliminate the transitive dependency, resulting in 3NF:

  3. 3
    Course (CourseID, CourseName, TutorID)Tutor (TutorID, TutorName, Department)\begin{aligned} \text{Course (CourseID, CourseName, TutorID)} \\ \text{Tutor (TutorID, TutorName, Department)} \end{aligned}
  4. 4

    Check BCNF: Every functional dependency has a superkey on the left hand side, so both relations are also in BCNF.

Exam tip:

Stop at 3NF unless the question explicitly asks for BCNF. Extra work will not be penalised, but it wastes valuable exam time.

4. Denormalisation: Trade-offsβ˜…β˜…β˜…β˜†β˜†β± 8 min

Normalisation is not always the optimal final database design. Denormalisation is the intentional introduction of redundancy into a normalised database to improve performance.

πŸ“˜ Definition

Denormalisation

The process of combining relations from higher normal forms to reduce the number of joins required for common queries, at the cost of increased redundancy and higher risk of anomalies.

    • Benefit: Faster read queries, as fewer joins are required to retrieve commonly accessed data
    • Common use cases: Data warehouses, reporting systems, and read-heavy applications
    • Cost: Higher storage requirements and more complex logic to avoid data inconsistencies during updates
πŸ“ Worked Example

Give an example of when a designer would choose to denormalise a 3NF database.

  1. 1

    An e-commerce site that displays product names alongside every order in a customer's order history will choose to denormalise for performance.

  2. 2

    A normalised design requires joining the Order table with the Product table every time order history is loaded, which is a common operation.

  3. 3

    Storing the product name directly in the Order table removes the need for the join, speeding up query response time for end users, even though product name is stored redundantly.

5. Common Pitfalls

Wrong move:

Stopping at 1NF or 2NF when asked to normalise to 3NF, leaving dependencies unaddressed

Why:

Many candidates forget that normalisation is incremental, each normal form requires all rules of lower forms to be satisfied first

Correct move:

Always work sequentially: 1NF β†’ 2NF β†’ 3NF, checking each rule before moving to the next step

Wrong move:

Confusing partial dependencies (2NF) with transitive dependencies (3NF) in explanations

Why:

Candidates often mix up the definitions of the two dependency types that violate each normal form

Correct move:

Remember: Partial = non-key depends on part of a composite PK (2NF rule); Transitive = non-key depends on another non-key (3NF rule)

Wrong move:

Leaving multiple values in one cell and calling it 1NF, violating atomicity

Why:

Candidates sometimes rush and forget the core rule of 1NF that all attributes must be atomic

Correct move:

Split repeating groups into separate rows or separate relations to ensure every cell holds exactly one value for 1NF

Wrong move:

Claiming denormalisation is always bad practice and should never be used

Why:

Normalisation is designed to reduce anomalies, but it is not the optimal choice for all use cases

Correct move:

Recognise denormalisation as a deliberate design choice for read-heavy systems, where performance gains outweigh the cost of redundancy

6. Quick Reference Cheatsheet

Normal Form

Key Requirements

Unnormalised

Contains repeating groups or non-atomic values

1NF

All attributes atomic; no repeating groups

2NF

1NF + no partial dependencies (non-keys depend on whole PK)

3NF

2NF + no transitive dependencies (non-keys depend only on PK)

BCNF

3NF + every functional dependency determinant is a superkey

When this came up on past exams

AI-estimated based on syllabus patterns β€” cross-check with official past papers for accuracy. Use only as revision-focus signals.

  • 2022 Β· 2

    Normalise relation to 3NF

  • 2023 Β· 2

    Identify and explain data anomalies

  • 2024 Β· 2

    Explain purpose of denormalisation

Going deeper

What's Next

Normalisation is a core design skill for relational databases that builds on foundational relational model concepts. Mastering it ensures you can design consistent, efficient databases that avoid the common data anomalies caused by redundant storage. It is a frequently tested topic in CIE A-Level 9618 Paper 2, so it is important to practice converting unnormalised relations to 3NF to build speed for the exam. Next, you can move on to learn how to implement your normalised database design with SQL, explore transaction processing and concurrency control, or study advanced database concepts for the full A-Level syllabus.