Database Management Assignment
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: EER Model with data dictionary
EER Model:
The Enhanced Entity Relationship diagram has developed according to the various activities like order processing of offered items, employee management of Gourmet Pizza. This EER diagram is drawn in Ms-Visio 2007 software where standard UML notation is available.
References:
T. Connolly and C. Begg, Database Systems, (2004). A Practical Approach to Design, Implementation and Management, 4th Edition, Addison -Wesley.
Silberschatz, K. S. (2010). Database System Concepts. McGraw-Hill.
Attachment:- Assignment Files.rar