In the previous lesson we closed requirements document v1.0 for the BiblioRed extension: fourteen functional requirements, ten business rules, twelve queries and a bounded scope. It is an excellent text for discussing things with the client and absolutely useless for programming. Between those two things one step is missing: turning the prose into a drawing.

The entity-relationship model is that drawing. Peter Chen proposed it in 1976 —we placed it in lesson 01-03— and fifty years later it is still the standard way of thinking through a domain before touching the keyboard. It is not a decorative diagram you produce at the end for documentation: it is a reasoning tool. Drawing forces you to decide things that prose lets you leave vague, and that is why half the useful questions in a project turn up while you are tracing a line between two boxes.

This lesson teaches the complete language —entities, attributes, relationships, cardinality, participation, hierarchies— and applies it, decision by decision, to the BiblioRed commission. The deliverable is the complete ER diagram of the extended BiblioRed, integrating the seven tables that already existed with everything new. There is still not a single CREATE TABLE statement: that is the next lesson, and there is a reason for the order.

1. What a conceptual model is and why it is drawn before any table exists
2. Entities: strong and weak
3. Attributes: simple, composite, multivalued, derived and identifiers
4. Relationships: binary, reflexive, ternary and with attributes of their own
5. Cardinality: 1:1, 1:N and N:M
6. Total or partial participation: the other half everybody confuses
7. The notations: Chen versus crow's foot
8. The extended ER model: generalization and specialization
9. Drawing BiblioRed, decision by decision
10. Deliverable: the complete ER diagram of the extended BiblioRed
11. Tools for drawing diagrams
12. Common Mistakes and Tips
13. Exercises
14. Conclusion

A conceptual model is a representation of what exists in a domain and how it is related, without committing to any technology. It says nothing about tables or documents or columns or types. It says: here there are members, here there are events, and a member registers for many events.

Being technology independent is not academic purism: it is what makes it useful. Three practical consequences:

It can be discussed with people who are not in IT. BiblioRed's activities coordinator is not going to review a CREATE TABLE, but she can look at a line between "event" and "room" and say "that's wrong, we hold the summer storytelling session in the courtyard". That remark, made at the drawing stage, costs you erasing a line. Made three months later, it costs you a migration.

It works equally well for relational and for document databases. The same BiblioRed diagram can be derived into a PostgreSQL schema (04-03) or into MongoDB collections with the techniques from 03-03. The decision to embed or reference is taken afterwards, on top of an already closed conceptual model. An ER diagram is, in that sense, more durable than either of the two schemas.

It makes the holes visible. Prose tolerates ambiguity; a drawing does not. The moment you trace the line between EVENT and ROOM you are obliged to decide three things: how many rooms per event, how many events per room, and whether there can be an event with no room. The prose of R4 did not answer the third one.

The quality criterion for a conceptual model is not that it looks nice, but that every line is a verifiable statement about the business that somebody can confirm or deny.

An entity is a thing in the domain with an existence of its own and about which we want to store information. An entity set (what we colloquially call "the entity") is the type: MEMBER, EVENT, ROOM. An instance is a specific individual: the member Marta Alsina, the book club on May 14.

A strong entity has an identifier of its own and exists by itself. MEMBER is strong: Marta Alsina exists even if she never takes out a loan. ROOM is strong, EVENT is strong, SPEAKER is strong.

A weak entity cannot identify itself: it needs the identity of another entity, called the owner or identifying entity. Two unmistakable signals:

1. Its instances make no sense without their owner.
2. Its identifier is partial: it distinguishes instances only within one owner.

The canonical example in BiblioRed is the copy. According to R2, copies are numbered within their material: "copy 3 of The Map of Time". The number 3 identifies nothing on its own —there is a copy 3 of every material in the catalog—; what identifies it is the pair (material, number). That "3" is the partial identifier or discriminant.

One clarification that avoids a lot of confusion: in the conceptual model, an entity is weak for a semantic reason (it does not exist without the other one), not because we are going to give it a composite key. The latter is a common consequence, but it is a decision belonging to the logical phase, and in 04-03 we will see that it is sometimes solved with a surrogate key for convenience while remaining conceptually weak.

The question that settles it: if the owner entity disappeared, would it make sense to keep this one? If "The Map of Time" disappears from the catalog, does it make sense to keep its copy 3? No. It is weak.

An attribute is a property of an entity or of a relationship. There are five kinds and telling them apart matters, because each one is transformed differently in 04-03.

