Normalisation to 3NF & Referential Integrity (OCR A-Level CS 1.3.2)

Free Normalisation Referential Integrity revision resources (OCR A-Level Computer Science, 1.3.2)

We’ve made exam-style practice for this exact topic, free to download: Normalisation Referential Integrity question sheet, mark scheme and cheat sheet. Grab them, have a go, then read the full guide below.

Normalisation is how you turn a messy, repetitive database into a clean, efficient one. Spec point 1.3.2 covers normalisation to third normal form (3NF) and referential integrity — regularly tested with "why is this in 1NF / 2NF / not in 3NF" questions and "explain referential integrity".

This guide explains each normal form, with a worked example, and what referential integrity protects.

What is normalisation and why do it?

Normalisation is the process of organising a database into well-structured tables to remove redundancy (repeated data) and avoid update, insert and delete anomalies. A normalised database stores each fact once, so it is smaller, more consistent and easier to maintain.

What is First Normal Form (1NF)?

A table is in 1NF if:

• there are no repeating groups (no field holds a list of values), and

• every field holds a single, atomic value, with each record uniquely identifiable (a primary key).

You reach 1NF by splitting any repeating group into separate rows or a separate table, so for example a booking that lists three passengers in one field is not in 1NF.

What is Second Normal Form (2NF)?

A table is in 2NF if it is in 1NF and has no partial dependencies — that is, no non-key field depends on only part of a composite (multi-field) primary key. Every non-key field must depend on the whole key. If a table's key is (OrderID, ProductID) and ProductName depends only on ProductID, that is a partial dependency, so it is not in 2NF; you move ProductName to a Product table. (A table with a single-field primary key and no repeating groups is automatically in 2NF.)

What is Third Normal Form (3NF)?

A table is in 3NF if it is in 2NF and has no non-key dependencies (no transitive dependencies) — no non-key field depends on another non-key field. For example, if Order holds CustomerID and CustomerName, then CustomerName depends on CustomerID (a non-key field), not on the primary key — so it is not in 3NF. You move customer details to a Customer table, leaving only the foreign key.

The exam shorthand: 1NF = no repeating groups; 2NF = 1NF + no partial dependencies; 3NF = 2NF + no non-key (transitive) dependencies.

Worked example

A flat Order table holds OrderID, CustomerID, CustomerName, ProductID, ProductName, Quantity with one row per product, repeating products in a field.

• To 1NF: remove the repeating group so each row is one product — one atomic value per field, with a key of (OrderID, ProductID).

• To 2NF: ProductName depends only on ProductID (part of the key), so move it to a Product table; CustomerID/CustomerName depend only on OrderID, so move them with the order.

• To 3NF: CustomerName depends on CustomerID (a non-key field), so move customer details to a Customer table, leaving CustomerID as a foreign key.

The result is separate Customer, Order, OrderLine and Product tables, each fact stored once.

What is referential integrity?

Referential integrity is the rule that a foreign key value must always match an existing primary key in the related table (or be left empty). It stops orphaned records — for example, an Order whose CustomerID refers to a customer who does not exist.

It is broken if you add a record with a foreign key that has no matching primary key, or delete a primary-key record that still has matching foreign keys (deleting a customer who still has orders). A DBMS enforces referential integrity to keep the linked data consistent.

Common exam mistakes

• Confusing 2NF and 3NF. 2NF removes partial dependencies (on part of a composite key); 3NF removes non-key/transitive dependencies (one non-key field on another).

• Saying a single-key table "isn't in 2NF". With a single-field key and no repeating groups, partial dependencies cannot exist, so it is already in 2NF.

• Vague 1NF answers. State both rules: no repeating groups and atomic values.

• Defining referential integrity loosely. It is specifically that a foreign key must match an existing primary key; mention orphaned records.

• Forgetting how it breaks. Deleting a referenced primary key, or inserting an unmatched foreign key, breaks it.

Quick recap

• Normalisation removes redundancy and anomalies; each fact stored once.

• 1NF = no repeating groups + atomic values.

• 2NF = 1NF + no partial dependencies (on part of a composite key).

• 3NF = 2NF + no non-key (transitive) dependencies.

• Referential integrity = every foreign key matches an existing primary key (no orphaned records); broken by deleting a referenced key or inserting an unmatched one.

Frequently asked questions

What is normalisation? Normalisation is the process of organising a database into well-structured tables to remove redundant (repeated) data and avoid update, insert and delete anomalies, so each fact is stored only once.

What is First Normal Form (1NF)? A table is in 1NF if it has no repeating groups and every field holds a single, atomic value, with each record uniquely identifiable by a primary key.

What is the difference between 2NF and 3NF? 2NF requires the table to be in 1NF with no partial dependencies, meaning no non-key field depends on only part of a composite primary key. 3NF requires it to be in 2NF with no non-key (transitive) dependencies, meaning no non-key field depends on another non-key field.

How do you normalise to 3NF? First reach 1NF by removing repeating groups. Then reach 2NF by moving any field that depends on only part of a composite key into its own table. Then reach 3NF by moving any field that depends on a non-key field into its own table, leaving a foreign key behind.

What is referential integrity? Referential integrity is the rule that every foreign key value must match an existing primary key in the related table, or be empty. It prevents orphaned records, such as an order linked to a customer who does not exist.

How can referential integrity be broken? It is broken if a record is added with a foreign key that has no matching primary key, or if a primary-key record is deleted while other tables still hold foreign keys referring to it, leaving orphaned records.