[SOLVED] Scheme SQL database Faculty of Engineering & Built Environment School of Electrical Engineering and Computing

Faculty of Engineering & Built Environment School of Electrical Engineering and Computing
COMP1140: Database and Information Management
Assignment 3: Mega Pizza Project Physical Database Design
Due: 11:50pm, Monday, November 25, 2019
WORTH 15% of final course assessment mark.
In this assignment, steps in the physical database design are conducted (as described below) as well 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 (Please refer to the marking scheme for more detailed requirements)
2.1 Write SQL scripts which create the normalised Mega Pizza database, including all necessary tables with 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 shift details of a delivery staff with first name xxx and last name ttt between date yyy and zzz.
Q.3 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.4 Print the salary paid to a delivery staff named xxx in current month. Note the current month is the current month that is decided by the system.
Q.5 List the menu item that is mostly ordered in current year.

Q.6 List the ingredient(s) that was/were supplied by the supplier with supplier ID xxx on date yyy.
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. At the beginning of this section, make sure write a reflection on your assignment 2 submission: briefly summarise your assignment 2 markers comments and suggestions, describe major places you will improve for the mapping and normalisation.
4. SQL script (both in your sql file and in your report file) which creates the Mega Pizza database as stated in 2.1.
5. SQL statements (both in your sql file and in your report file) satisfying the transaction requirements as stated in 2.2
Method of submission: Both softcopy and hardcopy submissions are required:
zip all required files into one zip file (including the project report, the project database SQL backup file, and any files you consider as part of the assignment). The file name MUST be identified by 4 sections: A3, your first name, your surname, and your student number, e.g., A3SimonLee1234567.zip
It must be submitted to BlackboardCourse MaterialsAssignment3.
Print the project report (with all related SQL codes), hand in the hardcopy to your instructor at the beginning of your lab session of week 12. The hard copy must have on the front a signed copy of the cover sheet which is available from: http://www.newcastle.edu.au/__data/assets/pdf_file/0008/75383/AssessmentItemCoverSh eet.pdf
Note: please make sure to fill in your Tutorial Group (i.e., time), Tutor Name, as well as other items. It is recommended that you put the same lab session as you put for your assignment 1 and assignment 2.
The SQL script part will be checked and marked in the Tut/Prac session of the week the assignment is due. Therefore, you MUST be present at the tut/prac session of the due week for grading in order to receive marks.
Absence to your tut/prac session of the due date will result in being zero mark for Complete major steps in the physical database design section. (If you have special circumstance, you need to discuss with lecturer in advance.)
Please note:
Zero mark will be given if you do not submit both hardcopy and softcopy.
If your hardcopy submission and softcopy submission are not at the same time, the time of the later submission will be counted as your assignment submission time.