Entity-Relationship Diagrams for Database Design

An entity-relationship diagram (ERD) is the primary blueprint for a relational database. It shows entities (things the business tracks), their attributes (descriptors), and the relationships among those entities. This lesson sets up the concepts you’ll use throughout the module to read, reason about, and draw ERDs correctly.

After completing this lesson, you will be able to:

1. Define an entity relationship and explain why ERDs matter.
2. Describe the three relationship types: one-to-one, one-to-many, many-to-many.
3. Identify relationship participation (mandatory vs. optional).
4. Recognize common ERD notations (Chen, Crow’s Foot/IDEF1X) and how to read their symbols.

• Entity: A business object or concept (e.g., Customer, Order). In tables, entities become tables; entity instances become rows.
• Attribute: A property of an entity (e.g., CustomerName, OrderDate). In tables, attributes become columns.
• Relationship: A meaningful association between entities (e.g., “Customer places Order”). Relationships are realized with keys/foreign keys in tables.
• Cardinality: How many instances can participate (1:1, 1:M, M:N).
• Participation (optional vs. mandatory): Whether an instance must participate in a relationship (mandatory) or may participate (optional).

• One-to-One (1:1): One instance of A relates to at most one instance of B, and vice versa.
  Use sparingly. Often indicates you have split a table for security/NULL-reduction or are modeling rare extensions.
• One-to-Many (1:M): One instance of A can relate to many instances of B; each B relates to one A.
  Most common. Implemented with a foreign key on the “many” side.
• Many-to-Many (M:N): Many A relate to many B.
  Not stored directly in tables. Resolve with an associative (bridge) entity that breaks M:N into two 1:M relationships.

Participation answers “Must this entity participate in this relationship?”

• Mandatory: Every instance must participate (e.g., every Order must have a Customer).
• Optional: Some instances may not participate (e.g., a Customer may have zero Orders).

Participation decisions influence NULL allowances, foreign-key constraints, and application rules.

ERDs can be drawn in several notations. Two you will see in this course:

• Chen: Entities as rectangles; attributes as ovals; relationships as diamonds.
• Crow’s Foot (IDEF1X): Compact, implementation-friendly. “Crow’s foot” indicates “many”; a single bar indicates “one”. See: Chen and Crow’s Foot Notation and Crow’s Foot Notation guide.

Tip: Regardless of notation, always read a relationship by naming the entities, the verb phrase, the cardinality (1 or many), and whether participation is optional or mandatory on each side.

1. Each entity → a table with a primary key (the entity identifier).
2. Each 1:M relationship → a foreign key on the “many” side.
3. Each M:N relationship → an associative table with foreign keys to both parents (and any attributes of the relationship itself, e.g., EnrollmentDate).
4. Optional participation → a nullable foreign key (unless business rules require NOT NULL).
5. Mandatory participation → a NOT NULL foreign key and/or additional constraints.

• Modeling collections (e.g., “Inventory”, “Medical History”) as a single entity—these are outputs from multiple entities/relationships.
• Leaving M:N relationships unresolved—always introduce a bridge entity before implementation.
• Ignoring participation—forgetting to mark optional vs. mandatory leads to incorrect NULL rules and integrity gaps.

Up next: We define entity relationships formally and practice reading cardinality and participation on real ERD fragments.