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 ⋈ 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

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 := ...

StackExchange Database Administrators Q#192579, answer score: 6