INFO90002 S1 2022
Assignment 2 - SQL
Due: see LMS
Submission: Via the Canvas LMS
Weighting: 10% of your total assessment (Graded out of 200 marks)
WesternVic Car Rentals
WesternVic Car Rentals is a medium size car rental company that currently has 5 depots in the
Western Suburbs of Melbourne and Western Victoria. It rents out cars for short periods (up to 6
weeks). Customers can hire a vehicle from any depot and if necessary vehicles are transferred
between depots. Rental price per day depends on the car category and the season (low, shoulder or
high). The company keeps track of vehicles, their transfers, their rentals and their maintenance. All
staff members are associated with a specific depot where they either perform admin duties or
conduct maintenance of vehicles.
Figure 1. WesternVic Car Rental Data Model
INFO90002
Assignment 2 s2 2022 The University of Melbourne v1 2
Instructions
1. Rename all tables to have the last 4 digits in table names the same as the last 4 digits of your
student ID
Download the file named CarRentals_9999.sql from the LMS.
Open this file in a text editor, e.g. in MySQL Workbench, Notepad, Notepad++ or some other
Text editor
Change all occurrences of 9999 to the last 4 digits of your student ID (one way to do this is
to perform a find and replace). For example, if your student ID is 12349876, your tables will
get renamed as car9876, transfer9876, staff9876, etc.
Note, if renaming is not done, you cannot get full marks, a heavy penalty of 50% will be
applied.
2. Run the script on the engineering server (and / or on your local MySQL server). This will create
the car rentals database with all required tables and populate them with data.
3. Write the following SQL statement and execute it.
SELECT '123459999' as StuID, depot9999.*
FROM depot9999
(Note that in the above statement 123459999 should be replaced with your Student ID
and both occurrences of 9999 would be last 4 digits of your student ID, as in step 1 above)
Notice how each row contains your student id and all attributes from the depot table.
You are expected to include your student ID in all queries
It is expected that
your script will produce correct results
your code meets standards of quality as discussed in lectures
Write a single SQL statement to answer the following questions. Do not use inline views / schema on
read, views unless explicitly instructed to do so. Views, inline views and schema on read for Q1-Q10
will earn 0 marks.
If your result set is less than 10 rows, show ALL results. If it is longer, show at least 10 rows (a couple
of rows extra is not a problem). Specify how many results were returned in red font under the
screenshot.
INFO90002
Assignment 2 s2 ? 2022 The University of Melbourne v1 3
Questions
1. List all car categories and vehicles within each category. Your list should show category
code, category description, registration, make, model, and depot name. List the result
in alphabetical order of category code, then by depot name, then make and model.
(15 marks)
2. How many rentals have been booked in each car category in 2021? The results should
display category code, category description and number of rentals sorted by category
code.
(15 marks)
3. List the cars that had repairs of their glovebox. The results should display the car
registration, make and model, repair dates in and out, job description, staff member
name who performed the repair. Order results by repair date in.
(15 marks)
4. List the registrations, makes and models of the cars that had more than 5 repair or
maintenance jobs. List results from highest to lowest number of jobs.
(10 marks)
5. List rental price per day for each car category in each season of whatever the current
year happens to be (e.g. this year it is 2022). The results should display category code,
category description, season description, season start date and rental price. Order the
result by category code, then season start date. The query should be usable in the
future years.
(20 marks)
6. For category A vehicles, produce a list showing make, model and rental details (i.e. all
columns from the rental table) for the whatever the previous year happen to be (i.e.
currently it is 2021). Sort the results by registration number. Your query needs to be
usable in the future years.
Rental year to be determined from the first date of rental (i.e. collection date). Even if the
rental starts in one year (e.g. 2021) and ends in the following year, we consider only the year
when rental period started.
(15 marks)
7. For category C vehicles, produce a list showing make, model and rental details (i.e. all
columns from the rental table) and rental price per day for the previous 2 years (i.e.
2020 and 2021). Rename the price column as ‘Price per Day’. Sort the results by make
and model. Your query needs to be usable in the future years.
Price per Day is determined checking for collection date being within a particular
season. Even if some rental dates are within one season and other days in another, for
simplification we assume that the collection date price applies to all rental dates.
(25 marks)
INFO90002
Assignment 2 s2 ? 2022 The University of Melbourne v1 4
8. For categories D and E vehicles, produce a report for the previous year (i.e. 2021). The
report should show category code, make, model and rental details (i.e. all columns
from the rental table), as well as rental price per day, number of rental days and
calculated cost of rental. Order the results by category code, make and model. Your
query needs to be usable in the future years.
Note, rental per day is charged for 24 hours. For simplification, we do not deal with
collection time and return time. So if a car is rented on 1/08/2022 and returned on
2/08/2022, it’s 1 rental day; if a car is rented on 1/08/2022 and returned on
3/08/2022, it’s 2 rental days.
(30 marks)
9. Count all transfers between depots for each car category. Your results need to show
category code and category description and number of transfers. Order the results by
category code.
(15 marks)
10. List all customers NOT from Victoria who rented a car. The results need to show
client’s first and last name and their postcode and be sorted first in the descending
order of postcodes and then alphabetical order clients last names. Each customer
should appear on the list only once.
Note, Victorian postcodes start with 3.
(15 marks)
11.
a. Write the SQL DDL to create a view that lists the depot id and depot name,
regNo, make, model, prodYear, collection date and return date for cars that were
rented in the current year (i.e. 2022). This view must be useful in the future
years.
You do not need to include StuID
You need to provide 2 screenshots
– the list of tables and views from the left pane of Workbench showing your created
view and
– the results of running SELECT from your View.
(15 marks)
b. Using the View you created in Task 11a, list all depots (id and names) and their
average number of rental days per month in the current year (i.e. 2022). The
results should be displayed as DepotID, depot name, Month, average number of
days. The average number should be an integer (no fractional part). Order the
results by Depot ID and month number. You do not need to include months with
no rentals.
Note, if collection and return dates are in different months, work with the month of the
collection date.
You must have your student ID in task b.
(10 marks)
INFO90002
Assignment 2 s2 ? 2022 The University of Melbourne v1 5
Submission Details:
Submit a single PDF showing your answers to all questions
Specify your student name and ID at the top of your answer document.
Formatting requirements for your submission
For each question, present an answer in the following format:
Show the question number and question in black text.
Show your answer (the SQL statement) in blue text (DO NOT use a screen shot)
Show a screenshot of the result from Workbench. If your result set is less than 10
rows, show ALL results. If it is longer, show at least 10 rows (few rows extra is allowed).
Show how many rows were actually returned, in red text.
Remember to include your student ID (the only exception is q.11a).
Show each query on a separate page.
You must not use in-line views, schema on read, views for questions unless explicitly
instructed to do so (Q11 only).
Example:
Q.XX List all Mazda vehicles the company has. The output should show regNo, make, model,
production year.
SELECT '123459999' as StuID, regNo, Make, Model, ProdYear
FROM vehicle9999
WHERE make="Toyota";
2 Rows returned
IMPORTANT: ATTEMPT EVERY QUESTION!
INFO90002
Assignment 2 s2 ? 2022 The University of Melbourne v1 6
APPENDIX A. WesternVic Car Rental Business Rules
Clients
Clients can be in WesternVic Car Rental system but have never rented a car.
Vehicles
WesternVic Car Rental is a budget type company so their vehicles are not very modern, some are
quite old.
Staff
There are admin staff and staff involved in vehicle maintenance
Car category
Type of car, e.g. compact, family, 4WD, etc.
Season
Type of tourist season (high, shoulder, low)
Season Dates
Shows start data and end date for each season type
Season Prices
Specifies the price for each car category during specific season dates range.
INFO90002
A2 S2^2021 INFO90002 ? The University of Melbourne 7
APPENDIX B. Sample Marking Schema
In this sample marking rubric Questions 1 and 2 are referring to a different case study. They each are worth 10 marks out of 200. Please attempt every
question. The approach is as important as the result.