Response Sheet 

IFN554 Databases 

Semester 2 SEM5C 2024

IFN 554 Assessment 2

SQL

All students will need to submit a PDF file of their responses.

Student Name

Student Number

Tutorial Group: 

Student Declaration

By submitting this assignment, I am aware of the University rule that a student must not act in a manner which constitutes academic dishonesty as stated and explained in the QUT Manual of Policies and Procedures. I confirm that this work represents my individual efforts. I declare that it does not contain plagiarized material.

Complete the following tasks and note your responses. Reminder – all snapshots must be clear and readable

Task 1 [6 marks] 

In this task you are required to write an SQL script that builds a database to match the relational model below. Your database should be named “Heavenly Department Store”.

The SQL statements in the script must be provided in the correct order. 

Heavenly Department Store relational model is as follows:

Note: Primary keys are denoted by bold and underline and foreign keys are in italics

• Supply (SupplierNr, ItemNr, QtyNr)

• Use (ItemNr, DeptCode)

• Item (ItemNr, ItemName, CountryMade) 

Note: 1.1 ItemNr or Name both possible PK using ItemNr as preferred PK

• Supplier (SupplierNr, SupplierName, FaxNr, SupplierCountry)

Note: 1.1 SupplierNr or FaxNr or SupplierName are all possible PK using SupplierNr as preferred PK

FOREIGN KEYS

• Supply (SupplierNr) references Supplier (SupplierNr)

• Supply (ItemNr) references Item (ItemNr)

• Use (ItemNr) references Item (ItemNr)

Other Constraints and Remarks

• All primary and foreign key attributes that you use are strings (not text) comprising eight digits (8).

• INTEGER type must be used for QtyNr is mandatory and must be greater than 0.

• TEXT type must be used for all other attributes.

• SupplierName and ItemName must contain a value (you will need to add values to these attributes)

(Insert snapshots of tables and values here)

Task 2 [14 marks] 

We have provided you with the Hotel database (Filename: IFN554_5C_Hotels.sql you can download from Canvas) to be used with MySQL Workbench. You must use this database in MySQL Workbench to extract the necessary information as per the following query requirements. 

Note: All tasks requiring SQL must provide as a screen snapshot of the execute SQL tab in MySQL Workbench that includes all sections. Snapshots must be readable

• SQL Query code

• Result Grid (table, view, update, new information etc.). The teaching team must be able to see the successful execution of the code

• Action Output result

• Schemas (where necessary)

In your database – a booking commences at 2 pm (check in) and finishes at 10 am the following day (check out). Example: A booking for 3 nights is made on Tuesday 8th August with a check-in date of Thursday 10th August from 2pm and a check-out time of Sunday 13th August at 10 am.

Task 3 [5 marks] – Individual

Perform the following tasks.

Task 4 [5 marks] 

Perform the following tasks continue using the Hotel database provided.

Task 5 [10 marks] 

Using the following table structure, identify all functional dependencies and then decompose this table into a set of 3NF relations. The table is in 0NF so please apply all rules accordingly.

Assumptions:

• Product Price is Per Unit

• There are not multi values

Task 6 [10 marks]

The use of Generative AI such as ChatGPT is not permitted for this task. QUT policy states the following: “If you submit an assessment “that has been produced or modified, wholly or in part, by an artificial intelligence tool, algorithm, or computer generator where such actions are not authorised in the assessment task”, this may be treated as a breach of our Academic Integrity Policy and appropriate penalties imposed.

This task is a reflective task. You are required to answer the question below in your own words. Your answer should be your opinions, experience and personal perspective. Your response should be supported by research to validate your viewpoints. Provide in text referencing where appropriate. Your response should be between 400 and 600 words not including references.

Question: 

How does the use of Electronic Health Records (EHRs) impact your privacy and confidentiality as a patient? What measures do you think modern EHealth should take to protect your information? How do you want your data managed?  What actions are taken or should be taken to ensure your data is protected and used in an appropriate and authorised manner?