Part 1: Requirements
Data Requirements - outlining the major data items:
1. GourmentPizza:
The shop's registration number given by the local Govt., location as well as contact number is maintained by this data item.
2. Customer: This data item contains details information about customers who want to place order by phone or walk-In. In case of phone order customer, the customer's phone number is recorded. But, customer's name is only stored for walk-in customer. For all types of customers, a unique customer's identification number is stored.
3. MenuItem: The offered items by Gourmet Pizza are maintained in this data store. The attributes must be unique item number, name of the item, size in cm for each item and selling price for the item.
4. Ingredient: This data item is necessary for storing ingredient information for making menu items. The information must be unique identification number, name, type and description of the ingredients, reorder level, the last date when the stock was recorded and suggested stock level.
5. Discount_prog: This data item stores about a particular discount program relating to menu items. The starting date, ending date, requirement, discount % and description for each discount program is stored as well as a unique discount code.
6. IngrOrder: For ordering ingredients in the following week for the shop; this data item is necessary. This data item must store unique identification number for ordering ingredients, actual level of ingredients/week and suggested order for ingredients/week. From these attributes, a derived attribute is necessary for calculating the order needed in the next week.
7. Supplier: It stores all currently suppliers' information including code, name, address and phone number. Those suppliers used to supply the ingredients.
8. Order: This data item stores information of an order placed by the customer. It stores all necessary info about the order including payable amount and payment mode for the order. The other attributes for Order data item should be, date of order, quantity for order, price for that order, discount amount(if any), discount code (if any), status, type and description for that order.
9. Order (Phone): It maintains the order that is done by phone. The customer's phone, duration of phone, call terminates time and CRM's ID must store here.
10. Order (Walk-In): It maintains the order that is done by customer who wants to visit the shop physically. The customer's name is recorded here.
11. ConfirmedOrder: It stores only confirmed order i.e. after confirmation of customer; CRM maintains it. It contains a unique identification number with Order Identification number that is placed at the first time by customer.
12. Unconfirm Order: It stores only unconfirmed order i.e. after checking of unrecognized customers by CRM. It contains a unique identification number with Order Identification number that is placed at the first time by customer.
13. CRM: It stores only assistant's ID and customer's contact number. This entity manages and verifies customers' order by phone as well as keeps confirmed and unconfirmed order after verification of customer. It also keeps records for unrecognized customers.
14. PaymentRec: Customer's payment information after receiving order is stored here. It can be two types like card and cash payment. The payment info should be date of payment, amount and mode of payment as well as a unique transaction number.
15. Card(Payment): It stores payment information after making payment by card. In addition a payment approval number is stored here.
16. Cash (Payment): It stores payment information after making payment by cash. In addition a transaction number is stored here.
17. Employee: All employees' information like first name, last name, address, phone number, tax file number, status and description are stored here. The employees are categorized into two types, FloorStaff and Driver.
FloorStaff: This data item is necessary for storing information about ShopWorker who used to serve for walk-in customers. The information about payment rate/hour, shifting data and total calculated pay is stored in this data item.
Driver: This data item is necessary for storing Information about Driver who used to serve phone order customers for home delivery. The information about license number, payment rate/order, shifting data and total calculated pay is stored here.
18. Bank: This entity stores bank's information of employees including Bank code, Bank name and Account Number.
Transaction requirements - outlining the data manipulation and queries
The data manipulation operations as well as different queries for the database can be managed in the following ways.
Data Manipulation Operations:
- Insert/ update/delete a MenuItem into the database.
- Insert/ update/delete an Ingredient of MenuItem into the database.
- Insert a new customer into the database
- Delete/Update an existing customer from database.
- Insert/ update/delete a service of the shop into the database
- Insert/ update/delete a CRM into the database
- Insert/ update/delete an order by customer into the database
- Insert/ update/delete a confirmed or unconfirmed order by CRM.
- Insert/ update/delete an UnRecognized Customer by CRM.
- Insert/ update/delete a Discount_prog into the database.
- Insert/ update/delete an IngrOrder.
- Insert/ update/delete a supplier for supplying ingredients.
- Insert/ update/delete a PaymentRec made by the customer into the database
- Insert/ update/delete a Driver as an Employee into the database
- Insert/ update/delete a FloorStaff as an Employee into the database
The other Queries for the database can be as follows,-
- Report based on confirmed and unconfirmed order in the last week.
- List all unrecognized customers.
- Display all menu items with size greater than 10 and less than equal to 20.
- Display total ordered amount in the last week.
- List all suppliers who supply the ingredient named cheese.
- Find the amount of ingredient, Bread that will be requiring in the next week.
- Report on currently available Discount program.
- List all currently available FloorStaff.
- Calculate and display the payment of Driver, Mr. John in the last shift.
- Display details of Bank A/C information given by Floor Staff, Mr. Newton.
- Report of total net income for the shop in the last month.
Business Rules:
- The GourmetPizza shop offers one or more Menu items for customers.
- One menu item can relate one or many discount programs.
- A menu item composed of several ingredients.
- A customer can place 0 or more orders that are related to the GourmetPizza shop.
- A customer's order can be two types; order by phone and order by WalkIn.
- The Gourmet Pizza shop offers two types of service like Delivery and Pick up service.
- One CRM (Customer Relationship Manager) manages order by phone call, verifies more than one customers as well as records one or more confirmed and unconfirmed order and unrecognized customers.
- The CRM system must record its identification number with customer's phone number.
- In case of Order (Phone), the call duration and termination call time is recorded along with customer's phone number and CRM's ID number.
- In case of Order (Welkin), the customer's name is only recorded.
- A new or old customer can offer more than one payment by card or cash.
- A supplier can supply more than one ingredient as well as one particular ingredient can be supplied by different supplier.
- A store Manager can manage more than one ingredient orders as well as one ingredient order is related to more than one ingredient.
- The GourmetPizza maintains mainly two types of employees like FloorStaff and Driver for servicing.
- The FloorStaff is paid as per shifting and payment rate/hour where shifting is measured by subtracting staffs' end date & time with start date & time.
- The Driver is paid as per shifting and payment rate/order where shifting is measured by subtracting staffs' end date & time with start date & time.
- Every employee must give their Bank details for their payment. The Bank details like Account number, Bank code and Bank name are stored in a separate entity like Bank.
- A unique key (Primary key) is needed for each entity.
- In case of maintaining ConfirmedOrder and UnConfirmOrder; the order's identification number (O_ID) is necessary as foreign key to check which orders are confirmed and un-confirmed.
Part -2:
Data Requirements - outlining the major data items:
1. Gourment_Pizza:
The name of the shop that stores information about the shop like registration number, address and contact number. The shop performs the various activities like order processing and employee managing as well describes its menus and items.
2. Customer: This entity stores information of physical customers who want to place order by phone or walk-in. Customer's phone number is stored for order booking by phone and customer's name is stored for order booking by walk-in into the shop. The database also stores a unique number for each customer.
3. Item: It contains details information of offered pizza items by Gourmet Pizza like a unique item number, name, size and current selling price for the item.
4. Ingredient: This data item contains information about required various ingredients for making items including unique number, type, name, description, suggested current stock level, reorder level and last date when stock record was taken.
5. IngredientOrder: This data item is needed for keeping records of order ingredients in the next week with the attributes like unique order number, stock take is taken each week, order for week and amount of order ingredients for the following week.
6. Supplier: This data item is used to keep all information about suppliers who used to supply ingredients weekly. This information should be unique supplier number, supplier's name, address and contact number.
7. Order: This data item stores all information about a particular order by the Customer. These information must include unique order number, date of the order, name of the item that is ordered, quantity for the item, price, discount amount, discount code if any for the order, tax chargeable, method of payment (cash or card), status, type and description for the order. The order can be categorized as, confirmed order (after confirmation by the Assistant), Pending order (for hoax customer's order), Phone order (contains phone duration and call terminate time) and walk-In order (stores customer's name).
8. Employee: It stores all information about employees for the shop. This information includes unique employee number, first name, last name, address, contact number, tax file number, payment rate, status and description. The attribute, bank details (contains back code, name and account number) given by each employee is also stored here. The employees are categorized into two types, ShopWorker and Driver.
ShopWorker: This data item contains unique number for the shop worker, start date & time, end date & time, payment rate/hour and total calculated pay for each shift. Each shift is calculated by end date& time- start date& time.
Driver: This data item contains license number of the driver, start date & time, end date & time, number of order delivered/shift, payment rate/order and total calculated pay for each shift. Each shift is calculated by end date& time- start date& time.
9. Discount_Scheme: This data item is required for storing details information about a particular discount program of some items offered by the Shop. These information should be discount code, the start date of discount program, end date of discount program, requirements for discount, discount percentage and a description
10. Payment: It stores information about payment including card and cash payment paid by the Customer. It must contain date of payment, amount of payment and mode of payment. In case of card payment, a unique approval number and for cash payment, a transaction number is maintained.
Transaction requirements - outlining the data manipulation and queries
It can be described as data manipulation operations and queries.
Data Manipulation Operations:
- Insert/ update/delete a Customer into the database.
- Insert/ update/delete an Item into the database.
- Insert/ update/delete an Ingredient into the database
- Insert/ update/delete an order for Ingredient into the database
- Insert/ update/delete a supplier into the database
- Insert/ update/delete an order by customer for menu items into the database
- Insert/ update/delete a ShopWorker into the database
- Insert/ update/delete a Driver into the database
- Insert/ update/delete an office_Assistant into the database
- Insert/ update/delete a Discount_scheme into the database
- Insert/ update/delete a payment info made customer into the database
Several Queries for the databases may be as follows,-
- Searching a confirmed order or pending order or phone call order or walk-in order from database.
- List all valid customers.
- Display all items whose current selling price is more than $20.
- List the item name that is ordered by maximum Customers.
- List all currently stocked ingredients.
- Find the amount of order for ingredients in the next week.
- List all current suppliers.
- Report of pending order and hoax customers.
- List all current Drivers.
- Calculate the total pay of ShopWorker, Mr. Boon for a particular shift.
- List all discount programs that is presently available in the shop.
- Find the total amount paid by cash and card by all customers in the month of February 2016.
Business Rules:
- The Gourmet_Pizza shop offers one or more items for customers.
- The Gourmet Pizza shop offers one or more discount programs of offered items for customers.
- The Gourmet Pizza shop manages more than one employee.
- A customer places one or more orders.
- Office Assistant maintains and verifies zero or more orders.
- A customer is specialized as existing, new and hoax.
- An office Assistant manages a POS (Point of Sale) system.
- A POS system verifies one or more customers.
- An office Assistant keeps one or more hoax customers for unconfirmation.
- An order can be categorized as confirmed, Pending, phone order and Walk-in order.
- Only existing and new customer proceeds for one payment after order confirmation.
- A payment can be categorized as card payment and cash payment where card payment should store an approval number and cash payment must store a transaction number.
- An item is made up of different ingredients.
- One ingredient can be supplied by many suppliers as well as many ingredients can be supplied by one supplier.
- A store Manager maintains different ingredients for one ingredient Order database.
- An Employee can be specialized as a shop worker or a driver.
- Pays for both types of employees are calculated as per shifting. Where, shifting is calculated as end date & time-start date & time of that employee.
- The payment rate is fixed per hour basis for the shop worker as well as payment rate is fixed per order basis for driver.
- Mainly driver is used for phone order delivery and shop worker is used for walk-In customers.
- The POS system must include assistant's ID and customer's phone.
- In case of phone order customer; the call duration with call terminating time is recorded as well as for walk-In customer; the customer's name is recorded into the database.
- Every entity must have a unique key i.e. primary key.
Part -3:
Assignment: Gourmet Pizzas Project - Physical Database Design
In this assignment, steps in the physical database design are conducted as well revising the concept database design and logic database design of assignment 1 and assignment 2, and a final report of the project is written.
This assignment has 3 parts as specified below.
1. Revise the concept database design and logic database design, based on your revision and the feedback provided by your marker. I.e., do the following three steps:
1.1 Do concept database design, including Requirement Specification (including data requirements, transaction requirements and business rules), and EER Diagram and Data Dictionary.
1.2 Map the EER model to the relational model. Document the relational schema in DBDL
1.3 Normalize the relational schema to Boyce-Codd Normal Form (Point out what norm form each relation is in, do the normalisation if any relation is not already in BCNF.). The final normalised schema must be documented in DBDL.
2. Completed major steps in the physical database design
- Write SQL scripts which create the normalised Gourmet Pizzas database
- Write SQL statements satisfying the transaction requirements including:
- Input proper data (as you consider legitimate) of at least three rows for every table, and
- Implement the following queries (make sure to populate with enough and proper data into related tables so that non-void result is shown for each query. A query will be given zero mark if it has void output or no output). Note: in the queries, the values of xxx and yyy can be the corresponding values in your database.
Q.1 For a staff with id number xxx, print his/her 1stname, lname, and hourly payment rate.
Q.2 List the ingredient details of a menu item named xxx.
Q.3 List all the orders that are made by customer xxx via phone between date yyy and zzz.
Q.4 Print the salary paid to a delivery staff named xxx in current month.
Q.5 List the menu item that is mostly ordered in current month.
Q.6 List the ingredient(s) that was/were supplied by supplier xxx on date yyy
3. Write a final report. The final report should include all the content of the above two tasks, including all the SQL scripts.
The final report should include the following:
- Requirement Specification (including data requirements, transaction requirements and business rules).
- EER Diagram and Data Dictionary
- Normalized Relational Schema in DBDL. Ensure that normalisation steps are shown.
- SQL script which creates the Gourmet Pizzas database
- SQL statements satisfying the transaction requirements
Attachment:- Assignment dbms.zip