A-Level Computer Science / Unit 8: Relational Databases and SQL

8.1.5 Normalising Data to Third Normal Form

🔒 Lesson slides are available to signed-in users. Sign in

8.1.5 Normalising Data to Third Normal Form

Normalisation is a systematic way to turn loosely organised data into related tables. At each stage, the designer studies which attributes determine other attributes and separates facts that belong to different entities.

By the end of this section, you should be able to:

  • Explain how normalisation reduces redundancy and update problems.
  • Identify repeating groups and atomic values.
  • Convert unnormalised data into First Normal Form.
  • Remove partial dependencies to reach Second Normal Form.
  • Remove transitive dependencies to reach Third Normal Form.
  • Explain why a given table is or is not in 3NF.
  • Produce a normalised design from a description, dataset or set of tables.

Why Normalise Data?

A single large table may repeat the same facts many times. Repetition wastes storage, but the more serious problem is that several copies of one fact can become inconsistent. Normalisation separates facts according to what they describe.

Normalisation: a database-design process that organises attributes into related tables according to their dependencies.
Design problem What can happen Example consequence
Update anomaly The same fact must be changed in several tuples. One copy of a team's base is updated while another keeps the old value.
Insertion anomaly A fact cannot be stored until an unrelated fact also exists. A new team cannot be recorded until it makes an equipment request.
Deletion anomaly Deleting one tuple accidentally removes another useful fact. Deleting a team's final request also removes the only stored team details.
Common misconception: Normalisation is not sorting records, changing presentation or making every table small. It is based on relationships between attributes.

Functional Dependencies

To normalise confidently, ask which attribute values determine other attribute values. The notation X → Y means that one value of X determines one value of Y.

Functional dependency: a relationship in which the value of one attribute, or a set of attributes, determines the value of another attribute.

In the expedition example used throughout this lesson:

RequestID → RequestDate, TeamID
TeamID → TeamName, TeamBase
ItemID → ItemDescription, UnitMassKg
RequestID + ItemID → QuantityRequested

These dependencies reveal four different kinds of fact: a request, a team, an item and the quantity of one item included in one request.

Exam tip: Before moving an attribute, complete the sentence “This value is determined by …”. The answer identifies the table in which the attribute belongs.

Starting with Unnormalised Data

A field-research organisation records an equipment request on one digital form. One request has one date and one team, but it can include several requested items.

Request RQ-731
Request date 2027-04-08
Team T-14 · Aurora Survey · North Depot
Requested items
ItemID Description Unit mass Quantity
I-208Water-quality probe1.4 kg3
I-315Solar charger0.8 kg5
I-422Sample case2.2 kg2

As an unnormalised attribute list, the repeating part can be shown inside nested brackets:

EquipmentRequest( RequestID, RequestDate, TeamID, TeamName, TeamBase, (ItemID, ItemDescription, UnitMassKg, QuantityRequested) )
Repeating group: a collection of attributes that can occur several times for one value of the non-repeating attributes.

The group of item attributes repeats because one request can contain several items. A relational design must not store this list in one cell or use numbered fields such as Item1, Item2 and Item3.

First Normal Form: Remove Repeating Groups

A relation is in First Normal Form (1NF) when every field contains one atomic value, there are no repeating groups, and each tuple can be identified by a key.

One way to create 1NF is to place each request–item combination in its own tuple:

RequestItemFlat( RequestID, ItemID, RequestDate, TeamID, TeamName, TeamBase, ItemDescription, UnitMassKg, QuantityRequested )

The compound primary key is (RequestID, ItemID). It identifies one item line within one request.

RequestID ItemID RequestDate TeamID TeamName TeamBase ItemDescription UnitMassKg Quantity
RQ-731I-2082027-04-08T-14 Aurora SurveyNorth DepotWater-quality probe1.43
RQ-731I-3152027-04-08T-14 Aurora SurveyNorth DepotSolar charger0.85
RQ-731I-4222027-04-08T-14 Aurora SurveyNorth DepotSample case2.22

This relation is in 1NF because every cell contains one value. However, it still repeats request, team and item details, so it is not yet in 2NF.

Common mistake: “One value per cell” is necessary for 1NF, but it does not prove that the table is in 2NF or 3NF.

Second Normal Form: Depend on the Whole Key

A table is in Second Normal Form (2NF) when it is already in 1NF and every non-key attribute depends on the whole of every compound candidate key.

Partial dependency: a non-key attribute depends on only part of a compound key.
Non-key attribute What determines it? 2NF decision
RequestDate, TeamID, TeamName, TeamBase RequestID only Move to a Request table.
ItemDescription, UnitMassKg ItemID only Move to an Item table.
QuantityRequested RequestID + ItemID Keep in the link table.

The 2NF design is:

Request(RequestID, RequestDate, TeamID, TeamName, TeamBase)
Item(ItemID, ItemDescription, UnitMassKg)
RequestItem( RequestIDFK, ItemIDFK, QuantityRequested)

RequestItem keeps QuantityRequested because the quantity is not a property of the item alone or of the request alone. It describes one specific item within one specific request.

Exam tip: Partial dependency can only occur when a candidate key has more than one attribute. A 1NF table with only single-attribute candidate keys cannot fail 2NF because of partial dependency.

Third Normal Form: Remove Non-Key Dependencies

A table is in Third Normal Form (3NF) when it is already in 2NF and its non-key attributes do not depend on other non-key attributes.

