An online food delivery system connects customers with restaurants, allowing them to browse menus, place orders, and have food delivered to their doorsteps. Efficient database design ensures that these operations are streamlined, scalable, and reliable.

In this case study, we’ll model an online food delivery system using Entity-Relationship (ER) diagrams and then map it to a relational schema.

Requirements Analysis

For our case study, let's outline the key functionalities that the HMS should support:

• Customer Management: Storing customer details and their delivery addresses.
• Restaurant Management: Managing restaurant details, menus, and ratings.
• Order Processing: Handling orders, payments, and delivery.
• Delivery Tracking: Tracking delivery statuses and assigning delivery personnel.
• Reviews and Feedback: Allowing customers to leave reviews for restaurants.
• Promotions: Managing discounts and promotional offers.

Step-by-Step ER Diagram Creation

We will build the ER diagram for the Hospital Management System through the following four steps.

Step 1: Identify Entities

Entities represent objects or concepts in the system that have data stored about them.

1. Customer
2. Address
3. Restaurant
4. Menu
5. Menu_Item
6. Order
7. Order_Item
8. Payment
9. Delivery_Person
10. Delivery
11. Review
12. Category
13. Coupon

Step 2: Detailing Attributes

1. Customer

• Customer_ID (PK)
• Full_Name
• Email
• Phone_Number
• Password
• Date_Joined

2. Address

• Address_ID (PK)
• Customer_ID (FK)
• Street
• City
• Postal_Code
• Address_Type (Home, Work, etc.)

3. Restaurant

• Restaurant_ID (PK)
• Name
• Location
• Contact_Number
• Rating

4. Menu

• Menu_ID (PK)
• Restaurant_ID (FK)

5. Menu_Item

• Item_ID (PK)
• Menu_ID (FK)
• Name
• Description
• Price
• Category_ID (FK)

6. Category

• Category_ID (PK)
• Name
• Description

7. Order

• Order_ID (PK)
• Customer_ID (FK)
• Address_ID (FK)
• Order_Date
• Total_Amount
• Status (Pending, Confirmed, Delivered, Cancelled)

8. Order_Item

• Order_Item_ID (PK)
• Order_ID (FK)
• Item_ID (FK)
• Quantity
• Subtotal

9. Payment

• Payment_ID (PK)
• Order_ID (FK)
• Payment_Date
• Payment_Method (Credit Card, Cash, etc.)
• Payment_Status (Paid, Pending, Failed)

10. Delivery_Person

• Delivery_Person_ID (PK)
• Full_Name
• Phone_Number
• Vehicle_Details

11. Delivery

• Delivery_ID (PK)
• Order_ID (FK)
• Delivery_Person_ID (FK)
• Delivery_Status (Pending, In Transit, Delivered)
• Delivery_Date

12. Review

• Review_ID (PK)
• Customer_ID (FK)
• Restaurant_ID (FK)
• Rating
• Review_Text
• Review_Date

13. Coupon

• Coupon_ID (PK)
• Code
• Discount_Percentage
• Expiry_Date
• Maximum_Usage

Step 3: Defining Relationships

1. Customer and Address

• A customer can have multiple addresses.
• Each address belongs to one customer.
• Type: One-to-Many (Customer to Address)

2. Restaurant and Menu

• A restaurant can have one menu.
• A menu belongs to one restaurant.
• Type: One-to-One (Restaurant to Menu)

3. Menu and Menu_Item

• A menu can have multiple items.
• Each item belongs to one menu.
• Type: One-to-Many (Menu to Menu_Item)

4. Customer and Order

• A customer can place multiple orders.
• Each order belongs to one customer.
• Type: One-to-Many (Customer to Order)

5. Order and Order_Item

• An order can contain multiple items.
• Each order item belongs to one order and one menu item.
• Type: One-to-Many (Order to Order_Item)

6. Order and Payment

• An order can have one payment.
• Each payment is linked to one order.
• Type: One-to-One (Order to Payment)

7. Order and Delivery

• An order can have one delivery.
• Each delivery is linked to one order.
• Type: One-to-One (Order to Delivery)

8. Delivery and Delivery_Person

• A delivery is assigned to one delivery person.
• A delivery person can handle multiple deliveries.
• Type: One-to-Many (Delivery_Person to Delivery)

9. Restaurant and Review

• A restaurant can have multiple reviews.
• Each review belongs to one restaurant.
• Type: One-to-Many (Restaurant to Review)

10. Customer and Review

• A customer can leave multiple reviews.
• Each review belongs to one customer.
• Type: One-to-Many (Customer to Review)

11. Menu_Item and Category

• A menu item belongs to one category.
• A category can have multiple items.
• Type: Many-to-One (Menu_Item to Category)

12. Order and Coupon

• An order can use one coupon.
• A coupon can be used in multiple orders.
• Type: Many-to-One (Order to Coupon)

13. Menu_Item and Order_Item

