Database Design for Meals2U: ERD and RDM in 3NF

Meals2U, a food delivery company, provides an order summary for customer C2988. The order details include the restaurant, quantity, and price of each item, as well as a total order value. Meals2U assigns a delivery rider to collect the orders from various restaurants and deliver them to the customer.

BIT103/DIP203 – SEM 3, 2016
ASSIGNMENT 2
Due date : 4/11/16
Weighting : 25% of total marks for the subject
Type : Pair assignment
(A penalty of 5 marks per working day deduction from the marks obtained for this assignment will
be imposed for late submission.)
Rationale
This assignment has been designed to assess students' ability to model data, by constructing
an entity-relationship diagram and a relational data model for a particular scenario. This
assignment addresses the following learning objectives for this subject:
 CLO2: discuss and apply database concepts such as primary key, secondary key, file,
record, field, normalization, database security, integrity, and performance
 CLO4: Create conceptual and logical database models for an information system
Assignment 2 evaluation
These criteria will be used to evaluate Assignment 2:
 The assignment is well presented
 The problem description is appropriate and the approach for the solution chosen is
properly detailed;
 Appropriate entities are identified;
 Appropriate relational data model is constructed;
 The entity-relationship diagram is consistent with the relational data model;
 Attributes are correctly identified;
 The primary keys are correctly identified;
 The relational data model is in third normal form.
 Foreign keys are correctly identified;
 Assumptions are listed.
Requirements
You are to create a database design specification (entity relationship diagram (ERD) and
relational data model (RDM)) from a given business description. You should present this in
a report which also includes a short discussion of the approach for the solution. This
assignment is to be carried out and reported individually.
You must also submit a softcopy, just like for assignment 1, into Turnitin.com. Sign in to
Turnitin and submit your Word document.
o class id: 8617113
o enrollment passwd: database

Meals2U Case Study
Meals2U is a restaurant meal-delivery service with branches in Kuala Lumpur, Ipoh
and Penang. They offer menu items from a large selection of restaurants in each city.
Customers may place orders for items from any number of restaurants in their city and have
their meals delivered directly to their doorstep.
Section A.
Meals2U would like to use a relational database to keep track of the various
restaurants and their menu items. A sample menu from one of the restaurants offered by the
Kuala Lumpur branch is shown in Fig.1.
Occasionally, a restaurant may offer promotions on certain items. The from-date and
to-date of the promotion must be recorded along with the promotional price.
Meals2U Kuala Lumpur
Pusat Bandar Damansara
Restaurant: R1102 Rasa Sayang
Address: 50, Jalan Sangsuria
50400 Kuala Lumpur
Phone Number: 03-20008888
Appetizers
A01 Tom Yam Soup RM 5.90
A02 Popcorn Shrimp RM 7.90
Main Dishes
M01 Nasi Lemak RM9.90
M02 Chicken Rice RM15.90
Drinks
D01 Iced Tea RM1.50
D02 Iced Milo RM 2.80
Dessert
T01 Cendol RM5.00
T02 Durian Puff RM3.00
April Food Fair Promotion!
Durian Puff at only RM2.50 per piece
Chicken Rice at RM14.00
From 1 – 30 April only
Please quote promo code FF12 when ordering
Figure 1: Sample Menu
Section B.
When a customer places an order, they will give their contact information. Meals2U
would like to store customer information in the database so that it can be easily retrieved.
The customer will state which items they would like to order, and from which restaurants. If
there are any promotions on certain items, the promotional price will be used in the order
instead of the menu price. Meals2U will inform the customer the total price of the order and
tell them when to expect the delivery. A sample customer order is shown in Fig.2.
The order details will be sent to the respective restaurants and a delivery rider will be
sent to collect the orders and deliver them to the customer. Meals2U has a team of delivery

riders to handle deliveries. Because an order may involve several restaurants, one rider will
be assigned to one delivery at a time so that it is not too confusing. The rider will collect the
payment and return to Meals2U to wait for the next assignment.
Meals2U Kuala Lumpur
Pusat Bandar Damansara
CustomerID: C2988
Name: Benedict Arnold
Phone: [phone]
Address: Room 23-12 HELP Residence
Order No: O8723
Order Date: 3 April 2013
Order Time: 6:26 pm
Delivery Rider: Jack Soong ID: JS01
Item Restaurant Qty Promo Code
Price
(RM)
Total
(RM)
M01 Nasi Lemak R1102 Rasa Sayang 2 - 9.90 19.80
T02 Durian Puff R1102 Rasa Sayang 2 FF12 2.50 5.00
MA11 Fish and
Chips
R541 Western Delite 1 - 15.00 15.00
Order Total RM
39.8
0
Cash only on delivery please.
Thank you and please call again.
Figure 2: Sample Customer Order Form
Required Items to submit:
Based on Melas2U case study, you are required to:
A. Individual part, each student will choose either Section A or Section B:
Identify the information required for each section in the case study:
1. Identify entities
2. Identify attributes
3. Identify functional dependencies
4. Identify relationship and cardinalities
5. List any assumptions that you make.
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).
Note: this should be done after you have finished all the questions in individual and
group parts.

