Question

Relational Algebra (50 pts): Consider the following database schema that keeps track of Sailors, Boats and the boats reserved by sailors.

Sailors(sid, sname, rating, age) Boatsbid, bname, color) Reserves(sid, bid, date) Keys are underlined in each relation.

Specify the following queries in Relational Algebra using above database schema.

(f) find the name of sailors with the highest rating

(g) find the name and age of oldest sailors

(h) find the age of youngest sailor for each rating level

(i) find the names of sailors who reserved more red boats than green

(j find names of sailors who have reserved all the boats

Answer 1

To reduce the issues of typing let us consider:

Sailors database as "s", Boats as "b", and Reserves as "r".

f)

Question statement-

Πsname(s)- Πs2.sname(σs2.rating<s.rating[ρs2(s)*(s])

Answer-

Ans(S) <- s(S, _, _, _), ¬ bad(S).

Bad(S) <- s(S, _, R, _), s(_, _, R’, _), R<R’.

g)

Question statement-

πsname,age([πsid(s)-πs2.sid(σs2.age<s.age(ρs2(s)*s))] ⋈s)

Answer-

ans(N,A) <- s(S, N, _, A), ­­­­ ¬ bad(S).

bad(S) <- s(S, _, _, A), s(_, _, _, A’), A<A’.

h)

SELECT S.rating, MIN(S.age)

FROM SAILOR S

GROUP BY S.rating

j)

Question statement-

πsname([πsid,bid(r) / πbid(b)] ⋈ s).

Answer-

ans(N) <- s(S, N, _, _), ¬ bad(S).

bad(S) <- s(S, _, _, _), b(B, _, _), ¬ res(S, B).

res(S,B) <- r(S,B,_).