Case Study - Scenario 1 (ER Model)

You have been engaged by a pizza shop to create a database to enable the business to efficiently keep records. The pizza shop sells pizzas to customers who walks in (takeaway) and also takes phone orders which may be delivered to the customer's address or held at the shop until the customer comes in to pick them up. When a customer orders by phone the shop assistant taking the order asks the customer for their phone number and enters it into the computer system. The assistant also enters his ID number so a record is also kept of who took the order. The time when the phone was answered is recorded as well as the time when the phone call was terminated. (From this we can calculate how many orders each assistant took and the average time for each order). If the customer has previously ordered by phone the name(last name only) and address appears on the screen. The customer is then asked for his name and address and then takes the order. If the customer has not ordered before or if the name and address given does not correspond with that in the computer or if that phone number and address is marked as being the subject of a hoax previously, after the order has been taken the assistant dials the number given and confirms the order with the customer. It is required that each order given by a customer be recorded. The price of each item is recorded with the order. Pizzas are made from ingredients. From the database you must be able to determine the name and amount of each ingredient for a particular type of pizza. For each ingredient is kept the amount on hand (in the shop) and the date of the last stocktake. (Stocktakes are done weekly). It should be possible to calculate from the database the total amount of each ingredient used in the making of the pizzas from one stocktake to the next. Any variation from the expected amount is also recorded for that stocktake as a percentage e.g.-1%. For each ingredient we also keep the name and address of the supplier. Each ingredient can be supplied by many suppliers and each supplier can supply many ingredients. Not all pizzas are the same price, so a record exists in database for the current price of each type of pizza. The employees at the shop may be divided into two types: those who work in the shop and those who carry out the deliveries (the drivers). For each employee is kept their name and address, home phone number and tax file number. For the drivers is also kept their driver's license number Hours of work are not regular so for each time an employee works a record must be kept of their hours. Employees inside the shop are paid at an hourly rate. Drivers are paid according to the number of deliveries the do. A record is kept for which orders a driver delivers and of how many deliveries a driver does on each shift.