COMP 1406A Assignment 3: Introduction to GUI’s

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.

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.

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’?