OCR H446 · A Level Computer Science · ~14 min read
Notes
—
Video
—
Slides
—
Worksheet
—
Quiz
What is Normalisation?
Normalisation is the process of structuring a relational database to reduce data redundancy (duplicated data) and improve data integrity (consistency and accuracy). It involves decomposing (splitting) a single large table into multiple smaller tables and linking them with keys. Normalisation removes update anomalies, insertion anomalies, and deletion anomalies.
Why Normalise?
Redundancy: storing the same data in multiple places wastes space and creates inconsistency risk.
Update anomaly: if a customer's address is stored in every order, updating their address requires changing many rows — missing one creates inconsistency.
Insertion anomaly: you can't insert a course until at least one student enrols (if student and course data are in the same table).
Deletion anomaly: deleting the last student from a course would also delete the course's information entirely.
Unnormalised Form (UNF)
Data in unnormalised form has no restrictions — repeating groups (multiple values in one cell) are allowed. This is the raw, flat-file representation before any normalisation is applied.
Example UNF table (Order):
OrderID
CustomerName
CustomerAddr
Items (repeating group)
O01
Aisha Khan
14 Oak St
Pen ×3, Book ×1
O02
Ben Smith
7 Elm Rd
Pen ×2
First Normal Form (1NF)
1NF Rules
All values must be atomic — each cell holds a single, indivisible value (no lists or repeating groups in a cell).
Each column must contain values of the same type.
Each row must be uniquely identifiable (there is a primary key).
No repeating groups of columns.
To achieve 1NF: split repeating groups into separate rows. Each item in an order becomes its own row.
OrderID (PK)
CustomerName
CustomerAddr
ItemName (PK)
Quantity
O01
Aisha Khan
14 Oak St
Pen
3
O01
Aisha Khan
14 Oak St
Book
1
O02
Ben Smith
7 Elm Rd
Pen
2
Still has redundancy (CustomerName and CustomerAddr repeat) but atomic values and composite PK (OrderID + ItemName).
Second Normal Form (2NF)
2NF Rules
Must already be in 1NF.
Every non-key attribute must be fully functionally dependent on the entire primary key — not just part of it.
This only applies when the primary key is a composite key (two or more fields).
If a non-key attribute depends on only PART of the composite key, it has a partial dependency — move it to a new table.
In the 1NF table above: CustomerName and CustomerAddr depend only on OrderID (not on ItemName) — this is a partial dependency. Quantity depends on both OrderID and ItemName — that's fine.
2NF result — split into two tables:
Order(OrderID PK, CustomerName, CustomerAddr) — CustomerName/Addr fully depend on OrderID
OrderItem(OrderID FK + ItemName = composite PK, Quantity)
Third Normal Form (3NF)
3NF Rules
Must already be in 2NF.
Every non-key attribute must depend directly on the primary key — not on another non-key attribute.
If a non-key attribute depends on another non-key attribute, this is a transitive dependency — move it to a new table.
Example: suppose the Order table contains CustomerID, CustomerName, CustomerAddr, CustomerCity, CustomerPostcode. The city and postcode might depend on CustomerID — fine. But suppose we also add: SupplierID, SupplierName where SupplierName depends on SupplierID (not on OrderID directly) — that's a transitive dependency.
A simpler example: if Order has: OrderID, CustomerID, CustomerName — CustomerName depends on CustomerID (not on OrderID). This transitive dependency should be removed:
Order(OrderID PK, CustomerID FK) — links to Customer
Customer(CustomerID PK, CustomerName, CustomerAddr) — customer data in its own table
In 3NF, every non-key attribute must depend on "the key, the whole key, and nothing but the key."
Summary: The Three Normal Forms
Form
Rule
Removes
1NF
All values atomic; primary key exists; no repeating groups
Repeating groups / multi-valued cells
2NF
1NF + all non-key attributes fully dependent on the whole primary key
Partial dependencies (only if composite PK)
3NF
2NF + no transitive dependencies (non-key attributes depend only on the PK)
Transitive dependencies
Functional Dependency
Attribute B is functionally dependent on attribute A (written A → B) if each value of A determines exactly one value of B. For example: StudentID → StudentName means knowing the StudentID tells you exactly one StudentName. The primary key functionally determines all other attributes in a normalised table.
Exam tip: The mantra for 3NF is "every non-key attribute must depend on the key, the whole key, and nothing but the key." 1NF = no repeating groups. 2NF = no partial dependencies. 3NF = no transitive dependencies. Know ALL three and be able to apply them to a given table.
Exam tip: 2NF only matters if the primary key is composite. If the PK is a single field, any table in 1NF is automatically in 2NF (there can be no partial dependency of a single-field PK).
⚠ Common Mistakes
Thinking that 2NF applies even when the primary key is just one field — partial dependency only occurs with a COMPOSITE primary key. A single-field PK table in 1NF is automatically in 2NF.
Confusing partial dependency with transitive dependency — partial dependency = non-key field depends on PART of the PK (2NF issue). Transitive dependency = non-key field depends on ANOTHER non-key field (3NF issue).
Forgetting to keep the foreign key when splitting tables — when you move a dependency to a new table, you must keep a foreign key in the original table to maintain the relationship.
✓ Notes completed!
▶
Video coming soon
Click to advance · Arrow keys also work
Click slide or press arrow keys to navigate
✍
Worksheet — 1.3.2c Normalisation
8 questions · 20 marks · instantly marked
Q1State what 'data redundancy' means and explain why it is a problem in a database.[2 marks]
✓ Mark scheme
Data redundancy is when the same data is stored in multiple places within a database [1]; it is a problem because: updating data requires changing it in multiple places — missing one creates inconsistency; it wastes storage space; it increases the risk of anomalies (update, insertion, deletion anomalies) where different copies of the same fact disagree [1].
Q2Explain what an 'update anomaly' is in a database context. Give a specific example.[2 marks]
✓ Mark scheme
An update anomaly occurs when updating a piece of data requires changing it in multiple rows/places — if not all copies are updated, the database contains contradictory information [1]; example: if a customer's address is stored in every order record, changing their address requires updating every order row for that customer. If any row is missed, the database shows two different addresses for the same customer [1].
Q3State the ONE requirement that a table must satisfy to be in First Normal Form (1NF).[2 marks]
✓ Mark scheme
Any 2 from: all values must be atomic — each cell holds one indivisible value, no lists or repeating groups [1]; each column must hold values of the same data type [1]; each row must be uniquely identifiable (there must be a primary key) [1]; no repeating groups of columns [1]. (Award 2 for 2 correct points.)
Q4Explain what a 'partial dependency' is, and state which normal form removes it.[3 marks]
✓ Mark scheme
A partial dependency is when a non-key attribute depends on only PART of the composite primary key, not on the entire composite key [1]; example: in a table with composite PK (StudentID, CourseCode), if StudentName depends only on StudentID (not on CourseCode), this is a partial dependency [1]; partial dependencies are removed when moving from 1NF to 2NF (Second Normal Form removes partial dependencies) [1].
Q5A table called Booking has fields: BookingID (PK), CustomerID, CustomerName, CustomerEmail, RoomID, CheckInDate. Identify ONE transitive dependency in this table and explain how you would resolve it to achieve 3NF.[4 marks]
✓ Mark scheme
Transitive dependency identified: CustomerName and CustomerEmail depend on CustomerID, not directly on BookingID (the primary key) — this is a transitive dependency because CustomerID is a non-key attribute and CustomerName/Email depend on it [1]; this means "the key → CustomerID → CustomerName/Email" rather than "the key → CustomerName/Email" directly [1]; to resolve: create a separate Customer table: Customer(CustomerID PK, CustomerName, CustomerEmail) [1]; update Booking to: Booking(BookingID PK, CustomerID FK, RoomID, CheckInDate) — CustomerID becomes a foreign key referencing Customer.CustomerID [1].
Q6Explain the difference between a partial dependency and a transitive dependency, giving one example of each.[4 marks]
✓ Mark scheme
Partial dependency: a non-key attribute depends on only part of a composite primary key (not the whole key) — removed in 2NF. Example: table with PK (OrderID, ProductID); if ProductName depends only on ProductID (not on OrderID), ProductName has a partial dependency on the PK [2 — 1 for definition, 1 for example]; transitive dependency: a non-key attribute depends on another non-key attribute rather than directly on the primary key — removed in 3NF. Example: table with PK OrderID; if CustomerPostcode depends on CustomerCity, and CustomerCity depends on CustomerID (a non-key field), then CustomerPostcode has a transitive dependency [2 — 1 for definition, 1 for example].
Q7State the classic statement used to remember the requirement for 3NF.[1 mark]
✓ Mark scheme
"Every non-key attribute must depend on the key, the whole key, and nothing but the key." [1 mark for correct or near-correct quotation]
Q8A student claims "since my primary key is just one field (StudentID), I don't need to worry about 2NF." Is this correct? Explain your reasoning.[3 marks]
✓ Mark scheme
The student is correct that the table cannot violate 2NF [1]; a partial dependency requires a composite primary key — if a non-key attribute depends on part of the key, there must be a key with multiple parts to be 'partial' with respect to [1]; with a single-field primary key, a table that satisfies 1NF automatically satisfies 2NF because every non-key attribute must depend on the only key field (there is no 'part of the key' to partially depend on) [1].
Topic Quiz
1 of 15
You scored
out of 15
🎯
Mini Test — 1.3.2c Normalisation
10 questions · 10 marks · 10 minutes
5 MCQ + 5 short answer
⏱10:00
10 marks
Section A — Multiple Choice
Q1What does First Normal Form (1NF) require?
Q2A partial dependency occurs when:
Q3Which normal form removes transitive dependencies?
Q4If a primary key consists of a single field, moving from 1NF to 2NF:
Q5What is an 'insertion anomaly'?
Section B — Short Answer
Q6State what 'data redundancy' means.
Mark schemeData redundancy is when the same data is stored in multiple places within a database, causing wasted storage and the risk of inconsistency when updates are made. [1 mark]
Q7State the 3NF mantra (the key, the whole key, etc.).
Mark scheme"Every non-key attribute must depend on the key, the whole key, and nothing but the key." [1 mark]
Q8What type of anomaly does normalisation prevent when the same data is in multiple rows?
Mark schemeAn update anomaly — if data is duplicated across rows, updating one copy without updating all others creates inconsistency. [1 mark]
Q9Explain what a transitive dependency is.
Mark schemeA transitive dependency is when a non-key attribute depends on another non-key attribute rather than directly on the primary key. E.g. if PK is OrderID, and CustomerName depends on CustomerID (a non-key field), then CustomerName has a transitive dependency. [1 mark]
Q10State ONE difference between 1NF and 2NF.
Mark scheme1NF requires all values to be atomic (no repeating groups); 2NF additionally requires that all non-key attributes are fully dependent on the ENTIRE composite primary key (no partial dependencies). [1 mark]