Consider a database with the following tables (with primary keys underlined):

Students (sid, studentName, age, city) Courses (cid, courseName, profName) Registered (sid, cid, credits)

The Students relation has 8,000 tuples stored in 400 pages. There are 40 and 200 distinct values for attributes age and city, respectively.

For the Students relation three indexed are defined: 1- a hash index on the sid attribute

2- a clustered B+ tree index using Alternative (2) on the city attribute; this tree has three levels and 500 leaf pages

3- an unclustered B+ tree index using Alternative (2) on the age; this tree has three levels and 400 leaf pages.

The Courses relation has 500 tuples stored in 50 pages.

The Registered relation has 36,000 tuples stored in 1000 pages.

(a) What is the physical query plan with the lowest estimated cost for the following query? What is the cost of your suggested plan? Discuss your suggestion. Select * From Students Where city = `Vancouver' and age = 22

(b) Assuming uniformity of attribute values and attributes independence, what is the number of tuples returned by the following query?

Select * From Students S, Courses C, Registered R Where S.sid = R.sid and C.cid = R.cid

(c) What are the one-relation, two-relation, and three-relation subplans that a System R optimizer will consider in optimizing the query presented in Question 7.b? Ignore the choice of join algorithm for this question.

(d) Assume there are 80 buffers pages available. Describe the physical query plan with the lowest estimated cost for the query presented in Question 7.b. Whats is the cost of the suggested plan?