AQA A-level Computer Science (7517) · Fundamentals of databases
Mini-Lesson
Fundamentals of databases
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.
Entity relationship modelling
Primary, foreign and composite keys; referential integrity
Normalisation — 1NF, 2NF, 3NF
SQL — SELECT, INSERT, UPDATE, DELETE and JOIN
Transactions, ACID and record locking
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.
4.10 · Modelling
Entity relationship modelling
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:
One-to-one — each person has one passport. Rare; often the two entities should just be merged.
One-to-many — one tutor has many students. The commonest case: put the foreign key on the "many" side.
Many-to-many — a student takes many courses, and a course has many students. A relational database cannot store this directly.
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.
Calculate
Your turn — resolving many-to-many
1A many-to-many relationship between Student and Course is resolved with a link table. How many attributes make up the composite primary key of that link table?
attributes
Hint: The link table must uniquely identify each pairing, so its key is made from the primary key of each side.
4.10 · Keys
Keys and referential integrity
Primary key — the attribute (or set of attributes) that uniquely identifies each record. It can never be null or duplicated.
Composite key — a primary key made of two or more attributes together.
Foreign key — an attribute in one table that refers to the primary key of another. This is what joins tables.
Secondary key / index — an extra index to speed up common searches.
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:
add an order for a customer who does not exist;
delete a customer who still has orders (leaving orphan records pointing at nothing).
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.
4.10 · Normalisation
Normalisation — 1NF, 2NF, 3NF
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.
A 3NF violation you can see
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.
Quick check
Normalisation
?A table OrderLine(OrderID, ProductID, Quantity, ProductName) has the composite primary key (OrderID, ProductID). Which normal form does it violate, and why?
4.10 · SQL
SQL — querying and updating
SELECT Name, Grade
FROM Student
WHERE Grade >= 60 AND YearGroup = 13
ORDER BY Grade DESC;
INSERT INTO Student (StudentID, Name, TutorID)
VALUES (105, 'Ada Lovelace', 3);
UPDATE Student
SET TutorID = 4
WHERE StudentID = 105;
DELETE FROM Student
WHERE StudentID = 105;
An INNER JOIN combines rows from two tables wherever the foreign key matches the primary key:
SELECT Student.Name, Tutor.TutorName
FROM Student
INNER JOIN Tutor ON Student.TutorID = Tutor.TutorID
WHERE Tutor.TutorName = 'Hopper';
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.
Calculate
Your turn — count the query results
2The Orders table holds these Total values: 20, 55, 60, 45, 100, 50.
SELECT COUNT(*) FROM Orders WHERE Total > 50;
What does the query return?
Hint: Strictly greater than 50 — so 50 itself does not qualify.
SELECT Student.Name
FROM Student
INNER JOIN Tutor ON Student.TutorID = Tutor.TutorID
WHERE Tutor.TutorName = 'Hopper';
How many rows does this return?
rows
Hint: Which tutor ID belongs to Hopper? Now count the students carrying that foreign key.
4.10 · Transactions
Transactions, ACID and concurrency
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.
Record locking — lock the record while it is being updated. Risk: deadlock, where A holds a lock B wants and B holds a lock A wants, and both wait forever.
Serialisation — force transactions to run one at a time on the contested data.
Timestamp ordering — each transaction is stamped; conflicts are resolved by age, and an offending transaction is rolled back and restarted.
Commitment ordering — commits are ordered to preserve serialisability without deadlock.
Quick check
ACID
?Halfway through a bank transfer, the server loses power: £100 has been debited from account A but not yet credited to account B. Which ACID property ensures the £100 does not simply vanish?
Quick check
Referential integrity
?A DBMS refuses to delete a customer record because that customer still has orders in the Order table. What is being enforced?
Sort it
Which normal form is broken?
Tap the flaw, then tap the normal form it violates.
1️⃣ Breaks 1NF
2️⃣ Breaks 2NF
3️⃣ Breaks 3NF
Match it
Match the SQL statement to its job
Tap an item on the left, then its partner on the right.
Job
SQL
Recap
The big ideas to know
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)