• A menu item belongs to multiple orders.
• An order item can have only one menu item.
• Type: Many-to-One (Menu_Item to Order_Item)

Here is the final ER diagram.

Mapping the ER Diagram to a Relational Schema

When converting the ER diagram into a relational schema for our Ecommerce Platform, we need to carefully handle various components to ensure data integrity and optimal performance. Here are the key considerations:

• Entity Tables and Attributes:

  • Create a table for each entity identified in the ER diagram, such as Customer, Restaurant, etc.
  • Define all attributes for each table, specifying appropriate data types and constraints.
  • Set primary keys (PK) for unique identification of records in each table.

• Foreign Keys and Relationships:

  • Establish foreign keys (FK) to represent relationships between entities, linking tables through common attributes.
  • Ensure referential integrity by enforcing foreign key constraints, which maintain consistent and valid references between related tables.

Here is the final relational model for the Ecommerce Platform.

Now, let's translate the ER model into SQL tables.

1. Customer Table

CREATE TABLE Customer (
    Customer_ID INT PRIMARY KEY,
    Full_Name VARCHAR(100),
    Email VARCHAR(100),
    Phone_Number VARCHAR(15),
    Password VARCHAR(50),
    Date_Joined DATE
);

2. Address Table

CREATE TABLE Address (
    Address_ID INT PRIMARY KEY,
    Customer_ID INT,
    Street VARCHAR(255),
    City VARCHAR(50),
    State VARCHAR(50),
    Postal_Code VARCHAR(10),
    Address_Type VARCHAR(20),
    FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID)
);

3. Restaurant Table

CREATE TABLE Restaurant (
    Restaurant_ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Location VARCHAR(255),
    Contact_Number VARCHAR(15),
    Rating DECIMAL(3, 2)
);

4. Menu Table

CREATE TABLE Menu (
    Menu_ID INT PRIMARY KEY,
    Restaurant_ID INT,
    FOREIGN KEY (Restaurant_ID) REFERENCES Restaurant(Restaurant_ID)
);

5. Menu_Item Table

CREATE TABLE Menu_Item (
    Item_ID INT PRIMARY KEY,
    Menu_ID INT,
    Name VARCHAR(100),
    Description TEXT,
    Price DECIMAL(10, 2),
    Category_ID INT,
    FOREIGN KEY (Menu_ID) REFERENCES Menu(Menu_ID),
    FOREIGN KEY (Category_ID) REFERENCES Category(Category_ID)
);

6. Category Table

CREATE TABLE Category (
    Category_ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Description TEXT
);

7. Order Table

CREATE TABLE Order (
    Order_ID INT PRIMARY KEY,
    Customer_ID INT,
    Address_ID INT,
    Order_Date DATE,
    Total_Amount DECIMAL(10, 2),
    Status VARCHAR(20),
    FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID),
    FOREIGN KEY (Address_ID) REFERENCES Address(Address_ID)
);

8. Order_Item Table

CREATE TABLE Order_Item (
    Order_Item_ID INT PRIMARY KEY,
    Order_ID INT,
    Item_ID INT,
    Quantity INT,
    Subtotal DECIMAL(10, 2),
    FOREIGN KEY (Order_ID) REFERENCES Order(Order_ID),
    FOREIGN KEY (Item_ID) REFERENCES Menu_Item(Item_ID)
);

9. Payment Table

CREATE

 TABLE Payment (
    Payment_ID INT PRIMARY KEY,
    Order_ID INT,
    Payment_Date DATE,
    Payment_Method VARCHAR(50),
    Payment_Status VARCHAR(20),
    FOREIGN KEY (Order_ID) REFERENCES Order(Order_ID)
);

10. Delivery_Person Table

CREATE TABLE Delivery_Person (
    Delivery_Person_ID INT PRIMARY KEY,
    Full_Name VARCHAR(100),
    Phone_Number VARCHAR(15),
    Vehicle_Details VARCHAR(100)
);

11. Delivery Table

CREATE TABLE Delivery (
    Delivery_ID INT PRIMARY KEY,
    Order_ID INT,
    Delivery_Person_ID INT,
    Delivery_Status VARCHAR(20),
    Delivery_Date DATE,
    FOREIGN KEY (Order_ID) REFERENCES Order(Order_ID),
    FOREIGN KEY (Delivery_Person_ID) REFERENCES Delivery_Person(Delivery_Person_ID)
);

12. Review Table

CREATE TABLE Review (
    Review_ID INT PRIMARY KEY,
    Customer_ID INT,
    Restaurant_ID INT,
    Rating INT,
    Review_Text TEXT,
    Review_Date DATE,
    FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID),
    FOREIGN KEY (Restaurant_ID) REFERENCES Restaurant(Restaurant_ID)
);

13. Coupon Table

CREATE TABLE Coupon (
    Coupon_ID INT PRIMARY KEY,
    Code VARCHAR(50),
    Discount_Percentage DECIMAL(5, 2),
    Expiry_Date DATE,
    Maximum_Usage INT
);