Key Idea: Database design means deciding what tables exist and what rules the database will enforce. Normalisation removes the redundancy that makes data go wrong. Denormalisation puts some back — deliberately, for speed.
Paper 1
- Definitions, keys, normal forms.
- Identify the dependency; state the form broken.
Paper 2
- A scenario to design or normalise.
- Construct an ERD, tables, or SQL.
Both
- Name the dependency, not just the normal form.
- Every design choice needs a justification.
⚠️ The three anomalies
These are the reason normalisation exists. Learn all three by name.
| Anomaly | What happens |
|---|---|
| Update | A repeated fact changed in one row and missed in another leaves two conflicting values |
| Insertion | A fact cannot be recorded because the row it would need does not exist yet |
| Deletion | Removing a row destroys information that had nothing to do with it |
📐 The normal forms
| Form | Requires | The fault it removes |
|---|---|---|
| 1NF | Every field holds a single atomic value | Repeating groups and lists in a field |
| 2NF | In 1NF, and no field depends on part of a composite key | Partial dependency |
| 3NF | In 2NF, and no field depends on another non-key field | Transitive dependency |
Every field depends on the key, the whole key, and nothing but the key. A partial dependency only exists when the key is composite. A transitive dependency is a field describing another field rather than the key.
Important: Saying 'it breaks 2NF' earns one mark. Saying 'ProductName depends on ProductID, which is only part of the key — a partial dependency' earns the rest.
🔒 Constraints and indexes
Why constraints belong in the schema
- Several programs, scripts and people write to the same data
- A check in one program is not a check at all — one omission lets bad data in permanently
- A schema constraint applies to every write, including a direct edit years later
It holds a column's values sorted with pointers, turning a full scan into a direct lookup. But it must be updated on every write, and it costs storage — so a table with six indexes pays that price six times on every insert.
⚖️ Denormalisation
| Normalised | Denormalised | |
|---|---|---|
| Each fact stored | Once | More than once, deliberately |
| Updating | One row | Every copy — or they disagree |
| Reading | Needs joins | No join |
| Suits | Many concurrent writers | Read-heavy reporting |
📝 Exam-style questions
A table Booking(BookingID, RoomID, RoomType, RoomRate, GuestName) has BookingID as its primary key. Identify the normal form it breaks, naming the dependency, and give the corrected tables.
Model answer plan
See the mark-by-mark plan — for / against / judgement, with marking guidance — in study mode.
A reporting database is rebuilt from scratch every night and read continuously all day. Suggest whether it should be denormalised, justifying your answer.
Model answer plan
See the mark-by-mark plan — for / against / judgement, with marking guidance — in study mode.
✅ Quick check
Cover the answers.
A table has a single-field primary key. Can it break 2NF? No. A partial dependency needs part of a composite key, so 2NF is automatic.
What is a transitive dependency? A non-key field depending on another non-key field — e.g. RoomRate depending on RoomID.
Name the three anomalies. Update, insertion and deletion.
What does adding an index cost? Storage, plus extra work on every insert, update and delete — it must be kept in step.
Why do constraints belong in the schema rather than the application? Several programs write to the same data; a check in one of them is not a check at all.
Exam tips
- Name the dependency, not just the normal form it breaks.
- A partial dependency requires a composite key — check the key before claiming one.
- A transitive dependency is a field describing another FIELD rather than the key.
- Always leave a foreign key behind when you move fields to a new table.
- Denormalisation is justified by a read-heavy workload with few writers — say so explicitly.
- Indexes speed reads and slow writes. Both halves earn marks.