Name …………………….. Assessment 2 – SQL
Due ………………………. Friday 22nd April 2022 11:59pm
Weight …………………… 50%
Type ……………………… Group with individual
Submit …………………… PDF Document via Blackboard
Rationale and 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 Outcomes
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 problems
3. Apply visual and written communication techniques to explain how the proposed design and solution meets data management needs
Instructions
This assessment will have a Group task with Individual components.
• For this assignment, you must use SQLite DBMS.
• For Tasks 2 to Task 5, you must use Hotel database (Filename: Hotel IFN554 22s1.db you can download from Blackboard).
• Assignment submissions are to be made via IFN554 Blackboard.
• Assignment submissions MUST be in a single PDF file properly identified (student name and number).
• Groups should be of 2 members (exceptions can be made with the approval of the teaching team). Group members should belong to the same tutorial group.
• Assignment submissions MUST be a single PDF file properly identified (student names and numbers).
Feedback
Feedback will be provided on specific questions related to the assessment during the dropin session times, and via MS Teams. Please note, that the teaching team will NOT respond to general “is this good enough” style questions or requests to pre-assess your work.
Submission of assessment
All assessment will be submitted via standard QUT electronic submission methods (e.g., Blackboard, Turnitin), and you will need to submit your assessment in the format specified in the assessment task sheet. Assessment will not be accepted by any other method or in any other format. You should submit early to avoid delays from technical issues. The most recent assessment prior to the due date will be used for marking. Please ensure that you are aware of QUT’s policy on late assignments:
Late Submission – requesting an extension
No member of the teaching team, including the unit coordinator, can grant you an extension for an assignment. If you need one, you must apply through HiQ:
http://external-apps.qut.edu.au/studentservices/concession/
You now have two options for assignment extension. There is one assignment extension online form with options for longer extension and an Automatically approved 48-hour extension. You must select unit, assignment and extension type. More information and extension form: qut.to/late-assessment
If you don't have an approved extension, you should submit the work you have done by the due date, and it will be marked against the assessment criteria. Assignments submitted without an approved extension will not be marked and will receive a grade of 1 or 0%.
Reviews
We cannot remark assessments. All assessments will be returned with feedback explaining the reasons for the marks allocated. If you require additional feedback or clarification, discuss it with your tutor. If you believe that there is a component that has not been marked in accordance with the criteria sheet, you must identify it in writing to your tutor.
Academic Honesty
Any action or practice on your part which would defeat the purposes of assessment is regarded as academic dishonesty. The penalties for academic dishonesty are provided in the Student Rules. For more information consult the QUT Library resources for avoiding plagiarism.
Resources
The following resources may assist with the completion of this task:
• Refer to tutorial materials, Microsoft Teams, and any lecture videos.
• Refer to the recommended book.
Questions
Questions related to the assessment should be directed to the teaching team during the workshops or drop-in sessions, and also via MS Teams.
The teaching team will not be available to answer questions outside business hours, nor in the hours immediately before the assessment is due.
Assessment Tasks
Task 1 [6 marks] – Group
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. You are required to create a database for the fictitious bookstore Oktomook for Task 1. The database is based on the following model.
THE OKTOMOOK RELATIONAL MODEL:
Branch (branchNo, bName, bStreetNo, bStreetName, bPostCode, bState, numberEmployees)
Publisher (publisherNo, pName, pStreetNo, pStreetName, pPostCode, pState,)
Author (authorID, aFirstName, aLastName)
Book (ISBN, title, publisherNo, genre, retailPrice, paperback)
Wrote (ISBN, authorID)
Inventory (ISBN, branchNo, quantityInStock)
PRIMARY KEYS are denoted by bold and underline
FOREIGN KEYS
• Book(publisherNo) is dependent on Publisher (publisherNo)
• Wrote (ISBN) is dependent on Book (ISBN)
• Wrote (authorID) is dependent on Author (authorID)
• Inventory (ISBN) is dependent on Book (ISBN)
• Inventory (branchNo) is dependent on Branch (branchNo)
Other Constraints and Remarks
• branchNo is a string comprising three digits.
• ISBN is a string comprising 10 digits.
• publisherNo is a string comprising one letter and two digits.
• authorID is a string comprising four digits.
• PostCode is a string comprising four digits.
• State is a string with three letter or less letters
• INTEGER type must be used for retailPrice, numberEmployees and quantityInStock.
• TEXT type must be used for other attributes (except retailPrice, numberEmployees and quantityInStock).
• Book title must contain a value.
MARKING CRITERIA:
Marks will be awarded for the following:
• Successfully creating new tables (2 marks)
• Including all attributes and correct data types (1 mark)
• Correctly creating Primary Keys (1 mark)
• Correctly creating Foreign Keys (1 mark)
• Correctly creating other constraints (1 mark)
Task 2 [15 marks] – Group
We have provided you the Hotel database (Filename: Hotel IFN554 22s1.db you can download from Blackboard) to be used with SQLite DBMS. You should use this database in SQLite 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, type, price)
• Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
• Guest (guestNo, guestName, guestAddress)
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 double, self or deluxe with a price
more than $110 (2 marks).
2. List the hotelNo which have 2 or more double rooms (2 marks).
3. How many different guests visited the Grosvener Hotel? (3 marks).
4. What is the total income from bookings for the Grosvenor Hotel? (4 marks).
5. List all the guests’ names who have stayed in a hotel (4 marks).
MARKING CRITERIA:
• Full marks will be awarded for each query if the query produces the correct output.
Task 3 [3 marks] – Group
Perform the following tasks.
• Write commands to insert one row of data in each of the Hotel database tables (1 marks).
• Write a command to delete the row you inserted in the table Guest (1 marks).
• Write a command to update the price of all rooms by 15% (1 mark).
MARKING CRITERIA:
• Full marks will be awarded for each query if the query produces the correct output.
• As four Insert commands are required, 0.25 mark for each working command will be given.
(0 mark if an Insert command is not able to insert a row).
• 1 mark if the command is able to delete the row otherwise 0 mark.
• 1 mark if the command is able to update the price by 10% otherwise 0 mark.
Task 4 [2 marks] – Group
Perform the following tasks.
• Currently the database only contains a small number of records, however the data contained within it is expected to grow significantly in the future. Creating indexes on commonly searched columns is a way performance issues can be minimised. Write a command to create an index on guestName of the Guest table (1 mark).
• 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 Cairns (1 mark).
MARKING CRITERIA:
• Full marks will be awarded for each query, if the query produces the correct output. Otherwise, 0 marks.
Task 5 [4 marks] – Group
Nikki and Phil work with the Hotel database as database administrator. Provide the commands required to grant or revoke access so the following security requirements are met:
Perform the following tasks.
Note: You will not be able to try these commands in SQLite.
• User Nikki must be able to add records to the Booking table (1 Mark)
• User Nikki must be able to remove records from the Booking table (1 Mark)
• User Phil is no longer allowed to add data to the Guest table (1 Mark)
• User Phil is no longer allowed to delete records from the Guest table (1 Mark) Assume usernames of employees Nikki and Phil are nikki and phil respectively.
MARKING CRITERIA:
• Full marks will be awarded for each query if the query is correct. Otherwise, 0 mark.
Task 6 [10 marks] – Individual
Taking as example the current COVID-19 pandemic, write a 300 – 500 words report on the possible considerations for data collection, ethical use and management of reporting your RAT result (a positive Rapid Antigen Test (RAT)) to Queensland Health.
MARKING CRITERIA:
• The key considerations for ethical use of data are clearly articulated and comprehensively discussed & Excellent formatting and impeccable grammar. (10-8 Marks)
• The key considerations for ethical use of data are clearly identified and discussed & Good formatting and very minor grammatical errors. (7-6 Marks)
• The key considerations for ethical use of data are adequately identified and discussed & Satisfactory formatting and a few grammatical errors. (5-4 Marks)
• Some of the key considerations for ethical use of data are identified and discussed & Poor formatting and many grammatical errors. (3-2 Marks)
• None of the key considerations for ethical use of data are identified or discussed &
Non-existent formatting and rife with fundamental grammatical errors. (1-0 Marks)
Task 7 [10 marks] – Individual
Using the following table structure, identify all functional dependences and then decompose this table into a set of 3NF relations.
Assumptions:
In the following table, these assumptions can be made:
1. there are no multivalued dependencies
2. any invoice numbers (invoiceID) may reference more than one product
3. any given product is supplied by a single vendor, but a vendor can supply many products
invoiceID productID customerID productDescription vendorID numberSold productPrice
867547 RS-E3422QW C001 Rotary Sander V211 10 $60.00
867547 DB-300932X C001 0.25-in. drill bit V211 8 2 $9.00
867547 BS-995748G C001 Band Saw V309 13 $55.00
867548 RS-E3422QW C004 Rotary Sander V211 23 $50.00
867549 PD-778345P C004 Power Drill V157 15 $90.00
MARKING CRITERIA:
Marks will be awarded for the following:
• For this exercise you will begin with 10 marks and be deducted 1 mark for each error.
Appendix A – Declaration Template
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 and group efforts. I declare that it does not contain plagiarized material.
Full Name Student No. Signature