Reference no: EM133673047

Databases

Assessment: 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.

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

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

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

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 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. 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 full window of MySQL has been included in the snapshot.

Task 1 - Group
You are required to create a database for a fictitious Online Shoe Store called ‘Fabulous Footwear' (think Williams Shoes or Novo) for Task 1. Write an SQL script that builds a database to match the relational model below. These 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 Execute SQL tab SQL code and the results of the executed statements.
The ‘Fabulous Footwear' relational model:
Note: Primary keys are denoted by bold and underline and foreign keys are in italics
Branch (branchId, branchName, branchAddress, branchPostCode)
Employee (empId, empName, empAddress, empPostCode, empEmail, empMobile, empStartDate,
branchId)
Customer (custId, custName, custMobile, custEmail, custBirthDate)
Order (orderId, orderDate, total, GST, deliveryAddress, orderStatus, custId)
OrderDetail (orderId, prodId, quantity, retailPrice)
Product (prodId, size, colour, style, qtyOnHand, reorderQty, retailPrice, stockId)
ProductStockist (prodId, stockId)
Stockist (stockId, stockName, stockAddress, stockPostCode, stockEmail, stockPhone)
Payment (payId, type, amount, pDate, bankTransactNo, orderId)

FOREIGN KEYS
Employee(branchId) references Branch (branchId) `Order`(custId) references Customer(custId)
OrderDetail(orderId) references Order(orderId) OrderDetail(prodId) references Product(prodId) Payment(orderId) references Order(orderId)
Product(stockId) references Stockist(stockId)
ProductStockist(prodId) references Product(prodId)
ProductStockist(stockId) references Stockist(stockId)
Other Constraints and Remarks
All primary and foreign key attributes are strings (not text) comprising seven digits. All PostCodes are strings comprising four digits (4).
INTEGER type must be used for total, GST, quantity, retailPrice, qtyOnHand, reorderQty, and amount.
TEXT type must be used for all other attributes.
empName, branchName, custName, stockName must contain a value (you will need to add values to these attributes)
MY SQL has order as a keyword, to create this table you will need to use the backward quotation mark as shown `Order`.

Task 2 - Group
We have provided you with the Hotel database (Filename: IFN554 Hotel 24s1.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.

Once you have imported this file, you MUST verify that each table has several rows of data. There is an error that you need to find and correct. DO NOT create your own values, but problem-solve using the script provided. The problem is related to Foreign Keys. Hint: You MUST fix the "INSERT INTO Booking" statement to resolve the issue.
You should begin with importing the IFN554 Hotel 24s1.sql into My SQL and check the database and the information within. Look for the error. Then, to analyse and fix the problem you have discovered, simply open the .sql file (rather than import). Opening the file will show you the database script-fix the problem, save it, and then import it back into My SQL. Check the information and then proceed with the remainder of the assessment questions.
Note: All tasks requiring SQL must provide as a solution 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.
List the hotelNo, type and price of each room that is a self or single with price more than $200. Order the result by type, hotelNo, and price .
List the hotelNo which has 2 or more single rooms .
How many different guests visited the Coconut Hotel? .
What is the total income from bookings for the Meriton Hotel? .
List the guests' names who have visited more than 2 times .

Task 3 Group
Perform the following tasks.
Write a command to create an index on guestName of the Guest table and show the results.
Create a user with the name "Snoopy" @ local host with password Snoops_password.
Create a user with the name "Peanut" @ local host with password old_password. Force the user to update the password.
Create a view called Guest_View showing Guest No, Guest Name and Guest Address, Grant permission to Snoopy to select this view . Note: Both parts are necessary for a correct answer. No part marks.
Display a list of all users .
Grant Select, Insert to Peanut on booking table in hotel and show the Granted Access .
Remove Select and Insert privileges on booking table in hotel from Peanut and show Grants .

Task 4  - Group
Perform the following tasks (Make up your own hotel and guest names etc. at least 2 guests required).
Write commands to insert rows in each of the Hotel database tables and display .
Write a command to delete the row you inserted in the table Guest and display .

Write a command to update the price of all rooms by 25% and display .
Write a command to create a View to list the information (hotelName, roomType, and the total number of rooms booked) of the hotels which are in the city Gold Coast .

Task 5 - Group
Using the following table structure, identify all functional dependencies and then decompose this table into a set of 3NF relations.
Assumptions:
In the following table, these assumptions can be made:
There are no multivalued cells.
Receipts may have multiple items; however, be only for 1 client
Any given product is supplied by a single supplier, but a supplier can supply many.

Task 6 - Individual
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.
Data is everywhere and so are people who want to steal it for evil purposes. Many organisations have access to your personal data. You provide your data in many ways both knowingly and unknowingly.
Your task is to reflect on the many data breaches that have happened worldwide. Some of the most noted ones includes Medibank, Optus and even QUT. Have the many data breaches impacted you and your sharing of personal data? Whether you have been impacted or not, how have you changed your habits regarding data protection and why? If you have not changed your habits, why not.
Write 400 - 600 of your own words on how an organisation should secure your data. What actions can an organisation take to prevent loss of data through unauthorized actions? What consequence

do you feel needs to be imposed to organisation that suffer data breaches? What action can you / have you taken to protect your data? Make recommendations using research and information provided in lectures and reading materials.
Key considerations:
What can you do to secure your data regarding collection and storage? What should an organisation do to ensure it uses only what is allowable?
What disclosures or disclaimers should an organisation consider when requesting data from its clients? How should they provide assurance of this?
What do you consider ethical use of data? What do you consider unethical use of data?
What are the consequences of breaches of data privacy? What are some of the consequences to users? What process should an organisation have in place to protect their data from threats?