INFO6001: Database Management 1 Assignment 3
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
School of Information and Physical Sciences (SIPS)
INFO6001: Database Management 1
Assignment 3: Superdeli Pizza Project - Physical Database Design
Due: 11:59 pm, Thursday, April 13, 2023
WORTH 15% of the final course assessment mark.
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.
Note: if the provided solution EER is not used, zero marks will be given for all the assignment 3.
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, and 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).
Note: If your code cannot be run through successfully by the marker (including executing all the submitted code as a whole, running the section of creating tables, re-running the code to start the code again), you will get zero marksfor section 2. So it is strongly suggested that you try your code with a different account and on a different computer to make sure it will work at the marker end.
2.1 Write SQL scripts that create the normalised Superdeli Pizza database, including all necessary tables with the right parameters such as primary key, foreign key, default value.
Note: in creating foreign keys (FK), at least 5 FKs for all the tables should use “ON UPDATE CASCADE, ON DELETE CASCADE” . Otherwise, marks will be deducted.
2.2 Write SQL statements satisfying the transaction requirements including:
2.2.1 Input proper data (as you consider legitimate) of at least four 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 instore 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 made by a walk-in customer with first name xxx and last name ttt between date yyy and zzz.
Q.4 List the names of the menuitems that are ordered in the current year. Note the current year is the current year 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. Reflection on your assignment 2 submission: briefly summarise your assignment 2 marker’s comments and suggestions, describe major places you will improve in this submission on assignment 2 content.
2. Requirement Specification (including data requirements, transaction requirements and business rules).
3. EER Diagram and Data Dictionary
4. 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. (Note: refer to assignment 2 specification for the requirements of this content).
5. SQL script (both in your sql file and in your Word report file) which creates the Superdeli Pizza database as stated in 2.1.
6. SQL statements (both in your sql file and in your Word report file) satisfying the transaction requirements as stated in 2.2.
Method of submission: softcopy submissions only is 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 Canvas -> Assignments -> Assignment3.
• In the report, you must have on the front a signed copy of the cover sheet (Assessment Item Cover Sheet – Individual) which is available from:
http://www.newcastle.edu.au/data/assets/pdf_file/0008/75383/AssessmentItemCoverSheet. pdf
Note: please make sure to fill in your Tutorial Group (i.e., date/time), Tutor’s Name, as well as other items. Otherwise, your submission marking may be delayed.
Note: Ten percent of the possible maximum mark for the assessment item will be deducted for each day or part day that the item is late. This applies equally to week and weekend days. Assessment items submitted more than five days after the due date will be awarded zero marks.
2023-04-04
Superdeli Pizza Project - Physical Database Design