Database Design Coursework Template
Student Name: Seyer Raji
Student ID: 240023389
Student Number: 240023389
Scenario Topic Name Online Food Delivery Database
Scenario (100 words maximum)
The database manages an online food delivery service, allowing customers to place orders from
restaurants for delivery. Personal details like names and addresses are stored. Restaurants offer a
range of items from their menu. A discounted restaurant is a restaurant which offers a discount to its
customers. When a customer places an order, a unique OrderID is created for them and linked to the
restaurant which they ordered from. The order contains delivery details and the date it was placed.
Customers can write and reply to reviews, and restaurants can also respond to reviews they have
received.
Example queries (Minimum 5 – list, who, which, how many, most, fewest etc. - check that your
models have the attributes needed to answer the queries)
List all menu items for Henry’s Burgers
Which restaurant has the most reviews?
What is the average rating of reviews for Golden Restaurant?
How many orders has a specific customer placed?
Which restaurant has the lowest rating?
Which restaurant has the fewest orders?
Entity Relationship Model (insert a jpg image of your model exported from Visual Paradigm in the
space below).
Relational Model Tables
Copy and paste the table below for as many relational tables as you need
Replace the placeholder names (table-name1, attribute-name5 etc) with the table and
attribute names you derived from your ER model
List primary key attributes first
Add new rows to the tables (in the correct place) as needed
Delete any unnecessary rows (attribute rows and foreign key rows if not used)
Primary keys are to be specified in the format PRIMARY KEY (attribute-name1, attribute-
name2, etc)
Foreign keys are to be specified in the format ‘FOREIGN KEY (attribute-name) REFERENCES
table-name (attribute-name)
Relational table specification Marker’s corrections (Do not write in
this column)
Table name: Customer
Attributes
CustomerID
Address
Email
Phone_number
PRIMARY KEY (CustomerID)
Relational table specification Marker’s corrections (Do not write in
this column)
Table name: Restaurant
Attributes
RestaurantID
Name
Address
PRIMARY KEY (RestaurantID)
Relational table specification Marker’s corrections (Do not write in
this column)
Table name: Order
Attributes
OrderID
CustomerID
RestaurantID
Date_order_placed
Order_status
PRIMARY KEY (OrderID)
FOREIGN KEY (CustomerID) REFERENCES
Customer (CustomerID)
FOREIGN KEY (RestaurantID) REFERENCES
Restaurant (RestaurantID)
Relational table specification Marker’s corrections (Do not write in
this column)
Table name: Delivery
Attributes
DeliveryID
OrderID
Delivery_status
Delivery_time
Delivery_person
PRIMARY KEY (DeliveryID)
FOREIGN KEY (OrderID) REFERENCES Order
(OrderID)
Relational table specification Marker’s corrections (Do not write in
this column)
Table name: Discounted Restaurant
Attributes
RestaurantID
Discount_percentage
Discount_description
PRIMARY KEY (RestaurantID)
FOREIGN KEY (RestaurantID) REFERENCES
Restaurant (RestaurantID)
Relational table specification Marker’s corrections (Do not write in
this column)
Table name: Menu Item
Attributes
ItemID
RestaurantID
Item_name
Price
Description
PRIMARY KEY (ItemID)
FOREIGN KEY (RestaurantID) REFERENCES
Restaurant (RestaurantID)
Relational table specification Marker’s corrections (Do not write in
this column)
Table name: Review
Attributes
ReviewID
RestaurantID
CustomerID
Review_message
Rating
Responding_to_ID
PRIMARY KEY (ReviewID)
FOREIGN KEY (RestaurantID) REFERENCES
Restaurant (RestaurantID)
FOREIGN KEY (CustomerID) REFERENCES
Customer (CustomerID)
FOREIGN KEY (Responding_to_ID) REFERENCES
Review (ReviewID)
Marker’s Comments (Do not write in this section)
Important: Please note that marker’s corrections to your relational tables are there to help
you construct a working database for the second coursework. They are not the determinant
of your mark. For more information on how your work is assessed see the coursework
specification and grade related criteria.
ER Model:
The ER diagram contains sensible entities and attributes, it shows most of the features
demonstrated in class. The diagram fulfils to an extent your intentions stated in the
scenario. You have also demonstrated some technical knowledge by applying constraints
thoughtfully and adding recursion in a meaningful way.
There are however some issues:
-You could develop your scenario further to showcase a better use of
Generalization/specialization, the subtype Discounted Restaurant seem rather
artificial/redundant, the discount attributes in that table can be easily included in the
restaurant table itself as nullables. You could consider other more realistic types of
restaurants i.e Fine Dining restaurants Fast Food restaurant etc.. with more meaningful
information that might add more value to your Database.
-There seem to be an important omission in the model; order should be associated with
Menu Item in a many-to-many relationship as an order can contain multiple menu items and
a menu item can be present in many orders, so a third table (Order_Item) is necessary here
to keep track of the details of which items are included in an order.
- The length for some attribute types is inappropriate; some are specified using the
maximum length (255) especially for varchars.
-In most relational database management systems, when you define a column as a Primary
Key, it is automatically considered unique. This means you do not need to explicitly label a
primary key column as ‘U’ Unique.
70/100
Relational Tables:
The Translation of the ER model into relational model is correct.
20/20
Coursework Mark (100 marks available): 71
