Division Operation on Relational Algebra

Question

I have this schema:

CLUB(Name, Address, City)
TEAM(TeamName, club)
PLAYER(Badge, teamName)
MATCH(matchNumber, player1, player2, club, winner)

• Club in TEAM references Name in CLUB;
• teamName in PLAYER references TeamName in TEAM;
• Player 1, player 2, winner in MATCH reference Badge in PLAYER;
• Club in MATCH references Name in CLUB;

A player can be part of only one team. Matches can be played between teammates. A team can have only one club.

And I need to do this algebra relation:

Extract the players who have always won and the related team.

I know that this can be done by using the division operation structured as well:

r(A, B) / s(B)

The fact is that I don't know how to set up it. In the first relation I have an attribute A which is the one that I wanna extract (I guess), so it should be "TeamName". The secondo one (B) should be one attribute which is common with s(B) (where s(B) is a subset of r(A, B)). In this case can it be the attribute "Winner"?

Answer 1

OK let's assemble some building blocks.

Note on notation: I'm using the greek letters for relational operators; angle brackets around attribute names for them (usually shown suffixed); round brackets for the relation parameter.

• Players that have played & won at least one match:

  PlayednWon := ρ⟨Badge := winner⟩(π⟨winner⟩(MATCH))
  

• Players that have played & lost at least one match:

  PlayednLost := ρ⟨Badge := player1⟩(π⟨player1⟩(σ⟨winner ≠ player1⟩(MATCH)))
                 ∪
                 ρ⟨Badge := player2⟩(π⟨player2⟩(σ⟨winner ≠ player2⟩(MATCH)))
  

• Players that have played & won every match they played. (i.e. have never played & lost)

  PlayednWonAll := PlayednWon - PlayednLost
  

• Players that have never lost (possibly because they've never played at all)

  NeverLost := π⟨Badge⟩(PLAYER) - PlayednLost
  

Didn't need no relational division so far. Neither can I see it would make any of these more succinct. I'll add some more code later.

Are you reproducing the exam question faithfully? For example, are you told that MATCH includes at least one match between each possible pairing of players across the whole league? What has the course told you about the purposes for using division?

Does the exam question give extra marks for creative thinking around plausible scenarios? Is the lecturer some sort of sadist? Does the lecturer understand RA, or is this some sort of token coverage of the topic before getting on to the meat of SQL?

I could make a wild guess at why you're told "matches can be played between teammates."

• Suppose the query were looking for players that have "always won" against everybody from a team. That's a good use for division.
• Then there would be a point in reporting "related team": a given player might have "always won" against several different teams; and we'd want to report each such team -- not necessarily the player's 'home' team.
• But it might include 'home' team (because "matches can be played between teammates"). Then there's a gotcha: no player can play themselves (presumably); then they need to have played and "always one" against everybody in their 'home' team except themselves.

Would marks be given in the exam for exploring all those possibilities?

Addit: Let's use division to find a subtle variant.

• Another building block: pair up the winner and loser from each match (this is a variation on PlayednLost):

  WinnernLoser := ρ⟨Badge := player1⟩(π⟨winner, player1⟩(σ⟨winner ≠ player1⟩(MATCH)))
                  ∪
                  ρ⟨Badge := player2⟩(π⟨winner, player2⟩(σ⟨winner ≠ player2⟩(MATCH)))
  

• Now those who've been on the winning side against everyone that's ever lost:

  PlayednBeatAll := WinnernLoser ÷ PlaydnLost
  // equivalently:  WinnernLoser ÷ π⟨Badge⟩(WinnernLoser)
  

  • Quick: what attribute(s) are in the result? Why?
  • Then how do we get the "related team" per the original question?
  • This is a subset (not nec. proper) of PlayednWonAll. Why?

Notice that with relation PlayednWonAll or PlayednBeatAll, there might be many players in the result: Anna has won every match she played; so has Barbara. How come? Because Anna has never played Barbara; there's no overall champion. In the case of PlayednBeatAll, it means Anna and Barbara have played everybody except each other.

What's the difference between PlayednWonAll as compared with PlayednBeatAll? Carla has played only one match, against Donna, which Carla won. Anna and Barabara played and won against not only Donna but also Emilia (neither played Carla). So Anna & Barbara appear in both results, Carla only in PlayednWonAll.

• Here's a useless divide:

  ThinkAboutIt := WinnernLoser ÷ π⟨Badge⟩(PLAYER)
  

  • PLAYER possibly includes players who've never played. Then nobody can have beaten them. Even if every PLAYER has played:
  • PLAYER includes everybody who's "always won"; they can't have lost to themselves.
  • IOW ThinkAboutIt is always empty.

Gotcha's like this are why I think teaching relational divide is pointless. There's actually several variants of divide, each trying to cope differently with corner cases like empty relations or divisors with attributes not a subset of the dividend. (Or semantics like "always won" vs "never lost".) And lecturers/textbooks rarely get into the subtleties.

Furthermore divide is not available in SQL, so learning about it is of marginal benefit. Division can always be obtained by other operators, and usually those give a just as succinct expression, and an expression that's easier to understand. (I mean other RA operators: in SQL simulating divide needs ghastly code, as usual.) QED.