Entity-Relationship Diagrams: Module Summary and Design Checklist

1. Start with Meaning, Not Symbols

The introduction to diagram conventions established a distinction that applies throughout database design: business requirements determine the rules, and notation records them. Drawing two boxes and connecting them does not establish whether participation is mandatory, whether historical relationships must be retained, or what uniquely identifies each instance. Those decisions require definitions and evidence from the people who use the data.

As explained in the purpose of an ER diagram, an entity type describes a category, such as CUSTOMER. An instance is one particular customer. The entity set is the collection of instances at a given time. Sample rows help demonstrate a proposed rule, but they cannot prove the full rule. Two orders for one customer do not mean the business permits a maximum of two orders.

Keep the modeling level explicit. A conceptual model emphasizes business entities and relationships. A logical relational model refines attributes, identifiers, tables, and references. A physical design adds database-specific types, indexes, and implementation decisions. These levels can inform one another as requirements develop; they are not interchangeable labels for notation families. A Crow’s Foot diagram is not automatically a physical design.

2. Recognize the Notation Before Reading the Lines

Lesson 3 compared three notation families using one rule: an office may have zero or many employees, while every employee belongs to exactly one office. The drawings look different because their symbol systems differ. Translate the meaning rather than copying individual symbols from one notation into another.

• Crow’s Foot: endpoint combinations express minimum and maximum counts. A circle permits zero, a bar can represent one, and a three-pronged foot represents many. The role of a bar depends on its position within the endpoint.
• Chen: rectangles represent entities, diamonds represent relationships, and ovals can represent attributes. In the module’s convention, single and double connections express partial and total participation, while 1/N labels express maximum cardinalities.
• IDEF1X: key placement and relationship styles make identity dependency explicit. A solid identifying connection contributes the parent key to the child primary key; a dashed non-identifying connection does not.

The office example uses a mandatory non-identifying IDEF1X relationship. EMPLOYEE has its own identifier, but still requires an OFFICE reference. Identifier independence therefore does not mean that a parent is optional. The supplementary IDEF1X comparison expands on entity shapes, key dividers, child cardinality markers, and the parent-end diamond used for an optional non-identifying relationship.

Check the legend whenever you open an unfamiliar diagram. Colors, special corners, abbreviations, and line styles can include tool conventions. A clearly documented drawing is more useful than one that assumes everyone interprets every mark in the same way.

3. Separate Identity, Description, and Reference

Entities, attributes, and keys introduced STUDENT, CLASS, and STUDENT_CLASS. Student names describe students; a class name describes a class offering; an enrollment grade describes a particular student’s participation in that offering. Assigning each fact to the appropriate entity makes the model easier to explain and avoids copying descriptions into every relationship row.

A primary key identifies a row. A foreign key refers to an eligible key in another table, or sometimes in the same table. The same column can participate in both roles. In STUDENT_CLASS, StudID and ClassID together form one composite primary key, while each is also a separate foreign key. They are not two independent primary keys.

The pair must be unique, but either component may repeat. Student 101 can enroll in classes 501 and 502, and class 501 can contain students 101 and 102. Making either junction column individually unique would prohibit valid pairings. Conversely, adding a generated enrollment ID alone would not prevent duplicate student-class pairs; retain the appropriate business uniqueness constraint if duplicates are prohibited.

Define the scope of identifiers carefully. Here, CLASS means a particular offering. A later offering has a different ClassID. If the same student can make multiple enrollment attempts within the same offering, the pair no longer identifies every recorded attempt. That requirement changes the identity rule, not merely the display label on the diagram.

4. Read Maximum Cardinality and Minimum Participation Together

The relationship-types lesson distinguished 1:1, 1:N, and M:N. These labels summarize maximum cardinalities. They do not establish whether zero is permitted. A complete relationship statement needs the minimum as well as the maximum in each direction.

For Crow’s Foot notation, start with one instance and read the opposite endpoint to count its related instances. Starting with SUPPLIER, read the marks beside PRODUCT. Starting with PRODUCT, read the marks beside SUPPLIER. This simple procedure avoids reversing optionality when a drawing is rotated or rearranged.

