INFS1200/7900 Information Systems – Assignment 4 (7.5 marks) Due (revised): Friday 12 June, 2020 @ 11:59 PM The purpose of this task is to help students become familiar with SQL Data Query Language and the various techniques database administrators use to write successful and logically-correct queries. Students will learn to use SQL to draw meaningful information out of large datasets. 1. Task - QL DQL (Data Query Language) Using the correspondence on the pages below, complete the following tasks. Please ask your tutors for help if you require clarification on any aspects of the brief. Note: Please read Correspondence 1 and attachments 1 and 2 before attempting this task. The emails at the end of this assignment description contain a relational model. Please complete Assignment 4 using this model, and not your own v ersion that you submitted for earlier Assignments. In Peter’s email to Elaine, he mentioned that Dirt Road Driving have implemented a MySQL database to support their ride sharing application. Now that the database is sufficiently populated, they need help writing eight SQL queries which can process this data and return answers for specific questions/scenarios. Using the correspondence and the database provided, complete the following tasks for each of these eight questions: 1. Write an SQL query which returns the needed data 2. Provide a screenshot of the query/view output The submission template may highlight some additional query requirements for specific questions. Each question will also contain a brief description specifying the format and type of data your query should return. Unless otherwise stated, you can use set operations and nested queries. Note: The difficulty level of these queries does not necessarily follow a successive progression. An example question and response has been provided below. Ensure your Output Screenshot’s include the same detail as provided in this example. SEE NEXT PAGE FOR EXAMPLE QUESTION Example Query Question Return the driver(s) with the highest rating. Explanation This query should return a table containing three columns: first name, last name and rating of the driver(s) with the highest rating. SQL Solution SELECT fName, lName, MAX(rating) FROM Staff, UserRatesDriver WHERE Staff.id = UserRatesDriver.driverID Output Screenshot Note: As seen in the example provided above, your Output Screenshot must show two things: 1. The query being run (at least in part) 2. All the output produced by the query (unless otherwise specified in the question) Failure to provide an image meeting these requirements may result in a penalty being applied. 2. Submission Submissions will be done via Blackboard. Marking will be done through an electronic marking tool called Gradescope, which will also be used for providing feedback. Please use the supplied answer template for all answers. Your work must fit in the predefined sections or it will not be marked Submit your assignment electronically via the provided link on the INFS1200/INFS7900 Blackboard site under the Assessment folder. 3. Marking The parts of Assignment 3 have marks as indicated, totalling 7.5 marks (of 30 marks for all four assignments). 4. Plagiarism The University has strict policies regarding plagiarism. Penalties for engaging in unacceptable behaviour can range from cash fines or loss of grades in a course, through to expulsion from UQ. You are required to read and understand the policies on academic integrity and plagiarism in the course profile (Section 6.1). If you have any questions regarding acceptable level of collaboration with your peers, please contact either the lecturer or your tutor for guidance. Please do not post assignment questions on public discussion forums. SEE NEXT PAGE FOR CLIENT CORRESPONDENCE Correspondence 1: From:
[email protected] To:
[email protected] Date: 14/5/2020 02:25 PM Subject: RE: Student Support for Industry Project Hi Elaine, How are you? I hope you and your team are all settling into the new work arrangements in place during the coronavirus pandemic. On behalf of our IT department, I would again like to express our deepest gratitude for the ongoing hard work of both yourself and the INFS1200 student teams. The quality of their work has more than exceeded our initial expectation when we initially agreed to this partnership. In one of our first meetings, you mentioned that in the INFS1200 course also has a module which deals heavily on SQL Data Query Language. If you student teams are willing to assist on another project, we have some DQL related tasks we would love for them to take a look at! After consulting with your students’ teams EER diagrams and mapping our database administrator’s setup a MySQL database for the Dirt Road Driving application and populated it with some data from our initial beta trails. The only problem is our chief database administrator is sick this week and we need help generating queries/views to answer some questions. I have listed these questions below and provided a brief description for the more complex tasks. 1. Return the vin numbers of all vehicles with make “Toyota”. 2. Return the vin number and ride height of the 4WD which has the highest ride height with “alloy” wheels. 3. Return a list of all the locations stopped at by users born after 1st January 2000. 4. Return the user(s) who has been on the most trips with driver “Verity Choi”. 5. Return a combined list of the average ratings for both vehicles and drivers. 6. The police suspect one of our drivers may have been involved in a robbery on the 3/3/2020. They have requested the names of all drivers who were working that night anytime from 8PM to 10PM. 7. Our Board of Directors want to perform a feasibility audit on our client emergency contact system. We need a list of all the trip instances where an emergency contact of the passenger was also on a trip at the same time. 8. The tax office requires all ride sharing companies to provide a log showing the amount of time each car in our systems has been involved in ride sharing activities. Create a view which shows this information while obscuring private trip information like the driver and user IDs. EMAIL CONTINUES ON NEXT PAGE As you can see, some of these tasks are quite simple while others may be a little challenging for your student team. We would really appreciate solutions they could offer for any of these queries! I have attached to this email an SQL file containing an export of the database. Feel free to use this file to help write/test queries for the problems mentioned above. Our database administrator did make some minor changes to the relational schema sent to us by your student teams, I have attached the updated version to this email. Please let me know if your student teams would be interested in helping us out with this as well. Thank you! Kind regards, Peter Thompson Director of Innovation | Dirt Road Driving PLEASE SEE NEXT PAGE FOR ATTACHMENTS Attachments Attachment 1: Database Export Click here to open the SQL attachment. Note: If the hyperlink does not work, please manually open DirtRoadDriving.sql in Blackboard Attachment 2: Dirt Road Driving Database Relational Model User[id, dob, fName, mName, lName] Staff[id, dob, fName, mName, lName] Vehicles[vin, make, model] EmergencyContact[fName, lName, userID, email, phone] UserRatesDriver[userID, driverID, rating] UserRatesVehicle[userID, vin, rating] Trip[userID, driverID, vin, bookingTime, startTime, endTime] Driver[id, licence] Admin[id, deskNumber] 4WD[vin, rideHeight, wheelType] 2WD[vin, frontWheelDrive] StaffPhone[id, phone] TripStop[userID, driverID, vin, bookingTime, location] EmergencyContact.userID references User.id UserRatesDriver.userID references User.id UserRatesDriver.driverID references Driver.id UserRatesVehicle.userID references User.id UserRatesVehicle.vin references Vehicles.vin Trip.userID references User.id Trip.driverID references Staff.id Trip.vin references Vehicles.vin Driver.id references User.id Admin.id references User.id 4WD.vin references Vehicles.vin 2WD.vin references Vehicles.vin StaffPhone.id references Staff.id TripStop.{userID, driverID, vin, bookingTime} references Trip.{userID, driverID, vin, bookingTime}
