IFN554 Assessment 2: SQL scripts-2

Java Python IFN554 Assessment 2: SQL scripts-2

Overview

Task description:

In Assessment 1, we analysed the core concepts, principles and skills required for understanding the kinds of techniques that may be used to model data, and an ability to develop a concise conceptual model that represents a given universe of discourse.

This assessment will involve the creation of several SQL queries in order to convert the data that is stored in a database into information that can be processed and understood by a human decision-maker.

You will use your knowledge from the lectures together with the techniques practiced in the tutorial sessions and apply both a set of tasks that refer to the SQL representation of a relational database. You will also make a critical reflection about the role of ethics in the use of data from the perspective of data management systems.

Unit Learning Outcomes assessed:

A successful completion of this task will demonstrate:

1. Apply standard querying techniques to the retrieval and manipulation of data.

2. Generate effective, ethical and culturally sensitive solutions to database management.

3. Apply visual and written communication techniques to explain how the proposed design and solution meets data management needs.

Instructions

Assessment 2 is to be completed individually. You are not to collaborate with others on this assessment. 

· For this assignment, you must use MySQL Workbench.

· Screen snapshots of MySQL showing the SQL code and results must be provided as a solution. Failure to provide the following in your responses may result in a nil mark.

o SQL Query code

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

o Action Output result

o Schemas (where necessary)

· For Tasks 2 to Task 5, you must use Hotel database (Filename: Hotel IFN554 Hotel 24s1.sql).

· Assignment submissions are to be made via IFN554 Canvas in a single PDF file properly identified (student name and number).

Assessment Tasks

Note: All tasks requiring SQL must provide as a solution a screen snapshot of the Execute SQL tab in MySQL that includes all sections (Query Code, Result Grid, Action Output and on occasion Schemas). The SQL code at the top, the results of the executed statements in the middle section and the Execution details in the bottom section. As shown in the example screen snapshot below.

For example, if selecting all rows from some database table called Branch was required then the solution presented is as follows.

The readable, full window of MySQL has been included in the snapshot (example provided)

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. The solution will be demonstrated in one or more MySQL screen snapshots that include the Query / Execute SQL tab SQL code, the result grid and the Action Output.  You will be required to add some values to your tables.

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)

Marks will be awarded for the following:

Task 2 [14 marks] 

For this task you are required to use the file provided called Hotel database 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.

IFN554 Hotel file: IFN554 Hotel 24SEM5C.sqlDownload IFN554 Hotel 24SEM5C.sql

Note: All tasks requiring SQL must provide as a solu IFN554 Assessment 2: SQL scripts-2 tion a screen snapshot of the Execute SQL tab in MySQL Workbench that includes all three sections. The SQL code at the top, the results of the executed statements in the middle section and the Execution details in the bottom section.

Note: A booking commences at 2 pm (check in) and finishes (check out) at 10 am the following day. 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.

The script. is based on the following relational schema:

· 

· Hotel (hotelNo, hotelName, city)

· Room (roomNo, hotelNo, roomType, price)

· Guest (guestNo, guestName, guestAddress)

· Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)

Note: Primary keys are denoted by bold and underline. Foreign keys are denoted by italics and maybe part of a primary key.

Write an SQL script. and show evidence of the script, result and output for querying data for the following information.

2a.       List the hotelNo which has 2 or more single rooms [2 marks]

2b.       How many different guests visited the Meriton Hotel? [2 marks]

2c.       What is the total income from bookings for the Meriton Hotel? [3 marks]

2d.       List the guests’ names who have visited more than 2 times [3 marks]

2e.      For each hotel, list the room type, for each room type list the number of each room type and the number with bookings [4 marks] Hint: think join or multiple join query.

MARKING CRITERIA:

Full marks will be awarded for each query if the query is correct and evidence (readable and complete snapshot) is provided. Otherwise, 0 marks may be allocated.

Task 3 [5 marks]

 Perform. the following tasks.

3a.             Write a command to create an index on hotel name and show the results [1 mark]

3b.             Create a user with the name ‘Mickey’ @ local host with password ‘iloveORM' and force the user to update the password.  Show results [1 mark]. 

3c.             Create a view called HotelSummaryView – list the hotelNo, type and price of each room. Order the result by hotelNo, room type and price [1 mark]

3d.             Grant permissions to select and read this view. Show results [1 mark]

3e.             Revoke permissions. Show results [1 mark]

MARKING CRITERIA:

Full marks will be awarded for each query if the query is correct and evidence (readable and complete snapshot) is provided. Otherwise, 0 marks may be allocated.

Task 4 [5 marks] 

Perform. the following tasks (make up your own guest names etc. at least 2 will be required.

4a.             Update the prices of all single rooms by 5% and show results [1 mark]

4b.             Insert 2 rows of new data in the table Guest and Booking [2 marks]

4c.             Delete one of the rows your inserted into the Guest table [2 marks]

MARKING CRITERIA:

Full marks will be awarded for each query if the query is correct. Otherwise, 0 mark may be allocated.

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

MARKING CRITERIA:

Marks will be awarded for the following:

· 3 NF relations noted with all PK’s and FK’s noted (Full mark but see the next bullet point).

· For this exercise, you will begin with 10 marks and be deducted 1 mark for each error.

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 (APA 7 referencing style)