From text to diagram: design choices

Entity or attribute: two tests

Page 73 lists the "Crucial design choices" of conceptual design as three questions: "Should a concept be modeled as an entity or an attribute? Should a concept be modeled as an entity or a relationship? Identifying relationships: Binary or ternary? Aggregation?" The first two decide most of a diagram, and the lecture answers each with one example.

Take an employee and her address (p. 74). If every employee has one address and you only ever print it on an envelope, an attribute of Employee is enough. The slide names the two situations in which it is not:

• "If we have several addresses per employee, address must be an entity (since attributes cannot be set-valued)."
• "If the structure (city, street, etc.) is important, e.g., we want to retrieve employees in a given city, address must be modeled as an entity (since attribute values are atomic)."

Two tests: several values for one owner, or a structure you need to look inside — either one makes the concept an entity, linked to its owner by a relationship.

The car-rental exercise of the session applies both tests without naming them. The inspecting company is one name with nothing inside: the attribute Company Name (p. 1296). The licence's "list of vehicles they are allowed to drive" is several values, but a short fixed list of yes-or-no facts: the composite attribute Drives, one component per category — Truck, Cars, Bus, … (p. 1295).

The same tests, on a relationship

The tests bite harder on the attributes of a relationship (p. 75). Say Joe worked in the toy department from 2015 to 2016 and came back in 2019. Work_In4 links Employee and Department and carries from and to. The slide's verdict: "Works_In4 does not allow an employee to work in a department for two or more periods (a relationship is identified by participating entities)."

A relationship instance is the pair (Joe, Toys) and nothing more; from and to only describe it. A second period would need a second instance with the same pair — which is the same instance. The fix repeats the address case: "We want to record several values of the descriptive attributes for each instance of this relationship. Accomplished by introducing new entity set, Duration." Duration is identified by from and to together, Work_In4 becomes a ternary relationship among Employee, Department and Duration, and (Joe, Toys, 2015–2016) and (Joe, Toys, 2019–…) are two instances: the "Binary or ternary?" of p. 73, answered once.

Hold on to that sentence — "a relationship is identified by participating entities". It is the rule the dungeon table of the warm-up breaks.

Where an attribute lives

Page 76, "Entity vs. Relationship", asks the second question through a budget. Each manager receives a discretionary budget, dbudget, and the first diagram hangs it on the relationship Manages2, next to since. The slide approves that drawing for one reading only: "First ER diagram OK if a manager gets a separate discretionary budget for each dept."

Change one fact — "What if a manager gets a discretionary budget that covers all managed depts?" — and the same drawing goes wrong twice:

• "Redundancy: dbudget stored for each dept managed by manager." A manager of three departments carries the same number in three Manages2 instances; update one, forget two, and the database contradicts itself.
• "Misleading: Suggests dbudget associated with department-mgr combination." The diagram claims the budget depends on the pair, when it depends on the manager alone.

The fix, in the slide's box "This fixes the problem!", is a subclass: Manager ISA Employee carries dbudget, and Manages2 now links Manager and Department and keeps only since, which really does depend on the pair.

An attribute lives on the element whose instance alone determines its value; anywhere else it is copied, and misread.

Two remarks. The subclass has both "Reasons for using ISA" of p. 72 (lesson 6): "To add descriptive attributes specific to a subclass" — dbudget — and "To identify entities that participate in a relationship" — only managers manage. And the same question — where is this value determined? — is what moves the dungeon's multiplier in the last section.

A whole schema, and three ways to build one

Page 77, "E-R Example", is a complete schema in the course's own notation: the training company of the textbook the slides are taken from (Atzeni and colleagues — background, not on the slide). Nine entities, ten relationships, and every (min,max) pair sits next to the entity it counts: in how many instances of the relationship one instance of that entity takes part (lesson 6). Reading it aloud, relationship by relationship, is the drill every exam diagram needs.

TELEPHONE is p. 74's first test in action: several numbers per instructor, so an entity and the relationship HOLDER. Four symbols of p. 77 come from the textbook, unexplained on the slides: a black dot is an identifier (Code of TRAINEE); an arc with one dot across Time, Room and Date of CLASS is a composite identifier — one room at one time on one date is one class; a dot on a bar across the line of EMPLOYMENT DATA is an external identifier — an EMPLOYEE record is identified through its trainee; a (0,1) on an attribute's stem makes it optional, like Marks. The exam papers list the attributes under the diagram and underline the keys; only the paper of 30 Jan 2026 draws them on the diagram, a filled dot for each key.

Three strategies

How does a designer get from a text to such a schema? Pages 78–83, "ER Design Methodology", answer with three pictures, two tables and no prose; the explanations below are the textbook's.

