Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

GATE | GATE IT 2006 | Question 86

  • Difficulty Level : Hard
  • Last Updated : 23 Nov, 2021

Consider a database with three relation instances shown below. The primary keys for the Drivers and Cars relation are did and cid respectively and the records are stored in ascending order of these primary keys as given in the tables. No indexing is available in the database.

2006_84_85

Attention reader! Don’t stop learning now.  Practice GATE exam well before the actual exam with the subject-wise and overall quizzes available in GATE Test Series Course.

Learn all GATE CS concepts with Free Live Classes on our youtube channel.

2006_84_85_2
2006_84_85_3
Let n be the number of comparisons performed when the above SQL query is optimally executed. If linear search is used to locate a tuple in a relation using primary key, then n lies in the range
(A) 36 – 40
(B) 44 – 48
(C) 60 – 64
(D) 100 – 104


Answer: (B)

Explanation:  



here we have to calculate the number of comparisons performed when the above SQL query is optimally executed.

from the first inner query:

select R.did from Cars C, Reserves R
where R.cid = C.cid and C.colour = ‘red’

C.color = “Red”, comparisons=4 (Cars has four rows)

R.cid=C.cid so there are five rows extracted to this where condition.
comparisons=(2 red cars * 10 Reserves rows)=20

from the second inner query:
select R.did from Cars C, Reserves R
where R.cid = C.cid and C.colour = ‘green’

C.color = “Green”, comparisons=4 (Cars has four rows)

R.cid=C.cid so there are three rows extracted to this where condition.
comparisons=(1 green car*10 Reserves rows)=10

R.did = {22, 22, 31,31, 64} for first inner query
R.did = {22, 31, 74} for second inner query

Here unique sets are, R.did={22,31,64} and R.did={22,31,74} respectively for first and second inner queries.
so for intersection, 6 comparisons (for 22, we hit on the first try and for 31, we hit on the second try, and for 74,we hit on all three try, so comparisons=1+2+3)
Finally we have to locate the did – 22 and did 31 from the driver table and did is the primary key. As told in the question, we use linear search and for 22,
we hit on the first try and for 31 we hit on the third try. So, 1 + 3 = 4 comparisons.
so total no of comparisons= 4+20+4+10+6+4=48
therefore B is the answer.

In short: So, first get 2 red cars by scanning 4 tuples of the cars relation. Now, for each of the two ‘red’ cars, we scan all the 10 tuples of the ‘Reserves’ relation and thus we get 2*10 + 4 = 24 comparisons.Similarly for the ‘green’ car we get 4+10 = 14 comparisons.

This solution is contributed by Nitika Bansal.

Quiz of this Question

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!