Entity Relationship Diagrams (ERD) and Crow's Foot Notation

What is an ERD?

An Entity Relationship Diagram (ERD) is a visual representation of the data structures and relationships within a database. ERDs are used in the conceptual design phase to model how data entities relate to each other before implementing the physical database.

ERDs help database designers and stakeholders understand the database structure at a high level, making it easier to identify requirements, relationships, and potential issues before coding begins.

Crow's Foot Notation

Crow's Foot notation (also called IE notation) is a popular ERD notation style that uses specific symbols to represent relationship cardinality and participation. The name comes from the three-pronged "crow's foot" symbol used to represent "many" in relationships.

Crow's Foot notation is widely used because it's intuitive and clearly shows:

• Entity types (tables)
• Attributes (columns)
• Relationships between entities
• Cardinality (one-to-one, one-to-many, many-to-many)
• Participation (optional vs. mandatory)

ERD Components

1. Entities

An entity represents a real-world object, person, place, or concept that can be distinctly identified. In an ERD, entities are shown as rectangles.

┌─────────────┐
│   Customer  │
└─────────────┘

2. Attributes

Attributes are properties or characteristics of entities. In Crow's Foot notation, attributes are listed inside or connected to the entity rectangle.

┌─────────────┐
│   Customer  │
├─────────────┤
│ customer_id │ (PK)
│ name        │
│ email       │
│ phone       │
└─────────────┘

Key Attributes:

• Primary Key (PK): Unique identifier for each entity instance
• Foreign Key (FK): Attribute that references another entity

3. Relationships

A relationship represents how entities are associated with each other. Relationships are shown as lines connecting entities.

Crow's Foot Symbols

Crow's Foot notation uses specific symbols to indicate cardinality:

Cardinality Symbols

One (1)

A straight line | or ─ indicates "exactly one" or "one".

Many

A crow's foot ┼ (three lines) indicates "many" or "zero or more".

Zero or One (Optional)

A circle ○ indicates "zero or one" (optional participation).

One or More (Mandatory)

A perpendicular line | indicates "one or more" (mandatory participation).

Relationship Types

One-to-One (1:1)

Each entity instance in the first entity is related to exactly one instance in the second entity, and vice versa.

┌──────┐         ┌─────────┐
│ User │◄─────►│ Profile │
└──────┘   1:1  └─────────┘

Symbol: |───| (one on both sides)

Example: Each user has exactly one profile, and each profile belongs to exactly one user.

One-to-Many (1:M)

One entity instance can be related to many instances in the second entity, but each instance in the second entity relates to only one instance in the first.

┌──────────┐         ┌──────────┐
│Department│         │ Employee │
└──────────┘   1:M   └──────────┘
      |                    ▲
      └────────────────────┘

Symbol: |───┼ (one on left, many on right)

Example: One department has many employees, but each employee belongs to one department.

Many-to-Many (M:N)

Many instances of one entity can relate to many instances of another entity.

┌─────────┐         ┌────────┐
│ Student │         │ Course │
└─────────┘    M:N  └────────┘
      ┼                  ┼
      └──────────────────┘

Symbol: ┼───┼ (many on both sides)

Note: Many-to-many relationships typically require an intermediate table (junction table) in relational databases.

Participation (Optional vs. Mandatory)

Mandatory Participation

Indicated by a perpendicular line | - every instance of the entity must participate in the relationship.

┌──────────┐         ┌──────────┐
│Department│         │ Employee │
└──────────┘         └──────────┘
      |                    |
      └────────────────────┘
      (mandatory on both sides)

Optional Participation

Indicated by a circle ○ - an entity instance may not participate in the relationship.

┌──────────┐         ┌────────┐
│ Customer │         │ Order  │
└──────────┘         └────────┘
      ○                    |
      └────────────────────┘
      (optional customer, mandatory order)

Complete ERD Example

Here's a complete ERD example for a simple e-commerce system using Crow's Foot notation:

┌──────────┐                    ┌────────┐
│ Customer │                    │ Product│
└──────────┘                    └────────┘
      │                              │
      │ 1                            │ 1
      │                              │
      │                              │
      ┼                              ┼
      │                              │
      └──────────────┬───────────────┘
                     │ M
                     │
                ┌─────────┐
                │ Order   │
                ├─────────┤
                │order_id │ (PK)
                │date     │
                │status   │
                └─────────┘
                     │ 1
                     │
                     │
                     ┼ M
                     │
                ┌────────────┐
                │OrderItem   │
                ├────────────┤
                │order_id    │ (FK)
                │product_id  │ (FK)
                │quantity    │
                │price       │
                └────────────┘

Relationships Explained:

• Customer ──< Order: One-to-Many - One customer can have many orders
• Product ──< OrderItem: One-to-Many - One product can be in many order items
• Order ──< OrderItem: One-to-Many - One order contains many order items

Converting ERD to Database Tables

When converting an ERD to database tables:

1. Each entity becomes a table
2. Each attribute becomes a column
3. Primary keys are identified and marked
4. Foreign keys are added to establish relationships
5. Many-to-Many relationships require junction tables

-- ERD Entity
┌──────────┐
│ Customer │
├──────────┤
│customer_id │ (PK)
│name        │
│email       │
│phone       │
└──────────┘

-- Database Table
CREATE TABLE Customer (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20)
);

Best Practices for ERD Design

• Use clear entity names: Nouns that represent real-world objects
• Define relationships clearly: Use descriptive relationship names
• Show all attributes: Include all important attributes, especially keys
• Indicate cardinality: Always show relationship cardinality
• Mark participation: Show whether participation is optional or mandatory
• Avoid redundancy: Each entity should represent a distinct concept
• Normalize relationships: Ensure relationships are properly defined

Next Steps

Now that you understand ERDs and Crow's Foot notation, you're ready to learn more about Entity-Relationship Modeling techniques and Database Design processes.