The owner of Royal Foods & Cafe (RFC) wants to develop a software application for his...

Transcribed Image Text:

The owner of Royal Foods & Cafe (RFC) wants to develop a software application for his new restaurant. The aims are to keep track of orders, food and drinks on the menu, ingredients and their stocks. He is also interested in getting information about the most popular food items and drinks. You have been asked to design a database for Food Order Management System (FOMS) for RFC. A customer can place one food order at a time, which is assigned to one of the chefs at RFC. FOMS is expected to keep track of customer names, and optionally their email and phone numbers. For each chef, FOMS will store first and last names, title (like Junior Chef, Senior Chef, Head Chef) and joining date. Each food order has a unique order-id and we need to keep track of date, time, amount, tax and total amount for the order. An order may contain multiple food items, and each item may have multiple instances. A customer may rank each food item, on a scale of 1 to 5, where 5 is the best rating. This information will be used by the owner to keep track of best selling dishes and drinks. Each food item may be either a dish or a drink, and we collectively refer to these as FoodnDrinks. FOMS should keep track of each food item's name, price and optionally a link (URL) to it's recipe, which is available on the RFC's website. For every item in Food- nDrinks, we would like to keep track of the required ingredients, their quantities, name, type (like dairy, oils, vegetables, meats, fruits, spices etc), units (like grams, milliliters) and cost-per-unit. Lastly, the owner also wants to keep track of the kitchen stock, by storing information about each ingredient, its batch-num, stock quantity and expiry date. (a) Design an Entity-Relationship model for the Food Order Management System (FOMS). Explain the key design decisions made in your choice of entities, relation- ships and any other (ownership or hierarchical) aspects. [9 marks] Annotate it with multiplicities and hierarchy annotations. [4 marks] (b) Carry out logical design for the model, representing the design with relational schemas for tables. Annotate the schemas with primary keys and the possibility of null attributes. [7 marks] The owner of Royal Foods & Cafe (RFC) wants to develop a software application for his new restaurant. The aims are to keep track of orders, food and drinks on the menu, ingredients and their stocks. He is also interested in getting information about the most popular food items and drinks. You have been asked to design a database for Food Order Management System (FOMS) for RFC. A customer can place one food order at a time, which is assigned to one of the chefs at RFC. FOMS is expected to keep track of customer names, and optionally their email and phone numbers. For each chef, FOMS will store first and last names, title (like Junior Chef, Senior Chef, Head Chef) and joining date. Each food order has a unique order-id and we need to keep track of date, time, amount, tax and total amount for the order. An order may contain multiple food items, and each item may have multiple instances. A customer may rank each food item, on a scale of 1 to 5, where 5 is the best rating. This information will be used by the owner to keep track of best selling dishes and drinks. Each food item may be either a dish or a drink, and we collectively refer to these as FoodnDrinks. FOMS should keep track of each food item's name, price and optionally a link (URL) to it's recipe, which is available on the RFC's website. For every item in Food- nDrinks, we would like to keep track of the required ingredients, their quantities, name, type (like dairy, oils, vegetables, meats, fruits, spices etc), units (like grams, milliliters) and cost-per-unit. Lastly, the owner also wants to keep track of the kitchen stock, by storing information about each ingredient, its batch-num, stock quantity and expiry date. (a) Design an Entity-Relationship model for the Food Order Management System (FOMS). Explain the key design decisions made in your choice of entities, relation- ships and any other (ownership or hierarchical) aspects. [9 marks] Annotate it with multiplicities and hierarchy annotations. [4 marks] (b) Carry out logical design for the model, representing the design with relational schemas for tables. Annotate the schemas with primary keys and the possibility of null attributes. [7 marks]