Entity-Relationship Diagrams

IB Syllabus: A3.2.2: Construct ERDs, including cardinality and modality of the relationships between entities.

Key Concepts

What an ERD Is

An Entity-Relationship Diagram (ERD) is a visual notation for describing the structure of a database before any tables are built. It shows:

The entities (the real-world things that become tables)
The attributes (the columns each entity carries)
The relationships (how entities link together)
The cardinality of each relationship (how many of one connect to how many of the other)
The modality of each relationship (whether participation is mandatory or optional)

ERDs are the standard artefact for the conceptual and logical schemas covered in Database Schemas. A clear ERD lets developers, designers, and stakeholders agree on the structure on paper, where changes are cheap, before any data exists.

An ERD is a planning and communication tool. The role of an ERD is to give a compact, graphical representation of entities and relationships that is independent of the chosen DBMS, so that the same diagram can drive implementation by individuals or teams. Mark schemes explicitly refuse to award marks for “describing what an ERD looks like”, explain its role (planning, abstraction, logical structure, team coordination), not its symbols.

Entities and Attributes

An entity is a real-world thing that the database needs to track, a Member, a Class, an Order, a Vehicle. In the implemented database, each entity normally becomes a table; each instance of the entity becomes a row.

An attribute is a property of an entity, the columns that every record carries. A Member entity might have attributes MemberID, FirstName, Surname, DateOfBirth, MembershipTier.

In ERDs, entities are usually drawn as rectangles with the entity name inside; attributes are either listed inside the rectangle or drawn as ellipses attached to it, depending on the notation style.

Relationships, Cardinality, and Modality

A relationship is a meaningful link between two entities, “a Member makes a Booking”, “a Class has Bookings”. Each relationship has two important properties:

Cardinality: the maximum number of instances on each side:

Cardinality	Symbol	Meaning
One-to-one (1:1)	`1, 1`	One instance of A links to at most one instance of B, and vice versa. Example: each driver holds at most one driving licence, each licence belongs to one driver.
One-to-many (1:M)	`1, M`	One instance of A can link to many instances of B, but each B links back to only one A. Example: one coach runs many classes; each class has one coach.
Many-to-many (M:M)	`M, M`	Each A can link to many B’s, and each B can link to many A’s. Example: a student takes many courses; each course has many students.

Modality: the minimum participation on each side:

Modality	Meaning
Mandatory	Every instance must participate. Example: every booking must have a member, a booking with no member is impossible.
Optional	Participation is not required. Example: a member may have zero bookings, a brand-new member who has not yet booked anything is still a member.

A complete relationship description says both how many (cardinality) and whether required (modality) on each side. Many older treatments only show cardinality, but modality is what tells you whether a row can exist with no link at all.

Crow’s-Foot Notation

The most widely used ERD notation in industry is crow’s foot, named after the three-pronged “crow’s foot” symbol used for “many.” Each end of a relationship line carries two symbols: one for cardinality (one or many) and one for modality (mandatory or optional). Read them as a pair, working outward from the entity.

Symbol at line end	Visual	Cardinality	Modality
`\|\|`	two parallel bars	one (1)	mandatory
`\|o`	bar + circle	one (1)	optional
`>\|`	crow's foot + bar	many (M)	mandatory
`>o`	crow's foot + circle	many (M)	optional

Read the symbols from outside in: the symbol nearest the entity is the cardinality (one or many); the symbol further out is the modality (mandatory bar vs optional circle).

Reading a Crow’s-Foot Diagram

   MEMBER ||---------------o< BOOKING

Read each end toward the entity at the other end:

MEMBER end (||): when we look from BOOKING toward MEMBER, exactly one member, mandatory, every booking must belong to exactly one member.
BOOKING end (o<, circle plus crow’s foot): when we look from MEMBER toward BOOKING, many bookings, optional, a member may have any number of bookings, including zero.

In words: “A member has zero or many bookings; each booking belongs to exactly one member.”

This is a one-to-many (1:M) relationship with the modality “optional on the many side, mandatory on the one side”, by far the most common shape in real databases.

Resolving Many-to-Many with a Junction Table

Many-to-many relationships cannot be stored directly in a relational database. There is no clean way to fit “this member is linked to all of these classes” inside a single row. The standard fix is to introduce a junction table (also called a linking, bridge, or associative table) that sits between the two main entities.

A many-to-many between MEMBER and CLASS becomes:

   MEMBER ||---o< BOOKING >o---|| CLASS

Two one-to-many relationships pointing inward, with BOOKING in the middle. The BOOKING table holds:

Its own primary key (BookingID)
A foreign key to MEMBER (MemberID)
A foreign key to CLASS (ClassID)
Any attributes that describe the booking itself (BookedOn, Status)

The pattern is universal: every M:M relationship in a real schema is implemented as a junction table.

Steps to Construct an ERD

A reliable process for the kind of “Construct an ERD for this scenario” question that recurs every year:

