IB COMPUTER SCIENCE | A3.2.2 Entity Relationship Diagrams

Lesson objective

Construct ERDs showing entities, relationships, cardinality and modality, with appropriate attributes, identifiers and many-to-many resolution.

Learn

A3.2.2 | CONSTRUCT ERDS

01 | DRAW THE RULES BEFORE BUILDING THE TABLES

A school wants to record students and clubs. Before creating tables, the designer needs to know whether a student may join several clubs, whether an empty club can exist and whether every membership must identify a student. A list of names and IDs alone does not answer those questions.

An entity relationship diagram (ERD) shows the kinds of things recorded and the relationships between them. With an appropriate notation, it also shows how many related instances are permitted and whether participation is optional or required.

ERDs help people check requirements, detect missing relationships and plan an organised relational design. They do not automatically generate every correct constraint, query or performance setting.

This lesson builds diagrams from plain-language rules. It uses crow’s foot notation with explicit minimum/maximum labels, so the meaning stays visible even while the symbols are unfamiliar.

02 | ENTITY, INSTANCE AND ATTRIBUTE

An entity type is a kind of thing about which the system stores information, such as Student, Club or Enrolment. An instance is a particular occurrence: student S01 or club C01.

An attribute describes an entity, such as StudentName. Do not draw a separate entity for every simple attribute or every current student. A Student box represents the type, not just Maya’s current record.

Choose entities from meaning, not merely every noun in a sentence. “Record a student’s name” identifies a useful Student entity and a name attribute, not necessarily separate Student and Name entities.

A relationship is an association between instances, such as a student having a membership. Name the relationship with a useful verb or verb phrase when it improves clarity.

03 | CARDINALITY AND MODALITY ANSWER DIFFERENT QUESTIONS

In the convention used here, cardinality identifies the maximum participation: one or many. Modality identifies the minimum: zero for optional participation, or one for required participation in these examples. Some references use cardinality broadly for the entire minimum/maximum pair.

For one instance at the other end, ask two questions: what is the minimum number of this entity allowed, and what is the maximum? Write the result as 0..1, 1..1, 0..N or 1..N. N means many, not one fixed measured total.

A club may have zero students today even though it is permitted to have many. The diagram should represent the allowed rule, not just count today’s rows.

The labels “one-to-many” and “many-to-many” describe maximum patterns, but do not by themselves settle optionality. You must decide the minimum at each end too.

04 | READ THE FOUR ENDPOINT COMBINATIONS

Minimum..maximumMeaningCrow’s foot marks
0..1Optional; at most oneCircle plus a bar
1..1Exactly oneBar plus bar
0..NOptional; many permittedCircle plus crow’s foot
1..NAt least one; many permittedBar plus crow’s foot

A circle means zero, a bar means one and a crow’s foot means many. The marks work in pairs: the mark nearest the entity gives the maximum, while the inner mark gives the minimum in this convention.

In the diagrams below, numeric minimum/maximum labels repeat the symbols. Learn the meaning of both rather than treating the symbol as a decorative arrow.

LEFT ENTITYRIGHT ENTITYRELATED TO1..10..N
For one left instance, zero or many right instances; for one right instance, exactly one left instance.

05 | THE MARK BESIDE AN ENTITY COUNTS THAT ENTITY

This is the reading rule students most often reverse: the marks next to Order describe how many orders relate to one Customer. The marks next to Customer describe how many customers relate to one Order.

Assume a customer may place zero or many orders, and every order belongs to exactly one customer. Put 0..N next to Order and 1..1 next to Customer. Read the relationship in both directions.

The line is not an arrow showing which record is created first. Its endpoints describe participation rules. A database can have additional creation or deletion policies that need separate implementation.

CUSTOMERORDERRELATED TO1..10..N
One Customer has 0..N Orders. One Order belongs to 1..1 Customer.

06 | CONSTRUCT FROM TWO PLAIN-LANGUAGE STATEMENTS

