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!
請加QQ：99515681 郵箱：
[email protected] WX：codinghelp
