Question

Write the following questions as queries in RELATIONAL ALGEBRA. Use only the operators discussed in class (select, project, Cartesian product, join, union, intersection, set difference and renaming).

The following database schema is given:

ATHLETE(name,age,height,weight,country) RACE(id,location,date,time-start,distance) COMPETES(aname,rid,time,position)

where ATHLETE has information about runners (their name, age, height, weight, and nationality); RACE has information about races (id, location, date when it’s held, time it starts, and distance ran); and COMPETES keeps track of which runners run on with race, the time it took them to complete the race, and their position on it (winner = ’1’, second = ’2’, third = ’3’, and so on).

(a) List the names of athletes under 30 years old from Norway that have ran a marathon (distance=26.2).

(b) List the names of athletes who have finished a race in 1st position and another race in second position.

(c) List the names of athletes who have never won a race (win=1st position).

(d) (*) List the races (id) where all the athletes were under 30 years old.

Answer 1

The relational algebra operators used are:

• Projection: The operator shows the list of the attributes required in the result. All the other attributes are eliminated from the result.
• Selection: The operator selects the tuples that specify the given conditions.
• Join (AND operator): The operator joins the conditions to give the result to be projected.

(a) Relational Algebra:

Explanation: The expression projects the name from the athlete table where the following conditions are true:

- The age is less than 30

- The country is 'Norway'

- The distance is equal to 26.2. The distance is taken from the race table

(b) Relational Algebra:

Explanation: The expression projects the name of athletes who have finished the race at 1st and 2nd position.

(c) Relational Algebra:

Explanation: The expression projects the name of the athletes who have never won a race. The data is taken from the competes table.

(d) Relational Algebra:

Explanation: The expression projects the race id from the race table, for the athletes who are under 30. The age data is taken from the athlete table.