A3.2.2 · Database design

Constructing entity-relationship diagrams

Before a single table gets built, a good designer sketches it, an ERD is that sketch, made precise enough that two different people would build the exact same database from it.

Core for SL & HL Command term: construct Builds on A3.1.1 & A3.2.1

Explainer

Eastgate Food Bank wants a system to manage its volunteer rota. Before anyone writes a single line of SQL, the designer sketches it out: a box for VOLUNTEER, a box for SHIFT, a line between them. But "a line between them" isn't enough information to build a real database from, the designer also has to answer two separate questions: how many shifts can one volunteer sign up for, and does a shift actually need at least one volunteer assigned before it can run? An ERD answers both, precisely, using notation instead of prose.

This is A3's guiding question in a very concrete form: what are the principles, structures and operations that form the basis of database systems? A3.2.1 described schemas in words; an ERD is how the conceptual schema's structure gets drawn, and it's the standard way stakeholders, designers and future developers all read the same design without arguing over prose.

An entity-relationship diagram (ERD) draws the entities a database holds as boxes, and draws how those entities connect to each other as lines between the boxes, turning a design that would otherwise live only in someone's head into something a whole team can actually look at together. That shared picture keeps paying off long after the first design meeting: it is what a new developer reads to understand the schema months later, what a database constraint gets checked against before it is added, and the reference everyone returns to once the "why did we build it this way" question inevitably comes up.

Two separate questions, two separate axes

Every relationship line on an ERD is really answering two independent questions about each side of it, and mixing them up is the single most common way to misread or mis-draw one.

Cardinality answers "how many, at most?" Fix a single record on one side of the relationship, then count how many records on the other side it is allowed to reach. That upper limit is the cardinality, and it gives the four familiar shapes: one-to-one, one-to-many, many-to-one, or many-to-many.

Modality asks something cardinality never touches: does this side of the relationship have to be filled in at all? Zero means it can genuinely be left empty, one means every instance on the other side needs at least one match here. Because the two questions (how many, at minimum how many) are answered completely separately, all four combinations exist in practice: mandatory-and-one, mandatory-and-many, optional-and-one, optional-and-many.

A reusable technique: the "I am..." rule

Both axes have to be checked from BOTH entities' points of view separately, and the easiest way to do that reliably is to mentally become each entity in turn and ask two fixed questions.

Pretend you ARE one of the two entities, looking across at the other one, then ask:

Cardinality (max)

"Can I have more than one of the other thing?" Yes → many. No → one.

Modality (min)

"Do I NEED the other thing just to exist?" Yes → mandatory (1). No → optional (0).

Then swap: become the OTHER entity and ask the exact same two questions again. Each entity answers for itself, about the entity across from it, never about itself.

Reading the notation: modality × cardinality

The crow's-foot symbols aren't six unrelated shapes to memorise, four of them are just modality and cardinality drawn together on the same end of a line. The tick and circle answer the modality question; the single line and crow's foot answer the cardinality question.

Cardinality: one

Cardinality: many

Modality: mandatory

One and only one

Two ticks: must exist, and there is exactly one.

One or many

Tick plus crow's foot: at least one must exist, more are allowed.

Modality: optional

Zero or one

Circle plus tick: might not exist at all, but never more than one.

Zero or many

Circle plus crow's foot: fully optional, with no upper limit either.

Two real examples show why you can't guess modality from cardinality, or the other way round, they're genuinely independent:

Patient → medical records

Most patients have medical records, but a newborn or brand-new patient might have none yet.

Zero or many: optional (a patient can have zero records) AND unlimited (can have many over time).

Order → customer

Flip the logic around and it becomes obvious: nothing in the system called an "order" can ever exist unless some customer generated it by placing one.

One and only one: mandatory (an order must have a customer) AND exactly one (not several).

Worked example

Constructing an ERD: a volunteer rota

VOLUNTEER and SHIFT are the two entities. Click through the real construction sequence, from raw entities to a finished, correctly notated relationship line.

Step 1 of 5 Entities

Applying the "I am..." rule to a new pair: Employee and Department

Same technique, a fresh scenario this time, EMPLOYEE and DEPARTMENT in a company database, to prove the rule transfers rather than only working for VOLUNTEER and SHIFT.

Direction 1: become the Employee, look at Department

"I am an Employee. Can I belong to more than one department at once? No, just the one I'm assigned to. Do I NEED a department to exist as an employee record? Yes, every employee is assigned to a department."

Result: cardinality one, modality mandatory → one and only one (1, 1)

Direction 2: become the Department, look at Employee

"I am a Department. Can I have more than one employee? Yes, most departments have several. Do I NEED an employee to exist? No, a brand-new department might not have anyone assigned to it yet."

Result: cardinality many, modality optional → optional many (0, N)

Final answer: one department optionally has many employees (0, N); every employee mandatorily belongs to exactly one department (1, 1), a one-to-many relationship overall, from Department to Employee.

DEPARTMENT

mandatory + one

EMPLOYEE

optional + many

Read each symbol as describing the entity FARTHEST from it: the double-tick sits near DEPARTMENT but describes how many departments an employee needs (exactly one, mandatory), the circle-and-crow's-foot sits near EMPLOYEE but describes how many employees a department can have (zero to many, optional). This is the same reading direction used in the SHIFT/VOLUNTEER example above.

Check your understanding

"A shift must have at least one volunteer assigned." Is this statement describing cardinality or modality?

Common mistake

Markers reported this pattern for A3.2.2

RecurringA relationship's modeling is only complete once BOTH cardinality and modality are pinned down, and candidates routinely skip that check, assuming one setting from the other instead of testing it separately. Calling a "many" relationship automatically "mandatory," or an "optional" one automatically "one," is the exact shape of that mistake. They're independent settings, a relationship can be optional-and-many just as easily as mandatory-and-one.

What a weak answer looks like: "Since a shift can have many volunteers, the relationship is optional." This confuses cardinality (many, a maximum) with modality (optional, a minimum), they were never linked.

✓

What it should say: "A shift's cardinality with volunteers is many (a shift can have several volunteers rostered). Separately, a shift's modality is mandatory (a shift cannot run without at least one volunteer)." States each axis on its own terms, then combines them.

Revision questions

Past-paper style. Write an answer, then reveal the marking points and mark yourself.

Question 1 · 2 marks

[2]Distinguish between cardinality and modality in an ERD.

Question 2 · 3 marks

[3]A library system has BOOK and MEMBER entities, where a member can borrow many books, but a book that isn't currently borrowed has no associated member. State the cardinality and modality of this relationship on the MEMBER side, with a reason for each.

Question 3 · 4 marks

[4]Construct an ERD, with correct crow's-foot notation, for a hospital system where a DOCTOR entity treats many PATIENT entities, and every patient must be assigned to exactly one doctor.

Flashcards

Active recall for this statement's key terms.