All subjects · Information Technology · WIT13 2 hour written exam on data, enabling technologies and systems development
Normalising data to 1NF, 2NF and 3NF
Edexcel IAL Information Technology · WIT13 2 hour written exam on data, enabling technologies and systems development
Exam questions give you a set of unnormalised data (often a form or flat table) and ask you to normalise it to third normal form, showing tables, primary keys and foreign keys (spec 12.2.3).
Unnormalised form (UNF) to 1NF
Start from a flat list of all attributes, including repeating groups (several values for one key, such as many products on one order).
First normal form (1NF):
- every attribute holds a single atomic value (no lists in a cell, no 'Item1, Item2, Item3' columns);
- there are no repeating groups;
- each table has a primary key.
Method: move the repeating group into its own table and give it a composite key of the original key plus the repeating group's key.
Example UNF: ORDER(OrderID, OrderDate, CustomerID, CustomerName, CustomerTown, {ProductID, Description, Price, Quantity}).
1NF: ORDER(OrderID, OrderDate, CustomerID, CustomerName, CustomerTown) and ORDER_ITEM(OrderID, ProductID, Description, Price, Quantity).
1NF to 2NF
Second normal form (2NF): in 1NF, and no partial dependencies: every non-key attribute depends on the whole of a composite primary key, not just part of it.
(A table with a single-attribute primary key that is in 1NF is automatically in 2NF.)
In ORDER_ITEM(OrderID, ProductID, Description, Price, Quantity): Description and Price depend only on ProductID; Quantity depends on both.
2NF: PRODUCT(ProductID, Description, Price) and ORDER_ITEM(OrderID, ProductID, Quantity).
2NF to 3NF
Third normal form (3NF): in 2NF, and no transitive (non-key) dependencies: no non-key attribute depends on another non-key attribute.
A handy check: every non-key attribute depends on 'the key, the whole key and nothing but the key'.
In ORDER(OrderID, OrderDate, CustomerID, CustomerName, CustomerTown): CustomerName and CustomerTown depend on CustomerID, which is not the key.
3NF: CUSTOMER(CustomerID, CustomerName, CustomerTown) and ORDER(OrderID, OrderDate, CustomerID*), where * marks the foreign key.
Final 3NF design
| Table | Primary key | Other attributes | Foreign key(s) |
|---|---|---|---|
| CUSTOMER | CustomerID | CustomerName, CustomerTown | none |
| ORDER | OrderID | OrderDate | CustomerID |
| PRODUCT | ProductID | Description, Price | none |
| ORDER_ITEM | OrderID + ProductID | Quantity | OrderID, ProductID |
Four tables in 3NF. Exam tip: underline primary keys, mark foreign keys, and keep every original attribute somewhere.
Worked example
A gym records: MemberID, MemberName, {ClassID, ClassName, InstructorID, InstructorName, DateAttended}. Normalise to 3NF (assume each class has one instructor).
- 1NF: MEMBER(MemberID, MemberName); ATTENDANCE(MemberID, ClassID, DateAttended, ClassName, InstructorID, InstructorName). Key: MemberID + ClassID + DateAttended (a member attends the same class on many dates).
- 2NF: ClassName, InstructorID and InstructorName depend only on ClassID, so CLASS(ClassID, ClassName, InstructorID, InstructorName); ATTENDANCE(MemberID, ClassID, DateAttended).
- 3NF: InstructorName depends on InstructorID (non-key), so INSTRUCTOR(InstructorID, InstructorName); CLASS(ClassID, ClassName, InstructorID*).
Answer: MEMBER(MemberID, MemberName); ATTENDANCE(MemberID*, ClassID*, DateAttended); CLASS(ClassID, ClassName, InstructorID*); INSTRUCTOR(InstructorID, InstructorName)
Check yourself
- Which feature shows a table is NOT in first normal form?
- The table has a composite primary key
- A cell holds several phone numbers separated by commas
- A non-key attribute depends on another non-key attribute
- The table has a foreign key
Show answer
A cell holds several phone numbers separated by commas. 1NF needs atomic values and no repeating groups.
- ENROL(StudentID, CourseID, StudentName, Grade) has key StudentID + CourseID. Why is it not in 2NF?
- Grade depends on the whole key
- It has a composite key
- It contains a repeating group
- StudentName depends only on StudentID, part of the key
Show answer
StudentName depends only on StudentID, part of the key. That is a partial dependency.
- BOOK(ISBN, Title, PublisherID, PublisherName) has key ISBN. Which change puts it into 3NF?
- Make ISBN + Title the key
- Move PublisherName to a PUBLISHER table keyed on PublisherID, keeping PublisherID in BOOK as a foreign key
- Move Title into a separate table
- Delete PublisherID
Show answer
Move PublisherName to a PUBLISHER table keyed on PublisherID, keeping PublisherID in BOOK as a foreign key. PublisherName depends on PublisherID, a non-key attribute: a transitive dependency.
- A table in 1NF has a single-attribute primary key. What can you say about 2NF?
- It can never be in 2NF
- It must be split into two tables
- It is already in 2NF, because partial dependency needs a composite key
- It is automatically in 3NF
Show answer
It is already in 2NF, because partial dependency needs a composite key. Partial dependency can only exist with a composite key.
- The gym example in the worked example ends with how many tables in 3NF?
Show answer
4. MEMBER, ATTENDANCE, CLASS and INSTRUCTOR: 4.
- In the 3NF design CUSTOMER(CustomerID, ...) and ORDER(OrderID, OrderDate, CustomerID), what is CustomerID in ORDER?
- The primary key of ORDER
- A repeating group
- A foreign key linking each order to one customer
- A partial dependency
Show answer
A foreign key linking each order to one customer. It is the primary key of CUSTOMER placed in ORDER.
- Which composite key suits ATTENDANCE(MemberID, ClassID, DateAttended) if a member can attend the same class each week?
- MemberID + ClassID + DateAttended
- MemberID alone
- ClassID alone
- MemberID + ClassID
Show answer
MemberID + ClassID + DateAttended. Without the date, repeat attendances would duplicate the key.
Sources
- Pearson Edexcel International Advanced Level in Information Technology specification (first teaching 2018), Issue 2, February 2021
- Pearson Edexcel IAL Information Technology: scheme of work for Unit 3
Last updated 8 November 2026. Spotted a mistake? Tell us.