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. They want to use the customer’s telephone number to identify the customer. They also are interested in an order detail table to store the price charged. They restrict delivery to zip codes: 29572, 29575, 29577, 29578, 29579, and 29588. They want the city and state auto filled but want the option to enter other cities and states due to pick up orders. For Milestone 2: 1) Using the entities and attributes you created in Milestone 1, create an Entity Relationship Diagram (ERD) for Perfect Pizza a. Be sure to indicate the Primary Key in Bold b. Be sure to indicate the Foreign Key in Italics 2) For each Entity: a. List the Attributes with the Data Type, Field Size, and Format if needed you plan to use. This should be neatly done in a separate table for each entity

Answer 1

ERD for given requirement :

Question 1:

This ERD consist Entities are :

1. Customer entity
2. Order entity
3. Product entity
4. Order_product entity
5. Employee entity
6. Order_Detail entity

Relationships :

1. There are make relationship between Customer entity and Order entity
2. There are consist relationship between Order entity and Order_Detail entity
3. There are For relationship between Order entity , product entity and Order_product entity
4. There are Handle relationship between Product entity and Employee entity

Multiplicity :

1. There are 1:M multiplicity between Customer entity and Order entity
2. There are 1:M multiplicity between Order entity and Order_product entity
3. There are 1:M multiplicity between product entity and Order_product entity
4. There are M:M multiplicity between Employee entity and product entity
5. There are 1:1 multiplicity between Order entity and Order_Detail entity

Pk and FK in the ERD:

PK :

• 1) TelephoneNo : PK in Customer entity
• 2) OrderID : PK in Order entity
• 3) product_id : PK in product entity
• 4) Employee_id : PK in employee entity

FK :

• 1) TelephoneNo : FK in Orders entity
• 2) OrderID : FK in Order_Detail entity and Order_product entity
• 3) product_id : Fk in Order_product entity   

Question 2:

Attributes with its data type :

1) Customer entity  

Attributes	Data type
TelephoneNo	INT(10)
name	VARCHAR(20)
email_id	VARCHAR(40)
state	VARCHAR(20)
city	VARCHAR(20)
ZIP_CODE	INT(10)

2) Orders entity:

Attributes	Data Type
OrderID	INT(15)
Type	VARCHAR(10)
Date	DATE
Time	TIME
TelephoneNo	INT(10)

3) Order_Detail entity:

Attributes	Data Type
price_charged	DECIMAL
City	VARCHAR(20)
state	VARCHAR(10)
ZIP_Code	INT(10)
OrderID	INT(15)

4) Order_product entity:

Attributes	Data Type
OrderID	INT(15)
Product_ID	INT(10)

5) Product entity:

Attributes	Data Type
product_id	INT(10)
name	VARCHAR(10)
description	VARCHAR(225)
price	DECIMAL

6) Employee  entity

Attributes	Data Type
Employee_id	INT(5)
name	VARCHAR(20)
address	VARCHAR(20)
PhoneNo	INT(10)

Thank you..............