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.
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
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?
All of the following applications can be used to store data sources EXCEPT ________
A) OneNote B) Outlook C) Word D) Excel
Use the ____________________ filter category to apply lighting effects.
Fill in the blank(s) with the appropriate word(s).