The office examples deliberately use different business meanings. A shared workplace may contain many employees, whereas an individually assigned office may be limited to one employee. The words OFFICE and EMPLOYEE do not determine the relationship by themselves. Record the intended meaning before deciding whether the design is 1:1 or 1:N.

5. Know What the Constraints Actually Enforce

Mandatory and optional participation connected diagram rules to implementation. For a simple, single-column child reference, a foreign key requires a matching parent when a value is present. NOT NULL requires a value. Together they require every child row to identify an existing parent. They do not require every parent to have a child.

For the individual-office example, UNIQUE on the office’s employee reference prevents two office rows from referencing the same employee. Adding a foreign key and NOT NULL makes every office reference one existing employee while allowing an employee to have no office. A shared-primary-key child can express a similar maximum of one without requiring a child for every parent.

Some rules need additional enforcement. A foreign key from LineItems to Orders cannot guarantee that every submitted order contains a line item. The application and database design must enforce that transition appropriately, including concurrent changes. Distinguish an empty draft from a submitted order when the business permits drafts.

Deletion rules also need explicit decisions. Preventing deletion, cascading deletion, and setting a reference to null have different consequences. Setting a foreign key to null conflicts with a mandatory reference unless the business rule and column definition change. Database products differ in supported actions, so verify the chosen platform rather than assuming every SQL option is portable.

6. Resolve Many-to-Many Relationships at the Right Level

A direct M:N relationship is valid in a conceptual model. Students can attend many classes, and classes can contain many students. In the relational implementation, an associative table records the individual pairings through two one-to-many relationships. Each enrollment references exactly one student and one class, even when either parent has no enrollments yet.

Relationship attributes belong with the association when they describe that pairing. Grade and EnrolledOn describe an enrollment, not a student independently of a class. Similarly, a quantity and selling price belong to an order line because they describe a product within one sale. A reusable catalog description belongs with the catalog item.

Choosing a composite or surrogate primary key does not remove the need to define valid duplicates. Ask whether one pair can recur, whether the association has a history, and whether other tables must reference a particular occurrence. Then select keys and uniqueness rules that preserve the agreed meaning.

7. Review the Model with the People Who Use the Data

The end-user review tests business objects, business rules, and information needs. Invite representative everyday users as well as specialists and decision makers. A manager may describe the intended process while staff reveal exceptions, delayed information, and workarounds that affect what the database must record.

Use concrete scenarios. Ask whether a customer can register before placing an order, whether an order can lack a customer, and whether a past delivery address must remain available after an address change. These are separate questions. Guest checkout does not necessarily mean an order has no customer record; it may mean that the buyer has no login account.

Record each decision with its rationale, responsible person, validation example, and approval status. When customers can exist without orders, change the minimum number of orders per customer to zero. Do not automatically make the order’s customer foreign key nullable: every order may still require a customer.

A conceptual review can establish whether the model represents the information needed for reports and transactions. It cannot prove that queries will execute quickly. Performance requires a later implementation, representative data, workload testing, and suitable physical design. Treat the review as a checkpoint, with further user involvement when assumptions change.

8. Verbalize the Stories on CD Model

Verbalization turns the drawing into statements that stakeholders can challenge. Use business verbs and explicit counts: exactly one, zero or one, one or more, or zero or more. Write both directions for every relationship. “Customers have orders” is too vague to determine whether either side is mandatory.

Under the lesson’s assumptions, every order belongs to exactly one customer, while a customer may have zero or many orders. Every line item references one order and one CD. A submitted order must contain at least one line item; a CD may appear in no sales yet. Each CD has one distributor in this simplified model, although a business that sources CDs from several distributors would need a different relationship.

The line-item key (OrderNo, CDNo) permits one line for a particular CD within an order. Quantity records several copies on that line. If separate lines for the same CD are needed, perhaps because prices or delivery arrangements differ, an order-scoped line number may provide a more appropriate identifier. This is a business decision with schema consequences.

