Reference no: EM133732553

Data Modelling and Database Development

Assessment - Entity Relationship Diagram (ERD) Case Study Assessment

Assessment Detail 1: For this assessment, you are provided with a case study (see page 3) for which you are required to develop an appropriate ERD and third normal form relations. The purpose of this assessment is to assess your knowledge about ERD for a business case study and transform the ERD to 3NF Relations.

Assessment Description:

Case Study:

Foodservice Australia is the leading exhibition for the food industry in Australia. Foodservice Australia has just run in Sydney and was a huge success, connecting thousands of industry buyers with the latest food. Next year, the exhibition will be running in Melbourne. There will be over 450 chefs (exhibitors), both established and lesser known, and special events, including the Chef of the Year. The Event Organiser has decided to assign you the task of building an IT product for the upcoming event in 2025. The core of this product is a database that captures all the information the food exhibition needs to maintain.

Foodservice Australia keeps information about chefs, each represented by their name (first name, last name), address (street, suburb, state, postcode), phone number, and email. Chefs prepare and present various dishes, and a chef may prepare at least one dish. Each dish is from a cuisine (e.g. Italian, Chinese, Indian), and is given a short descriptive title. Ingredients used for the preparation of the dish need to be recorded, along with the price of the dish.

Dishes are exhibited and sold at different food stalls, each identified by a name and a location. A food stall exhibits one or more dishes, however a particular dish is presented at only one food stall. Furthermore, a food stall is managed by at least one food stall keeper. A food stall keeper has a name (first name, last name), address (street, suburb, state, postcode), phone number, and email, and manages only one food stall.

A customer has a name (first name, last name), address (street, suburb, state, postcode), phone number, and email. Orders are of two types, dine-in orders and take-away orders. A customer may place any number of dine-in orders and any number of take-away orders, however, each dine-in order and each take-away order is placed by only one customer. For each order, a quantity and an order date must be recorded. Additionally, a dine-in order has a time for preparation and a service charge, and a take-away order has a time for collection. Furthermore, a dish may be included in any number of dine-in orders and any number of take-away orders. However, it is required that each dine-in order includes only one dish, and each take-away order includes only one dish.

Question 1: Create an ERD for Foodservice Australia, following the Crow's Foot Notation. Note the following requirements in your ERD:

Identify the key entities with suitable names.
Include the attributes for each entity with appropriate naming standards and mark the primary keys.
Model the relationships and cardinalities between the entities.
Indicate the minimum and maximum cardinality constraints for each relationship (e.g., optional/mandatory, one/many).
State any assumptions and/or business rules you make about the data or relationships between entities, if not specified in the description. Avoid introducing new rules if they have been already described in the text.
Chen's Notation or UML Class Diagram Notation will not be accepted.

Question 2: Map the ERD into a set of relations and transform to the third normal form (3NF).

Write your answer in the Database Design Language (DBDL) form.

Question 3: Suppose the following changes to the relationships apply.
Each dine-in order may include one or more dishes, each with separate quantities.
Each take-away order may include one or more dishes, each with separate quantities.

How do you modify the ERD and relation schema in previous questions (1) and (2) to accommodate the proposed changes?

Note: You are not required to redraw the entire ERD or rewrite the entire DBDL. You may show only the changes to the associated entities/relations in the previous ERD and relation schema documentation (DBDL).

Question 4: "Why database normalisation is important?". Discuss with relevance to the above case study. Word limit is 130 - 150 words (strict word limit apply).

State the word count.