Normalising splits a table so each fact is stored once. Work through the problems in order: repeating groups, then partial dependencies, then dependencies between non-key columns.
This lesson opens database development. The reasons behind it are in explaining update anomalies.
Worked example: a sports day register
The starting table is original.
| StudentID | StudentName | House | HouseTeacher | Event1 | Event2 |
|---|---|---|---|---|---|
| S01 | Aina | Merah | Puan Lim | 100m | Relay |
| S02 | Bala | Biru | Encik Raj | Relay | |
| S03 | Chen | Merah | Puan Lim | 200m | 100m |
Step 1: remove repeating groups. Event1 and Event2 repeat the same kind of fact. Move events into their own table, one row per student and event.
Entry
| StudentID | Event |
|---|---|
| S01 | 100m |
| S01 | Relay |
| S02 | Relay |
| S03 | 200m |
| S03 | 100m |
The key of Entry is the pair (StudentID, Event).
Step 2: check for partial dependencies. Student has a single-column key, StudentID, so no column depends on only part of the key.
Step 3: remove dependencies between non-key columns. HouseTeacher depends on House, not on StudentID. Move it out.
Student
| StudentID | StudentName | House |
|---|---|---|
| S01 | Aina | Merah |
| S02 | Bala | Biru |
| S03 | Chen | Merah |
House
| House | HouseTeacher |
|---|---|
| Merah | Puan Lim |
| Biru | Encik Raj |
Now Puan Lim is stored once. Student.House is a foreign key to House.
The mistake that costs marks
Some students split a table by copying columns into new tables without a key linking them. The pieces cannot be joined back.
| Split | Can you rebuild the original? |
|---|---|
| Student, House and Entry with StudentID and House as keys | yes |
| a table of names, a table of houses, no link between them | no, you cannot tell whose house is whose |
The fix is to keep a foreign key in every child table, then test the rebuild.
Check yourself
A table has OrderID, Customer, CustomerPhone and Item1, Item2. List the two problems and the new tables.
Answer
Problem 1: Item1 and Item2 are a repeating group, so move items to OrderItem (OrderID, Item). Problem 2: CustomerPhone depends on Customer, not on OrderID, so move it to Customer (Customer, CustomerPhone). Order keeps OrderID and Customer as a foreign key.
What to study next
Split tables must be joined again to answer questions. Continue with joining related tables, then try the database development practice set.
If you want a teacher to go through your normalising steps, see online one-to-one Computer Science tuition.