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.