Skip to main content

Course Progress

Loading...

Entity-Relationship Diagrams

Duration: 60 minutes
Module 3: Session 2

Learning Objectives

  • Understand ER diagram notation and symbols
  • Learn to create professional ER diagrams
  • Master Chen and Crow's Foot notations
  • Interpret cardinality and participation
  • Convert ER diagrams to database schemas

What are ER Diagrams?

Entity-Relationship (ER) diagrams are visual tools that show how entities relate to each other in a database. Think of them as the blueprint of your database! 🏗️

💡
Why ER Diagrams Matter
ER diagrams help you:
  • Visualize database structure before coding
  • Communicate design with team members
  • Identify missing relationships
  • Document your database design

ER Diagram Notations

Chen Notation (Traditional)

Crow's Foot Notation (Modern)

Cardinality and Participation

graph TB subgraph "Cardinality Examples" A[One-to-One
1:1] B[One-to-Many
1:N] C[Many-to-Many
M:N] end A --> D[Person ↔ Passport] B --> E[Department ↔ Employees] C --> F[Students ↔ Courses] style A fill:#dbeafe style B fill:#dcfce7 style C fill:#fef3c7

Understanding Participation

Creating an ER Diagram

Step-by-Step Process

  1. Identify Entities:Find the main objects (nouns)
  2. Identify Attributes:List properties of each entity
  3. Identify Relationships:How do entities interact?
  4. Determine Cardinality:One-to-one, one-to-many, or many-to-many?
  5. Determine Participation:Total or partial?
  6. Add Keys:Primary keys and foreign keys
erDiagram CUSTOMER ||--o{ ORDER : places ORDER ||--|{ ORDER_ITEM : contains PRODUCT ||--o{ ORDER_ITEM : "ordered in" CUSTOMER { int customer_id PK string name string email string phone } ORDER { int order_id PK int customer_id FK date order_date decimal total } ORDER_ITEM { int order_id FK int product_id FK int quantity decimal price } PRODUCT { int product_id PK string name decimal price int stock }

Converting ER to Database Schema

From Diagram to SQL

-- Step 1: Create tables for entities
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20)
);

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0
);

-- Step 2: Create tables with foreign keys for relationships
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    total DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Step 3: Create junction table for many-to-many relationships
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);
✅
Conversion Rules
  • Eachentitybecomes a table
  • Eachattributebecomes a column
  • One-to-many: Add foreign key to "many" side
  • Many-to-many: Create junction table
  • One-to-one: Add foreign key to either side (with UNIQUE)

Practice: Create an ER Diagram

💻
Exercise
Design an ER diagram for a Library System with:
  • Books (ISBN, title, publication_year)
  • Authors (author_id, name, nationality)
  • Members (member_id, name, email, join_date)
  • Loans (loan_id, loan_date, return_date)
Consider: Books can have multiple authors, members can borrow multiple books
💡 Click for Solution
erDiagram AUTHOR ||--o{ BOOK_AUTHOR : writes BOOK ||--o{ BOOK_AUTHOR : "written by" BOOK ||--o{ LOAN : "is loaned" MEMBER ||--o{ LOAN : borrows AUTHOR { int author_id PK string name string nationality } BOOK { string isbn PK string title int publication_year } MEMBER { int member_id PK string name string email date join_date } LOAN { int loan_id PK string isbn FK int member_id FK date loan_date date return_date } BOOK_AUTHOR { string isbn FK int author_id FK }

Additional Resources