PUNJAB UNIVERSITY COLLEGE OF INFORMATION AND
TECHNOLOGY
DATABASE SYSTEMS
PROJECT REPORT
SUBMITTED BY:
Meerab Nishat(BSEF23M035)
Hafsa Siraj (BSEF23M030)
Rana Hassan (BSEF23M038)
Ubaid (BSEF23M031)
SUBMITTED TO: Mr. Asif Sohail
1
Food delivery system
Overview
The food delivery system is a web or app-based platform that allows customers to browse menus from
various restaurants, place orders, make payments, and track deliveries. Restaurants manage their menu
offerings, while delivery staff fulfill the orders. The system ensures smooth interaction between all
stakeholders: customers, restaurants, and delivery personnel, with backend support for storing,
processing, and retrieving data.
Working
1. Customer Management:
o Customers register with personal details (name, email, phone, address) and can place
orders through the system.
o They can view restaurant menus, select items, and track order status in real-time.
2. Restaurant Operations:
o Restaurants maintain their profiles, including cuisine type and contact information.
o They manage menu items (with descriptions, prices, and availability) and receive
customer orders.
3. Order Processing:
o The system records order details (items, quantities, subtotals) and calculates the total
amount.
o Orders progress through statuses (pending, preparing, out for delivery, delivered).
4. Payment Handling:
o Secure payment processing supports multiple methods (cash, card).
o Payment status is tracked and linked to specific orders.
5. Delivery Coordination:
o Delivery staff are assigned orders based on availability.
o Real-time tracking updates delivery status until completion.
6. Feedback System:
o Customers can rate restaurants (1-5 stars) and provide comments.
o Feedback helps maintain service quality and improve operations.
2
Entity Relationship Diagram
Relational Schemas
All Attributes in One Single Table:
3
TABLE(OrderID,OrderDate,Status,TotalAmount,CustomerID,CustomerName,CustomerEmail,
CustomerPhone, CustomerAddress, CustomerPassword, RestaurantID, RestaurantName,
RestaurantEmail, RestaurantPhone, RestaurantAddress, CuisineType, RestaurantPassword, ItemID,
ItemName, ItemDescription, Price, Availability, Quantity, Subtotal, PaymentID, PaymentDate, Amount,
PaymentMethod, PaymentStatus, DeliveryID, DeliveryStaffID, DeliveryStatus, DeliveryTime,
DeliveryStaffName, DeliveryStaffPhone, DeliveryStaffEmail, DeliveryStaffStatus, FeedbackID, Rating,
Comments, FeedbackDate)
1NF Conversion
No multivalued or repeating groups. Our table is already in 1NF because
All values are atomic.
Each field stores one value only.
Rows are uniquely identified
2NF Conversion
Now remove partial dependencies. We break the table into logical relations:
CUSTOMER (CustomerID, Name, Email, Phone, Address, Password)
(Non-key attributes depend fully on CustomerID)
RESTAURANT (RestaurantID, Name, Email, Phone, Address, CuisineType, Password)
(Non-key attributes depend fully on RestaurantID)
MENUITEM (ItemID, RestaurantID, Name, Description, Price, Availability)
(Attributes depend fully on ItemID)
ORDER (OrderID, CustomerID, RestaurantID, OrderDate, Status, TotalAmount)
(OrderID is PK; no partial dependency)
ORDERDETAILS (OrderDetailID, OrderID, ItemID, Quantity, Subtotal)
(Composite dependencies resolved by separate key OrderDetailID)
PAYMENT (PaymentID, OrderID, PaymentDate, Amount, PaymentMethod, PaymentStatus)
DELIVERY (DeliveryID, OrderID, DeliveryStaffID, DeliveryStatus, DeliveryTime)
DELIVERYSTAFF (DeliveryStaffID, Name, Phone, Email, Status)
FEEDBACK (FeedbackID, CustomerID, RestaurantID, Rating, Comments, FeedbackDate)
4
3NF Conversion
Now remove transitive dependencies (non-key attributes depending on other non-key attributes):
Check for indirect dependencies like:
If DeliveryStaffStatus depends on DeliveryStaffID, not on DeliveryID already separated.
If RestaurantEmail depends on RestaurantID, not on Order already separated.
All transitive dependencies have been removed.
Final design is in 3NF.
CUSTOMER (CustomerID, Name, Email, Phone, Address, Password)
Here CustomerID = PK
RESTAURANT (RestaurantID, Name, Email, Phone, Address, CuisineType, Password)
Here RestaurantID = PK
MENUITEM (ItemID, RestaurantID, Name, Description, Price, Availability)
Here ItemID = PK, RestaurantID = FK
ORDER (OrderID, CustomerID, RestaurantID, OrderDate, Status, TotalAmount)
Here OrderID = PK, CustomerID = FK, RestaurantID = FK
ORDERDETAILS (OrderDetailID, OrderID, ItemID, Quantity, Subtotal)
Here OrderDetailID = PK, OrderID = FK, ItemID = FK
PAYMENT (PaymentID, OrderID, PaymentDate, Amount, PaymentMethod, PaymentStatus)
Here PaymentID = PK, OrderID = FK
DELIVERYSTAFF (DeliveryStaffID, Name, Phone, Email, Status)
Here DeliveryStaffID = PK
DELIVERY (DeliveryID, OrderID, DeliveryStaffID, DeliveryStatus, DeliveryTime)
Here DeliveryID = PK, OrderID = FK, DeliveryStaffID = FK
FEEDBACK (FeedbackID, CustomerID, RestaurantID, Rating, Comments, FeedbackDate)
Here FeedbackID = PK, CustomerID = FK, RestaurantID = FK
TABLES:
The tables of customer ,restaurant ,delivery staff,feedback,menu order,order
details,payment,delivery are given in a periodic format.
5
CUSTOMER
Attribute Data Type Size Constraint RESTAURANT
CustomerID INT PRIMARY Attribute Data Type Size Constraint
KEY
RestaurantI INT PRIMARY KEY
Name VARCHAR 100 NOT D
NULL
Name VARCHAR 100 NOT NULL
Email VARCHAR 100 NOT
NULL, Email VARCHAR 100 NOT NULL,
UNIQUE UNIQUE
Phone VARCHAR 15 NOT Phone VARCHAR 15 NOT NULL,
NULL, UNIQUE
UNIQUE
Address TEXT NOT NULL
Address TEXT NOT
NULL CuisineType VARCHAR 50 NOT NULL
Password VARCHAR 100 NOT Password VARCHAR 100 NOT NULL
NULL
DELIVERYSTAFF FEEDBACK
Attribute Data Type Size Constraint Attribute Data Type Siz Constraint
e
DeliveryStaffI INT PRIMARY
D KEY, FeedbackID INT PRIMARY KEY,
Name VARCHAR 100 NOT NULL CustomerID INT FOREIGN KEY
REFERENCES
Phone VARCHAR 15 NOT CUSTOMER(ID)
NULL,
UNIQUE RestaurantID INT FOREIGN KEY
REFERENCES
Email VARCHAR 100 NOT RESTAURANT(I
NULL, D)
UNIQUE
Rating INT CHECK (Rating
Status VARCHAR 50 DEFAULT BETWEEN 1 AND
'Available' 5)
Comments VARCHAR 200
FeedbackDat DATETIM NOT NULL
e E
6
MENUITEM
ORDERDETAILS
Attribute Data Type Data
Attribute SizeType Size Constraint
Constraint
ItemID INT PRIMARY KEY,
OrderDetailI INT PRIMARY KEY,
RestaurantI D INT FOREIGN KEY REFERENCES
D RESTAURANT(ID)
OrderID INT FOREIGN KEY REFERENCES
Name VARCHAR 100 NOT NULL
ORDER(ID)
Description ItemID
TEXT INT FOREIGN KEY REFERENCES
MENUITEM(ID)
Price NUMBER (8,2) NOT NULL
Quantity INT NOT NULL
Availability BOOLEAN DEFAULT TRUE
Subtotal NUMBER (10,2) NOT NULL
ORDER
Column Data Type Size Constraint
OrderID INT PRIMARY KEY,
CustomerID INT FOREIGN KEY REFERENCES
CUSTOMER(ID)
RestaurantI INT FOREIGN KEY REFERENCES
D RESTAURANT(ID)
OrderDate DATETIME NOT NULL
Status VARCHAR 50 DEFAULT 'Pending'
TotalAmount NUMBER (10,2) NOT NULL
PAYMENT
Attribute Data Type Size Constraint
7
DELIVERY
Attribute Data Type Size Constraint
DeliveryID INT PRIMARY KEY,
OrderID INT FOREIGN KEY
REFERENCES ORDER(ID),
UNIQUE
DeliveryStaffI INT FOREIGN KEY
D REFERENCES
DELIVERYSTAFF(ID)
DeliveryStatus VARCHAR 50 DEFAULT 'Out for Delivery'
DeliveryTime DATETIME
PaymentID INT PRIMARY KEY,
OrderID INT FOREIGN KEY REFERENCES
ORDER(ID), UNIQUE
PaymentDate DATETIME NOT NULL
Amount NUMBER (10,2) NOT NULL
PaymentMethod VARCHAR 50 NOT NULL
PaymentStatus VARCHAR 50 DEFAULT 'Pending'
8
Creating tables (SQL)
CUSTOMER table
CREATE TABLE CUSTOMER (
CustomerID NUMBER PRIMARY KEY,
Name VARCHAR2(100) NOT NULL,
Email VARCHAR2(100) UNIQUE NOT NULL,
Phone VARCHAR2(20) NOT NULL,
Address VARCHAR2(200) NOT NULL,
Password VARCHAR2(100) NOT NULL);
RESTAURANT table
CREATE TABLE RESTAURANT (
RestaurantID NUMBER PRIMARY KEY,
Name VARCHAR2(100) NOT NULL,
Email VARCHAR2(100) UNIQUE NOT NULL,
Phone VARCHAR2(20) NOT NULL,
Address VARCHAR2(200) NOT NULL,
CuisineType VARCHAR2(50) NOT NULL,
Password VARCHAR2(100) NOT NULL);
MENUITEM table
9
CREATE TABLE MENUITEM (
ItemID NUMBER PRIMARY KEY,
RestaurantID NUMBER NOT NULL,
Name VARCHAR2(100) NOT NULL,
Description VARCHAR2(500),
Price NUMBER (10,2) NOT NULL,
Availability NUMBER (1) DEFAULT 1 NOT NULL,
CONSTRAINT fk_menuitem_restaurant FOREIGN KEY (RestaurantID)
REFERENCES RESTAURANT(RestaurantID));
ORDER table
CREATE TABLE ORDER_TABLE (
OrderID NUMBER PRIMARY KEY,
CustomerID NUMBER NOT NULL,
RestaurantID NUMBER NOT NULL,
OrderDate TIMESTAMP NOT NULL,
Status VARCHAR2(20) NOT NULL,
TotalAmount NUMBER (10,2) NOT NULL,
CONSTRAINT fk_order_customer FOREIGN KEY (CustomerID)
REFERENCES CUSTOMER(CustomerID),
CONSTRAINT fk_order_restaurant FOREIGN KEY (RestaurantID)
REFERENCES RESTAURANT(RestaurantID));
ORDERDETAILS table
CREATE TABLE ORDERDETAILS (
OrderDetailID NUMBER PRIMARY KEY,
10
OrderID NUMBER NOT NULL,
ItemID NUMBER NOT NULL,
Quantity NUMBER NOT NULL,
Subtotal NUMBER (10,2) NOT NULL,
CONSTRAINT fk_orderdetails_order FOREIGN KEY (OrderID)
REFERENCES ORDER_TABLE(OrderID),
CONSTRAINT fk_orderdetails_item FOREIGN KEY (ItemID)
REFERENCES MENUITEM(ItemID));
PAYMENT table
CREATE TABLE PAYMENT (
PaymentID NUMBER PRIMARY KEY,
OrderID NUMBER NOT NULL,
PaymentDate TIMESTAMP NOT NULL,
Amount NUMBER (10,2) NOT NULL,
PaymentMethod VARCHAR2(20) NOT NULL,
PaymentStatus VARCHAR2(20) NOT NULL,
CONSTRAINT fk_payment_order FOREIGN KEY (OrderID)
REFERENCES ORDER_TABLE(OrderID));
DELIVERYSTAFF table
CREATE TABLE DELIVERYSTAFF (
DeliveryStaffID NUMBER PRIMARY KEY,
Name VARCHAR2(100) NOT NULL,
Phone VARCHAR2(20) NOT NULL,
Email VARCHAR2(100) UNIQUE NOT NULL,
11
Status VARCHAR2(20) NOT NULL);
DELIVERY table
CREATE TABLE DELIVERY (
DeliveryID NUMBER PRIMARY KEY,
OrderID NUMBER NOT NULL,
DeliveryStaffID NUMBER NOT NULL,
DeliveryStatus VARCHAR2(20) NOT NULL,
DeliveryTime TIMESTAMP,
CONSTRAINT fk_delivery_order FOREIGN KEY (OrderID)
REFERENCES ORDER_TABLE(OrderID),
CONSTRAINT fk_delivery_staff FOREIGN KEY (DeliveryStaffID)
REFERENCES DELIVERYSTAFF(DeliveryStaffID));
FEEDBACK table
CREATE TABLE FEEDBACK (
FeedbackID NUMBER PRIMARY KEY,
CustomerID NUMBER NOT NULL,
RestaurantID NUMBER NOT NULL,
Rating NUMBER (1) NOT NULL,
Comments VARCHAR2(500),
FeedbackDate TIMESTAMP NOT NULL,
CONSTRAINT fk_feedback_customer FOREIGN KEY (CustomerID)
REFERENCES CUSTOMER(CustomerID),
CONSTRAINT fk_feedback_restaurant FOREIGN KEY (RestaurantID)
12
REFERENCES RESTAURANT(RestaurantID),
CONSTRAINT chk_rating CHECK (Rating BETWEEN 1 AND 5));
VIEWS:
ORDER TRACKING :
CREATE VIEW V_ORDER_TRACKING AS
SELECT
o.OrderID,
o.OrderDate,
o.Status AS OrderStatus,
o.TotalAmount,
c.Name AS CustomerName,
c.Phone AS CustomerPhone,
r.Name AS RestaurantName,
r.CuisineType,
ds.Name AS DeliveryStaffName,
ds.Phone AS DeliveryStaffPhone,
d.DeliveryStatus,
d.DeliveryTime,
p.PaymentMethod,
p.PaymentStatus
FROM
ORDER_TABLE o
JOIN
13
CUSTOMER c ON o.CustomerID = c.CustomerID
JOIN
RESTAURANT r ON o.RestaurantID = r.RestaurantID
LEFT JOIN
DELIVERY d ON o.OrderID = d.OrderID
LEFT JOIN
DELIVERYSTAFF ds ON d.DeliveryStaffID = ds.DeliveryStaffID
LEFT JOIN
PAYMENT p ON o.OrderID = p.OrderID;
Purpose:
Provides real-time order status to customers, restaurants, and delivery staff.
Shows complete order journey from placement to delivery.Includes contact information
for all parties involved.
Displays payment status for financial tracking
RESTAURANT PERFORMANCE
CREATE VIEW V_RESTAURANT_PERFORMANCE AS
SELECT
r.RestaurantID,
r.Name AS RestaurantName,
r.CuisineType,
COUNT(DISTINCT o.OrderID) AS TotalOrders,
SUM(o.TotalAmount) AS TotalRevenue,
AVG(f.Rating) AS AverageRating,
COUNT(f.FeedbackID) AS FeedbackCount,
14
MIN(o.OrderDate) AS FirstOrderDate,
MAX(o.OrderDate) AS LastOrderDate
FROM
RESTAURANT r
LEFT JOIN
ORDER_TABLE o ON r.RestaurantID = o.RestaurantID
LEFT JOIN
FEEDBACK f ON r.RestaurantID = f.RestaurantID
GROUP BY
r.RestaurantID, r.Name, r.CuisineType;
Purpose:
Helps restaurants monitor their business performance
Provides insights for marketing and menu optimization
Shows customer satisfaction metrics (ratings)
Tracks order volume and revenue trends
Useful for platform administrators to identify top-performing restaurants
SELECT STATEMENTS:
1. Daily Orders Summary
SELECT
r.RestaurantID,
r.Name AS RestaurantName,
COUNT(o.OrderID) AS TotalOrders,
SUM(o.TotalAmount) AS TotalRevenue
FROM
15
ORDER_TABLE o
JOIN
RESTAURANT r ON o.RestaurantID = r.RestaurantID
WHERE
TRUNC(o.OrderDate) = TO_DATE('2025-06-13', 'YYYY-MM-DD')
GROUP BY
r.RestaurantID, r.Name;
Purpose: Show the number of orders and total revenue for each restaurant on a specific day.
2. Top 5 Most Ordered Menu Items (Platform-Wide)
SELECT
m.Name AS ItemName,
r.Name AS RestaurantName,
SUM(od.Quantity) AS TotalQuantityOrdered
FROM
ORDERDETAILS od
JOIN
MENUITEM m ON od.ItemID = m.ItemID
JOIN
RESTAURANT r ON m.RestaurantID = r.RestaurantID
GROUP BY
m.Name, r.Name
ORDER BY
16
TotalQuantityOrdered DESC
FETCH FIRST 5 ROWS ONLY;
Purpose: Identify the five most popular items based on total quantity ordered.
3. Orders with Failed or Pending Payments
SELECT
o.OrderID,
c.Name AS CustomerName,
p.PaymentMethod,
p.PaymentStatus,
o.TotalAmount,
o.OrderDate
FROM
ORDER_TABLE o
JOIN
PAYMENT p ON o.OrderID = p.OrderID
JOIN
CUSTOMER c ON o.CustomerID = c.CustomerID
WHERE
p.PaymentStatus IN ('Pending', 'Failed');
Purpose: Track orders that have not been successfully paid.
4. Delivery Staff Performance
SELECT
17
ds.DeliveryStaffID,
ds.Name AS DeliveryStaffName,
COUNT(d.DeliveryID) AS TotalDeliveries,
ROUND(AVG((d.DeliveryTime - o.OrderDate) * 24), 2) AS AvgDeliveryTime_Hours
FROM
DELIVERY d
JOIN
DELIVERYSTAFF ds ON d.DeliveryStaffID = ds.DeliveryStaffID
JOIN
ORDER_TABLE o ON d.OrderID = o.OrderID
WHERE
d.DeliveryStatus = 'Delivered'
GROUP BY
ds.DeliveryStaffID, ds.Name;
Purpose: Number of deliveries handled and average delivery time per staff.
5. Restaurants with Below Average Ratings
SELECT
r.RestaurantID,
r.Name AS RestaurantName,
AVG(f.Rating) AS RestaurantAvgRating
FROM
RESTAURANT r
JOIN
FEEDBACK f ON r.RestaurantID = f.RestaurantID
18
GROUP BY
r.RestaurantID, r.Name
HAVING
AVG(f.Rating) < ( SELECT AVG(Rating) FROM FEEDBACK );
Purpose: Highlight restaurants whose average rating is below the overall average.
PL/SQL Function
Get Customer Order Count
CREATE OR REPLACE FUNCTION GetCustomerOrderCount(p_CustomerID NUMBER)
RETURN NUMBER
IS
v-OrderCount NUMBER;
BEGIN
SELECT COUNT(*) INTO v_OrderCount
FROM ORDER_TABLE
WHERE CustomerID = p_CustomerID;
RETURN v_OrderCount;
END;
Purpose: Returns the number of orders placed by a specific customer.
Example Usage:
SELECT GetCustomerOrderCount(101) AS OrderCount FROM DUAL;
19
PL/SQL Stored Procedure
Update Delivery Status
CREATE OR REPLACE PROCEDURE UpdateDeliveryStatus(p_DeliveryID NUMBER,
p_NewStatus VARCHAR2
)
IS
BEGIN
UPDATE DELIVERY
SET
DeliveryStatus = p_NewStatus,
DeliveryTime = CASE
WHEN p_NewStatus = 'Delivered' THEN SYSDATE
ELSE DeliveryTime
END
WHERE DeliveryID = p_DeliveryID;
COMMIT;
END;
Purpose: Updates the status of a delivery and records the delivery time.
Example Usage:
BEGIN
UpdateDeliveryStatus(1001, 'Delivered');
END;
20
Database Trigger
Auto Insert Payment Record After Order
CREATE OR REPLACE TRIGGER trg_AutoInsertPayment
AFTER INSERT ON ORDER_TABLE
FOR EACH ROW
BEGIN
INSERT INTO PAYMENT (
PaymentID,
OrderID,
PaymentDate,
Amount,
PaymentMethod,
PaymentStatus)
VALUES (
PAYMENT_SEQ.NEXTVAL, -- Assumes a sequence for PaymentID
:NEW.OrderID,
SYSDATE,
:NEW.TotalAmount,
'Cash', -- Default method
'Pending'
);
END;
Purpose: Automatically creates a pending payment record when a new order is placed.
21
Example Usage:
CREATE SEQUENCE PAYMENT_SEQ START WITH 1 INCREMENT BY 1;
_______________________
22
