You are given the following information: • The table Dog has attributes id, bree

Question

You are given the following information:

• The table Dog has attributes id, breed, name, and age; all are string fields of the same length.

• The id attribute is a candidate key.

• The relation contains 10,000 pages and each page has 100 tuples.

• The indices indicated below are in memory.

• You can assume that the size of RID pointers in the index is ignorable.

Consider the following query: SELECT D.breed, D.id FROM Dog D WHERE D.breed=‘Poodle’; Assume that only 5% of Dog tuples meet the selection condition.

(a) Suppose that a clustered B+ tree index on breed is the only index
available. Describe the possible query plans (if any) under Alternatives 1 and 2 and their
costs. Compare the two. Does using indexing help?
(b) Suppose that an unclustered B+ tree index on breed is the only index
available. Describe the possible query plans (if any) under Alternatives 1 and 2 and their
costs. Compare the two. Does using indexing help?
(c) Suppose that a clustered B+ tree index on (D.id, D.breed) is the only
index available. Describe the best query plan and its cost.

Explanation / Answer

A) when clustered B+ tree is used for the query SELECT D.breed, D.id FROM Dog D WHERE D.breed=‘Poodle’;

based on the breed given only the id is used ,then the data that is satisfying the breed is found as the clustered B+ tree has the dogs that have common features or criterias(breed)

for Alternative 1 , cost =root to the left most leaf

for alternative 2 , cost = additional cost of retrieving records,each page fetched just once

B) using unclustered B+ tree and only id is used then the tree is searched using depth first algorithm

cost (logf 0.15B)+qualifying records

C)if clustered B+tree is used with id and breed then the data is found with minimum cost

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

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