DDD3 — ER diagrams: cardinality and entity-occurrence diagrams
- I can describe the three types of cardinality: one-to-one, one-to-many, and many-to-many.
- I can determine the cardinality of a relationship by examining real sample data.
- I can draw an entity-occurrence diagram to justify a cardinality decision.
- I can explain, in my own words, what each of the three cardinality types means.
- I can draw an entity-occurrence diagram showing individual instances and the lines connecting them.
- I can explain why a many-to-many relationship cannot be implemented directly in a relational database, and how a resolving entity fixes this.
Answer before the lesson begins — it's fine if you're unsure.
Key vocabulary
Determining and proving cardinality
What is cardinality?
Cardinality describes how many records in one entity can be linked to a record in another entity. DDD2 showed you how to draw cardinality using crow's-foot notation, once you were told which side was "many". This lesson teaches how to work that out in the first place, from real data, and how to prove it using an entity-occurrence diagram — one of the terms SQA explicitly names in the Higher specification alongside the ER diagram itself.
Entity-occurrence diagrams — proving cardinality with real data
An entity-occurrence diagram shows two entities as tall ovals, each containing a dot for every individual record ("occurrence") being considered — not the whole entity, just a handful of real or realistic examples. Lines are drawn connecting related occurrences. Counting how many lines leave each dot tells you the cardinality directly: if every dot on both sides has exactly one line, the relationship is one-to-one; if dots on one side have several lines but dots on the other side only ever have one, it's one-to-many; if dots on both sides can have several lines, it's many-to-many.
One-to-one relationships
In a one-to-one relationship, each occurrence in one entity is linked to exactly one occurrence in the other, with no exceptions on either side. A classic example: a School is managed by exactly one Headteacher, and a Headteacher manages exactly one School at a time. Every School dot has exactly one line out; every Headteacher dot has exactly one line in.
One-to-many relationships
In a one-to-many relationship, occurrences on one side can connect to several occurrences on the other, but not the reverse. The Instructor–Class relationship from DDD2 is exactly this: one instructor's dot can have several lines out (to each class they teach), but every class's dot has only one line in, back to a single instructor.
Many-to-many relationships — and why they can't be implemented directly
In a many-to-many relationship, occurrences on both sides can connect to several occurrences on the other. Looking at Member and Class directly (ignoring Booking for a moment): one member can book several different classes, and one class can have several different members booked onto it. Both sides have dots with multiple lines out. A relational database table can only store one value per field per record, so a direct many-to-many relationship cannot be built as a single foreign key on either side — there's nowhere to put "all the classes this member has booked" as a single field value.
Resolving Member and Class with Booking
This is exactly why Booking exists. Instead of Member linking straight to Class, Booking sits between them: each Booking record stores exactly one memberID and exactly one classCode. That turns one many-to-many relationship into two one-to-many relationships — Member makes many Bookings (each Booking has only one Member), and Class generates many Bookings (each Booking is for only one Class) — both of which a relational database can store directly, one foreign key each. This is precisely why DDD2's ER diagram never drew a direct line between Member and Class.
Worked examples
Build this entity-occurrence diagram in draw.io the same way you built DDD2's ER diagram: ovals and dots instead of entity boxes, plain connector lines instead of crow's feet. See the entity-occurrence section of the draw.io guide if you need a reminder — it's a simpler diagram than an ER diagram, no special connector library needed.
Sample data (real Craigmillar bookings): Booking BKG001 is for Member M001 and Class PIL01. Booking BKG002 is for Member M001 and Class YOG01. Booking BKG006 is for Member M002 and Class PIL01.
In draw.io, build the entity-occurrence diagram between Member and Booking, then answer:
- What is the cardinality between Member and Booking?
One-to-many. M001 has more than one booking, but every booking belongs to only one member — Member is the "one" side, Booking is the "many" side, matching the crow's foot drawn at Booking in DDD2's ER diagram.
- Guessing cardinality instead of checking the data. Cardinality must be justified by looking at whether occurrences actually have one connection or several — not assumed from the entity names alone.
- Drawing an entity-occurrence diagram with the wrong number of dots. Every distinct record mentioned in the sample data needs its own dot — missing one, or drawing a dot for a record that wasn't given, breaks the diagram's accuracy.
- Trying to implement a many-to-many relationship directly. A relational database table cannot store multiple values in a single field, so Member cannot have a "classCode" field listing several classes — a resolving entity like Booking is required.
- Mixing up which side is "many". This is genuinely counter-intuitive: the "many" side is the entity whose occurrences always have exactly one line each (there are simply many such records, each linking back to a single occurrence on the other side) — it's the "one" side whose occurrences can have more than one line out. In Example 2, Instructor dots can have several lines out, yet Instructor is the "one" side; Class dots always have exactly one line, yet Class is the "many" side. Always check both directions before deciding, since it's easy to label the side with visibly more lines as "many" when it's actually the opposite.
When asked to "exemplify" an entity-occurrence diagram, draw an actual diagram with real (or realistic) sample values as dots and lines — a written description of the cardinality alone is not the same thing and will not gain full marks. Always state the cardinality explicitly at the end (e.g. "this is a one-to-many relationship") rather than leaving the marker to infer it from the diagram.
📁 File this in OneNote under:
Higher Computing Science → Database Design & Development → DDD3
Suggested timing (trim applied, 19 Jul 2026 unit audit): 7 min warm up · 12 min notes & cardinality theory · 12 min worked examples 1–2, built live & narrated in full · 3 min worked example 3 (many-to-many), shown already-completed on screen — read and discuss the diagram rather than constructing it stroke-by-stroke · 18 min Task Set B · 3–5 min Task Set C / review, if time allows. This totals ~55–57 minutes, fitting the single period. The "now you try" draw.io build is marked optional in class — set it as homework if the above still runs long, since Task Set B's coverage doesn't depend on it.
Key misconception: pupils often check only one side of a relationship before deciding cardinality (e.g. seeing one instructor teach two classes and stopping there, without checking whether any class has two instructors). Model checking both directions explicitly, every time, using the "does any dot on this side have more than one line?" question for each entity in turn.
Live demo suggestion: before showing Example 3, ask pupils to predict the cardinality between Member and Class from memory (most will guess one-to-many by analogy with Instructor-Class) — then reveal the actual sample data and let them discover it's many-to-many themselves. This makes the "why Booking exists" payoff land harder than just being told.
SQA command words covered: "exemplify" (produce an actual diagram with real sample data — see the exam tip box), "describe", "justify" (B8's reasoning question).
Extension suggestion: pupils who finish Task Set C early could revisit DDD2's full ER diagram and annotate, for each of the three relationships, which entity-occurrence diagram (from this lesson) proves its cardinality.
⚠️ Pacing risk (calendar.md §4 / ddd/DDD.md §11) — trim applied 19 Jul 2026 unit audit: this lesson is scheduled as a single ~50-60 min period, and its content (3 distinct cardinality types + 3 full entity-occurrence diagrams) was judged double-scoped in the 13 Jul 2026 period audit. The operational trim is now the default delivery, not an optional fallback: keep all three cardinality types (they're short, and the spec wants all three distinguished), build Examples 1–2's entity-occurrence diagrams live and narrated in full, but present Example 3 (many-to-many) already completed on screen — spend the time reading and discussing why it shows many-to-many rather than constructing it dot-by-dot. This keeps the suggested timing above within the single period; all R-D6–R-D10 content coverage is retained (nothing is cut, only the live-construction time on one diagram).
draw.io now-you-try — optional in class: the "now you try" question is a draw.io build (matching DDD2's pattern; pupils were already onboarded to draw.io there, so no new setup time is needed). On this lesson's tight slot, it's marked optional in class and set as homework if the timing above still runs long — it isn't required for Task Set B's coverage.