Cambridge 9618 · International A Level Computer Science · ~18 min read
Notes
Video
Slides
Quiz
Worksheet
What is Normalisation?
Normalisation is the process of structuring a relational database to reduce data redundancy and eliminate insertion, update, and deletion anomalies. It involves decomposing tables step-by-step into smaller, well-structured tables.
There are three main normal forms to know for Cambridge 9618: 1NF, 2NF, and 3NF. Each builds on the previous.
Why Normalise? — Anomalies
Consider an unnormalised table storing all order data in one table:
Orders (Unnormalised)
OrderID
CustomerName
CustomerEmail
ProductID
ProductName
Price
001
Alice
alice@email.com
P1
Laptop
899
002
Alice
alice@email.com
P2
Mouse
25
003
Bob
bob@email.com
P1
Laptop
899
⚡ Update Anomaly
If the Laptop price changes to 799, we must update EVERY row containing P1. Missed updates create inconsistency.
➕ Insertion Anomaly
We cannot add a new product unless someone has already ordered it — ProductName depends on OrderID.
🗑️ Deletion Anomaly
If we delete Order 003 (the only Bob order), we lose ALL information about Bob's email address.
Normal Forms
Unnormalised Form (UNF)
❌ Contains repeating groups
Data has not been structured. May contain repeating groups (e.g. multiple product values in one cell: "Laptop, Mouse, Keyboard"), multi-valued attributes, or no clear primary key. Raw data as collected — spreadsheet-style.
First Normal Form (1NF)
✓ All values atomic · No repeating groups · Has a primary key
A table is in 1NF if:
• Every cell contains exactly ONE value (no multi-valued attributes like "Maths, Science" in one cell)
• No repeating groups (no multiple columns for the same thing: Phone1, Phone2, Phone3)
• There is a primary key that uniquely identifies each row
To achieve 1NF: Split multi-valued cells into separate rows or separate tables. Add or identify a primary key.
Second Normal Form (2NF)
✓ 1NF + No partial dependencies
A table is in 2NF if it is in 1NF AND every non-key attribute is fully dependent on the WHOLE primary key.
Partial dependency = a non-key attribute depends on only PART of a composite primary key. (This only applies when the PK has multiple columns — a composite key.)
To achieve 2NF: If a non-key attribute depends on only part of the PK, move it to a new table with that part of the PK as the key.
2NF Example
Suppose OrderLine has a composite PK of (OrderID, ProductID):
(OrderID, ProductID)→Quantity✓ Full dependency — OK ProductID→ProductName, Price✗ Partial dependency — NOT OK in 2NF
Fix: Move ProductName and Price to a separate Product table with ProductID as PK.
OrderLine (2NF)
OrderID
ProductID
Quantity
001
P1
1
001
P2
2
Product (new)
ProductID
ProductName
Price
P1
Laptop
899
P2
Mouse
25
Third Normal Form (3NF)
✓ 2NF + No transitive dependencies
A table is in 3NF if it is in 2NF AND every non-key attribute is directly dependent on the primary key only — not on another non-key attribute.
Transitive dependency = A → B → C, where A is the PK, B and C are non-key attributes. C depends on A indirectly via B (not directly).
To achieve 3NF: Move transitively dependent attributes to a new table with the intermediate attribute as PK.
StudentID→CourseID✓ Direct — OK CourseID→CourseName✗ Transitive: CourseName depends on CourseID (not StudentID) — NOT OK in 3NF StudentID→CourseNameIndirect (transitive) dependency
Fix: Move CourseName to a Course table with CourseID as PK. Student table keeps CourseID as a foreign key.
Student (3NF)
StudentID
Name
CourseID
S001
Alice
CS
S002
Bob
MATH
Course (extracted)
CourseID
CourseName
CS
Computer Science
MATH
Mathematics
Summary of Normal Forms
Form
Requirement
Removes
UNF → 1NF
Atomic values, no repeating groups, has PK
Multi-valued cells, repeating column groups
1NF → 2NF
No partial dependencies (all non-key attributes depend on the WHOLE PK)
Partial dependencies (only relevant when PK is composite)
2NF → 3NF
No transitive dependencies (non-key attributes depend on PK only, not on other non-key attributes)
Transitive dependencies (A → B → C chains)
Benefits of Normalisation
Eliminates data redundancy: each piece of data is stored only once
Prevents update anomalies: update one place only, not many
Prevents insertion anomalies: can add new data independently
Prevents deletion anomalies: deleting one record doesn't accidentally delete other data
Improves data integrity: the data is consistent and accurate
Cambridge 9618 exam tip: For normalisation questions, always start by identifying the primary key (or composite key). Check for partial dependencies (only possible with composite keys). Check for transitive dependencies. When describing a dependency use the format: "Attribute X depends on Attribute Y" or "Y → X". When asked to normalise, show each step: write out the new tables, underline PKs, and state what type of dependency was removed. Always check: does every non-key attribute depend DIRECTLY and ONLY on the whole PK?
⚠️ Common Mistakes
Partial dependencies require a composite key — you cannot have a partial dependency if the primary key is a single column. Partial dependencies only arise with composite keys. A table with a single-column PK in 1NF is automatically in 2NF.
Confusing partial and transitive dependencies — PARTIAL: non-key attribute depends on only PART of the composite PK. TRANSITIVE: non-key attribute depends on ANOTHER non-key attribute (which in turn depends on the PK). These are different problems requiring different fixes.
Losing information during normalisation — when splitting a table, ensure the relationship can be reconstructed. The foreign key in the original table must match the PK of the extracted table so you can JOIN them back.
Not all tables need to reach 3NF — for very simple tables with single-column PKs and no transitive dependencies, 1NF may already be 3NF. The normal form steps apply where there is an actual problem to fix.
Normalisation trade-offs — over-normalised databases require many JOINs which can slow down queries. In practice, some denormalisation (controlled redundancy) is used for read-heavy applications. Cambridge asks for the ideal normalised form but real databases may not always follow it strictly.
✅ Notes completed!
▶
Video coming soon
Click slide or press arrow keys to navigate
Worksheet — 4.5.2 Normalisation
8 questions · Cambridge 9618 standard
Q1Define each term: (a) First Normal Form (1NF). (b) Partial dependency. (c) Transitive dependency.[6]
✅ Mark scheme
(a) 1NF: a table is in First Normal Form when every attribute contains only ATOMIC (single, indivisible) values — no multi-valued attributes or repeating groups — AND there is a primary key that uniquely identifies each row [2 — 1 for atomic/no repeating groups, 1 for primary key]; (b) Partial dependency: a non-key attribute depends on only PART of a composite primary key — rather than on the whole key; only possible when the PK consists of two or more columns [2]; (c) Transitive dependency: a non-key attribute depends on ANOTHER non-key attribute rather than directly on the primary key — a chain of the form PK → B → C where B and C are both non-key attributes [2].
Q2The following table is in 1NF with a composite primary key of (StudentID, ModuleCode): Student(StudentID, StudentName, ModuleCode, ModuleName, Grade). Identify any partial dependencies and explain what changes are needed to reach 2NF.[4]
✅ Mark scheme
Partial dependencies identified [2]: (1) StudentID → StudentName: StudentName depends only on StudentID (not on the whole composite key) — partial dependency [1]; (2) ModuleCode → ModuleName: ModuleName depends only on ModuleCode — partial dependency [1]; (3) (StudentID, ModuleCode) → Grade: Grade depends on BOTH parts of the key (a student's grade for a specific module) — this is a FULL dependency, which is correct; Changes to reach 2NF [2]: split into three tables: (a) Student(StudentID PK, StudentName) — StudentID is the full PK [1]; (b) Module(ModuleCode PK, ModuleName) [1]; (c) Enrolment(StudentID FK, ModuleCode FK, Grade) with composite PK (StudentID, ModuleCode) [1]. Award 2 for correct identification + 2 for correct decomposition. Max 4.
Q3The table Employee(EmpID, Name, DeptID, DeptName, DeptLocation) is in 2NF. Identify any transitive dependencies and explain what changes are needed to reach 3NF.[4]
✅ Mark scheme
Transitive dependencies identified [2]: EmpID → DeptID → DeptName: DeptName does NOT directly depend on EmpID (the PK) — it depends on DeptID, which is a non-key attribute [1]; EmpID → DeptID → DeptLocation: same — DeptLocation depends on DeptID (non-key), not directly on EmpID [1]; Changes to reach 3NF [2]: split into two tables: (a) Employee(EmpID PK, Name, DeptID FK) — DeptID becomes a foreign key referencing Department [1]; (b) Department(DeptID PK, DeptName, DeptLocation) — a new table for department data [1]. Name depends directly on EmpID (correct — not transitive). DeptID is retained in Employee as a foreign key to maintain the relationship between employees and departments.
Q4Explain what an "update anomaly" is. Give a specific example using an Orders table that stores both OrderID, CustomerID, CustomerEmail, and ProductName.[3]
✅ Mark scheme
Update anomaly: an inconsistency that occurs when the same data is stored in multiple places and only some instances are updated [1]; example: CustomerEmail is stored in the Orders table — if the same customer (say CustomerID=C001) has made 10 orders, their email appears in 10 rows; if the customer changes their email, all 10 rows must be updated [1]; if only some rows are updated (perhaps 8 of 10), the database now has inconsistent data — some orders show the old email and some the new one; there is no longer a single source of truth for that customer's email address [1].
Q5A table Book(ISBN, Title, AuthorID, AuthorName, AuthorEmail) has the single primary key ISBN. Is this table in 2NF? Explain. Is it in 3NF? Explain.[4]
✅ Mark scheme
2NF: YES — the table IS in 2NF [1]: 2NF requires no PARTIAL dependencies; partial dependencies only arise when the primary key is COMPOSITE (multiple columns); here the PK is a single column (ISBN) — partial dependencies are impossible; therefore it is automatically in 2NF if it's in 1NF [1]; 3NF: NO — the table is NOT in 3NF [1]: there is a TRANSITIVE dependency: ISBN → AuthorID → AuthorName and ISBN → AuthorID → AuthorEmail; AuthorName and AuthorEmail depend on AuthorID (a non-key attribute) rather than directly on ISBN (the PK) [1]; Fix: split into Book(ISBN PK, Title, AuthorID FK) and Author(AuthorID PK, AuthorName, AuthorEmail).
Q6State TWO benefits of normalising a database to 3NF. For each benefit, explain how normalisation achieves it.[4]
✅ Mark scheme
Benefit 1: Eliminates data redundancy [1] — normalisation ensures each piece of data is stored in exactly ONE place; for example, a customer's email is stored once in a Customer table rather than repeated in every order row; this reduces storage requirements and keeps the database smaller [1]; Benefit 2: Prevents update anomalies [1] — because data is stored only once, updating it requires changing only one row in one table; there is no risk of partial updates where some copies of the data are updated and others are not, leading to inconsistency [1]. Accept any two from: eliminates redundancy / prevents update anomalies / prevents insertion anomalies (can add a customer without an order) / prevents deletion anomalies (deleting an order doesn't lose customer data) / improves data integrity / simplifies maintenance. Must state benefit AND explain mechanism for full marks.
Q7A table Orders has attributes: OrderID, CustomerName, CustomerAddress, ProductID, ProductName, ProductPrice, Quantity. Identify one partial dependency and one transitive dependency in this table. For each, state which normal form eliminates it and how.[6]
✅ Mark scheme
Partial dependency example: ProductName and ProductPrice depend only on ProductID, not on the full composite key (OrderID, ProductID) [1]; 2NF eliminates partial dependencies — move ProductID, ProductName, ProductPrice to a separate Products table with ProductID as primary key [1]; Transitive dependency example: CustomerAddress depends on CustomerName, which depends on OrderID (not directly on the key) / or CustomerAddress → CustomerID → OrderID [1]; 3NF eliminates transitive dependencies — move CustomerName, CustomerAddress to a Customers table with a CustomerID primary key [1]; Award 1 mark each for clear explanation of how the normal form removes the dependency [2 max combined].
Q8A database designer chooses to de-normalise a heavily queried reporting table by storing ProductName alongside each order record rather than joining the Products table at query time. State two advantages and one disadvantage of this de-normalisation decision.[4]
✅ Mark scheme
Advantage 1: faster query performance — no JOIN operation required to retrieve ProductName, reducing query execution time on large datasets [1]; Advantage 2: simpler queries — SELECT statements do not need JOIN clauses, making them easier to write and maintain [1]; Disadvantage: data redundancy / update anomaly — if a product name changes, every order row containing that name must be updated, risking inconsistency if any row is missed [1]; Award 1 additional mark if the student links the advantage specifically to read-heavy workloads / reporting scenarios [1 max].
Topic Quiz
Question 1 of 10
You scored
out of 10
Card 1 of 8
Click to reveal definition
🎉
All cards reviewed!
Term
Definition
🎯
Mini Test — 4.5.2 Normalisation
10 questions · 10 marks · 10 minutes
⏱ 10:00
Section A — Multiple Choice [5 marks]
Q1A partial dependency can only occur when:
Q2A transitive dependency means:
Q3Which normal form requires that there are no transitive dependencies?
Q4An insertion anomaly means:
Q5A table with a SINGLE-column primary key that is in 1NF is automatically in:
Section B — Short Answer [5 marks]
Q6Explain what is meant by a "deletion anomaly" and give an example to illustrate your answer.
Mark schemeDeletion anomaly: when deleting one piece of data inadvertently removes other data that should be retained [1]; example: in an unnormalised Orders table storing Customer, Product, and Order data together — if a customer placed only one order and that order is deleted, all information about that customer (their name, email, address) is also deleted, even though the customer information is still needed [1]; proper normalisation prevents this by storing customer data in a separate Customer table — deleting an order only removes the order row; customer data persists in the Customer table [1].
Q7State the requirement for First Normal Form (1NF). Give an example of a table that violates 1NF and explain how to fix it.
Mark scheme1NF requirements: all attribute values must be ATOMIC (single, indivisible — no lists in one cell); no repeating groups; a primary key must exist [1]; example violation: a table with a column "Subjects" containing values like "Maths, Science, English" in a single cell — this is not atomic [1]; fix: create a new row for each subject, creating a composite PK of (StudentID, Subject) [1]. Alternative violation: columns Phone1, Phone2, Phone3 — repeating group. Fix: create a separate PhoneNumbers table with StudentID as FK [accept]. Award all 3 marks for correct requirement + valid example + correct fix.
Q8A table Booking(BookingID, RoomID, RoomType, GuestID, GuestName, CheckIn) has a single PK of BookingID. Identify any transitive dependencies.
Mark schemeTransitive dependency 1: BookingID → RoomID → RoomType. RoomType depends on RoomID (a non-key attribute), not directly on BookingID (the PK) — this is a transitive dependency [1]; Transitive dependency 2: BookingID → GuestID → GuestName. GuestName depends on GuestID (a non-key attribute), not directly on BookingID [1]; Fix: extract Room(RoomID PK, RoomType) and Guest(GuestID PK, GuestName), keeping Booking(BookingID PK, RoomID FK, GuestID FK, CheckIn) [1]. CheckIn depends directly on BookingID (it's when the specific booking is) — not transitive. Award 1 per transitive dependency correctly identified + 1 for fix.
Q9Explain why normalisation may cause queries to run MORE slowly even though the database structure is improved.
Mark schemeNormalisation splits data into more tables to eliminate redundancy [1]; queries that previously needed only one table now require JOIN operations to combine data from multiple tables; JOINs require matching rows from different tables using their key columns, which is more computationally expensive than reading a single flat table [1]; for read-heavy applications with large datasets, many JOINs can significantly slow query performance; this is why some databases use controlled DENORMALISATION — deliberately introducing some redundancy to reduce JOIN overhead and speed up common read queries [1]. This is a known trade-off: normalisation improves update/insert/delete integrity but may reduce read query performance.
Q10A table has composite PK (CourseID, StudentID) and attributes: CourseName, StudentName, Grade. State which attributes have partial dependencies and what they depend on.
Mark schemeCourseID → CourseName: CourseName depends only on CourseID (the course name is the same regardless of which student) — PARTIAL dependency on CourseID [1]; StudentID → StudentName: StudentName depends only on StudentID (the student's name is independent of which course) — PARTIAL dependency on StudentID [1]; (CourseID, StudentID) → Grade: Grade depends on BOTH parts of the composite PK (a specific student's result in a specific course cannot be determined from either key alone) — this is a FULL dependency — correct, no problem [1]. Fix: Course(CourseID PK, CourseName), Student(StudentID PK, StudentName), Enrolment(CourseID FK, StudentID FK, Grade) with composite PK (CourseID, StudentID) [1].