In this assignment, steps in the physical database design are conducted (as described below) as well as revising the conceptual database design and logical 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 (given in A2) is not used, zero marks will be given for all the assignment 3.

. You must attend your week 12 lab to get your assignment 3 marked.

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 conceptual database design, including Requirement Specification (including data requirements, transaction requirements and business rules), and EER Diagram and Data Dictionary.

1.2 Map the solution 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, and re-running the code to start the whole code again by

clicking “Execute”), you will get zero marks for 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’s end. (Note: to meet this requirement, it is essential that the codes for dropping all tables are included and work.)

2.1 Write SQL scripts that create the normalised SCS Resource Management 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 five (5) 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.

Q1: Print the name of student(s) who has/have enrolled in the course with course id xxx.

Q2: Print the maximal number of speakers that the student with name xxx can borrow. The student is enrolled in the course with course id yyy. Note: speaker is a category.

Q3: For a staff member with id number xxx, print his/her name and phone number, the total number of reservations that the staff had made in 2022.

Q4: Print the name(s) of the student member(s) who has/have borrowed the category with the name of camera, of which the model is xxx, in this year. Note: camera is a category, model attribute must be in movable table, and “this year” must be decided by the system.

Q5: Find the moveable resource that is the mostly loaned in current year. Print the resource id and resource name. Note: “current year” must be decided by the system.

Q6: For each of the three days, including May 1, 2024, June 5, 2024 and September 19, 2024, print the date, the name of the room, and the total number of reservations made for the room xxx on each day.

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, and 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 normalised 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 Numberone 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:

.    Submit 2 files: the project report in Word format, and the project database sql file that completes all the tasks specified in Section 2 of the assignment specification file. The file name MUST be identified by 4 sections: A3, your first name, your surname, and your student number, e.g., A3SimonLee1234567.doc and A3SimonLee1234567.sql.

.    It must be submitted to Canvas -> Assignments -> Assignment3.