Read the scenario twice. Underline every noun (potential entities) and every verb (potential relationships).
Pick the entities. Group nouns: things you would need a separate table for are entities. Things that just describe another entity are attributes.
Draw a box for each entity and name it (singular noun, all caps is a common convention: MEMBER, CLASS).
List the key attributes inside each box, at minimum the primary key.
For each relationship in the text, draw a line between the two entities and put a verb phrase on it (makes, belongs to, has, assigned to).
Decide cardinality at each end. Ask: “given one X, how many Y’s can there be?” and “given one Y, how many X’s?”
Decide modality at each end. Ask: “is participation required, or can there be zero?”
Resolve any M:M relationships by introducing a junction table, a new entity with two FKs and its own attributes.
Read the diagram back in words and check it matches the scenario.

Worked Examples

Example 1: Three Entities, One Junction Table

A community sports club, Harbour Run, wants a database to track members, the classes coaches run, and the bookings that link them. From the scenario:

Each member can book many classes; each class can be booked by many members.
Every booking belongs to exactly one member and exactly one class.
A member may have zero bookings (a brand-new member who has not booked yet).
A class may have zero bookings (a newly created class that no one has booked yet).

Crow’s-foot ERD:

erDiagram
    MEMBER ||--o{ BOOKING : "makes"
    CLASS  ||--o{ BOOKING : "is booked in"
    MEMBER {
        int MemberID PK
        string FirstName
        string Surname
        date DateOfBirth
        string Tier
    }
    CLASS {
        string ClassID PK
        string Title
        string Coach
        datetime StartsAt
    }
    BOOKING {
        string BookingID PK
        int MemberID FK
        string ClassID FK
        date BookedOn
        string Status
    }

Reading it back:

A member has zero or many bookings; each booking belongs to exactly one member. (1:M, optional on the M side, mandatory on the 1 side)
A class has zero or many bookings; each booking is for exactly one class. (Same shape, mirrored)
The two 1:M relationships together resolve the many-to-many between MEMBER and CLASS. The BOOKING junction table also carries its own attributes (BookedOn, Status), a sign that the junction is doing real work, not just acting as a bridge.

Example 2: A 1:1 Relationship

A national driving authority issues licences to individuals. Each adult may hold at most one driving licence; each licence belongs to exactly one individual.

erDiagram
    INDIVIDUAL ||--o| LICENCE : "may hold"
    INDIVIDUAL {
        int IndividualID PK
        string FullName
        date DateOfBirth
        string Address
    }
    LICENCE {
        string LicenceNum PK
        int IndividualID FK
        date IssuedDate
        date ExpiresOn
        string Category
    }

Reading it back:

An individual has zero or one licence (optional on the licence side, not every adult drives).
A licence belongs to exactly one individual (mandatory).
Cardinality: 1:1. Modality: optional on one side, mandatory on the other.

1:1 relationships are unusual. Often they signal that the two entities could be merged into one table; they are kept separate only when there is a strong reason, different security policies, different access patterns, or because participation on one side is rare and most rows would otherwise be null.

Example 3: From Words to Cardinality and Modality

A small bookstore database tracks AUTHOR, BOOK, and PUBLISHER. The business rules are:

Every book is written by at least one author and may have several co-authors.
Every book is issued by exactly one publisher.
Each author has written at least one book (otherwise they would not be in the database).
Each publisher has issued at least one book.

Work through the relationships systematically:

Relationship	A end	B end	Cardinality	Modality
AUTHOR writes BOOK	one author writes many books	one book is written by many authors	M:M	mandatory both sides
PUBLISHER issues BOOK	one publisher issues many books	one book has one publisher	1:M	mandatory both sides

The M:M between AUTHOR and BOOK needs a junction table, call it AUTHORSHIP:

erDiagram
    AUTHOR    ||--|{ AUTHORSHIP : "writes"
    BOOK      ||--|{ AUTHORSHIP : "has authors"
    PUBLISHER ||--|{ BOOK       : "issues"
    AUTHOR {
        int AuthorID PK
        string FullName
    }
    PUBLISHER {
        int PubID PK
        string PubName
    }
    BOOK {
        int BookID PK
        string Title
        int PubID FK
        int Year
    }
    AUTHORSHIP {
        int AuthorID FK
        int BookID FK
        int Position
    }

The AUTHORSHIP table carries an additional attribute, Position, recording whether each author is the first, second, or third co-author on a book. This is the natural place for such an attribute because it is a fact about the combination of author and book, not about either one alone.

Quick Check

Q1. What is the primary role of an ERD?

Q2. Which property of a relationship describes whether participation is mandatory or optional?

Q3. Each student in a school can enrol in many classes, and each class has many students. What is the cardinality of the relationship between STUDENT and CLASS?

Q4. A school has students and clubs, with a many-to-many relationship between them. What is the standard way to represent this relationship in a relational schema?

Q5. The crow's-foot notation INDIVIDUAL ||---o| LICENCE describes which kind of relationship?

Q6. Which properties should a complete ERD show for every relationship?

Decide the Relationship Type

A small online learning platform stores STUDENT, COURSE, and ENROLMENT. Read each business rule and decide the cardinality.

For each scenario, type the cardinality (1:1, 1:M, or M:M).

Scenario	Cardinality
One student can take many courses; each course has many students enrolled.
Each enrolment record belongs to exactly one student.
Each enrolment record is for exactly one course.
Each student is issued exactly one student ID card; each ID card belongs to exactly one student.
Each course is taught by one instructor; an instructor may teach several courses.

Fill in the Blanks

Complete the description of ERD notation.

ERD VOCABULARY
==============
A real-world thing that the database needs to track is called an
, in the implemented database it normally becomes a table.

A property of an entity (a column on the resulting table) is called an
.

The maximum number of instances on each side of a relationship
is called the , one or many.

Whether participation is required (mandatory) or not (optional)
is called the .

A many-to-many relationship is resolved in the schema using a
 that holds a foreign key to each of the two
main entities.

Spot the Error

A student wrote revision notes about ERDs. One line contains an error. Click the line with the error, then choose the correct fix.

1Cardinality describes the maximum number of instances on each side of a relationship 2Modality describes whether participation is mandatory or optional 3A many-to-many relationship can be stored directly in a relational database without a junction table 4A 1:1 relationship is rare, often the two entities could be merged into one table 5An ERD is a design tool, used before the database is built

Pick the correct fix for line 3:

</div>

Identify the Cardinality

"In a simplified literary database, each book is written by exactly one author. Each author may have written many books."

Type the cardinality of the relationship between AUTHOR and BOOK (use the form 1:1, 1:M, or M:M).

</div>

"Each person holds at most one current passport, and each passport is issued to exactly one person."

Type the cardinality of the relationship between PERSON and PASSPORT (use the form 1:1, 1:M, or M:M).

Practice Exercises

Core

[Core] ERDs [4 marks] Explain the role of entity-relationship diagrams when modelling data. Do not describe the symbols used; focus on the purpose of the diagram (planning, abstraction, stakeholder communication, future extension).
[Core] ERDs [2 marks] A clinic has tables PATIENT and APPOINTMENT. Each patient has had many appointments; each appointment is held by exactly one patient. State the cardinality and modality of the relationship between PATIENT and APPOINTMENT.
[Core] ERDs [3 marks] A bookshop sells books to customers. Each customer can place many orders; each order is placed by exactly one customer. Construct an ERD showing the relationship between CUSTOMER and ORDER, including cardinality and modality.

Extension

[Extension] ERDs [4 marks] A community sports club tracks members, classes, and bookings. Each member can book many classes; each class can be booked by many members; each booking is for exactly one member and one class. Construct an ERD that resolves the many-to-many relationship using a junction table. Include cardinality and modality at every end.
[Extension] ERDs [5 marks] A driving authority tracks individuals, driving licences, and vehicles. An individual may hold at most one driving licence; each licence belongs to exactly one individual. An individual may own many vehicles; each vehicle has exactly one owner. Construct the ERD, including cardinality and modality at each end.

Challenge

[Challenge] ERDs [6 marks] An online food-ordering platform connects customers, restaurants, menu items, and orders. A customer can place many orders; each order is from exactly one restaurant; each order contains one or more menu items; each menu item belongs to exactly one restaurant; the same menu item may appear in many orders. Construct a complete ERD with all four entities (plus any necessary junction tables), showing cardinality and modality.
[Challenge] ERDs [6 marks] Discuss the advantages and disadvantages of crow’s-foot notation compared with Chen notation for documenting a database used by a software-development team. Refer to readability, the ability to express cardinality and modality, and how easily the diagram supports later changes.

Exam-Style

[Exam-Style] ERDs [5 marks] A pet-passport database tracks OWNER, PET, and PASSPORT. An owner may have many pets. Each pet has exactly one passport, and each passport belongs to exactly one pet.
- (a) Construct an ERD for this scenario (3 marks).
- (b) Describe the relationship between PET and PASSPORT, including cardinality and modality (2 marks).

Note for IB CS learners: Construct items on A3.2.2 typically award 1 mark per correct relationship and one for resolving M:M via a junction table, a 3-mark question expects roughly three correct relationships or two relationships plus a correct junction. Modality is the new 2027 expectation; older mark schemes do not test it, but examiners will from 2027 onwards. Practise stating both cardinality and modality for every relationship.

Connections

Previous: Database Schemas, ERDs are the standard artefact for the conceptual and logical schemas.
Related: Relational Database Fundamentals. The relational vocabulary (entity, attribute, relationship type) that ERDs visualise.
Next: Data Types and Keys. The column types and key choices that flesh out an ERD into a complete logical schema.
Related: Normalisation. The process that refines an ERD into a low-redundancy 3NF design.
Forward: SQL Queries. The JOIN clauses in SQL queries trace the relationship lines on an ERD.