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.