Food Delivery Database Design Project

This document presents a database design project for a food delivery service. It includes an introduction to the project's goals, assumptions made during the design process, a dependency diagram, an entity-relationship diagram, and a relational model diagram. The project aims to transition the company from a paper-based system to a database management system, covering entities like orders, menus, restaurants, customers, and riders. The design process involved gathering data, creating visual representations, and addressing challenges such as handling large records and future requirements. The final diagrams provide a structured approach to database implementation.

ASSIGNMENT 2
STUDENT NAME
04 November, 2016

Contents
INTRODUCTION.....................................................................................................................................2
ASSUMPTIONS.......................................................................................................................................2
DEPENDENCY DIAGRAM........................................................................................................................3
ENTITY RELATIONSHIP DIAGRAM..........................................................................................................5
RELATIONAL MODEL DIAGRAM.............................................................................................................6
1 | P a g e

INTRODUCTION
The scenario is about a food delivery service which provides riders to deliver food from restaurant to
the customer. The system is a traditional paper system or we can say book keeping where all records
are on paper. Hence this makes it very complex to manage the information on paper and avoid any
errors. So the company has decided to get a database management system built for the use. The
requirements of the company are to hold information about orders, menus, restaurants, customers,
and riders.
So to design this we gathered all the available data to create a visual design of the database.
The design consists of the entities and the attributes that will be required to fulfil the user needs.
From this design, another database design will be built. Although designing makes the entire process
simple that of actual implementation, still there we some difficulties faced. There were large number
of records. We also had to find a way in which all past paper records can be added uniquely to the
system and still the information not being altered. Apart from that meeting the requirements in such
a way that future requirements are considered and added was also a complex task. But after careful
analysis and designing, the diagrams below have been created.
ASSUMPTIONS
The following assumptions were made
1. Each menu item will belong to a category
2. Category table will hold information about different categories of food like starters, deserts,
main course etc.
3. Each category will have unique id.
4. Each customer will have a unique id
5. Customer’s basic info will be stored.
6. Each rider will have information in rider table with unique rider id
7. Each menu id item will have a unique id
2 | P a g e

8. Each city will have zero or more restaurants
9. Each city will have a unique id
10. Each restaurant will have a city code associated
11. Each restaurant will provide zero or more promotions and will stored in a separate table.
DEPENDENCY DIAGRAM
CITY
CITY_ID CITY_NAME
RESTAURANT
PROMOTION
RI
D
PROMOTION_ID DETAILS FROM_DATE TO_DATE PROMOTIONAL_PRICE
MENU_ITEMS
ITEM_ID RID CID PRICE DESCRIPTION
ORDER_DETAILS
3 | P a g e
RID RNAME RADDRESS ZIPCODE PHONE CITY_ID
ORDER_I
D
ITEM_ID PROMOCODE PRICE QUANTITY

CATEGORY
CATEGORY_ID CNAME
ORDER
ORDER_I
D
ORDER_DATE ORDER_TIME DELIVERY_TIME CUSTOMER_I
D
RIDER_ID
CUSTOMER
RIDER
4 | P a g e
CUSTOMER_ID CUSTOMER_NAM
E
ADDRESS PHONE
RIDER_ID RIDER_NAME PHONE

ENTITY RELATIONSHIP DIAGRAM
5 | P a g e

RELATIONAL MODEL DIAGRAM
CITY (CITY_ID, CITY_NAME)
PRIMARY KEY: CITY_ID
RESTAURANT (RID, RNAME, RADDRESS, ZIPCODE, PHONE, CITY_ID)
PRIMARY KEY: RID
FOREIGN KEY CITY_ID REFERENCES CITY (CITY_ID)
PROMOTION (RID, PROMOTION_ID, DETAILS, FROM_DATE, TO_DATE, PROMOTIONAL_PRICE)
PRIMARY KEY (RID, PROMOTION_ID)
FOREIGN KEY RID REFERENCES RESTAURANT (RID)
CATEGORY (CATEGORY_ID, CNAME)
PRIMARY KEY: CATEGORY_ID
MENU_ITEMS (ITEM_ID, RID, CID, PRICE, DESCRIPTION)
FOREIGN KEY RID REFERENCES RESTAURANT (RID)
FOREIGN KEY CID REFERENCES CATEGORY (CATEGORY_ID)
PRIMARY KEY (ITEM_ID, RID, CID)
CUSTOMER (CUSTOMER_ID, CUSTOMER_NAME, ADDRESS, PHONE)
PRIMARY KEY CUSTOMER_ID
RIDER (RIDER_ID, RIDER_NAME, PHONE)
PRIMARY KEY RIDER_ID
ORDER (ORDER_ID, ORDER_DATE, ORDER_TIME, DELIVERY_TIME, CUSTOMER_ID, RIDER_ID)
PRIMARY KEY ORDER_ID
FOREIGN KEY CUSTOMER_ID REFERENCES CUSTOMER (CUSTOMER_ID)
FOREIGN KEY RIDER_ID REFERENCES RIDER (RIDER_ID)
6 | P a g e

ORDER_DETAILS (ORDER_ID, ITEM_ID, PROMOCODE, PRICE, QUANTITY)
PRIMARY KEY (ORDER_ID, ITEM_ID)
FOREIGN KEY ORDER_ID REFERENCES ORDER (ORDER_ID)
FOREIGN KEY ITEM_ID REFERENCES MENU_ITEMS (ITEM_ID)
7 | P a g e