A3.2.5 · Database design
Normal forms
By the end you can
Explain 1NF, 2NF and 3NF, the dependencies behind them and the anomalies each removes.
A badly designed table repeats facts, and repeated facts cause the update, insert and delete anomalies of A3.1.1. is the process of reorganising tables, step by step, so that each fact is stored once. There are three stages to know, each one building on the last: first, second and third normal form. The motto for all three is that every attribute should depend on the key, the whole key and nothing but the key.
Dependencies: who decides what#
The stages are defined using : attribute A determines attribute B when each value of A goes with exactly one value of B. A student ID determines the student's name; a course ID determines the course title. Three kinds matter:
- Full dependency: B depends on the whole key. (A grade depends on student and course together.)
- Partial dependency: B depends on only part of a composite key. (A student's name depends on student ID alone.)
- Transitive dependency: a non-key attribute depends on another non-key attribute. (A coach's room depends on the coach, who is determined by the course.)
The three normal forms#
- First normal form (1NF). Every cell holds a single, value (no lists such as “Chess, Art” in one cell), there are no repeating groups of columns, and every row can be identified by a primary key. To get there, split a multi-valued cell into rows, and split a name into first and last if you need them separately. Move repeating data into a table of its own with a foreign key back.
- Second normal form (2NF). The table is in 1NF and has no partial dependencies: every non-key attribute depends on the whole of the key. This only matters when the key is composite. Fix it by moving attributes that depend on part of the key into a new table, keyed by that part.
- Third normal form (3NF). The table is in 2NF and has no transitive dependencies: no non-key attribute depends on another non-key attribute. Fix it by moving them to a new table keyed by the attribute they depend on.
A related idea sometimes mentioned is a multi-valued dependency: two independent attributes (a car model's colours and its production years) that are both linked to the same key. Mixing them in one table forces a row for every combination, so they belong in separate tables.
First normal form
Every cell holds one value and each row is identified by a key. Here the key is StudentID and CourseID together. Everything is still in one table.
REGISTER
| StudentIDPK | StudentName | CourseIDPK | CourseTitle | CoachID | CoachName | CoachRoom | Grade |
|---|---|---|---|---|---|---|---|
| 101 | Ada Reyes | C1 | Robotics | K1 | Ms Dale | Lab 4 | A |
| 101 | Ada Reyes | C2 | Chess | K2 | Mr Ito | Hall 7 | B |
| 101 | Ada Reyes | C3 | Coding | K1 | Ms Dale | Lab 4 | B |
| 102 | Ben Cole | C1 | Robotics | K1 | Ms Dale | Lab 4 | B |
| 102 | Ben Cole | C3 | Coding | K1 | Ms Dale | Lab 4 | A |
| 102 | Ben Cole | C4 | Pottery | K3 | Ms Ford | Studio 2 | B |
| 103 | Cara Singh | C2 | Chess | K2 | Mr Ito | Hall 7 | A |
| 103 | Cara Singh | C3 | Coding | K1 | Ms Dale | Lab 4 | C |
Tables
1
Cells stored
64
Join back = original?
yes
What the data says (dependencies found in the rows)
- StudentID determines StudentName partial (breaks 2NF)
- CourseID determines CourseTitle partial (breaks 2NF)
- CourseID determines CoachID partial (breaks 2NF)
- CourseID determines CoachName partial (breaks 2NF)
- CoachID determines CoachName transitive (breaks 3NF)
- CourseID determines CoachRoom partial (breaks 2NF)
- CoachID determines CoachRoom transitive (breaks 3NF)
- CoachName determines CoachRoom transitive (breaks 3NF)
- StudentID + CourseID together determine the rest of the row (for example Grade) full: fine
Why the single table is a problem
- Update: Ms Dale moves to a new room, so 5 rows must all be changed. Miss one and the data disagrees with itself.
- Insert: a new course cannot be recorded until a student joins it, because StudentID is half of the key.
- Delete: when Ben’s only Pottery enrolment is deleted, the database also loses the Pottery course and its coach, Ms Ford.
In 3NF each fact is stored once, in one row of one table.
A Phones cell holds “0771 2345, 0772 9981”.
Order(OrderID, ItemID, Qty, ItemName): ItemName depends only on ItemID.
Student(StudentID, Name, TeacherID, TeacherRoom): the room depends on TeacherID.
Contact(ID, Phone1, Phone2, Phone3).
Book(BookID, Title, AuthorID, AuthorCountry): the country depends on AuthorID.
0 of 5 matched
Check yourself
Normal forms
Quick questions, made fresh every time and marked here. Do a few until they feel easy.
Exam practice
Paper 1-style questions
5 questions · 14 marks
Write your answer as you would in the exam, then show the mark points and tick each one your answer made. These are our own questions in the style of the exam, not past-paper questions.
1.Describe two reasons why a database should be normalised.
[4]Describe: A detailed account: say what happens, step by step if it is a process.
2.Describe the rules a table must meet to be in first normal form.
[3]Describe: A detailed account: say what happens, step by step if it is a process.
3.Distinguish between second and third normal form.
[3]Distinguish: Make the differences between two things clear.
4.A table Enrolment(StudentID, CourseID, StudentName, Grade) has the primary key (StudentID, CourseID). Identify the normal form it breaks and the attribute that causes it.
[2]Identify: Pick out the answer from the options or the context.
5.State the memorable rule that summarises what a table in third normal form must satisfy.
[2]State: Give a short answer, no explanation needed.
Past papers
Old exam questions on this topic
From HL Paper 1, May 2014 to November 2025. These papers were set on the previous syllabus, so the wording may differ from today's exam, but the topic is the same. Find the questions and markschemes in your school's past-paper pack.
| Paper | Question | Marks | About |
|---|---|---|---|
| May 2025 TZ2 | 3(c) | 6 | Paper 2 Option A: normalisation (same in TZ3) |
| November 2024 | 2(d) | 6 | Paper 2 Option A: normalisation |
| May 2023 | 3(b) | 8 | Paper 2 Option A: normalisation |
| November 2023 | 3(c) | 8 | Paper 2 Option A: normalisation |
| May 2019 | 3(c) | 8 | Paper 2 Option A: normalisation |
| May 2018 | 3(d) | 8 | Paper 2 Option A: normalisation |
| November 2018 | 3(d) | 8 | Paper 2 Option A: normalisation |
| November 2015 | 2(d) | 7 | Paper 2 Option A: normalisation |
In a nutshell
- Normalisation organises tables to remove redundancy and the insert, update and delete anomalies it causes.
- 1NF: every cell is atomic, there are no repeating groups, and each row has a key. 2NF: 1NF and no non-key attribute depends on only part of a composite key.
- 3NF: 2NF and no non-key attribute depends on another non-key attribute (no transitive dependency).
- Remember: every attribute depends on the key, the whole key and nothing but the key.
- To fix a violation, move the dependent attributes into their own table with the determining attribute as its primary key, and keep that attribute in the old table as a foreign key.