Activity 9: Create Your First ER Diagram

🌍 Real-World Scenario

The Challenge: FoodRush, a food delivery startup, needs to design their first database. They need to store information about customers, restaurants, and orders. Your job is to create an ER (Entity-Relationship) diagram to visualize how these entities relate to each other.

Learning Goal: Master the basics of ER diagram creation and understand entity relationships.

🎯 Your Mission

Create a simple ER diagram for a food delivery system with three main entities: Customer, Restaurant, and Order.

📋 What You'll Learn

• How to identify entities and their attributes
• Understanding different types of relationships (One-to-Many, Many-to-Many)
• Creating ER diagrams using simple tools or pen and paper

💡 Tip: For advanced data modeling theory, Docker deployment, and production-scale ER diagrams, see Reference/Database-Theory/Data-Modeling-Theory.mdx

📊 Step One: Understand the Business Requirements (10 min)

FoodRush needs to track the following information:

About Customers:

• Customer ID (unique identifier)
• Customer name
• Email address
• Phone number
• Delivery address

About Restaurants:

• Restaurant ID (unique identifier)
• Restaurant name
• Cuisine type (Italian, Chinese, etc.)
• Address
• Phone number

About Orders:

• Order ID (unique identifier)
• Which customer placed the order
• Which restaurant fulfilled the order
• Order date and time
• Total amount
• Order status (pending, preparing, delivered)

🔍 Step 2: Identify Entities and Attributes (15 min)

Let's break down our business requirements into database entities:

Entity 1: CUSTOMER

• customer_id (Primary Key) - Unique identifier
• name - Customer's full name
• email - Email address
• phone - Phone number
• address - Delivery address

Entity 2: RESTAURANT

• restaurant_id (Primary Key) - Unique identifier
• name - Restaurant name
• cuisine_type - Type of food (Italian, Chinese, etc.)
• address - Restaurant location
• phone - Contact number

Entity 3: ORDER

• order_id (Primary Key) - Unique identifier
• customer_id (Foreign Key) - Links to Customer
• restaurant_id (Foreign Key) - Links to Restaurant
• order_date - When the order was placed
• total_amount - Total cost
• status - Current order status

🔗 Step 3: Identify Relationships (10 min)

Now let's determine how these entities relate to each other:

1. Customer to Order Relationship

   • One customer can place many orders
   • Each order belongs to exactly one customer
   • Relationship Type: One-to-Many (1:M)

2. Restaurant to Order Relationship

   • One restaurant can fulfill many orders
   • Each order is fulfilled by exactly one restaurant
   • Relationship Type: One-to-Many (1:M)

3. Customer to Restaurant Relationship

   • Customers and restaurants are connected through orders
   • No direct relationship - they connect via the Order entity

📐 Step 4: Draw Your ER Diagram (15 min)

Create your ER diagram using these symbols:

Rectangles = Entities (Customer, Restaurant, Order) Ovals = Attributes (name, email, phone, etc.) Diamonds = Relationships (Places Order, Fulfills Order) Lines = Connect entities to relationships

Simple Text-Based ER Diagram:

CUSTOMER                    ORDER                    RESTAURANT
┌─────────────┐            ┌─────────────┐            ┌─────────────┐
│ customer_id │◄─────────┐ │  order_id   │ ┌─────────►│restaurant_id│
│    name     │          │ │customer_id  │ │          │    name     │
│   email     │          └─│restaurant_id│─┘          │cuisine_type │
│   phone     │            │ order_date  │            │  address    │
│  address    │            │total_amount │            │   phone     │
└─────────────┘            │   status    │            └─────────────┘
                           └─────────────┘

        │                         │                         │
        │                         │                         │
        ▼                         ▼                         ▼
   "Places"                  "Connects"               "Fulfills"
   Relationship              Customers &              Relationship
   (1:M)                    Restaurants               (1:M)

Using Online Tools:

You can also create ER diagrams using:

• Draw.io (free online tool)
• Lucidchart (web-based diagramming)
• dbdiagram.io (database-specific ER diagrams)

✏️ Step 5: Create SQL Tables from Your ER Diagram (15 min)

Transform your ER diagram into actual database tables:

