Question

Draw the Entity Relationship Diagram based on the requirements below. (use all forms of cardinality)

Both restaurant chains – ‘Lord of the Fries’ and ‘Pie In The Sky’ – require an application to manage food wastage. The Application Architect has analysed both sets of requirements and found a common application will work for both companies. You have been commissioned to draw the Entity Relationship Diagram (ERD) for the application. The Architect as analysed the requirements and rationalised the entities for the core data of the application. The core data will store Recipes, Shopping Lists, and record what Food a restaurant currently has on hand in the Refrigerator, Freezer and Pantry. The companies have multiple places they shop for food, so separate Shopping Lists need to be catered for. The application will then work with this data to help manage the food wastage in the restaurants. Architect Notes: The central Entity is Food. It requires a Primary Key – FoodID – that will be used in the relationships to the other Entities. The Food Entity requires an Attribute for the Name as well as ContainsGulten, ContainsDairy and ContainsNuts to record possible allergies. The second Entity is Recipe. It requires a Primary Key – RecipeID – for use in the relationships to other Entities. A Recipe has Attributes for Name, Description, CookingDuration and ServingSize. A Recipe can have Many steps to describe how to prepare a meal, so an Entity RecipeMethod is required. RecipeMethod has a composite Primary Key, the RecipeID for which the method belongs AND a RecipeMethodID. The RecipeMethod also requires an Attribute for Method. Each Recipe has at least one Method. There is a Many-to-Many relationship between the Food and Recipe Entitles. Resolve this with an Entity called RecipeIngredient. The RecipeIngredient needs to also record Quantity and the Units of the Food used in the Recipe. (For example, 3 Cups of Flour). Each Recipe has at least one Ingredient, but not every Food is in a Recipe – some Food is sold as-is (ie soft drink). Instead of making separate entities for Refrigerator, Freezer and Pantry, a single entity Storage will be used. It requires a Primary Key – StorageID – and an attribute for Name (e.g. Refrigerator, Freezer, Pantry). There is a Many-to-Many relationship between Food and Storage. Resolve this with StorageItem and appropriate composite Primary and Foreign Keys. Attributes for Quantity, Units and ExpiryDate are required. Each Storage has at least one Item. Instead of making separate lists for different shopping locations, a single entity ShoppingList with a Primary Key and Description attribute is required. The Many-to-Many relationship with the Food Entity needs to be resolved with ShoppingListItem and appropriate Primary and Foreign Keys. Attributes for Quantity, Units and IsPurchased are required. A ShoppingList starts empty

Answer 1

The Entity Relationship Diagram will contain entities for Food, Recipe, RecipeMethod, RecipeIngredient, Storage, StorageItem, ShoppingList, and ShoppingListItem, each with their own attributes, keys, and relationships to manage food wastage in the restaurants.

Step-by-step explanation:

To illustrate the database design required for the restaurant chains ‘Lord of the Fries’ and ‘Pie In The Sky’ for managing food wastage, we will create an Entity Relationship Diagram (ERD). This ERD will map out the essential entities and relationships based on the provided specifications.

The primary entity is Food, with attributes such as FoodID (Primary Key), Name, ContainsGluten, ContainsDairy, and ContainsNuts. The next entity is Recipe, which has attributes including RecipeID (Primary Key), Name, Description, CookingDuration, and ServingSize. Recipes are linked to RecipeMethod through a one-to-many relationship, as each Recipe has multiple methods. Here, RecipeMethod has a composite Primary Key of RecipeID and RecipeMethodID, and an additional attribute called Method.

To resolve the many-to-many relationship between Food and Recipes, we introduce the RecipeIngredient entity, holding the composite keys of FoodID and RecipeID, as well as the attributes Quantity and Units. Then, we establish a Storage entity that contains a StorageID (Primary Key) and an attribute for its Name like Refrigerator, Freezer, or Pantry. The many-to-many relationship between Food and Storage is handled by the StorageItem join table, capturing Quantity, Units, and ExpiryDate of food items.

Lastly, ShoppingList and its related entity ShoppingListItem are created to handle shopping data. The ShoppingList has a Primary Key and a Description, while ShoppingListItem links to it and to Food, recording attributes such as Quantity, Units, and IsPurchased.