A value that is indivisible as far as the business is concerned: capacity, title, registration_date. This is the normal case.

An attribute that breaks down into parts with a meaning of their own. The BiblioRed case is R13: a branch's address, today a single text field, which the website needs broken down into street, number, postal code and city.

The question for deciding whether to break it down is always the same: is anybody going to search, filter or sort by one of the parts? R13 explicitly says yes (searching by postal code), so it gets broken down. If nobody were going to do it, a single text field would be the correct and simpler option.

An attribute that can have several values at the same time for the same instance. In BiblioRed there are two, and both are detected by a plural in the statement of requirements:

• The subtitle languages of a DVD (R1).
• The phone numbers of a member, up to three (R12).

It is important not to confuse this with an attribute that changes over time: a copy's status changes, but at any given moment it has only one. Multivalued means simultaneous.

An attribute whose value is computed from others and therefore does not need to be stored. BiblioRed has several:

In a diagram they are marked (classically, with a dashed oval) precisely to place on record that they are not stored. That one of them sometimes ends up being stored for performance reasons is a later, conscious decision, which has a name —denormalization— and is studied in 05-04.

The attribute, or set of attributes, that tells one instance apart from another within the set. In the conceptual model it is called a candidate key; when one is chosen, it is the primary key. We are picking up here exactly what we saw in 02-01 and the design decision from section 10 of 04-01.

In BiblioRed the room illustrates a fine nuance: according to R3, a room name is unique only within its branch. That is, name is not an identifier of ROOM, but the pair (branch, name) is. An identifier that needs the related entity is the classic mark of a weak entity… and indeed, a room is conceptually weak with respect to its branch. We will take it that way and in 04-03 we will see what to do about it.

A relationship is an association between instances of entities. In the statement of requirements it almost always corresponds to a verb: a member registers for an event, an event is held in a room, a loan generates a fine.

The degree is how many entities take part:

The overwhelming majority of useful relationships are binary. Higher-degree ones are rare, hard to read and almost always three binary ones in disguise; we will come back to this in section 9 and more forcefully in 04-03.

It is a relationship of an entity with itself. It is always read with two different roles, and without naming them the diagram is incomprehensible.

In BiblioRed it is not part of the v1.0 requirements, but it is useful to see because it appears in almost every domain: employee/manager, category/subcategory, material/sequel.

This is the most important concept in the section. There is data that belongs to neither of the two entities, but to their crossing.

Think about the registration from R6. Is the registration date an attribute of the member? No: a member does not have "a registration date", they have many, one per event. Is it an attribute of the event? No either, for the same reason. It is an attribute of the relationship: it exists only when that specific member crosses with that specific event.

The infallible rule for spotting them: if an attribute needs to know both keys in order to have a value, it belongs to the relationship. "What is Elena Roig's fee?" has no answer; "what is Elena Roig's fee for the workshop on June 12?" does.

N:M relationships always admit attributes of their own; 1:N ones can have them too, although in practice they end up being absorbed into the N side.

Cardinality (or cardinality ratio) answers this: how many instances on the other side can one instance on this side be associated with?

You ask two questions, one per direction, and write down each answer:

Question 1: An event, in how many rooms is it held? → In one (R4). Question 2: A room, how many events does it host? → Many.

Conclusion: ROOM 1 — N EVENT.

Repeated for BiblioRed's new relationships:

The above is the maximum cardinality (1 or "many"). There is also the minimum one (0 or 1), and it is the one that expresses obligation. The full notation, widely used in Europe, writes both: (0,1), (1,1), (0,N), (1,N).

It reads: an event is held in one room at minimum and at maximum (mandatory, exactly one); a room hosts between zero and many events. That (0,N) says that a room can exist with no events at all, something that the 1:N cardinality alone did not say.

A detail that confuses many people: in the (min,max) notation the numbers are written on the side of the entity they describe, whereas in crow's foot notation the symbols are drawn next to the entity at the other end. It is the most frequent cause of diagrams being read backwards.

Participation answers a different question from cardinality: does every instance of this entity have to take part in the relationship, or can there be some that do not?

• Total participation (mandatory): every instance takes part. It is drawn with a double line in Chen, or with a perpendicular stroke | in crow's foot.
• Partial participation (optional): there may be instances that do not take part. Circle o in crow's foot.

That these are two different things is best seen with an example where they do not coincide:

