A database records information about athletes competing at the Olympics. An athlete competes for a particular country in one or more events. Events take place at a scheduled day and time in a particular venue. The result (rank) is recorded for all athletes in the final of the event. The medal (gold, silver or bronze) is also recorded for the medal winners in the event.
Note that we are not considering team sports or heats in this example - only individuals competing in the finals.
The schema for this database is as follows: (note that primary keys are shown underlined, foreign keys in bold).
ATHLETE (AthleteNo, AthleteName, CountryName)
COUNTRY (CountryName, NumberOfCompetitors)
EVENT (EventName, ScheduledStart, VenueName)
VENUE (VenueName, City, Capacity)
FINAL (AthleteNo, EventName, Rank, Medal)
Provide relational algebra (NOT SQL) queries to find the following information. Each question is worth 2 marks.
a. List the name and country of all athletes.
b. List the event name and scheduled start time for all events held in the Aquatics Stadium.
c. List the names of athletes who competed in an event in Rio de Janeiro, Brasilia, or both.
d. List the names of all Brazilian athletes who won a gold medal.
e. List full details of the events that were held in venues with a capacity of over 60,000.
f. List the name and city of all venues, and the events that were scheduled to be held in them (if any)
g. List the names of Australian athletes who won a gold medal in an event held in the Velodrome.
h. List the name of any athlete who was ranked 1 in both the Men's 200m and 400m.