DBMS Delitaste Pizza SQL_Project

Database and Information Management Delitaste Pizza SQL_Project

Repository containing a Project assignment SQL script used to develop a database management system for a Pizza retail shop. I have also provided the designed data requiment documents.

The project was part of my Database and Information Management course from the University of Newcastle (Uon) Master of Data Science Degree.

Physical Database Design Introduction

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

1.1 Write a 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.

Q.1 For a delivery 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.

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:

1. Requirement Specification (including data requirements, transaction requirements and business rules).

2. EER Diagram and Data Dictionary

3. Map the EER model to the relational model. Document the relational schema in DBDL. Give normalized relational schema in DBDL. Ensure that normalisation steps are shown.

4. SQL script (both in your sql file and in your Word report file) which creates the Delitaste Pizza database as stated in 2.1.

5. SQL statements (both in your sql file and in your Word report file) satisfying the transaction requirements as stated in 2.2