The classic mistake is to say "it is 1:N" and believe that says it all. Cardinality says how many; participation says whether at least one is required. They are independent axes and they combine freely:

The consequences in the logical phase are extremely direct, and that is why it is worth pinning this down now:

That last row is a real limitation of the relational model that is worth knowing from the outset: "every branch has at least one room" is guaranteed by no foreign key. It is documented as a business rule and you decide where it lives; we will settle the general criterion in 04-04.

The ER model has the same content in any notation; what changes is the drawing. You need to know two of them.

The original one. Rectangles for entities, diamonds for relationships, ovals for attributes, hanging off their entity.

It is very expressive —you can see the attributes, derived attributes carry a dashed oval, multivalued ones a double oval— and that is why it is used in teaching. And it is impracticable in a real domain: a diagram with twenty entities and all their attributes in ovals does not fit on any screen or on any wall.

The one used in industry today and the one every tool implements. Entities are boxes with the list of attributes inside, relationships are lines, and the symbols at the ends encode cardinality and participation at the same time.

The symbols are read at the end that touches the entity, and they describe how many instances of that entity correspond to one on the other side:

Look at the four rows that say "does not exist". Crow's foot loses expressiveness compared with Chen in multivalued, derived and composite attributes and in ternary relationships. It is not a casual defect: crow's foot sits halfway between the conceptual and the logical model, and those four concepts disappear in the transformation into tables (we will watch them die one by one in 04-03). That is why the usual practice is: think in Chen, draw in crow's foot and write down in text whatever the notation does not capture. It is exactly what we will do with BiblioRed.

Mermaid implements crow's foot. It is an excellent tool because the diagram lives as text in the repository, next to the code, and is versioned along with it. But it inherits the four limitations above and adds one of its own: it does not draw generalization hierarchies. When they show up, in section 8, we will explain them with tables and ASCII.

The basic ER model had no way of expressing that "a book is a type of material". The extended ER model (EER) adds type hierarchies, and that is exactly what R1 needs.

R1 says the catalog must accommodate books, DVDs, magazines and audiobooks. They all share title, language, publisher, year and the date they were added. Each one has its own: ISBN and pages for the book; duration, format and region for the DVD; ISSN, number and frequency for the magazine; duration, narrator and format for the audiobook.

Modeling it as four independent entities breaks everything else: a copy would have to point at one of the four and there would be no telling which; nor would a loan; and query Q10 ("the ten most borrowed materials, broken down by type") would need to union four queries. Modeling it as a single entity with all the attributes leaves narrator at NULL in 90% of the rows and makes it impossible to require a book to have an ISBN.

The solution is generalization: a super-entity MATERIAL with what is common and four sub-entities with what is specific.

Every hierarchy is characterized by two independent properties, and you have to pin them down explicitly because they change the design:

Axis 1 — Disjointness versus overlap

Axis 2 — Totality versus partiality

Combining them gives four cases, and each one is implemented differently in 04-03:

The material hierarchy is total and disjoint. It was discussed with the client and written down:

• Total: everything that gets cataloged is one of the four types. If maps or sheet music arrive tomorrow, a subtype is added. There are no "generic" materials.
• Disjoint: a specific material is a book or an audiobook, never both. The narrated version of "The Map of Time" is a different material, with its own identifier, not the same material with two natures. This question was asked explicitly and the answer steers the whole design.

A useful counterexample to see that it is not always like this: if BiblioRed decided to model PERSON as the super-entity of MEMBER and SPEAKER, that hierarchy would be overlapping (a member can run a workshop) and partial (there are people in the system who are neither one nor the other). In v1.0 it is not done: R7 says speakers are an entity in their own right, and duplicating the name of the four people who are both is a minor price compared with the complication of an overlapping hierarchy. It is a conscious decision and it goes into the decision log.

The three strategies for taking this hierarchy into tables —single table, table per subclass, table per concrete class— with their comparison table of pros and cons are section 10 of the next lesson.

Now we walk through the requirements document from 04-01 and build the diagram. The value of this section lies in the questions at each step, not in the result.

Seven entities from the current schema: BRANCH, AUTHOR, MEMBER, BOOK, COPY, LOAN, RESERVATION. They are not touched on a whim, but R1 forces a structural change: BOOK becomes a sub-entity of MATERIAL, and the relationships that pointed at BOOK (COPY and RESERVATION) now point at MATERIAL. It is the deepest change in the whole extension and it is worth identifying early.