• Top-down (p. 79): one abstract schema of the whole specification, the "Initial schema", refined through "Intermediate schema"s into the "Final schema"; each refinement applies a top-down primitive (p. 80).
• Bottom-up (p. 81): the "Specifications" split into components and sub-components, a small schema for each, then the "Integration of schemas" into the "Final schema"; its primitives (p. 82) create concepts from nothing or from loose pieces.
• Inside-out (p. 83): start from the most important concept — EMPLOYEE in the slide's example — and grow outward like an oil stain, each ring adding what is directly connected: PARTICIPATION and PROJECT, then MEMBERSHIP, MANAGEMENT and DEPARTMENT, then COMPOSITION and BRANCH.

Top-down T2, "From one entity to a generalization", is what Manager did to Employee. Top-down T4, "From one relationship to an entity with relationships", replaces a relationship by an entity of its own: the dungeon will need it in the last section. Duration was a different cure for the same illness: p. 75 keeps Work_In4 and adds a third participant.

What a diagram cannot say

Page 73 ends with a warning: "A lot of data semantics can (and should) be captured. But some constraints cannot be captured in ER diagrams." The exam papers write such constraints in a line under the diagram that starts with N.B. The restaurant paper (6 Sept 2024) draws Table 1:N — Serve — 1:N Waiter and adds "Tables are served by more Waiters overall, but only one at a time": the pairs count every instance the relationship ever holds, and no pair can say "at a time". The Netflix paper (21 Jan 2025) adds "Only the original digital content can be dubbed", a condition on an attribute value, not a count. The Formula 1 paper (11 Feb 2025) writes "Each Team Member might have worked for different Teams in different periods of time" — Joe's problem again.

From text to diagram: the car rental

Exercise 1 of the session (p. 1293) is a one-paragraph requirements text: "Design an ER Model for a car rental system that manages the customers, the cars and their related elements." Customers show an ID card, a driving licence and a credit card, cars are inspected, rentals produce invoices. Read the solution slides in order (pp. 1294–1298) and a method shows through; this notebook splits it into five passes over the text, each leaving its own mark:

1. Nouns. Things with an identity of their own — with facts to keep or, like the customer, with links to everything else — become entities; p. 1294 prints them in capitals and colour.
2. Attributes. The facts in parentheses, or after "described by", become attributes of the entity they describe.
3. Keys and assumptions. Underline what identifies each entity; where the text gives nothing, invent an identifier and write the assumption next to it.
4. Verbs and quantity words. A verb between two entities becomes a relationship; "each", "their", "the list of all" become (min,max) pairs.
5. The process out. What the system does is not what it stores: drop the steps, keep only their data footprint.

Five identifiers out of eight are the designer's. The customer is the extreme case: the text gives the customer no attribute at all — every personal datum sits on the ID card — so the assistants invent one, circle it in red and write "Assumption: Customer is identified through an E-Mail" (p. 1297). Copy the habit. An invented key, or your reading of an ambiguous sentence, goes on the paper as "Assumption: …": a choice that is written down can be accepted, a silent one can only be wrong.

Page 1298 draws the customer half of the diagram. Read each pair from its own side: Credit Card (1,1) — Owns — (1,N) Customer gives each card exactly one owner and each customer one or more cards; the two other Owns, (1,1) on both legs, give every customer exactly one ID card and one licence. The other half exists only as a link (p. 1309), so the dashed part of the widget is one possible completion, not the assistants'. Its open choice is Operation: a kind of work that recurs across inspections gives Car Inspection (1,N) — Includes — (1,N) Operation; a line of a single inspection takes (1,1). Say which you mean.

And then: tables

The exercise closes with "N.B. ER models will be then mapped on tables that will contain data." and two tables (p. 1299):

The plate appears twice: as the key of a car, and inside each inspection row. That is the relational form of Car (0,N) — Undergoes — (1,1) Car Inspection: every inspection concerns exactly one car, so the car's key travels into the inspection's row, never the other way. Which key travels where is the recipe of lesson 8. Notice also what is missing: the "3 months" of the text — the check is a query over Date.

Museums and dungeons: derive, specialise, collapse

The second text of the session (p. 1302) is the Dutch Museum Association: museums, halls, artworks, artists, visitors, tickets and a shop. One deck prints it again under the assistant's red pen (p. 1328): boxed nouns, underlined identifiers, a small composite-attribute sketch beside "location", "only one artist" boxed as a cardinality hint. One placement repeats the rule of section 2: "Visitors state the time when they would visit the museum when they buy tickets", so Visiting Time hangs on Buy, the purchase that fixes it (p. 1306).

Two hierarchies, each with a reason

Extra II (p. 1305) grows the shop: "now museums will include three different kinds of shops: restaurant, sweets shop, and gadgets shop. While the first two shops sell food, the latter sells gadgets. Sweet shops only sell sweets. Food is described by the same attributes as gadgets, plus an expiry date."

Each hierarchy has a reason. Product exists because food and gadgets share attributes: the shared ones go up, Expiry Date stays on Food — the first reason for ISA on p. 72, "To add descriptive attributes specific to a subclass". The kinds of shop exist because each sells a different kind of product through its own Sell — the second reason. Sweet has no attribute of its own; it exists to be the far end of the sweets shop's Sell.

