Key Idea: A relational database stores data in linked tables so that every fact is held once. The database itself then enforces the rules, rather than trusting every program that writes to it to remember them.
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 vocabulary
| Term | Meaning |
|---|---|
| Record | One row — all the data about one entity |
| Field | One column — the same attribute across every record |
| Primary key | Uniquely identifies each record; never duplicated, never empty |
| Foreign key | Holds the primary key value of a record in another table |
| Composite key | A primary key made of two or more fields together |
Names are not unique — two students called Anna Novak will enrol eventually. Names also change, and a changing key forces every record referring to it to be updated too. A generated identifier is unique, stable and short to compare.
🔗 Relationships
| Cardinality | How it is implemented |
|---|---|
| One-to-one | Often a sign the two tables should be combined |
| One-to-many | Foreign key goes in the table on the MANY side |
| Many-to-many | Needs a third link table, one row per pairing, composite key |
Important: A foreign key column holds one value. A Student row could record only one subject, and a Subject row only one student — neither side can hold many. Repeating columns fixes an arbitrary limit. The link table turns one many-to-many into two one-to-many relationships.
Referential integrity
- A foreign key value must already exist as a primary key in the referenced table
- A referenced record cannot be deleted while rows still point at it
- Without it, deleting a book leaves loan records pointing at nothing
- Queries then silently omit those rows — the data is wrong with no error reported
📝 Exam-style questions
A driving school records instructors, learners, and the lessons between them. Each lesson has one instructor, one learner, a date and a duration. Construct the tables needed, naming the keys.
🔒 Model answer plan
See the mark-by-mark plan — for / against / judgement, with marking guidance — in study mode.
A library deletes a member who still has two loans recorded. Explain what referential integrity does, and what would happen without it.
🔒 Model answer plan
See the mark-by-mark plan — for / against / judgement, with marking guidance — in study mode.
✅ Quick check
Cover the answers.
Where does the foreign key go in a one-to-many relationship? In the table on the MANY side — each of its rows relates to exactly one row on the other side.
Why not use a person's name as a primary key? Names are not unique and they change — and a changing key breaks every reference to it.
What does a link table need as its primary key? Usually the two foreign keys together — a composite key, which also prevents duplicate pairings.
Where does a loan's date belong? On the loan, not the book or the member — it is a fact about that particular borrowing.
What is an orphaned record? A row whose foreign key points at a record that no longer exists.
Exam tips
- Ask which key a field depends on — that single question places every field correctly.
- One-to-many: foreign key on the MANY side. Many-to-many: a link table.
- A fact about the pairing itself lives on the link table.
- Referential integrity is ENFORCED by the database, not remembered by the programmer.
- Say what goes wrong without a constraint — the consequence is where the marks are.