• Entity? Yes: it has attributes of its own (capacity, floor, accessible) and a life cycle.
• Relationship with branch? BRANCH 1 — N ROOM.
• Participation? Total on both sides: every room is in a branch and every branch has between 1 and 6 rooms.
• Fine-grained decision: the name is unique only within the branch (R3). Conceptually, ROOM is a weak entity of BRANCH with name as its discriminant.

We apply the criteria from section 8 of 04-01: it has attributes of its own (description, standard duration), the user manages the set of values. Entity, with the relationship EVENT_TYPE 1 — N EVENT. Total participation on the event side (every event has a type), partial on the type side (there may be a freshly created type with no events).

Strong entity, with title, description, start_time, end_time, offered_seats, status, published.

• Relationship with ROOM: 1:N (one room, many events).
• The obligatory participation question: can there be an event with no room? This is where the coordinator mentioned the summer storytelling session in the courtyard. Decision: partial participation —the room is optional—, but BR8 requires that if the event is published, it must have a room. It is recorded as a business rule for 04-04.
• Derived attribute: the event's branch is the branch of its room (R4). It is not drawn as a relationship with BRANCH: that would be redundancy. It is noted as derived.
• Derived attribute: the free seats (Q2). Not stored either.

Here is the heart of the extension.

• Cardinality? A member for many events, an event with many members: N:M.
• Does it have attributes of its own? Yes: date, status, companions. N:M relationship with attributes.
• Identifier? The pair (event, member), which is literally the rule from R6 ("a member cannot register twice for the same event"). That is: a weak entity dependent on two owners.
• Participation? Partial on both sides.

This is the point where the conceptual model pays its price: in Chen it would be a diamond with three ovals hanging off it; in crow's foot you already have to draw it as an intermediate box. It is not a flaw in the notation, it is a preview of the transformation in 04-03.

• SPEAKER: strong entity (first name, last name, email, biography, whether external).
• Relationship with EVENT: N:M with attributes (role, fee).
• The trap: R7 says that in the same event a person may play more than one role. That means the pair (event, speaker) does not identify the participation: you need the role too. Conceptually it is a ternary relationship between EVENT, SPEAKER and ROLE.

Is ROLE a real entity? Applying the criteria from 04-01: a small set (moderator, workshop leader, guest author, presenter), stable, with no attributes of its own, nobody manages it. It is an attribute, not an entity. So instead of a pure ternary we have an N:M identified by (event, speaker, role), with role as part of the identifier. It is the usual way apparent ternaries get resolved in practice, and in 04-03 we will come back to it with rule 9.

A clean N:M between EVENT and MATERIAL, with one attribute of its own: role (main / recommended). Partial participation on both sides. It is the simplest N:M example in the diagram and serves as a contrast with the two previous ones.

• Cardinality 1:1: an event has at most one report, a report belongs to one event.
• Participation: partial on the event side (many events have no report), total on the report side.
• The obligatory question: why not put the report's three fields inside EVENT? Because they only have a value for events that have already been held; in EVENT they would be NULL in most rows and there would be no way to tell "not written yet" from "zero attendees". The complete discussion of the three options for a 1:1 is rule 6 of 04-03.

• FINE is related to LOAN: 1:N, because a loan can generate up to three fines, one per reason.
• A debatable and therefore interesting decision: is the fine also related to the member? The member can be obtained through the loan, so that would be derived. But R10 allows fines for loss that might not come from a loan (a member who loses a material consulted in the reading room). The decision is to keep the direct relationship with MEMBER and make the relationship with LOAN a partial participation one. It goes on record as a decision, with its reason.
• PAYMENT is a weak entity of FINE (1:N): a payment does not exist without its fine. Total participation on the payment side, partial on the fine side (a freshly issued fine has no payments).
• Derived attribute: the outstanding amount (R11). Not stored.

The two plurals in the statement of requirements become weak entities:

• PHONE weak of MEMBER, with number as discriminant and type as an attribute.
• SUBTITLE weak of DVD, with language as discriminant.

The branch's address is broken down into street, number, postal code and city. It generates neither an entity nor a relationship: it is a change inside the BRANCH box.

This is the result. It is in crow's foot notation with mermaid, it integrates what exists and what is new, and it comes with the annotations that the notation cannot express.

As we warned in section 7, there are four concepts that crow's foot does not represent. They go here, and they are as much part of the deliverable as the drawing:

