Reference no: EM133507568

Databases

Assessment: SQL scripts

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.

Task 1
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.

Note: 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 (orderid, prodld, quantity, retail Price)
Payment (payld, type, amount, pDate, bankTransactNo, orderid)
Product (prodld, size, colour, style, qtyOnHand, reorderQty, retailPrice, suplrld) ProductSupplier (prodld, supA14)

Supplier (skiplrld, suplrName, suplrAddress, suplrPostCode, suplrEmail, supIrMobPh) FOREIGN KEYS
• StoreEmployee(storeld) references Store (storeld)
• 'Order'(custld) references Customer(custld)
• OrderDetail(orderld) 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 (not text) 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)
• 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 2352 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.

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 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 Group
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 old_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 [0.5 marks)
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?