The big idea: Each entity in the ERD becomes a table. Each relationship becomes a foreign key.
That is the whole translation — the diagram tells you what to build.
The one rule to get right: In a one-to-many relationship, the foreign key goes in the many table.
Put it on the one side and that row would need an unknown number of columns to hold all the values.
Free preview
This is the free notes preview
You're reading the free notes. Aimnova Pro unlocks the full study experience — and you can try it with your first topic free to keep:
- FlashcardsLock in vocabulary and key terms with spaced repetition.
- Practice questionsAnswer exam-style questions and get instant AI marking.
- Mock exams & past-paper vaultSit full mocks and see exactly how examiners award marks.
- Personalised study planA daily plan built around your exam date and weak areas.
Primary key
- Identifies each row uniquely in its own table
- Must never be empty and never repeat
- Often a plain ID the database generates itself
- Every table should have one
Foreign key
- Holds a primary key value from another table
- This is how a relationship is physically built
- The database refuses a value with no matching row
- Goes in the many table of a one-to-many
Composite and concatenated
- Composite key — two or more columns used together as the primary key
- Used when no single column is unique, as in a linking table
- Concatenated key — a single column made by joining values, such as 2026-S1
- A composite keeps the parts separate; a concatenated one glues them together
Step to 3NF: PK and FK markers show exactly where each kind of key ends up.
Interactive diagram
Explore the labelled diagram, charts and maps for this topic in full study mode.
Composite is usually the right one: A concatenated key looks tidy but hides the parts: you cannot filter on the year without pulling the string apart.
Keep the columns separate and make them the primary key together.
See how examiners mark answers
Access past paper questions with model answers. Learn exactly what earns marks and what doesn't.
What the definitions enforce
- Entity integrity — a primary key can never be empty or duplicated
- Referential integrity — a foreign key must match an existing row
- Domain integrity — a value must fit the column's data type
- The database refuses bad data instead of storing it and hoping
What happens without them
- Two rows for the same student, and no way to tell which is current
- A membership pointing at a club that was deleted last term
- A date stored as text, sorting in the wrong order
- Every program reading the data must re-check it all itself
The argument in one sentence: Rules enforced by the database apply to every program that touches it, including ones written years later.
Rules enforced in a program apply only to that program.
How this is tested — you must place each key in the right table and say what it enforces. It comes up two ways:
Paper 1 Section A
- Construct tables from an ERD or scenario, 4-6 marks
- Identify the primary and foreign keys
- Explain how keys maintain data integrity
Paper 1 Section B — case study
- Build the tables for the case study's data
- Explain what a given key prevents
The classic trap: Putting the foreign key on the one side of a one-to-many. If a teacher takes five classes, a TeacherID row cannot hold five ClassIDs — the key belongs in the Class table.
A library lends books to members. Construct the tables needed, stating the primary and foreign keys for each.
Model answer plan
See the mark-by-mark plan — for / against / judgement, with marking guidance — in study mode.