Aspire's Library

A Place for Latest Exam wise Questions, Videos, Previous Year Papers,
Study Stuff for MCA Examinations - NIMCET

Previous Year Question (PYQs)



Consider the relational schema of sailors $S$, Reserves $R$ and Boats $B$.Table 1: Sailors $S$
SidSnameRatingAge
22Dustin745.0
29Brutus133.0
31Lubber855.5
32Andy825.5
58Rusty1035.5
64Horatio735.5
71Zorba1016.0
74Horatio935.0
85Art325.5
95Bob363.5
Table 2: Reserves $R$
SidBidday
2210110/10/98
2210210/10/98
2210310/8/98
2210410/7/98
3110211/10/98
3110311/6/98
3110411/12/98
641019/5/98
641029/8/98
741039/8/98
Table 3: Boats $B$
BidBnameColor
101Interlakeblue
102Interlakered
103Clippergreen
104Marinered
Which of the following relational algebra query computes the Sid's of sailors with age over $20$ who have not reserved a red boat?





Solution

We need sailors whose age is over $20$ and who have not reserved a red boat.

First find sailors with age over $20$.

From Sailors table:

$\pi_{sid}(\sigma_{age>20}(Sailors))=\{22,29,31,32,58,64,74,85,95\}$

Now find red boats.

From Boats table, red boats are:

$Bid=102$ and $Bid=104$

Now find sailors who reserved red boats.

From Reserves table:

$Sid=22$ reserved $Bid=102$ and $Bid=104$

$Sid=31$ reserved $Bid=102$ and $Bid=104$

$Sid=64$ reserved $Bid=102$

So,

$\pi_{sid}((\sigma_{color='red'}(Boats))\bowtie Reserves\bowtie Sailors)=\{22,31,64\}$

Now subtract sailors who reserved red boats from sailors whose age is over $20$.

$\{22,29,31,32,58,64,74,85,95\}-\{22,31,64\}$

$=\{29,32,58,74,85,95\}$

So, the correct relational algebra expression must be:

$\pi_{sid}(\sigma_{age>20}(Sailors))-\pi_{sid}((\sigma_{color='red'}(Boats))\bowtie Reserves\bowtie Sailors)$


Online Test Series,
Information About Examination,
Syllabus, Notification
and More.

Click Here to
View More


Online Test Series,
Information About Examination,
Syllabus, Notification
and More.

Click Here to
View More

Ask Your Question or Put Your Review.

loading...