Reference no: EM133506265

Databases

Assessment - SQL scripts

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 requlring SQL must provide as a solution a screen snapshot of the Execofe 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 ‘WalkthisWay' (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.

/\/ofe: Primary keys are denoted by bold and underline and foreign keys are in italics

The ‘WalkthisWay' relational model:

Store (storeld, sName, sAddress, sPostCode)

StoreEmployee (empld, eName, eAddress, ePostCode, eEmail, eMobPh, eStartDate, storeld)

Customer (custld, cName, cMobilePh, cEmail, cBirthDate)

Order (orderld, oDate, total, GST, deliveryAddress, orderstatus, custld)

OrderDetail (order/c/, prodld, quantity, retailPrice)

Payment (payld, type, amount, pDate, bankTransactNo, orderld)

Product (prodld, size, colour, style, qtyonHand, reorderQty, retailPrice, suplrld)

ProductSupplier (prodld, suplrld

Supplier (suplrld, suplrName, suplrAddress, suplrPostCode, suplrEmail, suplrMobPh)

FOREIGN KEYS

• StoreEmployee(storeld) references Store (storeld)
• Order(custld) references Customer(custld)
• OrderDetail(orderId) references Order(orderld)
• OrderDetail(prodld) references Product(prodld)
• Payment(orderld) references Order(orderld)
• Product(suplrld) references Supplier(suplrld)
• ProductSupplier(prodld) references Product(prodld)
• ProductSupplier(suplrld) references Supplier(suplrld)

Other Constraints and Remarks

• All primary and foreign key attributes are strings comprising seven digits.
• All PostCodes are strings comprising four digits.
• INTEGER type must be used for total, GST, quantity, retailPrice, qtyOnHand, reorderQty, and amount.
• TEXT type must be used for all other attributes.
• sName, eName, cName, suplrName must contain a value (you will need to add values to these attributes)

Task 2 - Group

We have provided you with the Hotel database (Filename: IFN554 Hotel 23s2 v2.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: 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, hole//\/o, roomType, price)
• Guest (guestNo, guestName, guestAddress)
• Booking (hofeiñ/o,.goesf/\/o, 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 for querying data for the following information.

1. List the hotelNo, type and price of each room that is a suite or family with price more than $90. Order the result by type, hotelNo, and price.
2. List the hotelNo which has 2 or more family rooms.
3. How many different guests visited the Accor Hotel?
4. What is the total income from bookings for the Ritz Hotel?
5. List all the guests' names who have visited more than 2 times.

Task 3 - Perform the following tasks.
1. Write a command to create an index on guestName of the Guest table.
2. Create a user with the name "Jane" @ local host with password new_password
3. Create a user with the name "Alex" @ local host with password oId_password. Mark the password expired so that the user must choose a new one at the first connection to the server.
4. Create a view called GView showing Guest No and Guest Name and Grant permission to Jane to select this view [1 mark] Note: Both parts are necessary for a correct answer.
5. Display a list of users
6. Grant Select, Insert to Alex on table Hotel
7. Show Grants on table Hotel to Alex
8. Remove Select and Insert privileges on hotel from Alex and Show grants

Task 4 - Individual Perform the following tasks.

Perform the following tasks (Make up your own hotel and guest names etc.).

1. Write commands to insert rows in each of the Hotel database tables.
2. Write a command to delete the row you inserted in the table Guest.
3. Write a command to update the price of all rooms by 25%.
4. 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 Longreach.

Task 5 - Individual Perform the following task.

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.
• any invoice numbers (invoicelD) may reference more than one.
• any given product is supplied by a single supplier, but a supplier can supply many products.

Task 6 - Individual Perform the following task.

Data is everywhere. Organisations have access to your personal data. Reflect on the many data breaches that have happened worldwide. Some of the most noted ones include Medibank, Optus and even QUT. How have the many data breaches impacted you and your sharing of personal data?

Write 300 - 500 words on how an organisation should secure its data. What actions can it take to prevent loss of data through unauthorized actions? What can you do to protect your data? What actions have you taken, or will you take in the future?
Consider the following aspects in your discussion:

• Data collection and storage, what can you do to secure your data?
• What can 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?
• Think about the usage of data. What is considered ethical and what would not be considered ethical use of data?
• What are some of the results when data requirements such as security access and sharing are not considered?

Attachment:- Assessment Template.rar