Transitive dependency: the primary key determines a non-key attribute, which then determines another non-key attribute.

In Request, the dependency chain is:

RequestID → TeamID → TeamName, TeamBase

TeamName and TeamBase describe a team, not a request. Keeping them in Request would repeat those values every time the same team makes another request.

The final 3NF design is:

Team(TeamID, TeamName, TeamBase)
Request(RequestID, RequestDate, TeamIDFK)
Item(ItemID, ItemDescription, UnitMassKg)
RequestItem( RequestIDFK, ItemIDFK, QuantityRequested)
Table Primary key Foreign key(s) Why it is in 3NF
Team TeamID — TeamName and TeamBase depend directly on TeamID.
Request RequestID TeamID RequestDate and TeamID describe the request; no non-key attribute determines another.
Item ItemID — ItemDescription and UnitMassKg depend directly on ItemID.
RequestItem RequestID + ItemID RequestID, ItemID QuantityRequested depends on the complete compound key.
A useful memory aid is that each non-key attribute should depend on the key, the whole key and nothing but the key. This phrase supports the reasoning but should not replace a clear dependency explanation.

Explaining Whether Tables Are in 3NF

The syllabus requires more than carrying out a familiar worked example. You must be able to inspect unfamiliar tables and justify your conclusion.

1

Check 1NF: Are values atomic? Are repeating groups absent? Is each tuple identifiable?

2

Check 2NF: For every compound candidate key, does each non-key attribute depend on the whole key?

3

Check 3NF: Does any non-key attribute determine another non-key attribute?

Given table 3NF? Reason
Employee(EmployeeID, EmployeeName, DepartmentID, DepartmentName) No DepartmentID → DepartmentName, so a non-key attribute determines another non-key attribute.
Employee(EmployeeID, EmployeeName, DepartmentIDFK) Yes, assuming the stated dependencies Each non-key attribute depends directly on EmployeeID, with no partial or transitive dependency shown.
Enrolment(StudentID, CourseID, StudentName, Grade) No StudentName depends only on StudentID, part of the compound key.
Enrolment(StudentID, CourseID, Grade) Yes, assuming one grade per student–course pairing Grade depends on the complete compound key.
Strong justification: Name the normal form, identify the exact dependency and state which attribute or attributes should move to a new table.

A Reliable Method for Normalisation Questions

  1. List the attributes and mark any repeating group.
  2. Choose a primary key for each emerging table.
  3. Create 1NF by making values atomic and removing repeated sets.
  4. Write the dependencies rather than guessing where attributes belong.
  5. Create 2NF by removing attributes that depend on only part of a compound key.
  6. Create 3NF by removing dependencies between non-key attributes.
  7. Add foreign keys so the separated tables remain connected.
  8. Check the final design table by table and explain why each is in 3NF.
Common mistake: Splitting a table without preserving the relationship can lose information. Every new parent table usually needs to be referenced by an appropriate foreign key.

Interactive: Normalisation Step Builder

Move through the same expedition request from unnormalised data to 3NF. At each stage, the widget shows the current relations, the dependency problem and the design decision.

Current stage

Unnormalised equipment request

One request contains a repeating list of item details.

Dependency focus
RequestID identifies the request, but several item groups can occur inside it.
Problem to detect

Find the repeating group and make every value atomic.

3NF diagnosis challenge

Decide whether this table is in 3NF: Device(DeviceID, DeviceName, LabID, LabName)

Choose an answer and justify it before checking.

Common Mistakes and Misconceptions

  • Assuming atomic values alone prove that a table is in 3NF.
  • Using the sample values to guess dependencies instead of using the meaning of the data.
  • Removing every repeated value, including valid foreign-key repetition.
  • Checking 2NF without first identifying the complete candidate key.
  • Moving an attribute that depends on the whole compound key.
  • Calling a transitive dependency a partial dependency, or vice versa.
  • Creating new tables but forgetting their primary keys.
  • Splitting data without adding the foreign keys needed to reconstruct the original information.
  • Stating that a table is in 3NF without explaining the dependencies.

Practice

Original practice tasks

  1. Define repeating group, partial dependency and transitive dependency.
  2. Explain why RequestItemFlat is in 1NF but not in 2NF.
  3. Explain why QuantityRequested remains in RequestItem after conversion to 2NF.
  4. Explain why Request(RequestID, RequestDate, TeamID, TeamName, TeamBase) is in 2NF but not in 3NF.
  5. A relation has the key (PatientID, AppointmentDate) and includes PatientName, DoctorID and ConsultationLength. PatientName depends only on PatientID. Identify the normalisation problem and propose a correction.
  6. Determine whether Room(RoomID, Capacity, BuildingID, BuildingName) is in 3NF. State any assumption you make.
  7. A bicycle-hire form stores HireID, HireDate, CustomerID, CustomerName and a repeating list of BicycleID, BicycleType and HoursUsed. Produce a 3NF design.
  8. Explain one update anomaly and one deletion anomaly that normalisation can reduce.

Review

Stage Condition Problem removed Typical action
1NF Atomic values, no repeating groups and identifiable tuples Repeated sets within one record Make one tuple per occurrence and define a key
2NF 1NF plus full dependency on every compound candidate key Partial dependencies Move attributes determined by part of the key
3NF 2NF plus no dependency of a non-key attribute on another non-key attribute Transitive dependencies Create a table for the intermediate determinant
Final check: For each final table, identify its key and complete the sentence “Every non-key attribute depends on …”. This exposes most remaining problems.