This mini-lesson covers AQA 4.10 — Fundamentals of databases: how to model data properly, normalise it to third normal form, query it in SQL, and keep it correct when thousands of users hit it at once.
Work through each screen, answer the questions as you go (some are recall, some are calculations you must actually work out) and collect ⭐ stars. Press Start when you're ready.
An entity is a thing you store data about (Student, Course, Order). An attribute is a property of it. A relationship links entities, and its degree matters enormously:
A many-to-many is resolved with a link table (junction table), which turns it into two one-to-many relationships:
The pay-off: the link table is also the natural home for attributes belonging to the relationship itself — a grade belongs neither to the student alone nor the course alone, but to the pairing.
Referential integrity is the rule that every foreign key value must match an existing primary key — or be null. The DBMS enforces it, so you cannot:
Why it matters: without referential integrity, a database rots quietly. Orphan records accumulate, reports silently under-count, and the errors are almost impossible to trace back. The constraint is not bureaucracy — it is the database refusing to let you lie to it.
Normalisation restructures tables to remove redundancy and the update, insert and delete anomalies it causes.
| Form | Rule | Fixes |
|---|---|---|
| 1NF | Atomic values only — no repeating groups, no lists in a cell. Every record identifiable. | "Subjects: Maths, Physics" in one cell |
| 2NF | In 1NF and every non-key attribute depends on the whole primary key, not part of it. | Partial dependency (only ever an issue with a composite key) |
| 3NF | In 2NF and no non-key attribute depends on another non-key attribute. | Transitive dependency |
The exam mantra: every non-key attribute depends on the key, the whole key, and nothing but the key.
Student(StudentID, Name, TutorID, TutorName)
TutorName depends on TutorID, which is not the key. So change one tutor's name and you must edit every row for their students — miss one and the database now holds two contradictory names for the same person.
Fix: split into Student(StudentID, Name, TutorID) and Tutor(TutorID, TutorName).
The honest trade-off: a fully normalised database needs more joins to answer a query, which costs time. Data warehouses are often deliberately denormalised for read speed — but you only break the rule once you can explain exactly why.
An INNER JOIN combines rows from two tables wherever the foreign key matches the primary key:
Aggregates work with GROUP BY: COUNT, SUM, AVG, MIN, MAX.
The most dangerous line in SQL: DELETE FROM Student; — with the WHERE clause forgotten, that deletes every row in the table. The same is true of UPDATE. Always write the WHERE clause first.
A transaction is a group of operations that must succeed or fail as one indivisible unit — a bank transfer is a debit and a credit. The ACID properties guarantee it:
| Property | Guarantee |
|---|---|
| Atomicity | All of it happens, or none of it does. A failure halfway triggers a rollback. |
| Consistency | The database moves from one valid state to another; all constraints still hold. |
| Isolation | Concurrent transactions do not interfere; the result is as if they ran one after another. |
| Durability | Once committed, the change survives a power cut or crash — it is written to non-volatile storage. |
Concurrency control: if two users update the same record at once, one update can silently overwrite the other — the lost update problem.
Tap the flaw, then tap the normal form it violates.
Tap an item on the left, then its partner on the right.
ER modelling: one-to-one · one-to-many (foreign key on the many side) · many-to-many → link table with a composite key
Keys: primary (unique, never null) · foreign (points at a primary key) · referential integrity blocks orphans
Normalisation: 1NF atomic · 2NF no partial dependency · 3NF no transitive dependency — the key, the whole key, nothing but the key
SQL: SELECT/FROM/WHERE/ORDER BY/GROUP BY · INNER JOIN on FK = PK · INSERT · UPDATE · DELETE (never forget WHERE)
Transactions: ACID: atomicity · consistency · isolation · durability
Concurrency: record locking (beware deadlock) · serialisation · timestamp ordering · commitment ordering
That is the whole of AQA 4.10. Press Finish to see your score.
You've worked through Fundamentals of databases for AQA A-level Computer Science (7517). 🎉
Your stars: 0 / 0
Next: test yourself in the Verify stage.