Question : Conceptual Design

Use the case study description and list of requirements below to create an entity-relationship diagram showing the data requirements of the FastFoods4U database. Your ERD should be able to be implemented in a relational DBMS.

Bill is a university student who has been picking up meals from certain restaurants for his family on the way home from uni for the last two years, and has now hit upon the idea of making it into a business, which he is calling FastFoods4U. He intends to make available the menus of all the local restaurants for delivery to the homes in his neighbourhood, and has recruited several members of his family and some of his closer friends to make the deliveries. If the trial run is successful, he plans to make it into an Uber-style business (similar to UberEATS and Deliveroo).

Bill has phoned around the higher rated ethnic restaurants in his suburb, and has made a list of 20 who are willing to co-operate with him. Each restaurant has selected a subset of the items on their menus that they think will last the journey from their kitchen to the customer. They have decided that no high-cuisine meal can last more than 10 minutes in a heated container, so that has limited Bill's area of service - customers can only be from the same suburb as the restaurants.

Customers will order their food via a webpage, after registering on the site. They can register from any location, but won't be able to use the site unless the delivery address is in the same suburb as the restaurants. They can select a restaurant and choose a number of dishes from it, referring to the information available on the web page. They can also select dishes directly, by searching on particular requirements such as 'vegetarian' or 'pizza'. However, they can only order from one restaurant per delivery.

Once they have selected their dishes, the customer enters the delivery date, time and address required, and pays the cost of the meal plus delivery via PayPal. The information about the order is sent to the restaurant and also to Bill, who assigns a driver who is currently free to pick up and deliver the order. The driver collects the meal from the restaurant and delivers to the customer. The driver records the actual date and time delivered, as Bill needs to keep track of whether he can live up to his promise to deliver on time.

Bill has heard that you are studying Databases and has asked you to design a database to keep track of the information requirements of his business. He wants the database to record information about customers, restaurants, dishes, drivers, and of course orders and deliveries. At this stage he does not want you to model any of the financial side of the business.

He wants to record various items of information about each restaurant, including its ethnicity (Malay, Indian, Chinese, French, Italian, Australian...) and predominant style (BBQ, formal, pub grub, noodle house, open spit, dim sum, fast food ...). A brief description of each restaurant ('About Us') is to be included, as well as a general description of their food. He also wants to record any special certifications the restaurant as a whole has (e.g. vegan, locavore, organic, nut free, Jain, Halal, Kosher).

The dishes at each restaurant also need to have enough information stored about them so that the customers know what they are selecting. As well as name and brief description, customers are likely to want to know in general terms how the dish was prepared (fried, steamed, raw etc), its main ingredient (fish, cheese...), what type of course it is (soup, starter, main, dessert, side dish) and of course its price. As customers are increasingly aware of health issues, Bill also wants to record the

number of kilojoules in each dish, and also whether it is gluten free, dairy free, vegan, and/or vegetarian, and possibly other nutritional aspects of the dish in the future.

Customers also need some indication of how long the dish will take to arrive: Bill guarantees 10 minutes delivery from when the dish is picked up, but obviously some dishes take longer than others to prepare. He has a rough categorisation of 'fast' (under 15 minutes, including delivery), 'regular' (15 minutes to half an hour), and 'worth the wait' (over half an hour) total time from order to door for each dish.

Although all the actual ordering will be done through the website, Bill wants to print a booklet for each restaurant, so they can have it available to their in-house customers for advertising.

Below are several queries and reports that Bill has requested the database must be able to support. There may well be many others as Bill analyses his business and plans for the future; therefore, you should design for flexibility.

The database will have to support at least the following querying and reporting requirements:

1. All the details of an order for a particular customer. The driver needs this to pick up the dishes from the restaurant, and to confirm with the customer on delivery.
2. All the vegetarian dishes that can be delivered to the customer in less than half an hour.
3. The details of the orders for a particular restaurant on a particular date.
4. A list of all the vegan restaurants and the names, description and prices of the dishes they offer.
5. List of all drivers, and the customers (if any) they delivered to on a particular date.
6. List of drivers who are currently free (i.e. not out on a delivery).
7. The total number of orders for each restaurant so far.
8. The booklet which lists the dishes available from a particular restaurant, with their names, descriptions, course type, prices and delivery time.

What you have to do:

•  Use the case study description and querying requirements to create an entity-relationship diagram (ERD) for the FastFoods4U database. Your ERD should be able to be implemented in a relational DBMS.
•  List and explain any assumptions you have made in creating the data model.
•  You should use the crow's feet ERD notation we have been using in the lectures, and should include a legend to explain the notation. You should include attributes in the ERD, and indicate primary and foreign keys. The use of a drawing tool such as Visio will make this task easier.
•  Whichever tool you use, you must copy and paste the ERD into a word-processed document. This is because your tutor might not have access to the tools you have used.
•  Make sure your diagram is readable, e.g. by increasing the size of the attribute names and crow's feet if necessary.
•  Please note that hand-drawn ERDs are not acceptable. Some important things to note:
•  You don't have to create the database or any of the reports at this point. However, Assignment 2 will involve creating the database from your design, so you should be satisfied that it will work.
•  You should make any assumptions that are required, but must state them clearly. Obviously, your assumptions should not contradict any of the information already provided.