Keep it or derive it?

The dungeon text (pp. 1310–1311) describes players, characters, items, weapons and monsters; its solution opens with two red remarks (p. 1312). "Not all the data is useful": the password and e-mail confirmations check a form, they are not facts to store. "Do not forget the “Gold” attribute": the text only says gold "is provided to the player" when a monster is defeated, yet an amount that accumulates must live somewhere — the solution puts it on Character.

Then the session asks whether some attributes should be stored at all. Strength Score is "derived from the strength of the items equipped", and the slide's chain reads "It can be derived from “Weapons”" → "Redundancy" (p. 1314). A value you can compute from stored data is a derived attribute: storing it keeps two copies of one fact that can drift apart. The slide's next question, "What if the “Strength Score” would depend also on the “Experience Points”?", changes nothing: Experience Points are stored too. Page 1315 pushes on — "Can we remove “Gold” and “Experience Points” based on the same statement we made before?" — and prints both answers, each with its assumption:

Derivable means computable from what is recorded, and what is recorded depends on how you read the text — so the slide writes the assumption next to each verdict. Do the same.

When a subclass earns its place

In Extra III a bug lets players "equip weapons regardless of the class of their character, but they can only equip one weapon", and a "Weapon Efficiency Score" gives each weapon a multiplier against misuse (p. 1319). Page 1320 draws one Wield for everybody, Character (1,1) — Wield — (0,N) Weapon, makes the score a composite attribute of Weapon with one component per class, circles both hierarchies and asks "Is it still necessary to keep the ISA Hierarchies?" — "They can be transformed into attributes". With their wielding rules gone, the classes have nothing of their own: they collapse into an attribute Class of Character, the weapon kinds into an attribute of Weapon.

Extra IV runs the argument backwards. Developers start to play; they "have the same attributes of players, although they have a special tag named “DEV” associated with their account" (p. 1321), so the slide draws a Developer subclass, "OR" a Developer attribute — both work, and the attribute is simpler. Then "allowing players to add developers to their friends is not a good practice" (p. 1322): one kind of player now has a relationship the other must not have.

A subclass earns its place with an attribute or a relationship of its own; without one, it is an attribute of the parent.

The structural fix has a price: Befriend now exists only among basic players, so developers cannot befriend each other either — more than the text asked. If that matters, it is exactly the kind of constraint that goes into an N.B. line.

Two more cases go through the same test. Boss Monster (p. 1313) fails it — no attribute or relationship of its own, only larger values — yet the slide keeps the subclass, (P,E); a Yes/No attribute would do as well. The museum's Extra III (p. 1307) passes it: "painters only befriend other painters and sculptors only befriend other sculptors". No solution is printed, but the device of p. 1322 gives one: Painter and Sculptor under Artist, (P,E) if other artists exist and nobody is both (state it), each with its own recursive Befriend.

Monster or fight? When a relationship must become an entity

Back to the warm-up. Extra I (p. 1316) introduces the "Killing Blow", which "awards the player that kills the monster with extra experience points, based on a “Monster Killing Blow Multiplier” score (between 1.2 and 1.5)". That multiplier belongs to the kind of monster, so the solution puts KB Multiplier on Monster, and a relationship Slay, (0,N) on both sides, counts Slain Amount and KB Amount for each pair.

Extra II (p. 1317) rebalances the game: the multiplier is now "between 0.5 and 1.5" and "assigned depending on how well the player fought the monster". Its value is fixed by the fight, so — the rule of p. 76 — it leaves Monster and joins KB Amount on a second relationship, Killing Blow. The slide asks "Should I keep them both?" and p. 1318 prints two answers: "Yes - From a data perspective, we can only keep “Killing Blow” even though the name of the relationship would change drastically. Therefore, we could assume we keep slay and add a simple “Slay Modifier”." and "No - We should keep them both for clarity of their meaning and for text coherence." Read each label by its text: "Yes" argues for one relationship, "No" for two. Both are printed, so argue yours: an ordinary kill is a killing blow with multiplier 1.0, the row circled "Standard Kill".

That answers the warm-up: the sentence was p. 75's "a relationship is identified by participating entities". The cure is top-down primitive T4, "From one relationship to an entity with relationships": as an entity, three killing blows are three instances that may share a character and a monster. Turning a relationship into an entity is called reification (the textbooks' word, not the slides'); Work_In4 took the other cure, a third participant — both give each repetition an identity of its own. The museum's Extra I (p. 1304) needs the same if one artwork can be lent to the same museum twice: "keep track of the period each artwork spends in each museum" — one loan, one entity.

The exam papers keep their relationships binary and state such situations in words, like the Formula 1 "N.B." of section 3: spot them before you design the graph or the documents of the later exercises.