1Copyr i gh t © 2012, SAS I nst i t u t e I nc . A l l r i gh t s reserv e d.
INFS5710 IT Infra. for BA
ERM Sample Model
Dr Vincent Pang
SISTM
UNSW Business School
©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a
certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
.
Sample Question
(from A Previous Assignment)
In the Matthews Food Court, a small takeaway restaurant called Laksa Delight (Laksa Delight | Estate
Management - UNSW Sydney). Laksa Delight sells Laksa, noodle soup, hot meal deals and drinks.
One day, the owner of the restaurant decided to create a new delivery business in addition to its
takeaway business.
Here are some of the business rules for creating a delivery business:
a. All the ingredients and beverages are ordered from different suppliers.
b. Each chef creates a few signature dishes on the menu. Each signature dish has its own recipe.
c. A dish is created from several ingredients. Ingredients here are fresh products like
vegetables, meat, fish, salt, curry powder, noodles, rice, and so on.
d. All the beverages and dishes on the menu are priced by the owner of the restaurant.
e. Only delivery companies registered with Laksa Delight can deliver the orders.
f. Before a customer orders any food to be delivered, the customer must create an
account. The customer must enter an address and mobile number for the delivery
company.
©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a
certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
.
Sample Question
(from A Previous Assignment)
g. A customer can choose from a menu to order several dishes and drinks. The customer
has to choose a delivery company and time of delivery. The customers must pay for
their orders.
h. For simplicity,
• You do not need to worry about generating invoices and receipts. You just have to
model the order entities correctly.
• You do not need to model the payment of the order/invoice.
• You do not have to worry about different pricing of the dishes between takeaway
and delivery services.
• You do not have to worry about the purchase price of the goods from the suppliers.
i. You can do research on restaurants with the delivery of food. However, you do not have
to go into the depth of the delivery process.
j. The few entities in the Excel spreadsheet are to give you a starting point for your model.
You must model all the entities using Crow’s foot notation. Only Primary Keys (PKs) are
shown on these entities. You are expected to add and modify the entities as necessary.
©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a
certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
.
Delivery_Companies Entity (1,1) Invoices_Header Entity (1,N) Customers Entity
Delivery_Company_ID PK Invoice_No PK Customer_ID PK
Delivery_Company_Name (1,N) Customer_ID FK1 Customer_Name
Delivery_Company_Address Delivery_Company_ID FK2 (1,1) Customer_Mobile_No
(1,1) Delivery_Instructions Customer_Address
Invoice_Date PK Customer_ID
Contains Customer_Name
Customer_Mobile_No
Invoices_Details Entity Customer_Address
Invoices_Details Entity Invoice_No PK, FK1
Invoice_No PK, FK1 (1,N) Invoice_Line_No PK Assumptions:
Product_ID PK, FK2 Product_ID FK2 (1,1)
Invoice_Price Invoice_Price
Product_Quantity Product_Quantity
Products Entity
Product_ID PK
(1,N) Product_Name
Product_Price
Product_Type
Discounts Entity
Discount_ID PK d Product_Type
Discount_Name
Percentage
Product_Type = "M" Product_Type = "B"
Chefs Entity Menus Entity (1,1) Beverages Entity
Chef_ID PK Menu_ID PK Beverage_ID PK
Chef_Name (1,N) Menu_Name Beverage_Name
Salary Menu_Price Beverage_Price
Chef_Email_Address Chef_ID FK Supplier_ID FK (1,1)
(1,1)
Breaks Down
Supplies
Menu_Recipes Entity (1,N) (1,N) Ingredients Entity (1,N) Suppliers Entity
Menu_ID PK, FK1 Ingerdient_ID PK Supplier_ID PK
Ingerdient_ID PK, FK2 Ingerdient_Description Supplier_Name (1,N)
Ingredient_Quantity (1,1) Supplier_ID FK (1,1) Supplier_Email_Address
Supplier_Address
The alternative Invoice_Details
entity is to have Invoice_No and
Product_ID as the composite PK.
(a) I have put all the entities as mandatory
participation, but you can have optional
participation with reasonable justification.
(b) The justification for having Invoice_Price in the
Invoice_Details entity is on Slide 22 in Week 4
lecture - "crucial information purposes".
(c) I have not addressed the physical quantities of
the Products (Beverages, ingredients and so on).
Addressing this issue will bring complications to the
ERD. Again, nothing stopping you from addressing
this issue.
Contains
Discount Entity is not added here.
However, you can include this
entity if you want to include
discounts on the invoices/orders.
Note: All cardinalities are: (1,1)
to (1,N) between the entities as
it is assumed they are all
mandatory relationships.
Cooks
use
Supplies
You can use (1,N) or (1,M).
The Crow-Foot notation for an entity in the textbook
is shown below. The ones I used are here for
teaching in the past to tell the students this is an
entity. Either notations are fine but it is better if you
use the notations from the textbook.
Ships Orders
Customers
d
Sample Model