1

I am trying to write a relational algebra expression(without the use of division) to pick out those values of 'player' that are present in every 'team' attribute. For example, if my table looks like this:

R2:
    Team   Player
     1       x
     1       y
     2       x
     3       x
     3       y
     3       z
     4       x
     4       z

I want to return :

Player
  x

since x is the only player that is present in every single team in Team.

Permitted operations: I can use the operations: select, project, rename, joins (natural, cross, inner, outer), set minus, set intersection, aggregations such as sum, count, avg and group by

my attempt:

R1 = (already given, distinct list of Teams) [1,2,3,4]
R2 = the table as stated above
R3 = Count[Team](R1)                    # number of distinct teams
R4 = GroupBy[Player]_Count[team](R2)    # no. of teams grouped by Player
R5 = R4 join[R4.count == R3.count] R3   # only those players with teams = total number of teams
R6 = PROJ[player](R5)                   # list of players that played in each team

Does this make sense? or is there a more efficient way of doing it?

To improve my understanding, I've also gone through and explicitly written out the relations:

R1:
     Team
       1
       2
       3
       4

R3:
      Count
        4

R4:
       Player    Count
         x         4
         y         2
         z         2

R5:
        Player   Count
          x        4

R6:
         Player
           x
WeakLearner
  • 918
  • 14
  • 26
  • @philipxy im new to relational algebra, not quite sure about versions? I'm just teaching myself a little bit about it...i can only use the basic operations, select, project, rename, joins, aggregate...(the question specifically states that I should not use divide). where can I find out what version I am using – WeakLearner Mar 31 '17 at 00:48
  • 1
    @philipxy I've updated to include an attempt at the solution, as well as the operators I can work with. I'm assuming that the definition of these is universal? – WeakLearner Mar 31 '17 at 01:21
  • No, not universal. Names don't tell you what operators do to what things. Eg compare the current wikipedia article's algebra's relations & operators (with headings that are sets of attributes) to yours (whose are lists with duplicates allowed), and which is really SQL and is really a language not an algebra, as there are operands that do not represent values but expressions evaluated per tuple or group. Thus missing the point of teaching algebra, which is that algebra expressions correspond to predicate expressions. – philipxy Mar 31 '17 at 02:37
  • @philipxy I don't quite understand. Take the employee table and Dept table from the wiki page. They have columns with the same titles? I think I'm misunderstanding your last statement, could you give an explicit example ? Does this make my solution incorrect? – WeakLearner Mar 31 '17 at 03:07
  • On the wiki page, cartesian product is just natural join with no common attributes. But in algebras like yours, it produces a relation with a heading with a list of operators, first one operand's then the other's, even if they share attributes, giving duplicates in the result. Go to both sources and read the definitions carefully. Read my comments carefully. I don't know whether you are using your algebra properly because you didn't give a reference or definitions! But the calls in your answer seem consistent with an "algebra" I've seen. Only it isn't really an algebra, per my earlier comment. – philipxy Mar 31 '17 at 09:43
  • Your question says "every single team in Team", so in your first line you should generate R1 by a query using R2, not hardwire a list of values for this particular value for R2. Also, you reference attributes `count`, but there's no such attributes. Re "missing the point" see [this](http://stackoverflow.com/a/24425914/3404097). PS What is the problem with giving a reference to your algebra? You got these names and operator usages *somewhere*. – philipxy Mar 31 '17 at 09:53

0 Answers0