Search code examples
relational-databaserelational-algebra

Query in relational algebra

I have 2 tables and I need to write a query in relational algebra that will select all names of teams, who are not working with any client. I have these relations

team( id, name )


client( id, name, teamId ) 
    teamId ⊆ team.id

Tables looks like this

tables

Could you please help me what would be the query in relational algebra? I was thinking about joining these 2 tables and selecting rows there team has Client.teamId as NULL, but I don't know how to formally write it.

Solution

  • Here are the steps that must be done:

    1. Join Team and Client on Team.id = Client.TeamId and project this relation on Team.id and Team.name. You obtain the id and name of all the teams that work for some client.

    2. Subtract from the relation Team the relation obtained in the previous step: in this way you get all the teams that do not work for some client.

    3. Project the relation obtained at the previous step on Team.name. In this way you obtain the name of the teams that do not work for some client.

    The notations for relational algebra are different, here an expression with a typical notation:

    πname (team - πid,name(team ⨝id=teamId client))