代写INFO20003、代做SQL语言编程

日期：2024-03-23 09:53

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!