INFO20003 Semester 1, 2025

Assignment 2: SQL

Due: 11:59pm Friday, 2 May, 2025

Weighting: 10% of your total assessment

EV-XYZ: Electric vehicle and charger database

Description

EV-XYZ is a platform you’re creating to help keep track of its electric vehicles, charging stations, and charging activities.

An electric vehicle (EV) charging station provides charging facilities with different charging rates and costs to the electric vehicles. The charging stations can also be associated with other facilities like cafés and restaurants.

Charging station

For each charging station, the system records its details, that are – the address of the charging station (as street address, suburb, state, postcode), and the establishment date. Each charging station is also associated with at least one ‘company’ that owns that charging station. A charging station can be jointly owned by multiple companies.

Each charging station has at least one charging ‘outlet’ where electric vehicles can plug-in for charging. An outlet of a charging station can be uniquely identified with the charging station’s ID and the outlet’s ID, as ‘charging station ID X, outlet ID Y ’.  Each outlet has a charging rate in kW (e.g. 120), and the charging cost per kwh is also recorded (in $/kWh, e.g. 0.25 $/kWh). Different outlets of the same charging station can have different charging costs.

The system also stores information about ‘facilities’ (e.g., a café or restaurant), if they are associated with a charging station. A facility can provide discount coupons, which can be used for discounted rates of a ‘charging event ’. For each coupon, the system stores some values of the coupon, which are – the unique coupon ID, and discount value. A coupon can only be issued by one facility and used in at most one charging event.

Electric vehicle (EV) + People

Each electric vehicle is associated with a unique vehicle identification number (VIN), manufacturer company, model name, year, capacity of the battery (in kWh, e.g. 60kWh). For each manufacturer company - the name of the company, a unique ABN number, and the current CEO’s name are stored. Sometimes an EV company is owned by a parent EV company, which the model also stores.

Each electric vehicle is registered to one person. For each person, the system stores that person’s (unique) driving license number, and their name. One person can have multiple  electric vehicles registered with them.

Charging event

The system maintains the information of all charging events – that is, which electric vehicle is charged at which outlet of a charging station. When a person wants to charge a car, they request to charge at a particular charging station. The person who charges the car may not necessarily be the car’s registered owner, so we record the license number of the person who is charging. Once an outlet is available, the system will assign an outlet to the person, and they may use it to start charging. The kWh a charge event consumed is also recorded after charging is completed.

A charging event may or may not use a discount coupon, where the coupon can only be from one of the facilities. A discount coupon represents a ‘percentage discount’ (e.g. a value of 0.5 indicates a 50% discount).

Data Model

Figure 1: The physical ER model of EV-XYZ

Assignment 2 Setup

A dataset is provided which you can use when developing your solutions. To set up the dataset, download the file ev_2025.sql from the Assignment link on Canvas and run it in Workbench. This script creates the database tables and populates them with data. Note that this dataset is provided for you to experiment with, but it is not the same dataset as what your queries will be tested against (the schema will stay the same, but the data itself may be different). This means when designing your queries you must consider edge cases even if they are not represented in this particular data set, and should not hardcode information like IDs into your queries.

The    script.    is    designed    to    run    against    your    account    on    the    Engineering    IT    server (info20003db.eng.unimelb.edu.au). If you want to install the schema on your own MySQL Server installation, uncomment the lines at the beginning of the script.

Do NOT disable only_full_group_by mode when completing this assignment. This mode is the default, and is turned on in all default installs of MySQL workbench. You can check whether it is turned on by running the query \SELECT @@sql_mode;\. The command should return a string containing “ONLY_FULL_GROUP_BY” or “ANSI”. When testing, our test server WILL have this mode turned on, and if your query fails due to this, you will lose marks.

The SQL tasks

In this section are listed 10 questions for you to answer. Write one (single) SQL statement per question. Subqueries and nesting are allowed within a single SQL statement

In  general,  we  care  more  about  correctness  than  constructing  the  ‘most  efficient’  query (computationally, or in terms of number of characters/lines). However, you may be penalized for writing overly complicated  SQL  statements  (e.g  the  query  is  2-3x  longer  than  required,  using superfluous joins, etc), using very poor formatting, using very poor alias naming, or other decisions that make it hard for us to read/understand what you’re trying to do when marking!

