Question

Draw the MySQL Data Model

Maria’s Pizza – a local pizza shop that provides pizzas for pickup with one location - has hired you to create a database for them to store information about the store and its operations. Prior to building the database, you have decided to present the data model to the client for her approval. Here are the set of requirements that you have for the data model.

The client would like you to track the employees that they have in their store and the shift that is worked by the employees. They need to keep track of personal information about the employee including their name, phone number, address, social security number and other relevant information. This information does change from time to time as employees occasionally move. The store maintains two shifts, mid-day (11 AM – 5 PM) and night (5:00 PM to 11:00 PM). Employees can work across multiple shifts and shifts have multiple employees that work in that period. Shifts also have an employee as a supervisor. Therefore, different employees have different shift supervisors depending on which shift they are working – an employee in one shift may be the shift supervisor in another shift during the week.

Maria’s Pizza would also like this database to tie into their inventory system of food products that they offer. They sell only pizza for pickup, so the ingredients are what they need to keep track of – and all ingredients are tracked by packages of certain ounces (for example flour comes in 400 oz packages, mushrooms come in 64 oz packages, mozzarella cheese comes in 128 oz packages). They need to know the purchase price for each package, and the date of receipt of each shipment, and they update the quantity on hand every night before closing.

Maria’s Pizza has a single food distributor as its main supplier, but will buy a few items like sausage and fresh tomatoes from other local sources. After conducting inventory every night, they send orders if inventory levels get below certain thresholds. They would like to track the reordering of the different products from the suppliers including the date the re-order was placed, the employee that placed the order, and the scheduled delivery date of that order. Products can get re-ordered multiple times and employees can place multiple orders for products. Supplier information is also to be stored in the database including the supplier’s contact information, name and address. Because the same product can get ordered from multiple suppliers, it is important that the reorder contain information about which supplier that re-order was placed with.

Draw a data model for the above scenario.

Answer 1

The below image depicts how many tables are needed to be formed in the database and how they will be connected to one another:

Now, that we are clear on how the tables are inter- related to one another, let's create each table and add their attributes and attribute types:

Employee - Here, Employee_No, SSN are the primary keys.

Employee_No	Varchar(10)
Name	Varchar(25)
Phone	Varchar(12)
Address	Varchar(50)
SSN	Varchar(14)

Shifts - Here, Shift_ID is the primary key and Supervisor is the Employee_ID which is a foreign key (ie., primary key from the Employee table above)

Shift_ID	Varchar(10)
Shift_Type	Varchar(10)
Shift_Start	DateTime
Shift_End	DateTime
Supervisor	Varchar(10)

Shift_Detail - Here, Shift_ID is the foreign key (ie. primary key from Shift table) and Employee_ID which is a foreign key (ie., primary key from the Employee table above)

Shift_ID	Varchar(10)
Employee_No	Varchar(10)

Inventory - Here, Shipment_No is the primary key for this table.

Shipment_No	Varchar(10)
Item_Name	Varchar(30)
Package_Quantity	Int
Purchase_Price_Per_Package	Int
Each_Shipment_Date	Datetime
Quantity_On_Hand	Int
Updated_Shipment_Date	DateTime
ReOrder_ID	Varchar(10)

Reorder - Here , Reorder_ID is the primary key for Reorder table. Supplier_ID is the foreign key (ie. primary key for Supplier table, below).

Reorder_ID	Varchar(10)
Reorder_Date	DateTime
Employee_No	Varchar(10)
Delivery_Date	DateTime
Supplier_ID	Varchar(10)

Supplier - Supplier_ID is the primary key here.

Supplier_ID	Varchar(10)
Name	Varchar(25)
Phone	Varchar(12)
Address	Varchar(50)