INFO90002 Assignment 2 s2 2022 The University of Melbourne v1 1 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 v
1 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. Q1 (10) SELECT (2) first_name, last_name, job_title, dept_name 0.5 marks each FROM (2) staff departments 1 mark each JOIN (4) LEFT | RIGHT OUTER JOIN 4 – correct 3 – natural join | inner join 2 – left | right join without OUTER ; incorrect join condition 1 – Cartesian or any other join RESULT (2) 118 rows Kimberly Grant must be in the set 2 correct 0 other Q2 (10) SELECT (2) country_name (1 mark) count(staff_id) (2 marks) (alias ok too) FROM + JOIN (2) INNER JOIN | NATURAL JOIN 2 marks outer joins for no staff 1 mark GROUP BY (2 marks) ORDER BY (2 marks) RESULT (2 marks) correct order (1 marks) incorrect order unordered but correct data (0 marks) incorrect result; no result Any questions Check the Assignment 2 LMS Ed Discussion forum for suggestions and hints.