-- Create Customer table
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    address TEXT
);

-- Create Restaurant table
CREATE TABLE restaurants (
    restaurant_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    cuisine_type VARCHAR(50),
    address TEXT,
    phone VARCHAR(20)
);

-- Create Order table with foreign key relationships
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    restaurant_id INTEGER REFERENCES restaurants(restaurant_id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'pending'
);

Notice how:

• PRIMARY KEY constraints ensure unique identifiers
• FOREIGN KEY constraints enforce relationships
• REFERENCES links tables together

📝 Step 6: Add Sample Data to Test Relationships (10 min)

Insert sample data to verify your design works:

-- Insert sample customers
INSERT INTO customers (name, email, phone, address) VALUES
('John Smith', 'john@email.com', '555-1234', '123 Main St'),
('Alice Johnson', 'alice@email.com', '555-5678', '456 Oak Ave');

-- Insert sample restaurants
INSERT INTO restaurants (name, cuisine_type, address, phone) VALUES
('Pizza Palace', 'Italian', '789 Food St', '555-PIZZA'),
('Burger Barn', 'American', '321 Eat Ave', '555-BURG');

-- Insert sample orders (linking customers and restaurants)
INSERT INTO orders (customer_id, restaurant_id, total_amount, status) VALUES
(1, 1, 25.99, 'delivered'),  -- John ordered from Pizza Palace
(2, 1, 18.50, 'preparing'),  -- Alice ordered from Pizza Palace
(1, 2, 12.75, 'pending');    -- John ordered from Burger Barn

-- Test relationships with queries
SELECT 
    c.name AS customer_name,
    r.name AS restaurant_name,
    o.total_amount,
    o.status
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN restaurants r ON o.restaurant_id = r.restaurant_id;

🔄 Step 7: Verify Your Design with Business Questions (5 min)

Test your ER diagram by answering these business questions:

1. How many orders has John Smith placed?

SELECT COUNT(*) 
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.name = 'John Smith';

2. Which restaurants have received orders?

SELECT DISTINCT r.name
FROM restaurants r
JOIN orders o ON r.restaurant_id = o.restaurant_id;

3. What's the total revenue for Pizza Palace?

SELECT SUM(o.total_amount) as total_revenue
FROM orders o
JOIN restaurants r ON o.restaurant_id = r.restaurant_id
WHERE r.name = 'Pizza Palace' AND o.status = 'delivered';

If you can answer these questions easily, your ER diagram is working correctly!

🏆 ER Diagram Checklist

✅ Entities Identified: Customer, Restaurant, Order ✅ Attributes Defined: Each entity has appropriate attributes ✅ Primary Keys: Each entity has a unique identifier ✅ Relationships Mapped: One-to-Many relationships established ✅ Foreign Keys: Links between tables created ✅ Business Rules Enforced: Design supports business requirements

🔍 Key Takeaways

1. Entities: Represent real-world objects (Customer, Restaurant, Order)
2. Attributes: Properties of entities (name, email, phone)
3. Primary Keys: Unique identifiers for each entity
4. Foreign Keys: Create relationships between entities
5. Relationships: Define how entities connect (One-to-Many, Many-to-Many)

💡 Common ER Diagram Patterns

• One-to-Many (1:M): One customer places many orders
• Many-to-Many (M:M): Many customers can rate many restaurants (requires junction table)
• One-to-One (1:1): One customer has one loyalty account

❓ What's Next?

To extend this ER diagram, you could add:

• Menu Items: What food does each restaurant serve?
• Order Items: What specific items are in each order?
• Delivery Drivers: Who delivers the orders?
• Reviews: Customer ratings for restaurants

✅ Activity Complete

Congratulations! You've created your first ER diagram and transformed it into working database tables. You understand how entities relate to each other and can create foreign key relationships to maintain data integrity.

This foundation prepares you for more complex database designs with multiple entities and relationships.

📚 Theory Reference: For advanced data modeling concepts, Docker deployment strategies, automated ER diagram generation, and production-scale database architecture, see Reference/Database-Theory/Data-Modeling-Theory.mdx

📤 Submit Your Work

Complete this activity and submit your work through the Activity Submission Form