Question 1: Relational algebra (20 marks) 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 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, Location, Capacity) FINAL (AthleteNo, EventName, Place, Medal)
Provide Relational Algebra (NOT SQL) queries, with description as necessary to find the following information. Each question is worth 2 marks.
NOTE:
• You can use the symbols , , etc or the words ‘PROJECT’, ‘RESTRICT’ etc as you prefer.
• You do not need to try to make efficient queries – just correct ones.
• Where you use a join, always show the join condition.
• Provide a description to explain how the query will run.
Here are the relational algebra queries for the specified tasks, along with descriptions of how each query operates:
Query:
[
\Pi_{AthleteName, CountryName} (ATHLETE)
]
Description: This query projects the AthleteName and CountryName attributes from the ATHLETE relation, resulting in a list of all athletes along with their respective countries.
Query:
[
\Pi_{EventName, ScheduledStart} (\sigma_{VenueName = 'Velodrome'} (EVENT))
]
Description: This query restricts the EVENT relation to only those events where the VenueName is 'Velodrome' and then projects the EventName and ScheduledStart attributes.
Query:
[
\Pi_{AthleteName} (FINAL \bowtie_{FINAL.EventName = EVENT.EventName} \sigma_{Location = 'Rio de Janeiro' \lor Location = 'Sao Paolo'} (VENUE) \bowtie_{VENUE.VenueName = EVENT.VenueName} EVENT \bowtie_{FINAL.EventName = EVENT.EventName} ATHLETE)
]
Description: This query first restricts the VENUE relation to venues located in either 'Rio de Janeiro' or 'Sao Paolo'. It then joins this result with the EVENT relation to find the corresponding events. Finally, it joins with the FINAL relation to get the athletes who competed in those events and projects their names.
Query:
[
\Pi_{AthleteName} (\sigma_{Medal = 'Gold'} (FINAL) \bowtie_{FINAL.AthleteNo = ATHLETE.AthleteNo} \sigma_{CountryName = 'Brazil'} (ATHLETE))
]
Description: This query restricts the FINAL relation to only those records where the Medal is 'Gold'. It then joins this with the ATHLETE relation, restricting to athletes from Brazil, and projects their names.
Query:
[
\Pi_{*} (EVENT \bowtie_{EVENT.VenueName = VENUE.VenueName} \sigma_{Capacity > 50000} (VENUE))
]
Description: This query restricts the VENUE relation to venues with a capacity greater than 50,000. It then joins this result with the EVENT relation and projects all attributes of the events held in those venues.
Query:
[
\Pi_{VenueName, Location, EventName} (VENUE \bowtie_{VENUE.VenueName = EVENT.VenueName} EVENT) \cup \Pi_{VenueName, Location, NULL} (VENUE)
]
Description: This query first performs a join between VENUE and EVENT to get venues with scheduled events, projecting the venue name, location, and event name. It then uses a union with a projection of venues that have no scheduled events (using NULL for the event name).
Query:
[
\Pi_{AthleteName} (\sigma_{Medal = 'Gold'} (FINAL) \bowtie_{FINAL.EventName = EVENT.EventName} \sigma_{VenueName = 'Aquatics Stadium'} (EVENT) \bowtie_{FINAL.AthleteNo = ATHLETE.AthleteNo} \sigma_{CountryName = 'Australia'} (ATHLETE))
]
Description: This query restricts the FINAL relation to gold medal winners, then joins with the EVENT relation restricted to events held in the 'Aquatics Stadium'. It further joins with the ATHLETE relation to filter for Australian athletes and projects their names.
Query:
[
\Pi_{AthleteName} ((\sigma_{EventName = 'Men’s 100m' \land Place = 1} (FINAL) \bowtie_{FINAL.AthleteNo = ATHLETE.AthleteNo} ATHLETE) \cap (\sigma_{EventName = 'Men’s 200m' \land Place = 1} (FINAL) \bowtie_{FINAL.AthleteNo = ATHLETE.AthleteNo} ATHLETE))
]
Description: This query finds athletes who placed 1 in both the 'Men’s 100m' and 'Men’s 200m' events by performing two separate selections on the FINAL relation, joining with ATHLETE to get their names, and then taking the intersection of the two results.
Query:
[
\Pi_{AthleteName} (ATHLETE) - \Pi_{AthleteName} (\sigma_{EventName = 'Women’s 1500m Freestyle'} (FINAL) \bowtie_{FINAL.AthleteNo = ATHLETE.AthleteNo} ATHLETE)
]
Description: This query first projects all athlete names from the ATHLETE relation. It then finds the names of athletes who participated in the 'Women’s 1500m Freestyle' event and won a medal, and subtracts this set from the total set of athlete names.
Query: [ \Pi_{AthleteName} (ATHLETE) \cap \Pi_{AthleteName} (\sigma_{Medal = 'Gold'} (FINAL) \bowtie_{FINAL.AthleteNo = ATHLETE.AthleteNo} (FINAL)) ] Description: This query identifies athletes who won a gold medal in every event they competed in by comparing the set of all athletes with the set of athletes who won gold medals in all their events. The intersection of these two sets gives the desired result.
These queries utilize relational algebra to extract the required information from the database schema provided.