Question

Perfect Pizza is a pizza delivery and pick up shop. They are hiring you to create a database designed to track their customers, employees, products, and orders. Their needs are outlined below. They want to use the customer’s telephone number to identify the customer. They are interested in households, not individuals. They need the street address and zip code for delivery. They only deliver to the following zip codes: 29572, 29575, 29577, 29578, 29579, and 29588. Since all zip codes are in the Myrtle Beach area, they would like that information auto filled for the city and state, but want the option to enter other cities and states due to pick up orders from tourists. They also need an order detail table to store the price charged. They need to be able to change the price charged for two reasons: it may be different than product price due to a discount or special allowance and it will keep a historic record of the price. That way, when they are updating a product in the product table, it doesn’t affect the prices charged in past sales. 1) Make a list of Entities you feel this database needs and give an explanation for your reasoning behind each one. 2) For each of the entities you listed above, list the attributes you feel need to be included in each of those entities. 3) Identify the attribute that would be designated as the primary key in each entity. 4) Make a list of questions you would ask to help you better design the database. (Must be at least 5 questions) 5) Make a list of suggestions for the pizza shop owners to improve the database. (Must be at least 3 suggestions)

Answer 1

Perfect Pizza

1) List of Entities

1. Customers- To record the personal details of the customers
2. Employees- To record the personal details of the employees
3. Products- To record the details of the products
4. Orders- To record the details of the orders at the perfect pizza
5. Address- To record the personal address details of the customers and employees

2) List of attributes

1. Customers- (Customer_id, First_name, Last_name, contact, gender, dateofbirth, addressID)
2. Employees-(Employee_ID, First_name, Last_name, contact, gender, dateofbirth, addressID, job_description, salary)
3. Products- (Product_ID, Name, description, Cost)
4. Orders- (Order_id, Customer_id, Employee_Id, date, time, Product_id, quantity, cost)
5. Address-(house_number, street, Colony, City, state, country, Zipcode)

3) Primary Key in each Entity

1. Customers- Customer_id
2. Employees- Employee_ID
3. Products- Product_ID
4. Orders- Order_id
5. Address- house_number

4) List of questions

1. Would you like to create sub-types of the products, like veg, non -veg separate categories?
2. Would you like to have order history for each customer?
3. Would you like to have order dicount record for each customer?
4. Would you like to have employee order history separately?

5) List of suggestions

1. separate products category coulde be created.
2. Product customization should be an option with special ingredients table separetly.
3. Customer order history should be there

If any doubt, please feel free to ask and comment, i would like to answer them.
Thank you