Do my relational algebra operations produce the desired results?

Question

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 ⋈ Services
R2 := σ_color='Red'(R1)
R3 := σ_{color='Green'}(R1)
R4 := R2 ∩ R3
R5 := R4 ⋈ Sailors
R6 := π_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 ∩ R3

score 6 · Accepted Answer · edited Dec 08 '17 at 15:20

This is indeed the error:

    R4 := R2 ∩ 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' ∩ R3'
    R5  := ...
    R6  := ...

Do my relational algebra operations produce the desired results?

1 Answers1