INFO6001: Database Management – Gourmet Pizzas Project – Physical Database Design This assignment…

INFO6001: Database Management – Gourmet Pizzas Project – Physical Database Design

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.

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.
• 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

"Get 15% discount on your first 3 orders with us"
Use the following coupon
FIRST15

Order Now