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/queries computes/compute the names of sailors who have reserved a red boat?Q1 $\pi_{sname}((\sigma_{color='red'}(Boats))\bowtie Reserves\bowtie Sailors)$Q2 $\pi_{sname}((\pi_{sid}((\pi_{bid}(\sigma_{color='red'}(Boats)))\bowtie Reserves))\bowtie Sailors)$Q3 $\pi_{sname}((\sigma_{color='red'}(Reserves))\bowtie Boats\bowtie Sailors)$





Solution

We need the names of sailors who have reserved a red boat.

First, we must identify red boats from the Boats table.

Red boats are obtained by:

$\sigma_{color='red'}(Boats)$

After this, we need to join these red boats with Reserves table, because Reserves table tells which sailor reserved which boat.

Then we join with Sailors table to get sailor names.

So, Q1 is correct:

$\pi_{sname}((\sigma_{color='red'}(Boats))\bowtie Reserves\bowtie Sailors)$

Now check Q2.

Q2 first finds red boat IDs:

$\pi_{bid}(\sigma_{color='red'}(Boats))$

Then it joins with Reserves to find sailors who reserved those red boats.

Then it projects $sid$ and joins with Sailors to get sailor names.

So, Q2 is also correct.

Now check Q3.

Q3 applies condition $color='red'$ on Reserves.

But Reserves table does not contain the attribute $color$.

The $color$ attribute belongs to Boats table.

So, Q3 is incorrect.

Therefore, both Q1 and Q2 compute the required result.


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