Consider the following relational schema Sailors(sid, sname, rating, age)…
2020
Consider the following relational schema
Sailors(sid, sname, rating, age)
Reserves(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.
SELECT S.sname, S.rating
FROM Sailors S, Reserves R
WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red'
- B.
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red'
- C.
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND B.color = 'red' - D.
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE R.bid = B.bid AND B.color = 'red'
Attempted by 177 students.
Show answer & explanation
Correct answer: B
The given relational algebra expression selects boats with color = 'red', performs a natural join (⋈) across Boats, Reserves, and Sailors, and projects (π) only sname. In SQL, a natural join is achieved via cross-product in the FROM clause combined with matching primary/foreign key equality predicates (S.sid = R.sid AND R.bid = B.bid) in the WHERE clause alongside the filter condition.