Reference no: EM133748106

Data Modelling and Database Development

Assessment Description:

Part A: Design and implement a relational database management system (RDBMS)

Consider the following Case Study (from AT2).
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 described by a short descriptive title, cuisine (e.g. Italian, Chinese, Indian), ingredients used for preparation, and unit price, and is prepared by only one chef.

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. 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.

Suppose you were hired by Foodservice Australia to create a database for the above Food Exhibition, which ran for 5 days from 01-12-2024 to 05-12-2024. The corresponding relational database schema is as follows.

Chef (chefID, chefFirstName, chefLastName, chefPhoneNumber, chefStreet, chefSuburb, chefState, chefPostCode, chefEmail)

FoodStall (foodStallID, foodStallName, foodStallLocation)

Customer (customerID, customerFirstName, customerLastName, customerPhoneNumber, customerStreet, customerSuburb, customerState, customerPostCode, customerEmail)

FoodStallKeeper (foodStallKeeperID, foodStallKeeperFirstName, foodStallKeeperLastName, foodStallKeeperPhoneNumber, foodStallKeeperStreet, foodStallKeeperSuburb, foodStallKeeperState, foodStallKeeperPostCode, foodStallKeeperEmail, foodStallID)

Dish (dishID, dishTitle, dishCuisine, dishDescription, dishIngredients, dishUnitPrice, chefID, foodStallID)

DineInOrder (dineInOrderID, dishID, customerID, quantity, orderDate, timeForPreparation, serviceCharge)

TakeAwayOrder (takeAwayOrderID, dishID, customerID, quantity, orderDate, timeForCollection)

Question 1. Create a MySQL database for the above relational database schema in MySQL Workbench (you may follow the Week 8 Tutorial to install MySQL and MySQL Workbench).
Write SQL statements to create the tables in the above relational database schema, with suitable data types for different fields, primary keys, and foreign keys.

Insert meaningful data into all the tables. Provide screenshots of all tables populated with data (SQL statements for inserting data into the tables are not required as answers).
Important: Make sure that you add enough data records to the tables such that the result (query output) is not an empty set for any of the questions below.

Question 2. For each of the questions below, write the SQL statement and provide a screenshot of the result (query output).

List all the Chefs in a specific suburb of your choice (e.g., Clayton).

List the DineInOrder IDs that included Dishes belonging to a specific cuisine of your choice (e.g., Italian).
List the Food Stall Keepers (foodStallKeeperID, foodStallKeeperFirstName and foodStallKeeperLastName concatenated as foodStallKeeperFullName) who managed a Food Stall that offered a dish of a specific cuisine of your choice (e.g., Italian).

List the customers (customerID, customerFirstName and customerLastName concatenated as customerFullName) along with the number of dine-in orders they placed, naming the field as NumberOfDineInOrders. Note, NumberOfDineInOrders = 0 if a customer did not place any order for dine-in.

List the customers (customerID, customerFirstName and customerLastName concatenated as customerFullName) along with the number of dine-in orders and take-away orders they placed, naming the fields as NumberOfDineInOrders and NumberOfTakeAwayOrders, respectively.

List the chefs (chefID, chefFirstName, chefLastName) who prepared more than one dish, along with the number of Dishes made, naming the field as NumberOfDishes. Order the list from the greatest NumberOfDishes to the least. Add a secondary sort to order the list by the chefFirstName.

Which dishes were ordered more than once for take-away during the period from the second day to the fourth day of the exhibition? Show the Dish ID, Dish title, and the number of times it was ordered for take-away, naming the field as NumberOfOrders.

List the dishes (dishID, dishTitle) that did not have any orders.

List the Food Stalls (foodStallID and foodStallName), along with the total sales from take-away orders, naming the field as TotalTakeAwayOrderSales. Note that the quantity of dishes and unit price for each dish are provided.

Part B:

Critically analyze and investigate the importance of enforcing database integrity features for the above case study and write a report of 1500 words presenting your analysis and recommendations.

Include an Introduction, Body, Conclusion, and References. Organize the Body with proper sections and subsections as needed.