Do my relational algebra operations produce the desired results? I have these tables:


        Sailors          

sid  sname   rating  age
---  ------  ------  ---
22   dustin  7       45 
31   john    8       55 
58   ben     10      35 



         Boats        

bid  bname      color
---  ---------  -----
101  interlake  blue 
102  interlake  red  
103  clipper    green
104  clipper    red  



       Reserves

sid  bid  date
---  ---  ----------
22   104  8/10/2014
22   103  7/05/2014
58   103  8/11/2014
31   102  8/11/2014


I am trying to find sailors, sname and rating, who have reserved both a red and a green boat, and I need to write that in relational algebra notation.

I tried this:



Text version, for copying/referencing purposes:


R1 := Boats &#x22C8; Services
R2 := &#x03C3;color='Red'(R1)
R3 := &#x03C3;color='Green'(R1)
R4 := R2 &#x2229; R3
R5 := R4 &#x22C8; Sailors
R6 := &#x03C0;sname,rating(R5)


I was told it was wrong, but not given an explanation as to why. Can someone explain to me what the problem is?

This line was marked as wrong:  

 R4 := R2 &#x2229; R3
This is indeed the error:


    R4 := R2 &#x2229; R3 


If we try to get the intermediate results of your solution, we get: 

R1:


bid  bname      color       sid  date
---  ---------  -----       ---  ----------
102  interlake  red         31   8/11/2014
103  clipper    green       22   7/05/2014
103  clipper    green       58   8/11/2014
104  clipper    red         22   8/10/2014


and then:

R2: (only the 'red')


bid  bname      color       sid  date
---  ---------  -----       ---  ----------
102  interlake  red         31   8/11/2014
104  clipper    red         22   8/10/2014


R3: (only the 'green')


bid  bname      color       sid  date
---  ---------  -----       ---  ----------
103  clipper    green       22   7/05/2014
103  clipper    green       58   8/11/2014


R4 will be an empty relation because nothing can be both red and green. Of course that is not what the exercise asks for.

R4:


bid  bname      color       sid  date
---  ---------  -----       ---  ----------




What you need to do is to get R2' and R3', the projections of R2 and R3 respectively, with only the attribute sid. Then take their intersection for R4:


    R1  := ...
    R2  := ...
    R3  := ...
    R2' := πsid(R2)
    R3' := πsid(R3)
    R4  := R2' &#x2229; R3'
    R5  := ...
    R6  := ...