FIT2094 Databases
2022 Summer Semester B
Assignment 2B – SQL – Olympic Logistics Services
Assignment weighting 15%
Copyright By PowCoder代写 加微信 powcoder
Marked out of 100 returned as a grade out of 15
This task continues the OLS case study that you have worked on for Assignment 2A by developing queries according to the given requirements listed below against the OLS database under the Monash Oracle database account ¡®OLS¡¯.
OLS owns a fleet of vehicles, which vary from sedans to vans. Some vehicles have special features such as full blackout, armour plating, ability to transport long items such as javelins and pole vault poles, integrated GPS tracking etc. OLS wants to keep track of all such special features, if available, in each of their vehicles so as to assign a vehicle that matches the Olympic officials booking requirements.
Furthermore, OLS has informed us that all drivers must complete an initial ‘Transport of VIPs’ (code ‘VIP’) training module before they are permitted to drive OLS vehicles. Drivers are also able to complete additional training modules in areas such as specialist first aid training, advanced security training etc. These modules vary in length from 1 to 5 days. Not all drivers will complete these additional training modules. OLS wishes to record, for all modules successfully completed, the code and name of the module a driver has completed and the date on which they completed the module. In addition, they wish to maintain a total count (as an attribute within the system) of all the training modules a driver has completed since they were employed at OLS.
Locations that OLS travels to are recorded as a unique location name (eg. Aquatics Centre), street address, town and type of location (eg. Olympic venue, hotel, airport, private address, restaurant etc). Some locations do not have a name.
The OLS data model has been modified to accommodate the above extra data requirements and is shown below:
Page 1 of 7
The tables for the OLS have been created in the Monash Oracle server and are available via the user OLS who has given you select rights on the tables. You will use these tables to answer the SQL Queries listed below.
Page 2 of 7
Your answers for these tasks must be placed in the supplied SQL Script ols_queries.sql
******************************************************************************************************
In answering these queries you are ONLY permitted to use the SQL structures and syntax which have been covered within this unit ie. the material covered in SQL Basic (session 7 workshops & session 8 tutorial), SQL Intermediate (session 9 workshops & session 10 tutorial), SQL Advanced (session 10 workshops & session 11 tutorial) and the Oracle Common Functions document.
SQL syntax and commands outside of the covered work, as detailed above, will not be accepted/marked.
******************************************************************************************************
You must ONLY use the data as provided in the text of the questions. Where a particular case (upper case, lower case, etc.) for a word is provided you must only use that case. You may divide names such as into the given name of Martainn and a family name of Jenteau if required. Failure to adhere to this requirement will result in a mark of 0 for the relevant question.
ANSI joins must be used where two or more tables are to be joined, under no circumstances can “implicit join notation” be used – see the session 7 workshop slides and the session 8 tutorial
When required to show output which involves the official or driver name as a full name, the name must not have any leading or following spaces (ie. not start or end with a space).
Where a full name is requested in any output it should be in the form given name family name eg. .
REMEMBER you must keep up to date with the Moodle Ed Assignment 2B forum where further clarifications may be posted (this forum is to be treated as your client).
Please be careful to ensure you do not publicly post anything which includes your reasoning, logic or any part of your work to this forum, doing so violates Monash plagiarism/collusion rules and has significant academic penalties. Use private posts or email your allocated tutor to raise such questions.
Page 3 of 7
Queries to complete [100 marks]
Q1. List the full name (given and family) of all drivers who speak English AND have a clearance level 3. Display the output in ascending order of the full name.
Q2. List the Olympic official id and family name of those officials who come from a country with a country name which contains the letter ‘a’ and have an IOC role as a Head Coach. Display the output in ascending order of the family name, if two officials have the same family name, order them by the Olympic official id.
Q3. List the trip_id, the planned start and end date time of all trips (completed and non-completed) that have the airport (ie. the location name includes the word airport) as its starting or destination location. Display the list in the ascending order of planned start date time, where two or more trips start at the same date and time order them by the trip_id.
Q4.List the registration number/s of the vehicle/s that have the most number of current bookings (trips which have not as yet been completed). The output should display the vehicle registration number and the total current bookings for the vehicle. If there is more than one vehicle with the most number of current bookings, order the output in descending order of the registration number.
Q5. List all Olympic officials who have booked more than the average number of trips per official. The output should display the Olympic official id, their full name and the total number of trips they have booked. Order the output in descending order of the total number of trips each official has booked, and where two or more officials have the same number of bookings, order them by the Olympic official id.
Q6. List the completed trips whose trip duration is greater than the average completed trip duration. Your list must show trip_id, trip actual start date time, trip actual end date time, names of the original location and destination location and the trip duration in number of minutes. Order the list in ascending order of the trip actual start date time, where two or more trips start at the same start date and time order them by the trip_id.
[15 marks]
Page 4 of 7
Q7. Find the second most popular destination(s) that have been booked for trips. Display the location id, location name and total number of booked trips for the second most popular destination(s). Order the output in ascending order of location name.
[15 marks]
Q8. Find the total payment for all drivers during the period from 26th January to 9th February 2022 (inclusive). The payment rate is $40/hr. If a driver doesn’t complete any trips, show their total payment as ‘Not Applicable’. The total payment must be displayed in the following format: $1234.56. Your output must show driver id, driver full name and their total payment. Order the output in ascending order of driver_id.
Your output must have the form shown below (partial output only shown):
[16 marks]
Q9. For all languages, find out how many drivers can speak each language and the number of trips that have been booked (completed and non-completed) by the official who speaks each language. If there is a language that no driver can speak, the output should be 0. Similarly, for a language where there is no trip booked, the output should also be 0.
Display the language name, the total number of drivers who can speak each language (0 for no driver), and the total number of trips that have been booked (completed and non-completed) by the official who speaks each language (0 for no trips booked). Order the output in ascending order of language name.
Your output must have the form shown below (partial output only shown). In the screenshot, it is showing that regarding the ¡®Belarusian¡¯ language, there is no driver who can speak this language and no trips have been booked by the official who speaks this language.
— End of Queries —-
[16 marks]
Page 5 of 7
SUBMISSION REQUIREMENTS
Due Date: Friday 11th February 2022 at 2 PM (AEDT)
Please note, if you need to resubmit, you cannot depend on your tutors’ availability, for this reason, please be VERY CAREFUL with your submission. It is strongly recommended that you submit several hours before this time to avoid such issues.
For this assignment there is only one file you are required to submit: ¡ñ ols_queries.sql
If you need to make any comments to your marker/tutor please place them at the head of this script in the “Comments for your marker:” section.
Do not zip this file into a zip archive, submit the SQL script as it is. The SQL script must also have been pushed to the FIT GitLab server with an appropriate history as you developed your solutions (a minimum of four pushes). Please ensure your commit comments are meaningful.
Late submission will incur penalties at the rate of -5 mark for every 12 hours the submission is late.
Please note we cannot mark any work on the GitLab Server, you need to ensure that you submit correctly via Moodle since it is only in this process that you complete the required student declaration without which work cannot be assessed.
It is your responsibility to ENSURE that the file you submit is the correct file – we strongly recommend after uploading a submission, and prior to actually submitting, that you download the submission and double-check its contents.
Your assignment MUST show a status of “Submitted for grading” before it will be marked.
If your submission shows a status of “Draft (not submitted)” it will not be assessed and will incur late penalties after the due date/time.
Please carefully read the documentation under the “Assignment Submission” on the Moodle Assessments page which covers things such as extensions and resubmission.
Page 6 of 7
CRITERIA FOR MARKING
Submissions will be graded on:
¡ñ the correct application of SQL statements and constructs to:
¡ð retrieve the required data in the required format, and
¡ð where a layout or column heading has been specified, appropriately reflect these
requirements.
Submissions will be grade penalised (ie. lose marks) if they:
¡ñ contain SET ECHO … or SPOOL commands,
¡ñ the SQL code is not formatted consistently
¡ñ do not have a semicolon (;) closing the query for every query submitted,
¡ñ do not make use of column aliases when you use arithmetic calculation, concatenation, functions, or other output manipulation unless specified otherwise in the above,
¡ñ do not use to_char/to_date where appropriate in handling dates,
¡ñ use subqueries and SQL conditions unnecessarily (although you are not required to consider efficiency of your solution you should try an ensure that you use the minimum number of subqueries and SQL conditions when arriving at your answer),
¡ñ do not have an appropriate development history on the FIT GitLab server for all source files (at least four pushes required),
¡ñ do not restrict the SQL structures and syntax used to those which have been covered within this unit ie. the material in SQL Basic (session 7 workshops & session 8 tutorial), SQL Intermediate (session 9 workshops & session 10 tutorial), SQL Advanced (session 10 workshops & session 11 tutorial) and the Oracle Common Functions document. Any such material will NOT be marked.
Page 7 of 7
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com