B. Group part:
Combine all information from your individual parts (Section A and Section B):
1. Develop the Entity Relationship Diagram (ERD) in 3NF using crows’ foot notation
for cardinalities. You must show the entities, composite entities, relationships,
cardinalities and any optionalities. Please use DIA software to construct your ERD.
2. Develop the Relational Data Model (RDM) which corresponds to the ERD. That is,
the set of tables in third normal form that could be used to represent the whole of this
data model. Your RDM should identify primary and foreign keys. Your RDM should
be presented in the format:
TABLENAME (attribute1, attribute2, attribute3, …)
PRIMARY KEY attribute1
FOREIGN KEY attribute3 REFERENCES table name
Notes for ERD:
 Give meaningful names to all entities and relationships in your ERD, but do not show
attributes.
 Explicitly indicate the 'cardinalities' of all relationships (the 'one' or 'many' notations) in
your ERD.
 Where the problem description clearly implies optionality (the 'optional' or 'mandatory'
symbols; i.e. whether 'zero' is possible or not) you should explicitly indicate this in your
ERD. But when the question is not clear on optionality leave it out of your ERD rather
than making an arbitrary assumption.
 List any assumptions that you make.
Notes for relational data model:
 Your set of tables must be in Third Normal Form.
 Indicate primary keys.
 Indicate foreign keys by means of notes after each table.
 The size or type of attributes in your set of tables need not be indicated.
 List any assumptions that you make.
Notes for marking criteria and submission
 Full marks will be awarded where the solution provided is correct in all respects.
 Partial marks may be allocated where students are deemed to have provided a
significant effort toward a correct result, and thus the solution contains some error.
 No marks are awarded where either no solution is provided, or the solution provided is
deemed to be mostly incorrect.

 Hardcopy submission to IT Department. Print in sequence: coversheet, feedback
page, your answer (for individual part please indicate your name), Turnitin
report, and marking scheme.
Plagiarism will be assessed along the following lines:
Category Description Action
Blatant
plagiarism
 Complete assignment similar to the other
student(s).
 Clear intent to plagiarize
 Marks will be equally divided
by the number of students
involved.
i.e. if the assignment is
awarded 40/50 and there are 2
students involved, then each
student will get 20/50.
 Inform student and suggest
how to avoid the mistake in
the future
Accidental
plagiarism
 The program is similar to the other student(s)
except modification on the variables used.
 Little or no evidence of intent to plagiarize
 Maximum 50% of available
points
 Lecturer discretion
 Inform student and suggest
how to avoid the mistake in
the future

BIT103/DIP203 Assignment 2 Marking Scheme
1. Name: StudentID:
2. Name: StudentID:
Individual Marking Scheme
REQUIREMENT CRITERIA MARKS Student 1 Student 2
Dependency Diagrams
(25 marks)
CLO2
Correct entities listed 5
Correct attributes of entity 5
Functional dependencies
identified 5
Relationships identifies 5
Assumptions 5
Discussion (12 marks)
CLO2
How solution was
approached 6
Issues encountered 6
Total Individual Part 37
Group Marking Scheme
REQUIREMENT CRITERIA MARKS MARKS AWARDED
The Entity Relationship
Diagram (ERD) (produced
using a drawing tool such as
DIA tool) (32 marks)
CLO4
The correct number and type of
entities are identified 8
Cardinalities correctly shown 10
Optionality correctly shown 5
Relationships are appropriately
named 7
Composite entities correctly
shown 2
The Relational Data Model
which corresponds to the
ERD (31 marks)
CLO4
The entity-relationship diagram is
consistent with the relational data
model
5
Attributes are correctly identified 8
The primary keys are correctly
identified and unique 8
Foreign keys are identified and
correct 10
Total Group part 63
TOTAL
CRITERIA Student 1 Student 2
Individual Part
Group Part
Late submission (-5/day)
TOTAL (100%)