Start with a rule in each direction: “For one customer, there may be zero or many orders” and “For one order, there must be exactly one customer.” Underline the entity being counted in each statement, then place the marks beside that entity.

Check the minimum separately from the maximum. “May have orders” supports zero as a minimum. “Every order belongs to one customer” supports exactly one customer per order. Do not infer optionality from how many rows happen to exist today.

If the requirement is ambiguous, record an assumption or ask the stakeholder. A diagram cannot repair an unstated rule by choosing a random circle or bar.

A useful construction routine is: identify entities, state relationships both ways, establish minimum and maximum at each endpoint, then add identifiers and appropriate attributes at the requested detail level.

07 | ONE-TO-ONE IS STILL TWO SEPARATE RULES

Suppose a school records optional access cards: a student may have zero or one current card, and each recorded card is assigned to exactly one student. This is a one-to-one maximum pattern, with optional participation for the card from the student’s perspective.

The mark next to Card is 0..1 because one student may have no card. The mark next to Student is 1..1 because each card must identify one student. If unassigned cards must also be recorded, that second rule changes.

One-to-one relationships can support separation of optional or differently controlled information, but unnecessary splitting can complicate a design. Follow the actual domain rule rather than forcing every pair into the same shape.

STUDENTCURRENT CARDRELATED TO1..10..1
One Student has 0..1 Current Card; each recorded Current Card is assigned to 1..1 Student.

08 | MANY-TO-MANY AT THE CONCEPTUAL LEVEL

A student can join several clubs, and a club can have several students. Assume students and clubs can exist before any memberships are recorded. The conceptual Student–Club relationship therefore has 0..N at both ends.

For one student, count clubs: zero or many. For one club, count students: zero or many. The two many marks do not mean everyone attends every club or that each side has the same total number of records.

A conceptual ERD can show this many-to-many association. For a conventional relational implementation, a linking entity makes each individual association explicit.

STUDENTCLUBRELATED TO0..N0..N
Conceptual association: one Student joins 0..N Clubs; one Club has 0..N Students.

09 | RESOLVE MANY-TO-MANY WITH ENROLMENT

Introduce Enrolment as the linking or associative entity. Each enrolment belongs to exactly one student and one club. A student can have zero or many enrolments; a club can have zero or many enrolments.

Student has StudentID as primary key; Club has ClubID. Enrolment carries StudentID and ClubID as foreign keys. Under our rule of one membership per student-club pair, the pair is also its composite primary key.

A membership Role belongs in Enrolment because the same student can be a Builder in Robotics and a Designer in Game Design. It is a fact about the association, not one universal role of the student.

The final diagram below uses the same Enrolment entity once, with two relationships. Read both independently.

STUDENTPK StudentIDStudentNameEmailENROLMENTPK, FK StudentIDPK, FK ClubIDRoleCLUBPK ClubIDClubNameRoomHASHASEach enrolment has exactly one student and exactly one club.
One composite Enrolment primary key: (StudentID, ClubID). The many marks are beside Enrolment on both relationships.

10 | CHECK THE SYMBOLS AGAINST THE KEY RULES

A row in Enrolment must identify both parents, consistent with 1..1 beside Student and Club. The foreign keys and non-null composite key help enforce those references in the relational implementation.

However, a foreign key alone does not normally enforce “every parent must have at least one child”. If a changed rule requires every club to have at least one enrolment, additional transactional/application or DBMS-supported mechanisms may be needed.

A primary key in Enrolment prevents duplicate pairs under the stated rule; it does not automatically enforce a minimum of one membership for every student. Diagrams express requirements, while physical constraints must be selected to implement them.

Do not infer a whole ERD only from column names. Nullability, unique constraints, reference rules and domain requirements all affect the intended participation.

11 | EXPERIMENT WITH THE PARENT-CHILD RULE

Our builder keeps one rule fixed: every membership identifies exactly one parent student. Change how many memberships a student may have and observe the marks at the membership end.

This is a relationship-reading model, not a complete database generator. A minimum of one child is a requirement that the implementation must enforce appropriately; drawing a bar does not implement it.

