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:
To:
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}