Assignment 2: Database Design

This assignment involves designing a database for a food delivery service. The student identifies entities (City, Restaurant, Promotion, Category, Menu_Items, Customer, Rider, Order, Order_Details), attributes, and functional dependencies. Relationships and cardinalities between entities are defined, along with assumptions made during the design process. The solution includes an Entity Relationship Diagram (ERD) and a Relational Model Diagram, showing primary and foreign keys. A discussion section explains the approach to the modeling problem and challenges encountered, such as handling a large number of records and ensuring data integrity while accommodating future requirements. The student's work demonstrates a clear understanding of database design principles and their application to a real-world scenario.

ASSIGNMENT 2
STUDENT NAME
04 November, 2016

Contents
SECTION B..........................................................................................................................................2
GROUP PART......................................................................................................................................6
ENTITY RELATIONSHIP DIAGRAM............................................................................................6
RELATIONAL MODEL DIAGRAM...............................................................................................7
1 | P a g e

SECTION B
1. Identify entities
a. CITY
b. RESTAURANT
c. PROMOTION
d. CATEGORY
e. MENU_ITEMS
f. CUSTOMER
g. RIDER
h. ORDER
i. ORDER_DETAILS
2. Identify attributes
a. CITY: CITY_ID, CITY_NAME
b. RESTAURANT: RID, RNAME, RADDRESS, ZIPCODE, PHONE,CITY_ID
c. PROMOTION: PROMOTION_ID, RID, DETAILS, FROM_DATE,
TO_DATE, PROMOTIONAL_PRICE
d. CATEGORY: CATEGORY_ID, CNAME
e. MENU_ITEMS: ITEM_ID, PRICE, DESCRIPTION, RID, CID
f. CUSTOMER: CUSTOMER_ID, CUSTOMER_NAME, ADDRESS, PHONE
g. RIDER: RIDER_ID, RIDER_NAME, PHONE
h. ORDER: ORDER_ID, ORDER_DATE, ORDER_TIME, DELIVERY_TIME,
RIDER_ID, CUSTOMER_ID,
i. ORDER_DETAILS: ORDER_ID, PRICE, QUANTITY, ITEM_ID,
PROMOCODE
3. functional dependencies
2 | P a g e

a. CITY_ID -> CITY_NAME
b. RID -> RNAME, RADDRESS, ZIPCODE, PHONE,CITY_ID
c. PROMOTION_ID-> RID, DETAILS, FROM_DATE, TO_DATE,
PROMOTIONAL_PRICE
d. CATEGORY_ID -> CNAME
e. ITEM_ID, RID, CID -> PRICE, DESCRIPTION
f. CUSTOMER_ID -> CUSTOMER_NAME, ADDRESS, PHONE
g. RIDER_ID -> RIDER_NAME, PHONE
h. ORDER_ID -> ORDER_DATE, ORDER_TIME, DELIVERY_TIME,
RIDER_ID, CUSTOMER_ID,
i. ORDER_ID, ITEM_ID -> PRICE, QUANTITY, PROMOCODE
4. Identify relationship and cardinalities
Entities: CITY, RESTAURANT, PROMOTION, CATEGORY,
MENU_ITEMS, CUSTOMER, RIDER, ORDER, ORDER_DETAILS
Possible Relationship:
a. Customer place an Order
b. Restaurant provides promotions
c. City has restaurants
d. Menu_Items belong to category
e. Order_details contains menu_Items
f. Restaurant has menu_Items
g. Order has order_details
h. Rider delivers order
3 | P a g e

Possible cardinalities:
a. 1 Customer place zero or more Order, 1 Order placed by 1 Customer
b. 1 Order has one or more order details, 1 order_details has included by 1 Order
c. 1 Restaurant has one or more menu_Items, 1 menu_Items belong to 1
Restaurant
d. 1 Restaurant provides zero or more promotions, 1 promotion belongs to 1
restaurant.
e. 1 City has zero or more restaurants, 1 restaurant belongs to 1 city
f. 1 Rider delivers zero or more order, 1 order is delivered by 1 rider
g. 1 category has zero or more menu items, 1menu_item has 1 category
h. Zero or more Menu_Items are part of order_details, 1order_details has one or
more menu_Items.
5. List any assumptions that you make:
The following assumptions were made
a. Each menu item will belong to a category
b. Category table will hold information about different categories of food like starters,
deserts, main course etc.
c. Each category will have unique id.
d. Each customer will have a unique id
e. Customer’s basic info will be stored.
f. Each rider will have information in rider table with unique rider id
g. Each menu id item will have a unique id
h. Each city will have zero or more restaurants
i. Each city will have a unique id
4 | P a g e

j. Each restaurant will have a city code associated
k. Each restaurant will provide zero or more promotions and will stored in a separate
table.
6. Write a brief discussion of your solution, i.e. how you approached the overall
modelling problem and any issues you may have encountered (minimum of ½ page).
Answer: 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.
5 | P a g e

GROUP PART
ENTITY RELATIONSHIP DIAGRAM
6 | P a g e

7 | 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)
8 | P a g e

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)
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)
9 | P a g e