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

Question

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?

Explanation / Answer

a) Let us assume, Students(sid,studentName,age,city) table has 8000 tuples ( given ). Out of 8000 values for city column, given like 200 are distinct, which means ( 8000/200 = 40 students have are from same city ) and from 8000 values of age 40 are distinct ( 8000/40 = 200 students have same age )

So, I think lowest estimated cost of given query is 40

b)here, 8000 sid's and 500 cid's are availables. Each sid is mapped to 0 or more cid's. So, for the given query, answer can be maximum of 500.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.