INFO20003 S2 2022 A1 Page 1 of 4
INFO20003 Semester 2, 2022
Assignment 1: ER Modelling
Due: 26th August; 6:00pm (AEST)
Submission: Via LMS https://canvas.lms.unimelb.edu.au/
Commonwealth Games

The Commonwealth Games are a series of sporting events held every 4 years, which host competitors from a
body of 72 member nations and territories, including Australia. The 2022 Commonwealth games have just
ended, and the organisers are looking ahead towards the 2026 Games, which are to be held here in Victoria.

Your team is going to be helping to create the technical infrastructure for recording the athletes, events, and
results of the games. As part of this, you’ll be creating a MySQL database to store this information. The
following specifications have been provided to you to assist in your design. The team also wants to store data
about future games, and possibly previous ones too so that it can all be kept in the one system and accessed
conveniently.

Note: Some simplifications have been made compared to the real games, in particular we do not carefully
consider round-based sports (e.g. basketball), do not consider qualifier data, etc. If at any point your
understanding of The Games and this specification contradict, consider this specification as authoritative.

Regions, countries, and Games
Currently, 72 member nations (countries) compete in The Games. These nations are divided into 6 fixed
regions: Africa, Americas, Caribbean, Europe, Asia, and Oceania. For each member nation we should store their
name, and a link to an image of their flag.

The Games are held every 4 years, and are located in one of the member nations. Each set of games has a ‘host
city’ within that nation (e.g. Melbourne, Australia). Each set of games has its own ‘theme song’ for which we
need to store the name (e.g. ‘This is the Moment’), as well as a music video recording (to be played at the
event).

Sports, Events, and Competitors
The Games are broken down into various sports (e.g. ‘swimming’, ‘basketball’, ‘shotput’, ‘wheel-chair race’,
etc.). Each sport consists of a set of events (e.g. ‘100m men’s breast-stroke’ and ‘50m women’s breast-stroke’
are both events that fall under the sport of ‘swimming’). Each event is for only one sport. New sports and
events may be added, or old ones removed, from future games. Events may be either ‘time-based’ or ‘score-
based’. Additionally, all events have a set of rules (which can be 10,000+ chars long) which we should store.
Events are competed in by ‘competitors’. Events can be ‘individual’ or ‘group’ based. In individual events, each
‘competitor’ is a single ‘athlete’ (e.g. ‘100m men’s sprint’). For group events, each ‘competitor’ is actually a
group of several athletes that compete together (e.g. a relay race). Every competitor in The Games competes
as a representative of one of the member nations.
INFO20003 S2 2022 A1 Page 2 of 4
Note that each country might be represented by several competitors in the same event. This is true for both
group and individual events. For example, out of 10 swimmers in the ‘100m men’s breast-stroke’ (which is an
individual event), there might be 2x Australian athletes who are competing separately and are thus separate
competitors.
For each athlete, we need to record their name (which consists of their first, middle, and last name, and their
title), and date of birth. Athletes may compete in many different games over time, and might potentially
represent more than one country over time.
As part of each event, we may store up to 1 photo per competitor that was taken during the event. These
photos will have been uploaded online, and so we only store the link. For instance, for the ‘100m men’s
breaststroke’ (an individual event), we might store a link for up to one photo that was taken during the event
of each of the individual swimmers. For a group event like the ‘1000m men’s freestyle relay’, where the
‘competitor’ is in fact 4 athletes, we still only store up to one photo per competitor/group of 4.
Scores and Medals
For each competitor, we need to record their ‘performance’ in the event. Each event can be one of the
following types depending on what performance means in that context:
1. Time based events have a final time that the competitor took to perform a task (like swimming,
running), as well as a ‘penalty time’ which may be used if the competitor was penalised during the
event (i.e. broke an event rule).
2. Score based events have a final score that the competitor earned (like diving, archery). They also have a
field for ‘disqualification’ set to ‘true’ if the competitor was disqualified for any reason. Score-based
events may also have been scored by a panel of judges (e.g. in diving), in which case we need to record
the names of all judges on the panel for that event. The same judges will judge all competitors in the
one instance of the event (e.g. the same judges assessed all of the competitors in the ‘2022 men’s
springboard diving’)
Note that we record times/scores per ‘competitor’, so for group-based events we’d record only a single ‘time’
or ‘score’ record for the group as a whole.
For all event types, we should also record the date and time that each event occurred, and the number of
spectators present. For some events the average and maximum outside temperature recorded during that
event must be recorded.
Gold, Silver, and Bronze medals are awarded based on competitors’ performances.
Venues
Each event takes place in a venue provided by the host city. For each venue, we need to store the name,
latitude + longitude, spectator capacity, and a photo of the venue.
Some venues might be ‘inside’ of other venues. For example, a swimming event might be conducted in the
‘Queen Elizabeth Swimming Pool’, but this pool is a part of a larger venue called the ‘London Aquatics Center’.
Note that some other event might just be listed as being held at the ‘London Aquatics Center’, rather than
another venue inside of it.
INFO20003 S2 2022 A1 Page 3 of 4
Business Requirements
Your database design needs to be able to meet the business' needs to answer questions such as:
1. Which series of games did competitor ‘Emma McKeon’ participate in?
2. List names of the events that belong to a sport ‘swimming’ but have never been held at The Games.
3. List the names of all events that were held in the 1974 games.
4. Find the youngest Badminton player that has ever represented Bermuda.
5. Which athlete had the lowest time for the 2022 games’ Women’s Wheelchair Race event (and thus won
the gold medal)?
6. Which venue has the greatest number of venues inside of it?
7. How many sports have had events in the past, but had no events in the 2022 games?
8. Which country has had the greatest number of competitors across all games?
9. What were the names of the judges that scored the Men’s Diving event at the 2018 games?
10. Which sport’s events had the highest average number of spectators?
11. What is the record keeping time in 100m men’s breast-stoke across all the games recorded?
12. How many competitors were disqualified or received penalty time in the 2018 games?
13. Is there a link to a photo of athlete ‘Kyle Chalmers’ competing in the 2022 games in the event ‘men’s
100m freestyle’?
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).
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.

INFO20003 S2 2022 A1 Page 4 of 4
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. 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.
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.
Late submissions
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, 25st of August. Medical certificates need to be at least two days in length.
To request an extension:
• Email the Head Tutor (Colton Carner, colton.carner@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!