INFO20003代做、代写SQL编程设计

日期：2025-04-02 08:26

INFO20003 Semester 1, 2025

Assignment 1: ER Modelling

Due: Thursday 3 April, 2025 11:59pm

Your team will be helping to create the technical infrastructure for the missions, rockets, crews, faciliCes, etc. of

SpaceXYZ. As part of this, you will be creaCng a MySQL database to store this informaCon. The

following specificaCons have been provided to you to assist in your design.

FaciliDes

SpaceXYZ has various faciliCes serving different purposes, for example, headquarter, launch site,

landing site, control centre, etc. For each facility, the system records its facility ID, address, and

establishment date.

Mission

SpaceXYZ runs mulCple missions, and the missions have different objecCves. Examples include

satelliteslaunching mission, Earth orbit mission (that allows people to experience the planet from over

300 kilometres), transport mission (delivering cargo and astronauts to and from the InternaConal

Space StaCon), Mars colonisaCon mission, etc. For each mission, the system stores the mission ID,

mission type (that is, the objecCve), status (acCve, proposed, or complete), start date, and end data.

Launch event

This is the specific event of sending rocket(s) into space. A launch event may or may not be associated

with a mission, but if it is, then a launch event is associated with at most one mission. There can be

any number of launches associated with a mission. For each launch event, the system storesthe launch

date, launch site, and landing site informaCon.

A launch site or a landing site is one of the faciliCes of SpaceXYZ. A facility can be used for any number

of launch events. A parCcular launch event is associated with exactly one launch site, but there may

or may not be a landing site for a launch (that is, if the rocket is not meant to be returned). For some

launch events, SpaceXYZ may bring a tower with two long arms (called ‘Mechazilla’, o@en referred as

‘chopsCck arms’ due to the shape) to the landing site to catch a landing rocket. Therefore, the system

also records whether the landing site of a parCcular launch event has used a Mechazilla or not.

Rocket

A rocket is the launch vehicle that is associated with a launch event. For each rocket, the system

stores rocket ID, rocket type (e.g., Falcon 9, Falcon Heavy, etc.), number of engines, manufacture date,

and payload capacity. As rockets are someCmes reused by SpaceXYZ, for each rocket, the system also

keeps track of the number of total launches done by that rocket. For a parCcular launch event, one or

more than one rocket is used.

Payload

For each launch event, each rocket may carry zero or any number of payloads. A payload type could

be satellites, cargo, spacecra@, parts, etc. The system stores the informaCon of the payload type and

weight. The same payload may get carried at different launch events (e.g., a parCcular cargo being

carried by a rocket to the internaConal space staCon (ISS), and then later carried back from ISS to Earth

by a different rocket in another launch event). The system may store informaCon of the payloads that

have not been carried in any launch event yet.

Astronaut

The system records informaCon about individual astronaut, including their ID, name, naConality, and

experience level (a number between 1-10).

In SpaceXYZ, any number of astronauts can parCcipate in a launch event. (Note that, an astronaut does

not need to be in a rocket to parCcipate in a launch event). An astronaut can parCcipate in any number

of launch events. The astronauts that parCcipate in the same launch are called the ‘crews’ of that

launch.

Astronauts can train and be trained by other astronauts, and the system recording these training

relationships. Each astronaut can have any number of trainers, and can train any number of other

astronauts. For each training relation, the system stores the start and end date of the training (e.g.

Simon D’Alfonso trained Farzaneh Zirak between 01/01/2023 and 31/12/2023, and again Simon

D’Alfonso trained Farzaneh Zirak between 01/06/2024 and 31/12/2024). If the training is ongoing, the

end date is unpopulated.

Launch failure

If any failure happens for a launch event, the system also stores some informaCon for the failure

analysis, they are: failure Cme, cause (as a text descripCon of maximum 500 characters), and impact

(on a scale of 1-10). A launch can have zero or any number of failures (that is, a launch may conCnue

even a@er a failure event, and then another failure may happen). MulCple failures may also happen