DO NOT USE VIEWS (or ‘WITH’ statements/common table expressions) to answer questions.

1.   Find the model name and model year of the vehicle with the highest battery capacity. If there are ties, return a row for each of those model name and year with equal highest capacity. Your query should return results of the form. (model_name, model_year, battery_capacity). (1 mark)

2.   Find all the charging stations with at least one outlet of 100 or higher charging rate. Do not repeat the same station multiple times in the result if it has multiple outlets which meet the criteria. Your query should return results of the form. (station_id, state, postcode). (1 mark)

3.   Find all the charging stations that do not have any facility associated with them. Your query should return results of the form. (station_id). (1 mark)

4.   Find all the people who have electric vehicles registered in their name, where that vehicle has no charging event in the database. Only include people with at least one car registered to them that meets this criteria. Your query should return (license_number, name, total_num_of_cars_ with_no_charge_event_registered_to_person), ordered by name in increasing order. (2 marks)

5.   Find all facilities that have ever issued a coupon, but had no coupons redeemed on “2025-01- 01” (i.e., no charging event requested charging using that coupon on that day). Your query should return all such facilities in the form (facility_id). (2 marks)

6.   Find all vehicle models and model years that, on average, charge more than 50kWh when they charge at outlets with a charging rate > 68 kW. If a charging event has NULL for kWh value, it should not be considered in the average. The average_kwh must be rounded to two decimal places (hint:  use   the  `Round`  function).  Return  results  as  (model_name,  model_year, company_name, rounded_average_kwh). (2 marks).

7.   Find the total number of vehicles manufactured by the company with an ABN of ‘1’, or any of that company’s child or grandchild companies. Your query should return a single value of the form (total_number_manufactured) (2 marks).

Further clarification for Q7:

If a company X is owned by company Y, then X is the child company of Y. If company Y is owned by company Z, then X is the grandchild company of Z. You may assume there are no ‘great-grandchild’ companies (see example below). You may also assume that there are no circular relationships, e.g., if X is a child or grandchild of Y, then Y  cannot be a child or grandchild of X.

For example, suppose that the `Company` table looked like the following:

Since the company with abn “2” is a child of (owned by) company “1”, and company “3” is a child of company “2”, answering this question would involve finding the total number of cars manufactured by companies “1”, “2” and “3”. There will never be a company which has a parent_abn of “3”, since that would then be a “great-grandchild company”.

8.   Find all vehicles that have only ever been charged by people who are NOT the registered owner of the vehicle. Only include vehicles in the result that have been in at least one charging event. Return results as (VIN). Charging events with NULL kWh should still be considered. (3 marks)

9.   Find all (person, car) pairings where the person has charged that car at every outlet of every station that is both located in a postcode between 3000 and 4000 (including 3000 but not 4000) and owned by the manufacturer of the car. Return results as (license_number, VIN). Only consider stations owned by the company directly, not by child companies. Charging events with NULL kWh should still be considered. (3 marks)

Further clarification for Q9:

-     If a carY has been charged at all outlets matching the criteria by personX, and additionally has been charged at all outlets matching the criteria by personW, the results would include rows (license_number_personX, vin_carY) and (license_number_personW, vin_carY).

-     A row in the output of the query indicates that the same person charged the same car at all outlets that match the criteria for that car. Say there exists a carY, and station1 and station2 are the only two stations that fulfil the criteria for carY (have a postcode of 3xxx, and are owned by the manufacturer of carY). Say there exists a personA who has charged carY at every outlet of station1 but never charged at any outlet of station2. A different personB also exists, who has charged the same carY at every outlet of station2 but never at any outlet of station1. In this instance, no rows should be returned as result, because no single person charged carY at every outlet  matching  the  given  criteria  (even  though  the  car  was  charged  at  every  outlet  by somebody).

10. What was the total income of outlet `2` of the charging station located at street address `125 Collins Street` in postcode `3000` in January 2025? Use the `requested_at` date to determine whether a charging event was on that date. Your query should return a single value of the form (total_income), rounded to two decimal places (hint: use the `Round` function, and round after performing any aggregations). Note that you should consider the income after applying any discounts (see hint below). (3 marks)

Hint: The income generated from a single charging event E at an outlet O which used coupon C for a discount can be calculated as:

E.kwh x O.price_kwh x C.discount