HookThe weekend TSB showed you the wrong account
Over the weekend of 20–22 April 2018, TSB Bank tried to move around 1.3 billion customer records off the platform it had been renting from its former owner, Lloyds Banking Group, and onto a brand-new core banking system built by its Spanish parent, Banco Sabadell. The migration was meant to take a weekend. Instead it locked roughly 1.9 million customers out of online and mobile banking for days, and — most alarmingly — some customers reported logging in to find other people's accounts and balances on their screens. The clean-up cost TSB about £330 million, the regulators opened investigations, and the chief executive, Paul Pester, was gone by September. Not one line of arithmetic had failed.
What failed was the thing this entire topic is about: modelling data correctly, keeping it consistent, and controlling who touches which record and when. A database is not a spreadsheet with delusions of grandeur — it is a carefully designed structure in which every fact is stored exactly once, related facts are linked rather than copied, and thousands of users can read and write at the same time without corrupting each other's work. This section walks the whole pipeline: how you model a problem as entities and relationships, how those become relational tables with keys, how normalisation removes the redundancy that lets data drift out of step, how SQL asks questions of the result, and how a client-server architecture stops two users overwriting each other the way TSB's weekend went wrong.
ModelConceptual models and entity-relationship modelling
Before a single table is created, you build a conceptual data model — a description of the real-world things the system stores and how they connect, independent of any particular database software. Its building blocks are entities (the things you keep data about, such as Student, Course, Book), attributes (the properties of each entity, such as a student's name and date of birth) and relationships (how entities are associated, such as a student being enrolled on a course). By convention an entity is written with its attributes in brackets and its primary key underlined, for example Student(StudentID, Surname, Forename, TutorGroup).
The crucial property of a relationship is its degree, of which there are three. A one-to-one (1:1) relationship links exactly one of each — a country and its capital city. A one-to-many (1:M) relationship links one entity to many of another — one tutor group contains many students, but each student is in only one group. A many-to-many (M:N) relationship links many to many — a student takes many courses and a course is taken by many students. An entity-relationship (ER) diagram draws entities as boxes and relationships as lines, with the 'many' end marked by a crow's foot. The exam wants you to read a scenario, identify the entities, and state the degree of each relationship precisely — because the degree decides how the tables are eventually built.
A library scenario: a member can borrow many books, and over time a book can be borrowed by many members. Entities are Member and Book, and the relationship between them is many-to-many. A relational database cannot store a many-to-many relationship directly, so the model resolves it by inventing a third link entity — a Loan — that sits between the two. The relationship becomes one Member has many Loans and one Book has many Loans: two one-to-many relationships replacing the single many-to-many. The Loan entity is written Loan(LoanID, MemberID, BookID, DateBorrowed), where MemberID and BookID are foreign keys pointing back to the two originals. Spotting a many-to-many and resolving it with a link table is one of the most reliably examined moves in the whole topic.
ModelRelational databases: relations, keys and integrity
A relational database stores data in relations — tables — where each row is a tuple (a record) and each column is an attribute (a field). Every table needs a primary key: an attribute, or a combination of attributes, whose value is unique for every tuple, so that any single record can be found unambiguously. Where no single attribute is unique, a composite key made of two or more attributes does the job — in a table of exam entries, neither StudentID nor ExamCode is unique on its own, but the pair (StudentID, ExamCode) is.
Tables are linked by a foreign key: an attribute in one table that is the primary key of another. In Entry(StudentID, ExamCode, Mark), StudentID is a foreign key referencing the Student table and ExamCode is a foreign key referencing the Exam table. This is what stops data being copied around: a student's name lives in exactly one place, and everything else points to it. The rule that keeps the pointers honest is referential integrity — a foreign key must either be null or match a primary key that actually exists, so you cannot record an exam entry for a student who is not in the database, and you cannot delete a student while entries still reference them. TSB's nightmare of one customer seeing another's account is exactly what a database built on sound keys and enforced integrity is designed to make impossible.
ModelNormalisation: one fact, one place
Normalisation is the process of organising attributes into tables so that data is stored without unnecessary duplication. Redundancy is not just wasteful — it is dangerous, because the same fact stored twice can be updated in one place and not the other, leaving the database inconsistent. The A-level requires three progressive stages. First normal form (1NF): every attribute holds a single atomic value and there are no repeating groups, so no cell contains a list and no table has columns like Course1, Course2, Course3. Second normal form (2NF): the table is in 1NF and every non-key attribute depends on the whole primary key, not just part of a composite key (removing partial dependencies). Third normal form (3NF): the table is in 2NF and no non-key attribute depends on another non-key attribute (removing transitive dependencies).
The examiner's summary, worth memorising, is that in 3NF every non-key attribute depends on the key, the whole key, and nothing but the key. Normalisation is where conceptual modelling and relational theory meet in a mechanical procedure, and it is the single most common extended-answer question on this section, so you must be able to take a flat table and decompose it stage by stage, naming the dependency you are removing at each step.
Start with one flat table of exam entries, keyed on the composite (StudentID, ExamCode): Entry(StudentID, StudentName, TutorGroup, TutorName, ExamCode, ExamTitle, Mark). Assume every cell holds a single value, so it is already in 1NF.
Move to 2NF by removing partial dependencies. StudentName, TutorGroup and TutorName depend only on StudentID — half of the key — so they move to their own table. ExamTitle depends only on ExamCode — the other half — so it moves too. This leaves three tables: Student(StudentID, StudentName, TutorGroup, TutorName), Exam(ExamCode, ExamTitle), and Entry(StudentID, ExamCode, Mark), where Mark genuinely depends on the whole key because a mark belongs to a particular student in a particular exam.
Move to 3NF by removing the transitive dependency hiding in Student: TutorName depends on TutorGroup, which depends on StudentID — a non-key attribute depending on another non-key attribute. Split it out: Student(StudentID, StudentName, TutorGroup) and Tutor(TutorGroup, TutorName). The final design is four tables, each fact stored once. Change a tutor's name now and you change it in exactly one row — the redundancy that lets a database contradict itself is gone.
MechanismSQL: asking the database questions
Structured Query Language (SQL) is the standard language for defining and manipulating relational data. Retrieval uses the SELECT ... FROM ... WHERE pattern: SELECT lists the columns you want, FROM names the table(s), and WHERE filters the rows by a condition. ORDER BY sorts the output (ascending by default, or DESC for descending), and LIKE with the wildcard % matches text patterns, so WHERE Surname LIKE 'Mc%' finds every surname starting 'Mc'. When the answer spans two tables you join them: an INNER JOIN ... ON matches a foreign key in one table to the primary key in another, stitching the normalised tables back together for the query.
SQL also changes data. INSERT INTO adds a new record, UPDATE ... SET ... WHERE edits existing records, and DELETE FROM ... WHERE removes them — and the WHERE clause on an UPDATE or DELETE is safety-critical, because omitting it changes or wipes every row in the table. Structure is created with CREATE TABLE, which lists each attribute, its data type, and its keys. Because normalisation deliberately scatters data across many small tables, the JOIN is the everyday tool that reassembles it, and being fluent in reading and writing a two-table join is what separates a secure SQL answer from a shaky one.
Create a table, add a record, then interrogate it. CREATE TABLE Student (StudentID INTEGER PRIMARY KEY, StudentName VARCHAR(40), TutorGroup VARCHAR(6)); defines the structure. INSERT INTO Student VALUES (1024, 'Priya Shah', '13A'); adds a row.
Now list the name and mark of every student who scored at least 70 in the exam coded CS01, best marks first, pulling the name from one table and the mark from another: SELECT Student.StudentName, Entry.Mark FROM Student INNER JOIN Entry ON Student.StudentID = Entry.StudentID WHERE Entry.ExamCode = 'CS01' AND Entry.Mark >= 70 ORDER BY Entry.Mark DESC;. The JOIN matches each entry's StudentID to the student it belongs to, WHERE keeps only the CS01 rows scoring 70 or more, and ORDER BY DESC sorts the survivors highest first. To correct a single mark you would write UPDATE Entry SET Mark = 68 WHERE StudentID = 1024 AND ExamCode = 'CS01'; — and note how the composite key drives the WHERE clause so exactly one record changes and no other.
CaseClient-server databases and concurrent access
A real database is not used by one person at a time. In the client-server model the data lives on a central server and many client machines connect to it over a network, sending requests and receiving results. That raises the problem TSB's weekend embodied: concurrent access, where two clients try to change the same record at once. The classic failure is the lost update. Suppose an account holds £500 and two withdrawals happen simultaneously: client A reads £500 and prepares to take £200; client B also reads £500 and prepares to take £300. A writes back £300; B, still working from the £500 it read, writes back £200. Two withdrawals totalling £500 have left a balance of £200 — one update has been silently lost, and £300 has vanished from the bank's books.
The defences are on the specification by name. Record locking stops the second client accessing a record while the first is changing it — the record is locked on read and released on write — though careless locking can produce deadlock, where two transactions each hold a lock the other is waiting for and neither can proceed. Serialisation forces transactions to run one after another as if in a queue, guaranteeing the same result as some sequential order. Timestamp ordering stamps each transaction with the time it began and uses those stamps to decide precedence, aborting and restarting a transaction that arrives out of order. Commitment ordering controls the order in which transactions are finalised so their effects remain consistent. Every one of these mechanisms exists to guarantee that concurrent users leave the database in a state it could also have reached by running their transactions one at a time — which is exactly the guarantee that failed when TSB's customers saw accounts that were not their own.
VocabularyKey terms the mark scheme pays for
TrapsMisconceptions that cost marks
ExamWhat examiners want
Databases is assessed on the written Paper 2 (7517/2), and it rewards precise, technical answers over hand-waving. When a scenario question asks you to identify entities and relationships, state each relationship's degree explicitly (one-to-many, many-to-many) and, if you spot a many-to-many, immediately resolve it with a link table and name the two foreign keys — that resolution is almost always a mark in itself. Write entities in the standard notation with the primary key underlined so the examiner can see you know which attribute identifies the tuple.
Normalisation questions are won by showing the stages. Do not jump to the finished tables; move from unnormalised to 1NF to 2NF to 3NF and name the dependency you are removing at each step — 'TutorName depends on TutorGroup, a transitive dependency, so it moves to its own table'. A correct final design with no reasoning is a fragile answer. In SQL questions, get the clause order exactly right (SELECT, FROM, WHERE, ORDER BY), use INNER JOIN ... ON when the data spans two tables, and never write an UPDATE or DELETE without a WHERE clause. For client-server and concurrency questions, describe the lost update with a concrete before-and-after value, then name the specific control the scenario needs — record locking, serialisation, timestamp ordering or commitment ordering — and explain that all of them exist to make concurrent transactions equivalent to running them one at a time. Matching the named mechanism to the problem, rather than listing all four, is what earns the higher marks.