A3.2.2 · Database design
Entity-relationship diagrams
By the end you can
Construct an ERD with entities, attributes, keys, and one-to-one, one-to-many and many-to-many relationships.
An is the standard way to draw a schema. It shows the entities (as boxes), their attributes (listed inside) and the relationships between them (as lines). Several drawing styles exist (Chen with diamonds, Barker, and the crow's foot used here); they all show the same facts, so learn one well and be able to read the others.
How to read the lines#
Each end of a relationship line says two things about the entity at that end. The is the maximum number: a single bar | means one, the three-pronged foot means many. The is the minimum: a second bar means at least one (the relationship is mandatory) and a small circle ○ means zero is allowed (optional). A relationship is read in one direction at a time: a menu has many menu items, but a menu item only “belongs to” a menu if the diagram says so.
Constructing an ERD#
- Decide the level (conceptual, logical or physical): it sets how much detail to show.
- Find the entities: the nouns that the database needs to keep data about.
- Add the attributes of each (at logical level and below), choosing a primary key for each entity.
- Find the relationships: the verbs that join two entities (a customer places an order).
- Decide each relationship's cardinality (one-to-one, one-to-many, many-to-many) and modality (mandatory or optional).
- Replace every many-to-many relationship with a linking entity, and add the foreign keys.
Drawing the model first shows mistakes while they are still cheap to fix, and gives everyone a shared picture of the system.
One mother and her children (each child has one mother in this database).
Actors and films: actors appear in many films and films have many actors.
A country and its capital city.
A school and its students.
Customers and the products they buy.
0 of 5 matched
Check yourself
ERDs and cardinality
Quick questions, made fresh every time and marked here. Do a few until they feel easy.
Exam practice
Paper 1-style questions
5 questions · 19 marks
Write your answer as you would in the exam, then show the mark points and tick each one your answer made. These are our own questions in the style of the exam, not past-paper questions.
1.Identify the three main things an entity-relationship diagram shows.
[3]Identify: Pick out the answer from the options or the context.
2.Distinguish between cardinality and modality in a relationship.
[3]Distinguish: Make the differences between two things clear.
3.Explain how a many-to-many relationship between students and clubs is stored in a relational database.
[3]Explain: Give reasons: say what happens AND why, or how one thing causes another.
4.A library has members who borrow books. A member can borrow many books, and each book can be borrowed by many members over time. Construct an entity-relationship diagram (describe the entities, key attributes and relationships).
[4]Construct: Draw or write it out (a table, a diagram, a program).
5.Explain how entity-relationship diagrams are used to model a database.
[6]Explain: Give reasons: say what happens AND why, or how one thing causes another.
Past papers
Old exam questions on this topic
From HL Paper 1, May 2014 to November 2025. These papers were set on the previous syllabus, so the wording may differ from today's exam, but the topic is the same. Find the questions and markschemes in your school's past-paper pack.
| Paper | Question | Marks | About |
|---|---|---|---|
| November 2024 | 4(e) | 5 | Paper 2 Option A: ERDs and relationships |
| May 2023 | 1(a)(i) | 1 | Paper 2 Option A: ERDs and relationships |
| November 2020 | 1(a) | 2 | Paper 2 Option A: ERDs and relationships |
| May 2018 | 1(a) | 2 | Paper 2 Option A: ERDs and relationships |
| May 2017 | 2(b) | 4 | Paper 2 Option A: ERDs and relationships |
| May 2014 | 3(a) | 3 | Paper 2 Option A: ERDs and relationships |
In a nutshell
- An ERD draws a schema: entities as boxes, attributes inside them, relationships as lines, with the primary key marked and foreign keys starred or labelled.
- Cardinality is the maximum on each side of a relationship: one-to-one, one-to-many, many-to-many. Modality is the minimum: mandatory (at least one) or optional (zero allowed).
- Crow’s foot notation: a single bar means one, a three-pronged foot means many, a circle means zero (optional). Other styles (Chen, Barker) show the same facts.
- A many-to-many relationship needs a linking table holding both foreign keys, which makes two one-to-many relationships.
- To construct one: choose the level, find the entities, add attributes and keys, name the relationships with verbs, then add cardinality and modality.