Instructions:

You are tasked with providing database support to a small local ice cream shop. You will need to create an Entity Relationship diagram for the business and answer the below questions describing the data you are going to store.

You will provide some answers in this document which you should SAVE AS and name your file as HW1_LastName_FirstName. You need to use an ERD software product (i.e., MS Visio, ERwin, draw.io, etc.). You can save your ERD and relational schema as .jpg files and insert them into the end of this document. Alternatively, you can submit a PDF of your ERD and relational schema named as HW1_Figures_ LastName_FirstName. Please make sure the resolution of your ERD is sufficient to read.

The assignment is meant to test your thought process on thinking about how to store data, the scenario is meant to be missing a few pieces which youll want to think about. At the end of this document, you can provide any additional notes that you want to include on how you developed your ERD.

Assignment:

The Ice Cream shop keeps data on their customers, which includes an email address and the customers first and last name, and a phone number, not all customers have this information, and can only have 1 email, name, and phone number on record for each customer.

The Ice Cream shop is run by Employees with more veteran employees being classified as Managers. Using a database, the shop would like to store the full name, address, phone number and hourly wage of each employee.

The shop only has 3 locations, an Employee can only work at one location at a time. However, each location has multiple employees.

Each Location sells on average 20 different kinds of ice creams, gelatos, or sorbets. Each type of product has multiple ingredients that are used to produce the product.

A customer can purchase a product at any location. Not all products are sold at all locations.

Employees can only have one manager, while Managers have multiple employees.

--You will need to build a final ERD for the above scenario, you can do this at any time, and you might find it helpful to do a rough draft to help answer some of the below questions:

Question #1 (0.5 point): A. Describe the relationship of Employee with Location: (Is it binary or unary, and what is the name of this relationship?) B. Describe the relationship of Employee with Manager: (Is it binary or unary, and what is the name of this relationship?)

Question #2 (0.5 point): A. Describe the Cardinality of Employee and Location B. Describe the Cardinality of Manager and Employee C. Describe the Cardinality of Location and Product

Question #3 (0.5 point): Describe the attributes of the following Entities: A. Employee B. Customer

Question #4 (0.5 point): Not counting a ProductID and ProductName, make up and describe 3 attributes that could be stored in the Product entity: (ex. ProductName would store the name of the product being sold, such as Mint Chocolate Chip)

Question #5 (0.5 point): Make up and describe 2 attributes for the Ingredients entity (not counting the IngredientID you will need), also describe the values of data that would be stored in those fields: (Example: IngredientName : would store the Name of the ingredient such as Milk, Egg, Chocolate (you cannot use IngredientName in your list of 2 attributes))

Question #6 (1.5 point): Build an ERD like we went over in class. Include the attributes from Question #4 (the ones I mentioned, along with the three attributes you described). Include the attributes from Question #5 (include IngredientID and IngredientName).

Question # 7 (1 point): Translate the above ERD into a relational schema following the process we went over in class.