Faculty of Engineering & Built Environment
School of Electrical Engineering and Computing
INFO6001: Database Management 1
Assignment 3: Popular Pizza Project - Physical Database Design
Due: Week 12 (April 15, Wednesday), At the beginning of your lab session 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
2.1 Write SQL scripts which create the normalised Popular 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 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 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 current year.
Q.8 List the name(s) of 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 marker’s 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 Popular 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 Blackboard - Assessment - AssignmentsSubmission- Assignment3.
• 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/AssessmentItem CoverSheet.pdf
Note: please make sure to fill in your Tutorial Group (i.e., time), Tutor Name, as well as other items.
• 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 donot 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.