首页 > > 详细

代写INFS1200/7900-Assignment 4帮写SQL语言程序

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 
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. 
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. 
Correspondence 1: 
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. 
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 
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} 
  • QQ:99515681
  • 邮箱:99515681@qq.com
  • 工作时间:8:00-23:00
  • 微信:codinghelp

联系我们 - QQ: 99515681 微信:codinghelp
© 2014 www.7daixie.com