at the same Cme.

Rocket storage

When rockets are not in use, they may get stored securely in one of the faciliCes of SpaceXYZ. The

system records the storage start date-Cme and end date-Cme. The same rocket can be stored mulCple

Cmes in the same facility on different occasions. For example, rocket ‘123’ is stored in a parCcular

facility on 1/6/2024 10am. Then later, the rocket is taken out on 1/6/2024 1pm, used for some

purpose, and then the same rocket is stored in the same facility on 1/6/2024 9pm. If a rocket is

currently being stored, the end date-Cme is empty.

Rocket tesDng

A rocket may undergo any number of tests. For each test of a rocket, the system records the test date,

test type, status, and outcome. Any number of astronauts can parCcipate in a test. The system also

keeps track of the total number of Cmes a rocket is tested.

Headquarter

Each mission has at most one headquarter, where the headquarter is one of the faciliCes of SpaceXYZ.

A facility can be the headquarter of any number of missions. Note that, a mission may not have any

headquarter, for example, for the missions that have been just proposed.

Person in charge

For each mission, there is at most one ‘person in charge’. Note that, a mission may not have any person

in charge, for example, for the missions that have been just proposed. A person can be the ‘person in

charge’ of at most one mission in SpaceXYZ. The system records the name, a unique email address,

and phone number(s) of the person in charge. Note that, the person in charge of any mission is not an

astronaut. The system may store the informaCon of some other persons who are not ‘person in charge’.

Business Requirements

Your database design needs to be able to meet the business's needs to answer questions such as:

1. Which type of missions had at least 5 launch events in 2024?

2. Which rockets have been reused in more than one launch event?

3. Which missions have at least one launch event, where the launch had both at least one

astronaut parCcipated in it and at least one payload in it?

4. Which faciliCes have been used for both a landing site and for storing at least one rocket?

5. Which launch events have the same launching site and the same landing site?

6. Which missions have not had any failures occurring in any of their launches?

7. Which rockets have been tested in 2024 at least twice, but have had a launch failure occur in

2025?

8. Which launches have had an astronaut where the trainer of that astronaut is also a crew of

the same launch?

9. Which mission has ‘Simon D’Alfonzon’ as the person in charge, and has its headquarter in

‘Melbourne, Australia’?

10. Which astronaut(s) have parCcipated in the tesCng of a parCcular rocket, and also in a launch

event where that parCcular rocket is used?

11. For the launch event ID ‘12’, which parCcular rocket (that is, what is the ID of the rocket) is

carrying a satellite of weight 10.5kg as its payload?

Note that, you do not need to answer these business requirements, but your database's final design

should be capable of addressing and answering these requirements through its structure and

funcConaliCes.

InstrucDons

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 notaDon (can be

hand drawn) as taught in class and a Physical ER Model for a MySQL RelaConal Database in Crow’s

foot notaDon (modelled with MySQL Workbench).

You may list any assumpCons you have made about the model. This part is opConal. If you want to list

any assumpCons, there is a 200-word limit for assumpCons in total. AssumpCons must not be used to

simplify the assignment, but only to jusCfy your decision about any ambiguity in the study (for

example, if the assignment descripCon does not clearly specify the parCcipaCon constraint on a

relaConship, you can list what assumpCon you made for that).

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

notaCon

ii. a screenshot/export of your Physical ER Model done in MySQL workbench. Ensure that all

a,ributes are readable, and tables are fully expanded.

iii. any assumpCons you made (limit this to a maximum of 200 words)

2. A copy of your final .mwb MySQL Workbench file of your Physical ER model.

Note: This model 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.

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

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

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

assignment.

RequesDng 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 consideraCon page -

Use of AI tools

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 combinaCon of quizzes and end of semester

exam

It is our recommendaCon to students that you a1empt every assignment and every quesCon in the

exam.

GOOD LUCK!