A. The generalization hierarchy. The four MATERIALS ||--o| MATERIALS_* relationships are not four independent 1:1 relationships: they are a total and disjoint hierarchy. The real constraint, which no line expresses, is:

Every material belongs to exactly one of the four sub-entities, and the sub-entity must match the value of material_type.

B. The derived attributes. They deliberately appear in no box:

C. The weak entities. Mermaid draws ROOMS as a normal entity. The real statement is that a room's name is unique only within its branch, just as copy_number is within its material. Annotated in the box comments and picked up in 04-03.

D. Business rules BR1–BR10. None of them is drawable. They stay alive in the 04-01 document and become constraints in 04-04.

Before calling a conceptual model good, you walk through the list of queries in the requirements document and check that each one has a path in the diagram:

Twelve out of twelve. If any of them had failed, the diagram would be incomplete, and it is infinitely cheaper to find that out here than after writing the DDL.

A brief review; the detail is in lesson 09-03.

The selection criterion, more important than the tool: for the conceptual model, prioritize speed of change (paper, whiteboard, mermaid), because you are going to redraw it ten times. For the final diagram that accompanies the schema, prioritize that it lives next to the code and is versioned with it, because a diagram on somebody's hard disk is obsolete in three weeks.

Confusing cardinality with participation. Mistake number one. "It is 1:N" does not say whether the N side can be empty. The two questions are different and you have to ask both, always, for every relationship.

Putting the crow's foot symbol on the wrong side. The symbols go next to the entity they describe, counting how many instances of that entity there are for each one on the other side. In (min,max) notation it is the other way round. A diagram read backwards produces foreign keys on the wrong side, which is one of the most expensive mistakes to correct.

Modeling an N:M relationship without asking whether it has attributes. Almost all of them do. If REGISTRATIONS had been drawn as a plain N:M with no box, there would be nowhere to put the date, the status or the companions, and R6 would have been left uncovered.

Creating entities for things that are attributes. A languages table with two columns, a statuses table with four rows, a nationalities table... They multiply the JOINs without contributing anything. Apply the five criteria from 04-01 before creating each box.

Drawing redundant relationships. If EVENT → ROOM → BRANCH, drawing EVENT → BRANCH on top of that creates a cycle in which the two routes can give different answers. Every time a cycle shows up in the diagram, it has to be justified or removed: almost always one of the edges is derived.

Forgetting the time dimension. "A member belongs to a branch" is 1:N today. If you need to know which one they belonged to in 2024, it is an N:M with dates. Asking "do you need the history?" for each relationship costs five seconds.

Putting performance attributes into the conceptual model. registered_count in EVENTS is the answer to a problem that does not exist yet. In the conceptual model it goes in as derived; if measurement proves it is needed, it is stored in the physical phase with your eyes open (05-04).

Tip: name relationships with a verb and in a single direction. "Hosts", "generates", "is settled with". A diagram with unnamed relationships, or ones called "has", is a diagram you cannot read out loud, and reading it out loud is the best validation available.

Tip: draw it first without attributes. Ten boxes and the lines between them. The structure is much easier to see and the attributes, which are the easy part, are added afterwards.

Tip: count the lines coming out of each entity. An entity with seven relationships is usually doing two jobs and is a candidate for splitting. An entity with none is usually superfluous or badly connected.

For each statement, determine the cardinality and the participation of each side, and express it in crow's foot notation with mermaid (A ||--o{ B : "verb"). Justify the participation by citing the requirement.

1. A copy belongs to a branch; a branch holds many copies.
2. A payment settles a fine; a fine is settled with several payments.
3. An event type classifies many events; an event has one type.
4. A member takes out many loans; a loan belongs to one member.
5. An event has at most one report; a report belongs to one event.

BiblioRed adds the following requirement to v1.1:

R15 — Lending equipment to associations. Neighborhood associations (with a name, tax ID, contact person and phone number) can borrow technical equipment (projectors, speakers, screens). Each piece of equipment has an inventory code, a description, the branch where it is kept and its status. A lending docket can include several pieces of equipment at once, and has a checkout date, an expected return date and an actual date. If a piece of equipment comes back damaged, an incident is recorded with a date, a description and an estimated repair cost. A piece of equipment can have several incidents over its lifetime.

Draw the ER diagram fragment in mermaid. State for each relationship its cardinality and participation, point out whether there are weak entities, multivalued or derived attributes, and record at least two design decisions with their reason.

BiblioRed is considering unifying MEMBERS and SPEAKERS under a super-entity PEOPLE with the common data (first name, last name, email, phone).

