A spreadsheet of students, tutors and courses looks fine until a tutor changes name and you have to edit forty rows. Normalisation is a step-by-step process that splits data into tables so that every fact is stored exactly once.
Normalisation organises data into tables so that each fact is stored once, removing redundancy and the inconsistencies it causes. First normal form (1NF): no repeating groups of attributes, every attribute holds a single (atomic) value, and a primary key identifies each tuple. Second normal form (2NF): in 1NF, and no partial dependencies: every non-key attribute depends on the whole of a composite primary key. Third normal form (3NF): in 2NF, and no non-key dependencies: no non-key attribute depends on another non-key attribute.
Fig. 1: each normal form removes one kind of problem
Section 01
From UNF to 1NF.
A sixth-form college records which courses each student takes. In this unnormalised (UNF) table, one record per student holds a repeating group: the course details appear several times in the same record.
UNF: STUDENTCOURSE
StudentID
StudentName
TutorID
TutorName
CourseCode
CourseTitle
Grade
S1
Priya
T1
Mr Ade
CS, MA
Computer Science, Mathematics
A, B
S2
Leo
T2
Ms Roy
CS
Computer Science
C
S3
Sam
T1
Mr Ade
MA, PH
Mathematics, Physics
A, B
1NF: STUDENTCOURSE (one row per student per course)
StudentID
StudentName
TutorID
TutorName
CourseCode
CourseTitle
Grade
S1
Priya
T1
Mr Ade
CS
Computer Science
A
S1
Priya
T1
Mr Ade
MA
Mathematics
B
S2
Leo
T2
Ms Roy
CS
Computer Science
C
S3
Sam
T1
Mr Ade
MA
Mathematics
A
S3
Sam
T1
Mr Ade
PH
Physics
B
Worked example Choosing the 1NF key
Why is StudentID on its own not a suitable primary key for the 1NF table above?
After removing the repeating group, S1 appears in two rows and S3 appears in two rows.
So StudentID no longer identifies a single tuple.
StudentID and CourseCode together are unique for every row, so they form a composite primary key (both underlined).
Answer: The primary key must be the composite StudentID + CourseCode
Examiner trap
1NF is not finished just because each cell holds one value. You must also state the new primary key, and in a table like this it is a composite key.
Section 02
Removing partial dependencies.
The lab walks an orders example through UNF, 1NF, 2NF and 3NF. Step through it, then come back and apply the same moves to the college table.
Partial dependency: a non-key attribute that depends on only part of a composite key. StudentName, TutorID and TutorName depend on StudentID alone; CourseTitle depends on CourseCode alone.
Full dependency: Grade depends on the student and the course, so it stays with the composite key.
Shortcut: a 1NF table whose primary key is a single attribute cannot have a partial dependency, so it is already in 2NF.
Worked example Remove the non-key dependency
Is STUDENT(StudentID, StudentName, TutorID, TutorName) in 3NF? If not, normalise it.
TutorName depends on TutorID, and TutorID is not the primary key of STUDENT: a non-key dependency.
Because of it, Mr Ade's name is stored once for every student he tutors, so a change of name must be made in several rows.
Move TutorName into its own table keyed on TutorID, and leave TutorID in STUDENT as a foreign key.
Answer: STUDENT(StudentID, StudentName, TutorID) and TUTOR(TutorID, TutorName)
Your turn
Write the full 3NF design for the college data.
STUDENT(StudentID, StudentName, TutorID), TUTOR(TutorID, TutorName), COURSE(CourseCode, CourseTitle), ENROLMENT(StudentID, CourseCode, Grade). Foreign keys: TutorID in STUDENT; StudentID and CourseCode in ENROLMENT
Stage 1 · Learn
Check what the sim just showed you
Four quick checks. Each correct answer earns XP and records this skill on your profile.
Quick check+10 XP
What must be true for a table to be in 1NF?
Quick check+10 XP
What is a partial dependency?
Quick check+10 XP
In the lab's 2NF step, why do Description and Price move to a Product table?
Quick check+10 XP
A table is in 1NF and its primary key is a single attribute. What can you conclude?
Section 03
Is it in 3NF?
Exam questions often give you some tables and ask you to explain why they are, or are not, in 3NF. Check the three conditions in order and name the attributes involved.
1NF check: is there a repeating group or a field holding a list? Is there a primary key?
2NF check: if the key is composite, does any non-key attribute depend on only part of it?
3NF check: does any non-key attribute depend on another non-key attribute?
Why bother: each check removes duplicated data and so prevents update, insertion and deletion problems; for example, a course could not be recorded in the 1NF table until a student enrolled on it.
Worked example A hotel table
ROOM(RoomNo, Floor, RoomType, NightlyRate). Every room of the same type has the same nightly rate. Explain whether ROOM is in 3NF.
There are no repeating groups and RoomNo is a single-attribute key, so ROOM is in 1NF and 2NF.
NightlyRate depends on RoomType, which is not the primary key: a non-key dependency.
So ROOM is not in 3NF. Fix: ROOM(RoomNo, Floor, RoomType) and ROOMTYPE(RoomType, NightlyRate).
Answer: Not in 3NF, because NightlyRate depends on the non-key attribute RoomType
Examiner trap
"It is not in 3NF because it has repeated data" earns little. Name the dependency: say which non-key attribute depends on which other attribute.
Stage 2 · Exam
Exam-style questions
Unlocks once the four checks above are done. Worth more XP, written to AS Paper 1 and Paper 2 standard.
Finish the four checks above to unlock the exam questions
Exam style+20 XP
EMPLOYEE(EmployeeID, Name, DeptID, DeptName). Why is this table not in 3NF?
Exam style+20 XP
RESULT(StudentID, TestID, Mark, TestDate). Each test is sat on one date. Which normal form rule is broken?
Exam style+20 XP
Which is the main purpose of normalisation?
Exam style+20 XP
ORDERLINE(OrderID, ProductID, Quantity) has the composite key OrderID + ProductID. Is it in 3NF?
Skill unlocked
Normalisation to 3NF, mastered.
This skill is now recorded on your profile. Keep the chain going.