COMP1140 Database and Information Management : Pizza Databasse

Task:

In this assignment, steps in the physical database design are conducted (as described below) as well as 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. Complete major steps in the physical database design 

2.1 Write SQL scripts that create the normalised Delitaste Pizza database, including all necessary tables with the right parameters such as primary key, foreign key, default value. 

2.2 Write SQL statements satisfying the transaction requirements including:

2.2.1 Input proper data (as you consider legitimate) of at least three rows for every table, and 

2.2.2 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 ttt, xxx, yyy, zzz, etc., can be the corresponding values in your database.

Q.1 For an in-office staff with id number xxx, print his/her 1stname, lname, and hourly payment rate. 

Q.2 List all the ingredient details of a menu item named xxx. 

Q.3 List all the shift details of a delivery staff with first name xxx and last name ttt between date yyy and zzz 

Q.4 List all the order details of the orders that are made by a walk-in customer with first name xxx and last name ttt between date yyy and zzz. 

Q.5 List all the order details of the orders that are taken by an in-office staff with first name xxx and last name ttt between date yyy and zzz. 

Q.6 Print the salary paid to a delivery staff named xxx in the current month. Note the current month is the current month that is decided by the system. 

Q.7 List the name of the menu item that is mostly ordered in the current year. Note the current year is the current year that is decided by the system.