INFO20003 ER Modelling

INFO20003 Semester 1, 2025

Assignment 1: ER Modelling

Due: Thursday 3 April, 2025 11:59pm

Submission: Via LMS h1ps://canvas.lms.unimelb.edu.au/

will be helping to create the technical infrastructure for the missions, rockets, crews, faciliCes, etc. oSpaceXYZ. 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

Mission SpaceXYZ runs mulCple missions, and the missions have different objecCves. Examples includesatelliteslaunching mission, Earth orbit mission (that allows people to experience the planet from overSpace 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 beany number of launches associated with a mission. Foreach launch event, the system storesthe launchdate, 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 mayor may not be a landing site for a launch (that is, if the rocket is not meant to be returned). For somelaunch 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 systemstores 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

more than one rocket is used.

Payload

or 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

arried 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 thathave not been carried in any launch event yet. Astronaut The system records informaCon about individual astronaut, including their ID, name, naConality, andexperience level (a number between 1-10).In SpaceXYZ, any number of astronauts can parCcipate in a launch event. (Note that, an astronaut doesnot need to be in a rocket to parCcipate in a launch event). An astronaut can parCcipate in any numberof launch events. The astronauts that parCcipate in the same launch are called the ‘crews’ of thatlaunch.stronauts can train and be trained by other astronauts, and the system recording these trainingrelationships. Each astronaut can have any number of trainers, and can train any number of otherastronauts. For each training relation, the system stores the start and end date of the training (e.g.mon D’Alfonso trained Farzaneh Zirak between 01/01/2023 and 31/12/2023, and again SimonD’Alfonso trained Farzaneh Zirak between 01/06/2024 and 31/12/2024). If the training is ongoing, theend 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 conCnueeven 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 代写INFO20003 ER Modellingsecurely in one of the faciliCes of SpaceXYZ. Thesystem records the storage start date-Cme nd end date-Cme. The same rocket can be stored mulCpleCmes in the same facility on different occasions. For example, rocket ‘123’ is stored in aparCculaacility on 1/6/2024 10am. Then later, the rocket is taken out on 1/6/2024 1pm, used for someurpose, and then the same rocket is stored in the same facilityon 1/6/2024 9pm. If a rocket is

curently 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

eeps 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 anheadquarter, 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 personin charge, for example, for the missions that have been just proposed. A person can be the ‘person incharge’ 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 oneastronaut 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 ofthe 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 launcevent 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) iscarrying 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 designYou are to analyse this business case and design a Conceptual ER Model in Chen’s notaDon (can behand 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 listany assumpCons, there is a 200-word limitforassumpCons in total. AssumpCons must not be used tosimplify the assignment, but only to jusCfy your decision about any ambiguity in the study (forexample, if the assignment descripCon does not clearly specify the parCcipaCon constraint on arelaConship, 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 willrequire you to submit twofiles:

1. A SINGLE PDF document containing:i.a legible (hand drawn, or otherwise modelled) picture of a conceptual model in Chen’snotaCon

1. a screenshot/export of your Physical ER Model done in MySQL workbench. Ensure that all a,ributes are readable, and tables are fully expanded.

1. any assumpCons you made (limit this to a maximum of 200 words)Note: You can use an online tool like h,ps://smallpdf.com/merge-pdf to merge mul@ple PDFs together.

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.  you have an approved extension (see below), you will be penalised -10% of the total numberof 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  2 dayslate, you'd receive a 58% for theassignment.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-h1ps://canvas.lms.unimelb.edu.au/courses/210122/pages/feit-extensions-and-specialconsideraCon?module_item_id=6469145.

Use of AI toolsPlease follow the up-to-date policy of the university on the use of AI so@ware in preparing yourassessments

-h1ps://academicintegrity.unimelb.edu.au/plagiarism-and-collusion/arCficialintelligence-tools-and-technologies.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 is our recommendaCon to students that you a1empt every assignment and every quesCon in theexam.GOOD LUCK!