0

I am having trouble forming a relational algebra query for a question in an assignment. I have to find the name of all the teams that won a game on a specific date.

The database now has the following three schemas:

Team(teamid,teamname,stadium)
Player(playerid,name,teamid, height)
Game (gameid, hometeamid, guestteamid, date, home-score, guest-score)

I am a little confused on how to do this since the tables that I seem to need do not have anything in common (Game and Team). I see that Game has an id for both the home and away teams, but how can you find out who which team won?

The exact question that I have to answer is: Find the name of all the teams that won on 6/1/15. (Assume a team plays only one game a day and that a tie is not possible)

LoneWolf
  • 35
  • 8
  • All tables have at least one team ID, so there's something in common. And the scores for home and guest teams ought to give you a clue as to who won. – rd_nielsen Jun 22 '17 at 17:56
  • @rd_nielsen right I see that they all have some form of team ID, but how do you form the statement that tells you if you should be using the home team or the guest team's ID. Sorry, I am kind of new at this. – LoneWolf Jun 22 '17 at 17:59
  • There are many versions of "relational algebra". Please give a reference/link to yours. PS [Forming a relational algebra query from an English description](https://stackoverflow.com/a/43318188/3404097) – philipxy Jun 22 '17 at 22:37

1 Answers1

1

Try This

(select teamname from Team t, Game g 
      where t.teamid = g.hometeamid 
          and home-score > guest-score and date = '6/1/15')
 UNION 
(select teamname from Team t, Game g 
      where t.teamid = g.guestteamid 
          and guest-score > home-score and date = '6/1/15')

The first query represents games which home teams have won while the second query represents games which guest teams have won. The union of the two will be the required answer

Daniel Isaac
  • 745
  • 5
  • 15