Database Management Assignment
Part 1: Requirements
Data Requirements - outlining the major data items
Transaction requirements - outlining the data manipulation and queries
Business Rules
Part 2: EER Model with data dictionary
EER Model
Documentation - Data dictionary details
References
Part 1: Requirements
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 2: EER Model with data dictionary
EER Model:
The Enhanced Entity Relationship (EER) diagram has drawn as per case study of Gourmet Pizza including the shop's order processing, Menu Items, Ingredients and Suppliers as well as Employees. The diagram has presented in the MS-Visio 2007 software where standard UML notation for designing database models is shown.
Attachment:- Assignment Files.rar