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  郵箱：99515681@qq.com   WX：codinghelp