A flat table that repeats facts causes three problems: update, insertion and deletion anomalies. Normalising fixes them by storing each fact once.
This lesson belongs to relational data modelling. It gives the reasons behind normalising a small data set.
What does the flat table look like?
An original bookshop records orders in one table.
| OrderID | Customer | Phone | Book | Qty |
|---|---|---|---|---|
| 101 | Aina | 012-111 | Atlas | 1 |
| 102 | Aina | 012-111 | Dictionary | 2 |
| 103 | Bala | 013-222 | Atlas | 1 |
Aina’s phone number is stored in two rows.
Worked example: the three anomalies
Update anomaly. Aina changes her phone number to 012-999. The shop edits row 101 but forgets row 102. Now the table holds two different numbers for Aina, and nobody knows which is right.
Insertion anomaly. A new customer, Chen, registers but has not ordered anything. There is no OrderID to put in the row, so the shop cannot record Chen’s details without inventing an order.
Deletion anomaly. Bala cancels order 103 and the row is deleted. Bala’s name and phone number disappear with it, although he is still a customer.
How does splitting the data fix this?
Separate the facts into two tables, linked by CustomerID.
Customer
| CustomerID | Name | Phone |
|---|---|---|
| C1 | Aina | 012-111 |
| C2 | Bala | 013-222 |
Order
| OrderID | CustomerID | Book | Qty |
|---|---|---|---|
| 101 | C1 | Atlas | 1 |
| 102 | C1 | Dictionary | 2 |
| 103 | C2 | Atlas | 1 |
Aina’s phone now lives in one row, so one edit updates it everywhere. Chen can be added to Customer without an order. Deleting order 103 leaves Bala in Customer.
The mistake that costs marks
Students write “normalising makes the database smaller” or “neater”. Those answers do not explain a problem.
| Weak answer | Strong answer |
|---|---|
| “It makes the table neater.” | “Aina’s phone number is stored in two rows, so updating one row leaves the data inconsistent. After splitting, it is stored once.” |
The fix is to name the repeated fact, show what goes wrong, and say where the fact lives after the split.
Check yourself
A table lists Student, Class and Class Teacher in each row. The teacher’s name repeats for every student in the class. Describe one update anomaly and one deletion anomaly.
Answer
Update anomaly: the class teacher changes, but only some student rows are edited, so the table shows two teachers for one class.
Deletion anomaly: the last student in a class leaves and the row is deleted, so the class teacher’s name is lost too.
What to study next
Put these ideas to work by splitting a table yourself. Continue with normalising a small data set within syllabus scope, then check your links using checking a foreign key against sample rows.
If you want a teacher to practise these explanations with you, see online one-to-one Computer Science tuition.