INFS1200/7900
COMP2714 Module
Module 3 Case
3 Case Study
Study 2 2
Focus
The purpose of this case study is to help students become familiar with SQL Data Manipulation
Language and the various techniques database administrators employ to write successfully and
logically correct queries. Students will learn to use SQL to draw meaningful information out of large
datasets.
Task
correspondence on the pages below, complete
Using the correspondence complete the
the following
following tasks.
tasks. Please ask your tutors
for help if you require clarification on any aspects of the brief.
Section 1 – SQL DML (Data Manipulation Language)
Please read Correspondence 1, attachments 1, 2 and 3 before attempting this section.
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 following 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
You can use the spaces provided in attachment 3 to record your answers.
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 screenshots
include the same detail as provided in this example.
SEE NEXT PAGE FOR EXAMPLE QUESTION
The University of Queensland ABN: 63 942 912 684
Brisbane QLD 4072 Australia CRICOS PROVIDER NUMBER 00025B
Example Query
Question Return the driver(s) who have received the highest user rating.
Explanation This query should return a table containing three columns: first name, last
name and rating of the driver(s) who have received the highest rating. A
driver can appear multiple times in the output.
SQL Solution SELECT fName, lName, rating
FROM Staff, UserRatesDriver
WHERE [Link] = [Link]
AND rating >= ALL (SELECT rating
FROM UserRatesDriver)
LIMIT 10;
Output
N/A
Screenshot
Note: As seen in the example provided above, your output screenshot must show:
1) All the output produced by the query (unless specified otherwise in the question)
2) For queries with a returning relation of more than 10 tuples, you can use the LIMIT 10 clause
to only capture the first 10 tuples of the table.
SEE NEXT PAGE FOR CLIENT CORRESPONDENCY
CRICOS Provider No: 00025B 2
Correspondence
Correspondence 1:
From: peter@[Link]
To: infs1200@[Link]
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 your work has more than exceeded our initial expectation when we initially agreed to this
partnership.
In one of our first meetings, you mentioned that the INFS1200 course has a module which deals
heavily with SQL Data Manipulation Language. If your student teams are willing to assist on another
project, we have some DML related tasks we would love for them to take a look at!
After consulting with your student team’s EER diagrams and mapping our database administrator’s
setup, a MySQL database for the Dirt Road Driving has been populated 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 in my third
attachment.
As you can see in the attachment, some of these tasks are quite simple while others may be a little
challenging for your student team. We’d 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
CRICOS Provider No: 00025B 3
Attachments
Attachment 1: Database Export
Click here to open the SQL attachment.
Note: If the hyperlink does not work, please manually open [Link]
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]
[Link] references [Link]
[Link] references [Link]
[Link] references [Link]
[Link] references [Link]
[Link] references [Link]
[Link] references [Link]
[Link] references [Link]
[Link] references [Link]
[Link] references [Link]
[Link] references [Link]
[Link] references [Link]
[Link] references [Link]
[Link] references [Link]
TripStop.{userID, driverID, vin, bookingTime} references Trip.{userID, driverID, vin, bookingTime}
CRICOS Provider No: 00025B 4
Attachment 3: Question & Answer Form for SQL Queries
Query 1
Question Return the vin numbers of all vehicles with make “Toyota”.
Explanation This query should return a table with only one column containing vin number(s).
SQL Solution
Output Screenshot Not needed
Query 2
Question Return the vin number and ride height of the 4WD which has the highest ride
height with “alloy” wheels.
Explanation This query should return a table with two columns. The first column should
contain the vin number and the second column should contain the ride height.
SQL Solution
Output Screenshot
Not needed
CRICOS Provider No: 00025B 5
Query 3
Question Return a list of all the locations stopped at by users born after 1 st January 2000.
Explanation This query should return a table with three columns: first name, last name and
stop location. Different locations stopped at by the same user should appear as
separate rows.
SQL Solution
Output Screenshot Not needed
Query 4
Question Return the user(s) who has been on the most trips with driver “Verity Choi”.
Explanation This query should only return a table contain a single column of userID(s).
SQL Solution
Output Screenshot Not needed
CRICOS Provider No: 00025B 6
Query 5
Question Return a combined list of the average ratings for both vehicles and drivers.
Explanation This query should return a table with three columns: rating type, rating identifier
and average rating value. The rating type should either be “Vehicle” or “Driver”.
The rating identifier will then be either the vehicle vin number or driverID
depending on the rating type. Note: The output screenshot for this question
only needs to show 10 tuples.
SQL Solution
This requires UNION- Do it after the Friday lecture
Output Screenshot Not needed
CRICOS Provider No: 00025B 7
Query 6
Question 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.
Explanation This query should return the drivers first name and last name. The time
conditions for this question are inclusive, i.e. including 8PM and 10PM. A driver
is classified as “working” between the start and end time (inclusive) of a trip
which they drive for. Your solution must use at least one non-correlated
nested query.
SQL Solution
Output Screenshot Not needed
CRICOS Provider No: 00025B 8
Query 7
Question 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.
Explanation This query should return the full tuple for each instance in the Trip table which
meets the requirements. You may assume that assume the first name and last
name in the EmergencyContact table is equivalent to the first name and last
name in the User table. If an emergency contact has the same name as
someone listed in the user table, they are referring to the same person. A trip’s
start and end times are inclusive of the time a user is defined as being on a trip.
Your solution must use at least one correlated nested query.
SQL Solution
Output Screenshot Not needed
Hint: Read this: [Link]
CRICOS Provider No: 00025B 9
Query 8
Question 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.
Explanation Your SQL solution should create a view which shows the vehicle vin number
and the amount time that vehicles has been used in ride sharing activities. The
time should be recorded in the format, “hours:minutes:seconds”. You may need
to create a secondary helper view in order to answer this question. The view(s)
can be named as you please. If you use several views, you should only include
an output screenshot for the final view. Note: The output screenshot for this
question only needs to show 10 tuples.
Hint: You may want to use the TIMESTAMPDIFF and SEC_TO_TIME MySQL
functions
SQL Solution This requires working with Views- Do it after the Friday lecture
Output Screenshot Not needed
CRICOS Provider No: 00025B 10