Entity-Relationship Diagram Conventions
An entity-relationship diagram (ERD) describes the kinds of things a system records, their attributes, and the rules connecting them. Diagram conventions give those rules a readable form: entity names, attribute lists, relationship labels, and symbols for how many instances may participate.
This module builds on the relationships introduced in Module 6. You will learn to read and construct ER diagrams, explain their meaning in ordinary language, and revise them when business requirements change. The course project applies these skills to Stories on CD, Inc.
Lesson objective: Explain what ER diagram conventions communicate and distinguish business rules from the notation used to represent them.
Begin with the Business Rule
Before choosing symbols, state what the database must represent. For example: a customer may place zero or many orders, and every order must belong to exactly one customer. Reading the relationship in both directions identifies two separate rules.
| Element | Question | Example |
|---|---|---|
| Entity type | What kind of thing do we record? | CUSTOMER and CUSTOMER_ORDER |
| Attribute | What fact describes an instance? | Customer name or order date |
| Identifier | How do we distinguish instances? | CustomerID or OrderID |
| Relationship | How are instances associated? | A customer places an order |
| Maximum cardinality | At most how many may be related? | Many orders per customer; one customer per order |
| Minimum participation | Is participation required? | Zero orders permitted for a customer; a customer required for every order |
A box labeled CUSTOMER represents an entity type, not one particular customer. Likewise, a relationship line states a rule for all permitted instances; it does not show only the rows currently stored.
Keep Cardinality and Participation Distinct
The familiar labels 1:1, 1:N, and M:N describe maximum cardinalities. They do not, by themselves, tell you whether participation is optional or mandatory. Minimum and maximum counts together distinguish zero or one, exactly one, zero or many, and one or many.
In the customer-order example, the relationship is 1:N even when a particular customer has no orders. Requiring a customer for every order does not require every customer to place an order. These are rules in opposite directions.
Use One Notation Consistently
ER diagrams can use different notation families, including Chen, Crow's Foot, and IDEF1X. Their symbols are not interchangeable. This module emphasizes Crow's Foot notation, while introducing other conventions so you can recognize their differences.
In common Crow's Foot notation, entities appear as boxes and attributes are listed inside them. Relationship endpoints combine a circle or bar for minimum participation with a bar or crow's foot for the maximum. Read the symbols at an endpoint as the number of instances at that end associated with one instance at the opposite end.
Always check the diagram's legend. Tool-specific line styles, key markers, and identifying-relationship symbols may differ. An identifying relationship concerns whether a parent's identifier contributes to the child's identifier; it is not another name for mandatory participation.
Conceptual, Logical, and Physical Detail
Conceptual modeling captures the meaning of the data before selecting implementation details. An ER diagram can support different levels of design:
- Conceptual: identify business entities, important relationships, and participation rules.
- Logical: refine attributes and identifiers and map relationships into a relational schema, including foreign keys and associative tables.
- Physical: choose database-specific types, indexes, and other implementation details.
The level of detail is separate from the notation family. A Crow's Foot diagram is not automatically a physical model. A direct M:N relationship can be valid in a conceptual diagram; its relational implementation normally introduces an associative table with two 1:N relationships.
A diagram documents intended rules, but does not enforce them by itself. Implementation requires appropriate constraints. Some rules, such as requiring every parent to have at least one child, need more than a foreign key and NOT NULL on the child column.
Module Objectives
By the end of this module, you should be able to:
- Explain the purpose and scope of an ER diagram.
- Recognize different notation families and use a consistent legend.
- Diagram entities, attributes, and identifiers.
- Represent 1:1, 1:N, and M:N relationships with explicit minimum participation.
- Read each relationship in both directions and express it as business rules.
- Review a diagram with stakeholders and revise it when requirements change.
- Apply these conventions to the Stories on CD course project.
Before moving on: Can a customer have no orders while every order requires a customer? Yes: optional participation on one side can coexist with mandatory participation on the other.
The next lesson explains the purpose of an ER diagram and develops the customer-order example.