Delitaste Pizza Project – Requirement Analysis and Conceptual Design
Delitaste Pizza is a pizza outlet in Newtown. After years of managing the records manually, Delitaste Pizza has decided to computerise its records. You are tasked to design a database for the shop.
The proposed database system is developed in various modules, including requirements analysis, conceptual database design, logical database design, and physical database design. In this assignment 1, you are required to develop user requirements specification and the conceptual database model for Delitaste Pizza’s database based on the business requirements provided in this document and related discussions in lectures.
Your lecturer will act as your client and you can query him/her for any further information and clarifications.
Main Features and Business Requirements
Delitaste Pizza takes orders via phone as well as through walk-in customers. Delitaste Pizza provides both delivery and pickup services. When a customer orders, the customer’s phone number is entered into the system along with the id of the staff taking the order. If the customer has previously ordered, the name and address appear on the screen. The customer is then asked for his/her name and address, and then the order is taken. If the customer has not ordered before or if the name and address given do not correspond with what is recorded in the computer, a new customer record is created and the order is taken. For each phone order, the time the call was answered as well as the time the call was terminated is recorded. For each walk-in order, the time the customer walks in is recorded.
For a phone order, after the order has been taken, a verification process occurs whereby the staff dials the number given and confirms the order with the customer. If it is not confirmed, the customer is recorded as a hoax and the order is kept on hold (if and until the customer calls back in which case the verification process takes place before the hoax is removed and the order goes through).
Each order contains the date of order, the items ordered, the quantity of each item, the price of each item, the total amount due, payment method, order status, type
(phone/walk-in), and description. If the payment is via card, a payment approval number is recorded. For a phone order, if the order is a pickup order, the pickup time is recorded; if the order is a delivery order, the delivery time and address and the driver who delivered the order are recorded. For a walk-in order, only pickup is available, where the pickup time is recorded.
Menu Items, Ingredients, and Ingredient Order
Each item in the menu has an item code (unique), name, size and a current selling price. An item in the menu is made up of a number of ingredients. The ingredients and quantities used for the item are recorded in the database.
Each ingredient has a code (unique), name, type, description, stock level at current stocktake period, date last stocktake was taken, suggested current stock level, reorder level.
A stocktake is taken each week, where the actual levels of ingredients in store (i.e., stock levels of ingredients at the current stocktake period) are collected. The actual levels of ingredients in store, together with suggested current stock levels and reorder levels, are used by the store manager to order ingredients for the following week. Information about ingredient orders needs to be maintained in the database, including order number, date of the order, date received order, total amount, order status, description, quantity and price of all ingredients, and ingredient code.
Employees at the store can be divided into two types: those who work in the shop are paid hourly and those who carry out deliveries are paid by the number of deliveries. For each employee, there is an employee number, firstname, lastname, postal address, contact number, tax file number, bank details (bank code, bank name, account number), payment rate, status, and a description. Drivers also have a driver’s license number. Hours are not regular and a record is kept for each time an employee works – a shift (start date, start time, end date, end time). The orders a driver delivers during a shift is kept on the record.
Payment rates for shop workers and drivers are maintained in the database. Employee payments are made for each shift to the employee’s bank account. Employee payment record needs to be maintained in the database. It includes gross payment, tax withheld, total amount paid, payment date, payment period start date, payment period end date, and bank details of the employee.
The proposed database system is developed in various modules, including requirements analysis, conceptual database design, logical database design, and physical database design. In this assignment 1, you are required to complete the first two stages of the database design, i.e., to develop user requirements specification and the conceptual database model for the database based on the business requirements provided.
Part 1: Requirements
In this assignment, you are required to develop a user requirements specification truly fulfilling the data requirements (identify what types of data needs to be stored in the database), transaction requirements (identify the important and frequent database operations – data manipulation and queries), and business rules for the Delitaste Pizza’s database mentioned above.
Part 2: EER Model with data dictionary
Draw an EER model for the requirements identified in Part 1. The EER must be included in the report (you also need to submit your original EER file). The EER Model must be shown in UML notation which is discussed in class and provided in our text.
The EER model should be accompanied by a data dictionary which includes entity type table, relationship type table and attribute table.
Assignment submission format for the EER Model with Data Dictionary part: The document MUST have the following sections:
- Documentation – Data dictionary details (description of entities, relationships and attributes)