Simple Relational Algebra Query

0
votes

I have made two relations with the respectful attributes, TEAM(TeamName,TeamID) and PLAYER(TeamID, PlayerName, PlayerHeight)

I am trying to find all the team names in which ALL the players(of that team) have a height greater than 6feet.

My assumptions are that TeamName and TeamID are related, so say TeamName X has TeamID 1 and PlayerName A,B,C can have PlayerHeight 6,7,8 and TeamID 1. Those are examples!

To my understanding, what i must do is 1. Make a Relation of Players with: project-PlayerHeight, TeamID(PLAYER) 2. Somehow calculate if "for every" separate TeamID ALL the PlayerHeight > 6 return that TeamID 3. I can't figure out the last part, and i know the first two are wrong.

Please help, i am very confused and if explained to me i will learn it correctly. Thank you!

2
databasedatabase-designrelational-databasealgebrarelational-algebra
It is Relational Algebra! – sneaky_snake
Sorry bud, removed the tag – sneaky_snake
π(teamname) (TEAM |x| (PLAYER - (σ(height<6)PLAYER)) ) – nvogel
@sqlvogel Simpler:: π(teamname) (TEAM |x| σ(height>=6) PLAYER). These give names of teams with some player(s) >= 6'; but the question asks for names of teams with all players greater than 6'. You meant the subtraction to be TEAM minus teams with some player(s) less than or equal to 6'. – philipxy

2 Answers

0
votes

Group by the team and take only those having no player <= height 6

select t.teamname
from team t
join player p on p.teamid = t.teamid
group by t.teamname
having sum(case when p.playerheight <= 6 then 1 end) = 0
0
votes

Yes, you need to create a new relation between player heights and teams by joining your two existing relations. In this particular case, however, you don't really need to test that ALL players' heights for a team are greater than the threshold -- you just needs to test that the team's shortest player's height surpasses the threshold. Expressed in SQL, that might look like so:

SELECT TeamName, MIN(PLAYER.PlayerHeight) AS MinHeight
FROM TEAM
  JOIN PLAYER
    ON TEAM.TeamID = PLAYER.TeamID
GROUP BY TEAM.TeamName
HAVING MinHeight > 6