Question

1- Consider the following SQL query and related relations         

SELECT P.Name

FROM Players P, Games G

WHERE P.Id = G.PlayerId

AND P.Game = 'Football' AND G.Season ='2019'

where:

Players (Id, Name, Game)

Games (PlayerId, GameCode, Season)

A- Translate the above SQL query to a relational algebra expression.

B- Draw the Query tree for above relational algebra expression in part (a).

Note: Please no screenshot

Answer 1

SELECT P.Name

FROM Players P, Games G

WHERE P.Id = G.PlayerId

AND P.Game = 'Football' AND G.Season ='2019'

Let us see first symbol which will be used in translating the query in relational algebra:

1. As Select P.name is to extract a particular column from the table, Projection(π) symbol will be used.

π_P.name

2. Select σ symbol is used to extract tuples from table according to where clause condition

σ _Id(Player)= _PlayerId(Games)

So the relational algebra expression for the following part of Sql Query :

SELECT P.Name

FROM Players P, Games G

WHERE P.Id = G.PlayerId

is

π_P.name (σ _Id(Player)= _PlayerId(Games))

3. Now we create relational algebra expression using AND for the full query as

π _Player.name (σ _Id(Player)= _PlayerId(Games) and σ_{Game=’FootBall’}(Player) and σ_season=2019(Games))( Player X Games)

Where Player X Games shows Cartesian product to merge two relations Player and Games.

Query Tree