You are given the following tables:
Student(StudId, Name, Addr, Status)
Transcript(Id, CrsCode, Semester, Grade)
(a) Write a SELECT statement that outputs the names of all students who took CSE305 in
the spring of 2000.
(b) A naive query execution plan for your answer to (8a) would rst execute the FROM
clause, then apply the WHERE condition to the result, and then eliminate unwanted
columns using the attributes named in the SELECT clause. Express this plan as a relational
algebra expression.
(c) We're interested in a more ecient execution plan for this query than your answer to
(8b). Such a plan uses a join operation on relations which are as small as possible (some
rows of the tables to be joined have been eliminated and the remaining rows have been
shortened). Give a relational algebra expression equivalent to your answer to (8b) that
can be evaluated more eciently. The amount of credit you get depends on how ecient
the expression is.
(a) Write a SELECT statement that outputs the names of all students who took CSE305 in
the spring of 2000.
Solution:
SELECT S.Name
FROM Student S, Transcript T
WHERE T.Id = S.StudId AND T.CrsCode = 'CSE305'
AND T.Semester = 'S2000'
(b) A naive query execution plan for your answer to (8a) would rst execute the FROM
clause, then apply the WHERE condition to the result, and then eliminate unwanted
columns using the attributes named in the SELECT clause. Express this plan as a relational
algebra expression.
Solution:
(c) We're interested in a more ecient execution plan for this query than your answer to
(8b). Such a plan uses a join operation on relations which are as small as possible (some
rows of the tables to be joined have been eliminated and the remaining rows have been
shortened). Give a relational algebra expression equivalent to your answer to (8b) that
can be evaluated more eciently. The amount of credit you get depends on how ecient
the expression is.
Solution:
Full credit for:
You might also like to view...
Match the following terms to their meanings:
I. Formulas A. Addition, subtraction, multiplication, and division II. Mathematical operations B. Change static numbers into meaningful results III. Start a formula by using the... C. Text IV. A2+A3 (without = sign) D. Formula using a constant (not recommended) V. =10+2 E. Equals sign (=)
Under what conditions would you view two documents side by side?
What will be an ideal response?
In order to connect to the Internet, a computer on a TCP/IP LAN must be configured with an IP address, subnet mask, and __________________.
Fill in the blank(s) with the appropriate word(s).
When you create a new color in the Color panel, it becomes the active fill or stroke color on the Tools panel, depending on which button is active.
Answer the following statement true (T) or false (F)