Answer the following:

(a) The equivalence



where a1 and a2 are disjoint subsets of a satisfying a = a1

U a2 holds under certain conditions on a1, a2, and c. State the conditions.



(b) Using the schema:

Sailors(sid: integer, sname: string, rating: integer, age: real)

key - sid

Boats(bid: integer, bname: string, color: string)

key - bid

Reserves(sid: integer, bid: integer, day: date)

key - sid, bid, day



(c) Consider the SELECT statement:



SELECT S.sname, S.rating

FROM Sailors S, Reserves R

WHERE S.sid = R.sid AND R.day = '5/11/98'



which uses the schema of (7b). Express it as an unoptimized relational algebra expression

using the operators natural join, select, and project.



(d) Give an equivalent relational algebra expression for the same query that can be evaluated

more eciently. Explain why you expect more ecient evaluation (no numbers).




a) The equivalence



where a1 and a2 are disjoint subsets of a satisfying a = a1 U a2 holds under certain conditions on a1, a2, and c. State the conditions.

Solution:

a1 and a2 are subsets of a corresponding to R and S and all attributes named in c are

contained in a1 and a2



(b) Using the schema:

Sailors(sid: integer, sname: string, rating: integer, age: real)

key - sid

Boats(bid: integer, bname: string, color: string)

key - bid

Reserves(sid: integer, bid: integer, day: date)

key - sid, bid, day

Solution:

For each page of Sailors make a complete pass of Reserves.

100 passes over Reserves (= 100*200 page I/O's) +

1 pass over Sailors (= 100 page I/Os) = 20,100

Alternatively, 20,200 if Sailors and Reserves are reversed



(c) Consider the SELECT statement:



SELECT S.sname, S.rating

FROM Sailors S, Reserves R

WHERE S.sid = R.sid AND R.day = '5/11/98'





which uses the schema of (7b). Express it as an unoptimized relational algebra expression

using the operators natural join, select, and project.

Solution:





(d) Give an equivalent relational algebra expression for the same query that can be evaluated

more efficiently. Explain why you expect more ecient evaluation (no numbers).

Solution:



This eliminates irrelevant rows of R before taking the join and thus reduces the number

of tuples produced by the join and its cost. Alternatively,



Further efficiency since early projection reduces row size.

Computer Science & Information Technology

You might also like to view...

When a relational expression is false, it has the value

a. 1 b. 0 c. 0, 1, or -1 d. -1 e. None of these

Computer Science & Information Technology

What is the result of giving the which utility the name of a command that resides in a directory that is not in your search path?

What will be an ideal response?

Computer Science & Information Technology

All of the following applications can be used to store data sources EXCEPT ________

A) OneNote B) Outlook C) Word D) Excel

Computer Science & Information Technology

Use the ____________________ filter category to apply lighting effects.

Fill in the blank(s) with the appropriate word(s).

Computer Science & Information Technology