Consider the following relation schema Sailors( sid, sname, rati
Consider the following relation schema
Sailors(sid, sname, rating, age)
Reserve(sid, bid, day)
Boats(bid, bname, color)
What is the equivalent of following relational algebra query in SQL query.
πsname((σcolor= ‘red’Boats) ⨝ Reserves ⨝ Sailors)
A. <p>SELECT S.sname, S.rating</p> <p>FROM Sailors S, Reserves R</p> WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘red’
B. <p>SELECT S.sname</p> <p>FROM Sailors S, Reserves R, Boats B</p> <p>WHERE S.bid = B.bid AND B.color = ‘red’</p>
C. <p>SELECT S.sname</p> <p>FROM Sailors S, Reserves R, Boats B</p> <p>WHERE S.sid = R.sid AND B.color = ‘red’</p>
D. <p>SELECT S.sname</p> <p>FROM Sailors S, Reserves R, Boats B</p> WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘red’
Please scroll down to see the correct answer and solution guide.
Right Answer is: D
SOLUTION
Sailors:
|
Sid |
Sname |
Rating |
Age |
|
S1 |
SA |
1 |
30 |
|
S2 |
SB |
2 |
40 |
|
S3 |
SC |
3 |
50 |
|
S4 |
SD |
3 |
20 |
|
S5 |
SA |
4 |
30 |
Reserves:
|
Sid |
Bid |
Day |
|
S1 |
B1 |
Tue |
|
S2 |
B2 |
Wed |
|
S3 |
B3 |
Thu |
|
S4 |
B4 |
Fri |
|
S5 |
B5 |
Mon |
Boats:
|
Bid |
Bname |
Color |
|
B1 |
BA |
Red |
|
B2 |
BB |
Blue |
|
B3 |
BC |
Red |
|
B4 |
BD |
Green |
|
B5 |
BA |
Red |
Given query: πsname((σcolor= ‘red’Boats) ⨝ Reserves ⨝ Sailors)
This query first find the boats having red color than matches B.Bid with R.Sid where it finds equal than matches R.Sid with S.Sid , at that point finds the name of sailors with same sid.
Here it results in sailor SA, SC, and SD.
This query matches with:
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘red’