12 | TEST YOUR ERD USING POSSIBLE DATA

Imagine one student with no enrolments. Is that allowed by the optional end in our final diagram? Yes. Imagine one enrolment referring to no student. Is that allowed? No: each enrolment needs exactly one student.

Now try two memberships with the same StudentID and different ClubIDs. They are allowed under the composite-key rule. Two memberships with the same pair are not. These checks connect participation and identification without treating them as identical concepts.

Sample data can reveal a contradiction in a proposed model, but a small current sample cannot prove the maximum allowed by the business. One club with one member today does not prove a one-to-one relationship.

Use edge cases: zero related instances, one related instance, several related instances, a missing parent and a repeated association.

13 | A NEW REQUIREMENT CAN CHANGE THE ASSOCIATIVE ENTITY

The school now wants separate club memberships for each term. StudentID and ClubID alone no longer identify one membership across time: S01 can join C01 again in another term.

One possible design adds Term with TermID and includes TermID in Enrolment’s composite identifier, with a suitable reference. Alternatively, a designed membership identifier plus appropriate uniqueness constraints can represent the rule. State the chosen design.

The conceptual idea changes from a timeless membership to a term-specific membership. Attributes, references and uniqueness rules must follow. Existing records need a justified migration plan rather than invented term values.

This is why constructing an ERD involves understanding the scenario, not just memorising three table boxes.

14 | A SECOND SCENARIO: LIBRARY COPIES AND LOANS

A title is not the same thing as an individual physical copy. A library may have several copies of one title, and each copy belongs to exactly one title. A Loan records a borrowing event involving a copy and a member.

Across loan history, one copy can have many loans and one member can have many loans. Each loan identifies one copy and one member. A rule saying “a copy has at most one active loan at a time” concerns current status over time, not a maximum of one historical Loan row.

If titles can be catalogued before copies arrive, Title has zero as its minimum related copies; if policy forbids this, that minimum changes. Write the assumption before drawing marks.

Separate entity meaning, lifetime relationships and conditional rules. Otherwise a one-to-one symbol can accidentally prevent the history the library needs.

15 | PRESENT A COMPLETE AND READABLE ERD

Use singular, meaningful entity names, clear identifiers and a consistent notation. Include attributes at the level requested; distinguish primary-key and foreign-key roles. Keep connecting lines away from text and label relationship meaning where needed.

Show participation at both ends. Add a legend or explicit 0..1/1..N labels when useful, especially while learning. A line without endpoint meaning leaves important rules unspecified.

An ERD supports organised design and communication, but it is not proof of full normalization, best performance or every business rule being enforceable by one foreign key. Later design, implementation and testing remain necessary.

For an assessed construction, the diagram is the main answer. A paragraph can clarify assumptions, but should not replace the requested diagram.

16 | IDENTIFY THE RULE TO DRAW

Select the minimum/maximum pattern for the entity being counted. Each question states the direction explicitly; pay attention to which end needs the marks.

17 | COMMON MISCONCEPTIONS

ConfusionCorrection
The many mark beside Order counts customersIt counts Orders per one instance at the other end
One-to-many automatically means one or more childrenThe maximum pattern alone does not settle the zero/one minimum
Today’s one child proves a one-to-one ruleThe allowed business maximum may still be many
A linking table’s two PK parts are two independent primary keysThey form one composite primary key
A foreign key forces every parent to have a childIt usually constrains the referencing rows, not that parent-side minimum
One current loan means one loan everA conditional active-loan rule differs from the history relationship

Read every line as two sentences before accepting it. If either sentence contradicts the scenario, change the marks or the model.

18 | CONSTRUCT, THEN VALIDATE THE DIAGRAM

For construct, display the required information in diagrammatic or logical form. Identify the entities, add the relationships, determine minimum/maximum participation in both directions, then label attributes and keys at the requested level.

