NFO20003 Electric vehicle

NFO20003 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 an

costs to the electric vehicles. The charging stations can also be 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 a代写NFO20003  Electric vehiclet 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:

  

abn company_name parent_abn Note

  

“1” “General Motors LLC” NULL

  

“2” “GMC” “1” ‘child’ company of “1”

  

“3” “Hummer, Inc” “2” ‘grandchild’ company of “1”

  

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 Streetin postcode3000in January 2025? Use therequested_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

  

SQL Response Formatting Requirements

  

To help us mark your assignment queries as quickly/accurately as possible, please ensure that:

  

1. Your query returns the projected attributes in the same order as given in the question, and

  

does not include additional columns.

  

E.g., if the question asks ‘return as (userId, name)’, then:

  

? DO: “SELECT userId, name …”

  

? ? DON’T: “SELECT name, userId…”

  

You can, however, rename/name the columns to whatever you’d like using AS, only the order

  

matters.

  

2. Do NOT use “databaseName.tableName” format.

  

E.g.:

  

? DO: “SELECT userId FROM users…”

  

? ? DON’T: “SELECT userId FROM coltonc.users …”.

  

Note that you can use tableName.columnName format, like researchers.email.

  

3. Ensure that you are using single quotes( ‘ ) for strings

  

Double quotes should only be used for table names (but you shouldn’t need to do this since we don’t

  

have spaces in our table names)

  

E.g.:

  

? DO: …WHERE name = ‘bob’

  

? ?DON’T: …WHERE name = “bob”…

  

4. Do NOT delete the special comment markers in the SQL template file.

  

These include (where X is the question number):

  

-- BEGIN QX

  

-- END QX

  

-- END OF ASSIGNMENT

  

These help us mark your assignment!

  

5. Comments are optional, but will help tutors to understand your code!

  

Submission Instructions

  

Your submission will be in the form of an SQL script. There is a template file on the LMS, into which

  

you will paste your solutions and fill in your student details (more information below).

  

This .sql file should be submitted on Canvas by 6pm on the due date of Friday 2nd May. Name your

  

submission as 987654.sql, where 987654 corresponds to YOUR student id.

  

Filling in the template file:

  

The template file on the LMS has spaces for you to fill in your student details and your answers to the

  

questions. There is also an example prefilled script available on the LMS as well. Below are screenshots

  

from those two documents explaining the steps you need to take to submit your solutions:

  

Step Example

  

1. At the top of the

  

template, you’ll need to

  

replace “XXXXXXXX”

  

with your student

  

number and name

  

Template

  

Example Filled in

  

2. For each question 1- 10, place your SQL

  

solution in between the

  

“BEGIN QX” and

  

“END QX” markers.

  

Ensure each query is

  

terminated with a

  

semicolon “;”

  

Template

  

Example Filled in

  

3. Test that your script is

  

valid SQL by running it

  

from MySQL

  

Workbench. Run the

  

entire script by copy- pasting this entire file

  

into a new workbench

  

tab, placing your cursor

  

at the start of the file

  

(without selecting

  

anything), and pressing

  

the lightning bolt to run

  

the entire file.

  

All queries should run

  

successfully one after

  

another. If not, check to

  

make sure you added

  

semicolons ‘;’ after each

  

query.

  

All 10 queries ran sequentially and were successful.

  

Late submission

  

Unless you have an approved extension (see below), you will be penalised -10% of the total number of

  

marks in the assignment per day day (including weekdays and weekends) that your submission is late.

  

For instance, if you received a 78% raw score, but submitted 2 days late, you'd receive a 58% score for

  

the assignment.

  

Requesting a Submission Deadline Extension

  

If you need an extension due to a valid (medical) reason, you need to follow the procedure described in

  

FEIT Extensions and Special consideration page:

  

canvas.lms.unimelb.edu.au/courses/210… consideration?module_item_id=6469145.

  

Reminder: INFO20003 Hurdle Requirements

   To pass INFO20003, you must pass two hurdles:

 

 Hurdle 1: Obtain at least 50% (15/30) for the three assignments (each worth 10%)

 Hurdle 2: Obtain at least 50% (35/70) for the combination of the quizzes and final exam

Therefore, it is our recommendation that you attempt every assignment and question in the exam

GOOD LUCK! WX：codinghelp