Reference no: EM132495732

INFO6001 Database Management - University of Newcastle

Assignment : Popular Pizza Project - Physical Database Design

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.

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

Question 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 :ero mark if it has void output or no output). Note: in the queries, the values of tn. 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 Istname, Iname, 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 tit between date yyy and nz
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 tit 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

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

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

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

Assignment: Popular Pizza Project - Requirement Analysis and Conceptual Design

Assignment Tasks (individual assignment)

The proposed database system is developed in various modules, including requirements analysis, conceptual database design, logical database design, and physical database design. In this assignment 1, you are required to complete the first two stages of the database design, i.e., to develop user requirements specification and the conceptual database model for the database based on the business requirements provided in this document and related discussions in lectures. There are two parts to be completed in assignment 1 as described below.

You are required to write and submit a report that includes all the content of the two parts that are described below. The report must be in Microsoft Word.

Part 1: Requirements
In this assignment, you are required to develop a user requirements specification truly fulfilling the data requirements (identify what types of data needs to be stored in the database), transaction requirements (identify the important and frequent database operations - data manipulation and queries), and business rules (which is based on this document and elaborations in lecture) for the Popular Pizza's database mentioned above.

Assignment submission format for the Requirements part: The requirements document
MUST have the following sections:
• Data Requirements - outlining the major data items
• Transaction requirements - outlining the data manipulation and queries
• Business Rules
Hint: Sample requirements documents are discussed in weeks 2 and 3, and are available in appendices A and B of your main textbook.
You may interview your client (i.e. lecturer) for clarification and include your interview questions and responses

Part 2: EER Model with data dictionary

Draw an EER model for the requirements identified in Part 1. The EER Model must be shown in UML notation which is discussed in class and provided in our text. Please note: other notations (other than UML) will NOT be accepted, i.e., zero mark will be given for the EER model part if a notation other than UML is used.

The EER Model should be accompanied with a data dictionary which includes entity type table, relationship type table and attribute table.

Assignment submission format for the EER Model with Data Dictionary part: The document MUST have the following sections:
• EER Model
• Documentation - Data dictionary details (description of entities, relationships and attributes)

Assignment: Popular Pizza Project - Logical Database Design

In this assignment, steps in the logical database design are conducted (as described below) as well as revising the requirement analysis and conceptual design of assignment 1, and a report is written.

This assignment has 3 parts as specified below.

1. Revise requirements and EER diagram in Assignment 1, based on:
i. Either the EER Model submitted for your Assignment 1. Revise it based on feedback provided by your marker, or
ii. Alternatively, complete the partial EER model given at the end of this file, to meet the system requirements.
2. Map the EER model to the relational model. Document the relational schema in DBDL (Sample format is given below)

ISBN (id, number, itemNo)
Primary Key id
Alternate Key number
Foreign Key itemNo references Book(itemNo)
ON UPDATE CASCADE, ON DELETE CASCADE
3. Normalize the schema to Boyce-Codd Normal Form (if any relation is not already in BCNF). The final normalised schema must be documented in DBDL.

The final report should include the following:
1. Reflection on your assignment 1 submission: briefly summarise your assignment 1 marker's comments and suggestions, describe major places you will improve in this submission on assignment 1 content.
2. Requirement Specification (including data requirements, transaction requirements and business rules).
3. EER Diagram and Data Dictionary
4. The relational model mapped from EER (i.e., before normalisation)
5. Normalized Relational Schema in DBDL. Ensure that normalisation steps are shown if any.

Attachment:- Popular Pizza Project.rar