INFO6001: Database Management 1
Assignment 3: UNIE Library Project - Physical Database Design
Due: Week 12 (from April 7 to April 11, 2025), Demo at your registered lab
WORTH 15% of the final course assessment mark.
In this assignment, we step into the physical database design (as described below) as well as revising and reflecting on the conceptual database design and logical database design (i.e. assignment 1 and assignment 2).
This assignment has 2 parts as specified below.
Note:
• If the provided solution EER (given inA2) is not usedfor this assignment, zero marks will be given for the whole assignment 3.
• You must attend week 12 lab to demonstrate your work to get your assignment 3 marked.
1. Complete major steps in the physical database design.
Important: If the marker cannot successfully run your code (including executing all submitted code as a whole and re-running it from the beginning by clicking "Execute"), you will receive zero marks for this section. To avoid this, test your code on a different account and computer to ensure it functions correctly in the marker's environment. Additionally, you must include working code to drop all tables to meet this requirement.
1.1 Write SQL scripts that create the normalised UNIE Library database, including all necessary tables with the right parameters such as primary key, foreign key, default value.
Note: When creating foreign keys (FK), at least 5 FKs should be created over all (for all the tables). Should use “ON UPDATE CASCADE, ON DELETE CASCADE” (i.e., at least 5 CASCADE have to be used). Otherwise, marks will be deducted.
1.2 Write SQL statements satisfying the transaction requirements including:
1.2.1 Input proper data (as you consider legitimate) of at least five (5) rows for every table, and
1.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 xxx, yyy, zzz, sss, ttt, etc., can be set as the corresponding values in your database.
Q1: Print the details about the NewItemRequest that were requested by the member named xxx in 2025, including the member’s name, mobile phone number, and the title(s) of the requested items.
Q2: For a member named zzz, print the maximum number of items they can borrow, provided that all the items belong to the collection with collectionId sss.
Q3: For a member with id number ttt, print their name and phone number, the total number of Hold Request that the member has made in 2025.
Q4: Print the name(s) of the member(s) who has/have borrowed the book with the title “Database Design” this year, and print the barcode(s) of the book(s) that had been borrowed. Note: “this year” must be decided by the system.
2. Write a final report (4 pages minimum).
The final report should include the following:
1. Reflection on your assignment 2 submission, focus on the normalized tables, summarize the revisions, corrections and improvements that has been carried out based on feedback or your implementation.
2. Develop a comprehensive reflection on the entire design process, covering conceptual, logical, and physical design phases. Discuss the challenges encountered, key decisions made, and lessons learned throughout the process.
Method of submission: softcopy submissions only:
• Submit ONE zip file that includes 2 files: the project report in Word format, and the project database sql file that completes all the tasks specified in Part 1 of this assignment specification. 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.
• 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), as well as other items on the cover sheet. Otherwise, your submission marking may be delayed.
Attention please:
• The SQL script part of your work will be checked and marked in your registered lab of week 12. Therefore, you MUST be present at your lab for grading to receive marks.
• Absence to your lab will result in a zero mark for assignment 3. (If you have special circumstances, you should discuss with the lecturer in advance.)
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.