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.
List the name and country of all athletes. Name,Country(ATHLETE)
List the event name and scheduled start time for all events held in the Velodrome.
List the names of athletes who competed in an event in Rio de Janeiro, Sao Paolo, or both.
List the names of all Brazilian athletes who won a gold medal.
List full details of the events that were held in venues with a capacity of over 50,000.
List the name and location of all venues, and the events that were scheduled to be held in them (if any).
List the names of Australian athletes who won a gold medal in an event held in the Aquatics Stadium.
List the name of any athlete who was placed 1 in both the Men’s 100m and 200m.
List the names of the athletes who did NOT win a medal in the Women's 1500m Freestyle.
List the name of any athletes who won a gold medal in all the events he or she competed in.
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}((\sigma_{Location = 'Rio de Janeiro'}(VENUE) \bowtie EVENT) \bowtie FINAL) \cup (\sigma_{Location = 'Sao Paolo'}(VENUE) \bowtie EVENT) \bowtie FINAL)
]
Description: This query first finds venues in Rio de Janeiro and Sao Paolo, joins them with the EVENT relation to get the events held at those venues, and then joins with the FINAL relation to get the athletes who competed in those events. Finally, it projects the AthleteName from the results.
Query:
[
\Pi_{AthleteName}((\sigma_{Medal = 'Gold'}(FINAL) \bowtie ATHLETE) \sigma_{CountryName = 'Brazil'})
]
Description: This query restricts the FINAL relation to only those records where the Medal is 'Gold', then joins with the ATHLETE relation to find the corresponding athletes, and finally restricts to those athletes from Brazil before projecting their names.
Query:
[
\Pi_{*}((\sigma_{Capacity > 50000}(VENUE) \bowtie EVENT))
]
Description: This query restricts the VENUE relation to those venues with a Capacity greater than 50,000, then joins with the EVENT relation to get the full details of the events held in those venues, projecting all attributes.
Query:
[
\Pi_{VenueName, Location, EventName}((VENUE \bowtie EVENT) \cup (VENUE \times \Pi_{EventName}(EVENT))
]
Description: This query performs a natural join between VENUE and EVENT to get venues with scheduled events, and also includes venues with no events scheduled by using a Cartesian product with an empty projection of EventName. It then projects the VenueName, Location, and EventName.
Query:
[
\Pi_{AthleteName}((\sigma_{VenueName = 'Aquatics Stadium'}(EVENT) \bowtie FINAL) \bowtie (\sigma_{CountryName = 'Australia'}(ATHLETE)) \sigma_{Medal = 'Gold'})
]
Description: This query restricts the EVENT relation to those held in the 'Aquatics Stadium', joins with the FINAL relation to find athletes who participated, and then joins with the ATHLETE relation to filter for Australian athletes who won a gold medal, projecting their names.
Query:
[
\Pi_{AthleteName}((\sigma_{EventName = 'Men’s 100m'}(FINAL) \bowtie \sigma_{Place = 1}(FINAL)) \cap (\sigma_{EventName = 'Men’s 200m'}(FINAL) \bowtie \sigma_{Place = 1}(FINAL)))
]
Description: This query finds athletes who placed 1 in both the Men’s 100m and 200m events by intersecting the results of two selections from the FINAL relation, each filtered for the respective event and place, and then projecting the AthleteName.
Query:
[
\Pi_{AthleteName}(ATHLETE) - \Pi_{AthleteName}(\sigma_{EventName = 'Women’s 1500m Freestyle'}(FINAL))
]
Description: This query first projects all athlete names from the ATHLETE relation, then subtracts the names of athletes who participated in the Women's 1500m Freestyle event from the FINAL relation, resulting in athletes who did not win a medal in that event.
Query:
[
\Pi_{AthleteName}(\sigma_{Medal = 'Gold'}(FINAL)) \div \Pi_{AthleteNo, EventName}(FINAL)
]
Description: This query first selects athletes who won a gold medal from the FINAL relation, then divides this set by the set of all athletes and events they competed in, resulting in athletes who won gold in every event they participated in. The division operator ensures that only those athletes who meet the criteria are included in the final result.