For the club scenario, draw Student and Club with Enrolment between them. Put 1..1 beside each parent and 0..N beside Enrolment on both lines. Mark StudentID and ClubID as the parent keys, and the pair as Enrolment’s primary key as well as its references. State the one-membership-per-pair assumption.

Finally test possible cases and explain any rule not fully represented by the ordinary key constraints. “One student may have no memberships” should be allowed; “an enrolment has no student” should not.

The next lesson develops data types, followed by constructing tables that implement the design.

MATCH THE ERD IDEA TO ITS ROLE

Terminology

Terminology

ERD

An entity relationship diagram showing entities and associations with relevant participation rules.

Entity type

A kind of thing about which the system records information.

Entity instance

One particular occurrence of an entity type.

Attribute

A property represented for an entity or association.

Relationship

An association between entity instances.

Cardinality

In this lesson, the maximum one/many participation; some references use the term for the full minimum/maximum pair.

Modality

The minimum participation, such as zero optional or one required.

Optional participation

A minimum of zero related instances is permitted.

Mandatory participation

At least one related instance is required under the stated rule.

0..1

Zero or one related instance.

1..1

Exactly one related instance.

0..N

Zero or many related instances.

1..N

One or many related instances.

Crow’s foot notation

Relationship endpoint notation using zero, one and many marks.

One-to-one

Both maximum participation values are one; minimums still need specifying.

One-to-many

One maximum is one and the opposite maximum is many.

Many-to-many

Both maximum participation values are many.

Associative entity

An entity explicitly recording a relationship instance, such as Enrolment.

Linking table

A relational implementation recording associations through references.

Primary key

The selected unique, non-null row identifier.

Foreign key

A constraint requiring relevant referencing values to match a suitable key.

Composite key

A key using a combination of attributes.

Business rule

A domain requirement determining permitted data or relationships.

Relationship direction

The perspective of counting one entity for a single instance at the other end.

Referential integrity

Declared reference rules are satisfied.

Uniqueness constraint

A rule limiting duplicates for a specified column or combination.

Conceptual relationship

A domain-level association that may later be resolved into a relational structure.

Historical relationship

An association considered over stored events across time.

Conditional rule

A rule applying under a condition, such as at most one active loan.

Model validation

Checking a diagram against stated requirements and possible cases.

Questions

Questions

CHECK YOUR UNDERSTANDING

Select all correct choices. Each exact set earns one point.

1. Which are ERD purposes?
2. Which distinguish basic concepts?
3. Which describe modality/cardinality here?
4. Which read the Customer–Order example correctly?
5. Which describe the club linking design?
6. Which describe the final composite key?
7. Which are accurate constraint limits?
8. Which help validate an ERD?
9. Which fit the library scenario?
10. Which make a strong construction?

EXPLAIN AND COMPARE

Write your answers before revealing each sample.

1. Construct a Customer–Order relationship under the stated rules.

2. Explain how cardinality differs from modality in this lesson.

3. Construct the club ERD with an associative entity.

4. Explain why Enrolment is useful.

5. Describe how to read an endpoint correctly.

6. Explain why a current sample cannot determine all participation rules.

7. Construct a Student–CurrentCard relationship with optional student ownership.

8. Explain a foreign-key enforcement limit.

9. Describe how term-specific memberships can change the ERD.

10. Explain the library copy/loan distinction.

11. Describe how to validate a diagram.

12. Construct an answer suitable for the command term.

CONSTRUCT IT | A CLUB MANAGEMENT ERD

Construct a complete Student–Enrolment–Club ERD under the stated rules. Show minimum and maximum participation at both ends, identifiers and reference roles. State the uniqueness assumption, then test a student with no memberships, a missing parent and a repeated student-club pair. Extend your design for term-specific membership and explain the changed rule.

Flashcards

Flashcards

Click to flip. Select the ideas you need to revisit.

0 cards selected for revision.

    Selections are kept while this page is open.

    Workbook

    Workbook

    COMING SOON

    The workbook for A3.2.2 Entity Relationship Diagrams is coming soon. Complete the matching task and written questions in your exercise book.