DDD3 — ER diagrams: cardinality and entity-occurrence diagrams
📅 To be scheduled
⏱ ~50–60 minutes · single period
Learning intentions
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.
Success criteria
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.
Warm up — what do you already know?
Answer before the lesson begins — it's fine if you're unsure.
WU1
1. In DDD2 you drew a crow's foot at the "many" end of a relationship line. What shape is a crow's foot?
WU2
2. In DDD2's ER diagram, one instructor teaches many classes. Which entity — Instructor or Class — sits at the crow's-foot ("many") end of that relationship?
WU3
3. Which entity in the Craigmillar Community Sports Club database exists mainly to link Member and Class together?
Key vocabulary
Cardinality
The number of records in one entity that can relate to a record in another entity — one-to-one, one-to-many, or many-to-many.
Entity occurrence
One specific record within an entity — for example, one particular member, or one particular class.
Entity-occurrence diagram
A diagram showing real instances of two entities as dots, with lines connecting related instances — used to work out and justify cardinality.
One-to-one (1:1)
Each occurrence in one entity relates to exactly one occurrence in the other, and vice versa.
One-to-many (1:M)
Each occurrence in one entity relates to one or more occurrences in the other, but each of those relates back to only one.
Many-to-many (M:M)
Occurrences on both sides can relate to multiple occurrences on the other side.
Resolving entity
A new entity introduced to turn a many-to-many relationship into two one-to-many relationships, so it can be implemented in a relational database.
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
Example 1 — One-to-one: School and Headteacher
1
Sample data: School S1 is managed by Headteacher H2; School S2 is managed by Headteacher H1; School S3 is managed by Headteacher H3.
2
Draw a dot for each School and each Headteacher, then draw a line for each management link.
3
Check every dot: does any School connect to more than one Headteacher? No. Does any Headteacher connect to more than one School? No.
✓
Cardinality: one-to-one. See the diagram below.
Every School dot has exactly one line; every Headteacher dot has exactly one line. 1 : 1
Example 2 — One-to-many: Instructor and Class
1
Sample data: Instructor INS01 teaches PIL01 and PIL02; Instructor INS02 teaches YOG01; Instructor INS03 teaches SPN01 and SPN02.
2
Draw a dot for each instructor and each class, then a line for every teaches-link.
3
Check every dot: INS01 and INS03 each have two lines out — more than one class. Every class dot, though, has exactly one line in.
✓
Cardinality: one-to-many (Instructor is the "one" side, Class is the "many" side) — matching the crow's foot you drew at Class in DDD2.
INS01 and INS03 each have two lines out; every Class dot has exactly one line in. 1 : M
Example 3 — Many-to-many: Member and Class, before resolving
1
Sample data: Member M001 books PIL01 and YOG01; Member M002 books PIL01 and SPN01; Member M003 books YOG01 and SPN01.
2
Draw a dot for each member and each class, then a line for every booking link.
3
Check every dot: every Member dot has two lines out, and every Class dot has two lines in. Both sides have multiple connections.
✓
Cardinality: many-to-many — which is exactly why the real database doesn't link Member to Class directly, but routes every connection through Booking instead.
Every Member dot has two lines out, and every Class dot has two lines in — connections run both ways. M : M
Now you try
Sample data: Booking BKG01 is for Member M001 and Class PIL01. Booking BKG02 is for Member M001 and Class YOG01. Booking BKG03 is for Member M002 and Class PIL01.
Answer the following:
Draw (in words) the entity-occurrence connections between Member and Booking.
What is the cardinality between Member and Booking?
M001 connects to BKG01 and BKG02 (two lines out). M002 connects to BKG03 (one line out). Every Booking dot (BKG01, BKG02, BKG03) has exactly one line back to a single Member.
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.
⚠️ Common mistakes — examiner feedback
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". The "many" side is the entity whose occurrences can have more than one line — always check both directions before deciding, since it's easy to check only one side and stop.
📝 Exam tip
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.
Task Set B — Core questions
Work through all questions. Written answers use self-assessment — compare with the model answer.
B1
Fill the gap: an entity-occurrence diagram shows individual ___ of an entity, not the whole entity.
B2
Sample data: Booking BKG01 is for Class PIL01. Booking BKG02 is for Class PIL01. Booking BKG03 is for Class YOG01. What is the cardinality between Class and Booking?
B3
On an entity-occurrence diagram, if every dot on both sides can have more than one line, what is the cardinality?
B4
Why can't a many-to-many relationship be implemented directly as a single foreign key in a relational database?
B5
Explain how the Booking entity resolves the many-to-many relationship between Member and Class.
Model answer
B6
Sample data: Instructor INS01 is qualified to teach Pilates only. Instructor INS02 is qualified to teach Yoga only. If each instructor can only ever be assigned to lead exactly one class at the club, and each class has exactly one instructor, what is the cardinality between Instructor and Class in this (hypothetical) version of the scenario?
B7
What is the real, established cardinality between Instructor and Class in the actual Craigmillar Community Sports Club database (not the hypothetical version in B6)?
B8
Suppose the sports club changed its rules so that each member could only ever book one class in total, never more. Would Member and Class still be many-to-many? Explain your reasoning.
Model answer
B9
On the School–Headteacher entity-occurrence diagram (Example 1), how many lines does each School dot have?
Task Set C — Extension · Beyond the specification
Optional challenge questions. Not required for your exam.
C1
If the club added a new entity for recording members' preferred payment methods, what cardinality would you expect between Member and this new entity, and why?
One possible answer
C2
Could two entity-occurrence diagrams have the exact same number of dots on each side, but show different cardinality? Explain, using an example.
One possible answer
C3
Booking has its own primary key (bookingRef), rather than using memberID and classCode together as its identifier. Research or reason about why a resolving entity might need its own separate primary key rather than relying on the two foreign keys it links.
One possible answer
📁 File this in OneNote under: Higher Computing Science → Database Design & Development → DDD3
📌 Teacher notes — Shift+T to hide
Suggested timing: 8 min warm up · 15 min notes & cardinality theory · 15 min worked examples & EO diagrams · 5 min now you try · 20 min Task Set B · 5–10 min Task Set C / review, if time allows.
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.