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. USE symbol and show answer
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}(ATHLETE \bowtie FINAL \bowtie \sigma_{Location = 'Rio de Janeiro' \lor Location = 'Sao Paolo'}(VENUE))
]
Description: This query joins the ATHLETE, FINAL, and VENUE relations. It restricts the VENUE relation to only those venues located in 'Rio de Janeiro' or 'Sao Paolo', and then projects the AthleteName of athletes who competed in those events.
Query:
[
\pi_{AthleteName}(\sigma_{Medal = 'Gold'}(FINAL) \bowtie \sigma_{CountryName = 'Brazil'}(ATHLETE))
]
Description: This query first restricts the FINAL relation to only those entries where the Medal is 'Gold'. It then joins this result with the ATHLETE relation, restricting it to Brazilian athletes, and finally projects the AthleteName.
Query:
[
EVENT \bowtie \sigma_{Capacity > 50000}(VENUE)
]
Description: This query joins the EVENT relation with the VENUE relation, restricting the VENUE to those with a Capacity greater than 50,000. The result includes all attributes from the EVENT relation for those events held in such venues.
Query:
[
\pi_{VenueName, Location, EventName}(VENUE \bowtie EVENT)
]
Description: This query performs a join between the VENUE and EVENT relations, projecting the VenueName, Location, and EventName. If a venue has no scheduled events, it will not appear in the result.
Query:
[
\pi_{AthleteName}(\sigma_{Medal = 'Gold'}(FINAL) \bowtie \sigma_{VenueName = 'Aquatics Stadium'}(EVENT) \bowtie \sigma_{CountryName = 'Australia'}(ATHLETE))
]
Description: This query restricts the FINAL relation to gold medal winners, joins it with the EVENT relation for events held in the 'Aquatics Stadium', and then joins with the ATHLETE relation for Australian athletes, projecting the AthleteName.
Query:
[
\pi_{AthleteName}(\sigma_{Place = 1}(FINAL) \bowtie \sigma_{EventName = 'Men’s 100m'}(FINAL) \cap \sigma_{EventName = 'Men’s 200m'}(FINAL))
]
Description: This query restricts the FINAL relation to athletes who placed 1 in both the 'Men’s 100m' and 'Men’s 200m' events, using intersection to find common athletes, and then projects their names.
Query:
[
\pi_{AthleteName}(ATHLETE) - \pi_{AthleteName}(\sigma_{EventName = 'Women’s 1500m Freestyle'}(FINAL))
]
Description: This query projects all athlete names from the ATHLETE relation and subtracts those who won a medal in the 'Women’s 1500m Freestyle' 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 finds athletes who won a gold medal in all events they competed in by dividing the set of athletes who won gold medals by the set of all events they participated in, projecting their names.
These queries utilize relational algebra to extract the required information from the given database schema.