4

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:

enter image description here

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 
6

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.