Ms Khan changes her name
A school shop keeps one big table. Every order row has the customer's form tutor, Ms Khan, typed in again and again. There are 300 rows with her name.
Then Ms Khan changes her surname. Somebody changes 299 rows. One row is missed. Now the data says two different things.
Normalisation means organising tables so each fact is stored once. It fixes this problem.
Key words
| Word | What it means | Example |
|---|---|---|
| Form | A class group in a UK school. A tutor is the teacher who looks after one form. | 12B |
| Normalisation | Organising tables so each fact is stored once. | |
| Database | An organised store of data that programs can search and change. | |
| Primary key | A field that is different in every row. It finds one row. | |
| Foreign key | A field that holds the primary key of a row in another table. | |
| Redundancy | The same fact stored more than once. | |
| Repeating group | Several values put in one cell. | Hoodie, Cap |
| First normal form (1NF) | One value in every cell and no repeating groups. | |
| Second normal form (2NF) | In 1NF, and every field depends on the whole key. | |
| Third normal form (3NF) | In 2NF, and no field depends on another field that is not the key. |
The cells students fill in are left blank in this preview.
The trouble with one big table
When the same fact is stored many times, three bad things happen.
You waste space. You must change many rows for one change, and you can miss one. And you cannot store a fact on its own. You cannot add a new form tutor until a student orders something.
Normalisation fixes this in three steps.
Quick check: the problem
Have a go
Quick check: the problem
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Start: the unnormalised table
| Order | Customer | Form | Tutor | Items |
|---|---|---|---|---|
| 1 | Aisha | 12B | Ms Khan | Hoodie 25 pounds (quantity 1), Cap 8 pounds (quantity 2) |
| 2 | Ben | 10A | Mr Lee | Cap 8 pounds (quantity 1) |
| 3 | Sam | 12B | Ms Khan | Hoodie 25 pounds (quantity 1) |
The cells students fill in are left blank in this preview.
Step 1: first normal form
Look at the Items column. Order 1 has two items in one cell. This is a repeating group.
To reach 1NF, give each item its own row. Each cell holds one value. The key is now two fields together: Order and Item.
Result: 1NF
| Order | Item | Price | Quantity | Customer | Form | Tutor |
|---|---|---|---|---|---|---|
| 1 | Hoodie | 25 | 1 | Aisha | 12B | Ms Khan |
| 1 | Cap | 8 | 2 | Aisha | 12B | Ms Khan |
| 2 | Cap | 8 | 1 | Ben | 10A | Mr Lee |
| 3 | Hoodie | 25 | 1 | Sam | 12B | Ms Khan |
The cells students fill in are left blank in this preview.
Quick check: first normal form
Have a go
Quick check: first normal form
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.