辅导 INFO20003、讲解 SQL语言编程
INFO20003 Assignment 1 Page 1 of 4
INFO20003 Semester 1, 2024
Assignment 1: ER Modelling
Due: Thursday 28 March 2024 5:59pm
Submission: Via LMS https://canvas.lms.unimelb.edu.au/
Electric vehicle charging
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. Your team is going to be helping to create the technical infrastructure for the charging
stations, electric vehicles, and electric vehicle users. As part of this, you will be creating a MySQL database
to store this information. The following specifications have been provided to you to assist in your design.
Charging station
For each charging station, the system records its details, that are – the address of the charging station (as
street name, street number, suburb, state, postcode), establishment date, the opening hour, and the
closing hour (assume that the opening and closing hours are the same for each day of the week for a
charging station). 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. Only the companies that
manufacture electric vehicles can own charging stations (see ‘Electric vehicle’ section below for details of
EV manufacturing company). Not all companies that manufacture EVs own charging stations.
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., 72 or 96). There are two
types of charging costs associated with each outlet – peak-hour charging cost and off-peak hour charging
cost (e.g., peak-hour charging cost 25 cents/kWh and off-peak charging cost 20 cents/kWh). Different
outlets of the same charging station can have different peak and off-peak charging costs, but one charging
outlet has only one peak-hour charging cost and one off-peak hour charging cost. For each outlet, the
system also records whether the outlet is currently occupied or not occupied.
The system also stores information about ‘facilities’, if they are associated with any charging station. For
each facility, the system stores the type of the facility (e.g., café or restaurant), the opening hour, and the
closing hour (assume that the opening and closing hours are the same for each day of the week for a
facility, however those hours could be different from the opening/closing hours of a charging station
associated with it). A facility can be at most associated with one charging station, while the charging station
does not need to be connected to any facility (e.g. a charging station on a very long road in regional
Australia) but is connected to at most one facility. A facility can provide discount coupons, which can be
used for discounted rates of a ‘charging event’ (see ‘Charging event’ section below for details). For each
coupon, the system stores some values of the coupon, which are –the coupon ID, discount value, and
expiry date. A coupon can only be issued by one facility and used in at most one charging event.
INFO20003 Assignment 1 Page 2 of 4
Electric vehicle (EV)
Each electric vehicle is associated with a unique vehicle identification number (VIN), manufacturer
company, model name, year, capacity of the battery (e.g., 60kW). For each manufacturer company - the
name of the company, a unique ABN number, and the current CEO’s name are stored. An electric vehicle
is manufactured by only one company, while the company can manufacture multiple vehicles. The system
stores the company information of only the companies that manufacture at least one EV. Sometimes an
EV manufacturing company is owned by a larger EV manufacturing company. One EV manufacturing
company can own multiple companies, but one company can be owned by only one other company.
Each electric vehicle is registered with only one person. The system stores information about the
registered electric vehicles only (not the unregistered vehicles). For each person, the system stores that
person’s (unique) driving license number, their name, and one or more phone numbers. 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 using which coupon (if any). For each charging event, the system stores
the start datetime, and the end datetime of the charging.
A charging event may or may not use a discount coupon, where the coupon can only be from one of the
facilities. Only one discount coupon can be applied for a specific charging event. An EV can charge at the
same outlet at a different datetime, which will be considered as a different charging event.
While entering a charging station, the driver of each EV needs to provide their information in the system.
If the driver’s information has already been in the system (either as a person with whom an EV is
registered, or as driver entered before), then the driver only needs to enter their driving licence number.
Otherwise, the driver needs to enter the (unique) driving license number, name, and one or more phone
numbers. The system also records the entry time. An EV can enter at the same charging station at different
datetimes, which will be considered as a different entry event. If all the outlets at a charging station are
occupied upon their entry, then the electric vehicles need to wait in a queue. The system records the EVs
waiting in the queue for a charging station, capturing their position and arrival time.
Business Requirements
Your database design needs to be able to meet the business's needs to answer questions as:
1. How many charging stations are owned by the company ‘Tesla’, or by the other companies that are owned
by ‘Tesla’?
2. Which charging station in the state ‘Victoria’ has the most number of outlets, where the charging station
also has at least one ‘café’ or ‘restaurant’ as a facility associated with it?
3. Which electric vehicle has the highest battery capacity that is manufactured by the company ‘Tesla’?
4. What are the facilities whose coupon has not been used for any charging event today?
5. What was the total income by the outlet ID ‘1’ of charging station ‘2’ on January 1, 2024?
6. Which electric vehicle has been driven by two or more different drivers when entering different charging
stations on the same day?
7. How many electric vehicles are registered with the person ‘Renata Borovica-Gajic’?
INFO20003 Assignment 1 Page 3 of 4
8. Which charging station has the highest number of electric vehicles charges (charging events) in total?
9. What type of facility has the coupons that are used the most in charging by any electric vehicle
manufactured by ‘Tesla’ in the charging stations located in the state ‘Victoria’?
10. How many EVs that have entered after 10am today in the charging station with ID ‘10’ are still waiting in
its queue?
Instructions
Assignment 1 is worth 10% of your final mark. The assignment will be graded out of 100 marks as
described in the table below:
ER Physical Model with assumptions 80%
Conceptual Model in Chen’s Notation 20%
.mwb Physical Model File submitted Assignment Hurdle
You are to analyse this business case and design a Conceptual ER Model in Chen’s notation (can be hand
drawn) as taught in class and a Physical ER Model for a MySQL Relational Database in Crow’s foot
notation (modelled with MySQL Workbench or Vertabelo online tool).
You may list any assumptions you have made about the model. There is a 200-word limit for
assumptions. Assumptions must not be used to simplify the assignment, but only to justify your decision
about any ambiguity in the study.
Assignment Submission
You are to submit the assignment under the Assignments tab on Canvas LMS. The submission will require
you to submit two files:
1. A SINGLE PDF document containing:
i. a legible (hand drawn, or otherwise modelled) picture of a conceptual model in Chen’s notation.
ii. a screenshot/export of your Physical ER Model done in MySQL workbench or Vertabelo online tool.
Ensure that all attributes are readable, and tables are fully expanded.
iii. any assumptions you made (limit this to a maximum of 200 words)
Note: You can use an online tool like https://smallpdf.com/merge-pdf to merge multiple PDFs together.
2. A copy of your final .mwb MySQL Workbench file of your Physical ER model.
If you have used Vertabelo to design the Physical ER model, there is no need to upload this file. Please
ensure that all the details of the model in your report are perfectly clear and readable.
Note: The .mwb file will only be used by staff in circumstances where the screenshot/export of your
model in the PDF is unreadable (i.e. if we have to open this file, a penalty of 10% will occur), so remember
to include the export of your physical model in the PDF!
If you fail to submit clear and legible models your assignment will be penalised.
INFO20003 Assignment 1 Page 4 of 4
Unless you have an approved extension (see below), you will be penalised -10% of the total number of
marks in the assignment per day that your submission is late. For instance, if you received a 78% raw
score, but submitted 2 days late, you'd receive a 58% for the assignment.
Requesting a Submission Deadline Extension
If you need an extension due to a valid (medical) reason, you will need to provide evidence to support
your request by 5pm, 27 March. Medical certificates need to be at least two days in length.
To request an extension:
• Email Farhana Choudhury (farhana.choudhury@unimelb.edu.au) from your university email address,
supplying your student ID, the extension request and supporting evidence.
• If your submission deadline extension is granted you will receive an email reply granting the new
submission date. Do not lose this email!
Reminder: INFO20003 Hurdle Requirements
To pass INFO20003, you must pass two hurdles:
• Hurdle 1: Obtain at least 50% (15/30) or higher for the three assignments (each worth 10%)
• Hurdle 2: Obtain at least 50% (35/70) or higher for the combination of quizzes and end of semester exam
It is our recommendation to students that you attempt every assignment and every question in the
exam.
GOOD LUCK!