✓ No transitive dependencies — non-key attributes must depend only on the primary key, not on other non-key attributes
Example: if Order table has CustomerID PK, CustomerName, CustomerPostcode → CustomerName depends on CustomerID ✓, but PostcodeTown depends on CustomerPostcode (not CustomerID) → transitive dependency.
Fix: extract PostcodeTown into a Postcode table. Each non-key attribute must depend directly on the PK only.
Summary of Normal Forms
Form
Requirement
UNF
Raw data — no structure
1NF
Atomic values, unique rows (PK), no repeating groups
2NF
1NF + no partial dependencies (all non-key attrs depend on whole PK)
3NF
2NF + no transitive dependencies (non-key attrs depend only on PK, not on other non-key attrs)
Exam tip: AQA 7517 normalisation questions often give you a table in UNF or 1NF and ask you to normalise to 3NF. Remember: 2NF removes partial dependencies (only relevant if composite PK). 3NF removes transitive dependencies (A → B → C, where C doesn't directly depend on A). Normalisation to 3NF = no redundancy, no anomalies. You may need to draw resulting tables with PKs and FKs marked.
▶
Click through the slides at your own pace. Use arrow keys or click to advance.
Click slide or press arrow keys to navigate
Worksheet — 4.10.4 Normalisation
8 questions · instantly marked · AQA 7517 standard
Q1Explain what normalisation is and state two benefits of normalising a database to 3NF.[2]
✅ Mark scheme
Mark scheme
Normalisation: process of structuring a relational database to remove redundancy and improve data integrity [1]; done through a series of normal forms (1NF → 2NF → 3NF) [1]. Benefits (any 2): eliminates data redundancy — same data not stored multiple times [1]; removes update/insertion/deletion anomalies [1]; easier to maintain and update — change in one place [1]; improves query efficiency [1].
Q2State the three requirements for a table to be in First Normal Form (1NF).[2]
✅ Mark scheme
Mark scheme
All values are atomic (no repeating groups or multi-valued cells — one value per cell) [1]; all values in a column are the same data type [1]; each row is unique, identified by a primary key [1].
Q3Explain what a partial dependency is. Why is 2NF only relevant when a composite key exists?[2]
✅ Mark scheme
Mark scheme
Partial dependency: a non-key attribute depends on only part of a composite primary key, not the whole key [1]. Example: in OrderLine(OrderID, ProductID, CustomerName), CustomerName depends only on OrderID — not on ProductID [1]. 2NF only applies with composite keys because: with a single-column PK, a non-key attribute either depends on the whole PK (the only option) or is independent — there is no 'part' to partially depend on [1]; therefore a table in 1NF with a single-column PK is automatically in 2NF [1].
Q4Explain what a transitive dependency is and give an example. How is it resolved?[3]
✅ Mark scheme
Mark scheme
Transitive dependency: a non-key attribute depends on another non-key attribute (not directly on the PK) [1]. Example: in Employee(EmpID PK, EmpName, DeptID, DeptName) — DeptName depends on DeptID, not EmpID [1]; this is a transitive dependency: EmpID → DeptID → DeptName [1]. Resolution: extract DeptID and DeptName into a separate Department table; Employee keeps DeptID as a FK [1].
Q5A table has: OrderID, CustomerID, CustomerName, CustomerEmail, ProductID, Quantity. The PK is OrderID + ProductID. Identify any partial dependencies.[3]
✅ Mark scheme
Mark scheme
Partial dependencies: CustomerID, CustomerName, and CustomerEmail all depend only on OrderID — not on ProductID [1]; they are partially dependent on the composite PK [1]. Quantity depends on the whole composite PK (OrderID + ProductID — how much of a product was ordered) [1]; fix: move CustomerID, CustomerName, CustomerEmail to a separate Order/Customer table; keep OrderLine(OrderID, ProductID, Quantity) [1].
Q6Describe the difference between 2NF and 3NF in terms of which dependencies each removes.[3]
✅ Mark scheme
Mark scheme
2NF removes partial dependencies: non-key attributes depending on only part of a composite PK [1]; ensures every non-key attribute depends on the whole PK [1]. 3NF removes transitive dependencies: non-key attributes depending on other non-key attributes rather than the PK directly [1]; ensures every non-key attribute depends only on the PK — not on any other non-key column [1].
Q7What is meant by an unnormalised form (UNF) table? Give two characteristics.[2]
✅ Mark scheme
Mark scheme
UNF: raw, unstructured data with no normalisation applied [1]. Characteristics (any 2): cells may contain multiple values (e.g. a comma-separated list) [1]; repeating groups of data [1]; may have no clear primary key [1]; significant data redundancy [1].
Q8Normalise the following to 3NF. Table: Booking(BookingID PK, StudentID, StudentName, CourseID, CourseName, TeacherID, TeacherName). State the resulting tables.[3]
✅ Mark scheme
Mark scheme
1NF: already atomic with single PK [1]. 2NF: no composite PK so no partial dependencies — already in 2NF [1]. 3NF: identify transitive dependencies. StudentName → depends on StudentID (not BookingID directly) [1]; CourseName → depends on CourseID [1]; TeacherName → depends on TeacherID [1]. Result tables: Booking(BookingID PK, StudentID FK, CourseID FK) [1]; Student(StudentID PK, StudentName); Course(CourseID PK, CourseName, TeacherID FK); Teacher(TeacherID PK, TeacherName) [1].
Normalisation Quiz
Question 1 of 15
You scored
out of 15
Card 1 of 8
Click to reveal definition
🎉
All cards reviewed!
Term
Definition
🎯
Mini Test — Normalisation
10 questions · 10 minutes
⏱ 10:00
Section A — Multiple Choice [5 marks]
Q11NF requires that all values in a table are:
Q2A partial dependency occurs when:
Q3A transitive dependency occurs when:
Q42NF is automatically achieved for tables with:
Q5In 3NF, every non-key attribute must depend:
Section B — Short Answer [5 marks]
Q6What is an unnormalised form (UNF) table?
Mark schemeRaw data with no normalisation — may contain multi-valued cells (repeating groups), no clear primary key, and significant data redundancy [1].
Q7Why is normalisation important for database maintenance?
Mark schemeRemoves redundancy — data stored once, so a change in one place propagates everywhere [1]; eliminates update/insertion/deletion anomalies [1].
Q8A table has Employee(EmpID PK, EmpName, DeptID, DeptName). Is this in 3NF? Explain.
Mark schemeNot in 3NF — transitive dependency: DeptName depends on DeptID, not EmpID directly [1]; fix: extract DeptID and DeptName to a separate Department table [1].
Q9What two tables would you create when resolving the transitive dependency in Employee(EmpID, EmpName, DeptID, DeptName)?