1. Determine whether the hierarchy would be disjoint or overlapping and total or partial, justifying it with the requirements.
2. List two concrete advantages and two concrete drawbacks of doing it.
3. Decide whether you would do it in v1.0 and write the decision-log entry (decision / discarded alternative / reason).

Solution to Exercise 1

A note on case 5: o| at the right-hand end is what distinguishes an optional 1:1 from a mandatory 1:1, and it is precisely what will justify putting the report in a separate table in 04-03.

Solution to Exercise 2

• Weak entity: DOCKET_LINES, identified by (docket_id, equipment_id). It is the N:M between docket and equipment, and it appears because "a docket includes several pieces of equipment" (the plural in the statement).
• Multivalued attribute: none explicitly. If several contact phone numbers per association were requested, another one would appear.
• Derived attributes: days late on the docket (return_date − due_date), total incident cost for a piece of equipment.

Recorded decisions:

Solution to Exercise 3

1. Nature of the hierarchy.

• Overlapping: nothing stops a BiblioRed member from running a workshop. R7 explicitly says speakers can be "own staff or external professionals", and neither category rules out also being a member. A person could be a member and a speaker at the same time.
• Partial: if the super-entity PEOPLE also covered, for example, association contacts or administrative staff, there would be people who are neither members nor speakers. Even limiting it to the two current subtypes, it is partial in the sense that nothing guarantees every registered person is of one of the two types.

Therefore: a partial and overlapping hierarchy, the most flexible combination and also the most expensive to implement.

2. Advantages and drawbacks.

3. Decision for v1.0: do not do it. Log entry:

This last row illustrates the criterion from section 12.5 of lesson 04-01: generalization is correct from the theoretical point of view and over-engineering from the practical one. The design is not the purest model, but the one best suited to the real problem.

This lesson has turned fourteen requirements written in prose into a complete conceptual model.

• A conceptual model describes what exists and how it is related, without committing to any technology. That lets it be discussed with people outside IT, serve equally well for relational and document databases, and —most valuable of all— make visible the holes that prose tolerates.
• Entities are strong or weak. A weak entity cannot identify itself: it needs its owner's identity, and its identifier is partial. In BiblioRed the copies, the registrations, the phone numbers, the payments and the rooms are weak.
• Attributes come in five kinds and each one will be transformed differently: simple (a column), composite (several columns), multivalued (a separate table), derived (not stored) and identifier.
• Relationships are detected in the verbs. Their degree is almost always binary; reflexive ones need named roles and ternary ones almost always hide something simpler. The decisive point: relationships can have attributes of their own, and the infallible rule for spotting them is that they need both keys in order to have a value.
• Cardinality (1:1, 1:N, N:M) is determined with two questions, one per direction. Participation (total or partial) answers a different question —whether there can be instances that do not take part— and is an independent axis. Confusing them is the most frequent modeling mistake.
• Two notations are covered: Chen, more expressive and only viable in small domains, and crow's foot, the industry one, which loses four concepts (multivalued, derived and composite attributes and ternaries) precisely because they are about to disappear in the transformation into tables. The correct practice is to think in Chen, draw in crow's foot and write down in text whatever the drawing does not capture.
• The extended ER model contributes generalization/specialization, with two axes that must always be pinned down: disjoint or overlapping and total or partial. BiblioRed's material hierarchy is total and disjoint, and that decision conditions everything that comes afterwards.
• The BiblioRed diagram was built in eleven steps, with nine recorded decisions together with their reason and their discarded alternative.
• The deliverable is the complete ER diagram in mermaid, with twenty-one entities, plus the four annotations the notation cannot express: the hierarchy, the derived attributes, the weak entities and the ten business rules.
• The diagram was validated against the twelve queries in the requirements document, and all twelve have a path. That check is what separates a pretty drawing from a usable model.

We have the drawing, we have the annotations and we have the validation. What we still do not have is a single table. In the next lesson, 04-03 Transforming ER Diagrams into Relational Schemas, we will learn the algorithm that turns this diagram into CREATE TABLE: ten mechanical rules —strong entity, composite attribute, multivalued, derived, 1:N, 1:1, N:M, weak entity, ternary and hierarchy— each with its BiblioRed example and its SQL. That is where diamonds become foreign keys, multivalued attributes become tables and that total, disjoint material hierarchy is finally resolved by choosing between three strategies with very different consequences.