Historical facts need equal care. SellingPrice records the price agreed for the sale and should not change merely because RetailPrice changes later. A yes/no Shipped attribute may be adequate for an all-or-nothing line shipment, but partial shipments require additional detail. Clear sentences expose these assumptions before a convenient shorthand becomes an inadequate implementation.

Example ERD: Review a Publishing Model Critically

Begin with Publisher and Edition. The diagram places publisher_id and publication_id in Edition, alongside ISBN, print_date, and format. It distinguishes a publication from an edition of that publication. Therefore, describing this picture simply as “Publisher has Books” would obscure its actual structure. Ask whether ISBN is always available and appropriate as the edition identifier in the intended catalog.

Next inspect Author, Publication, and CoAuthor. Publication includes author_id, while CoAuthor contains coauthor_id and publication_id. This could distinguish a principal author from additional contributors, but the drawing alone does not establish that meaning. Confirm whether the direct author reference is intentional, whether contributor roles or sequence must be recorded, and what prevents duplicate associations.

Review contains review_id in its key area and another review_id labeled FK below it. The intended reference is unclear; do not silently assume it means reviewer_id or invent a missing reviewer entity. Subject includes parent_id, suggesting a hierarchy, but the relationship connector is cropped at the image boundary. Its exact participation should be confirmed from a complete diagram and documented rules.

These limitations illustrate an important review skill: distinguish what is shown, what you infer, and what remains unknown. A polished caption cannot repair an ambiguous model. Capture the questions, obtain decisions, and revise the source diagram before using it as an implementation specification.

Carry the Model into Normalization and Physical Design

A readable ERD is not proof that a schema is normalized. Review functional dependencies as the logical tables take shape. A student name depends on the student identifier, while an enrollment grade depends on the enrollment. Repeating the student name in every enrollment row creates opportunities for inconsistent updates without adding a new enrollment fact.

Normalization and relationship modeling support each other. Separating independently described facts can reveal missing entities; clarifying identity can reveal whether an attribute depends on a whole composite key. Avoid changing the design solely to make the picture look simpler. Preserve the facts and dependencies required by the business, then evaluate performance with evidence.

Prepare a handoff that includes the diagram, glossary, key definitions, relationship statements, unresolved issues, and approved exceptions. Match every important rule to an implementation mechanism or an explicit follow-up task. A diagram label reading FK is documentation; the corresponding database constraint must still be declared and tested.

Final Design Checklist

1. Scope: each entity has a purpose, and the model states whether it is conceptual, logical, or physical.
2. Identity: primary and alternate keys represent agreed uniqueness rules; composite-key components are clearly distinguished.
3. Relationships: both directions have explicit minimum and maximum counts, using one consistent notation.
4. Associations: relational M:N implementations preserve valid pairings and store relationship attributes in the appropriate place.
5. Constraints: required references, uniqueness, allowed values, and lifecycle rules have identified enforcement strategies.
6. History: the design preserves past facts needed by reports, transactions, and users.
7. Validation: ordinary cases and counterexamples have been reviewed, and unresolved questions have owners.
8. Consistency: the diagram, verbal statements, schema, and change log describe the same approved rules.

Check Your Understanding

Can a supplier have no products while every product requires a supplier? Yes. These are opposite directions of one relationship. A non-null foreign key on PRODUCT does not require a product row for every supplier.

Does an independent employee key make an office assignment optional? No. Key dependency and participation answer different questions. An employee can have an independent primary key and a mandatory office foreign key.

Does a clear sentence prove the drawing is correct? No. Compare the sentence with the symbols, sample cases, and stakeholder decisions. Clear verbalization makes a rule easier to test; it does not replace testing.

You have reached the conclusion of Module 7. Use the linked lessons to revisit any uncertain convention, then apply the checklist to the Stories on CD model or a model of your own. A useful ERD is one whose meaning can be explained, challenged, revised, and implemented consistently.