-Relations R1(a, b, c) and R2(c, d, e) have the following properties: -All field
ID: 3696528 • Letter: #
Question
-Relations R1(a, b, c) and R2(c, d, e) have the following properties:
-All fields store positive integer data.
-Field a is the primary key for R1; field c is the primary key for R2; R1.c is a foreign key to R2.c.
-R1 has 10,000 records, with 200 records per block. There is a primary B+-tree index on a with height ha = 3.
-R2 has 45,000 records, with 2,250 records per block. There is a primary B+-tree index on c with height hc = 3 and a secondary B+-tree index on d with height hd = 2.
Estimate the number of block reads required for each of the following operations. Explain your answers. Do not assume you have enough memory space to store an entire file in main memory.
-SELECT * FROM R1 WHERE b=1545
-SELECT * FROM R2 WHERE c=2883
-SELECT * FROM R2 WHERE d=521 assuming there are 10 records with d = 521
-SELECT * FROM R1 JOIN R2 ON R1.c = R2.c using indexed nested-loop join
-SELECT * FROM R1 JOIN R2 ON R1.c = R2.c using block nested-loop join
Explanation / Answer
Firstly we will consider given data in question:
(i) select * from R1 where b = 1545
Since b is not a primary key, we have to search for all values to get match with value 1545.
So searching will be done in sequential manner. So it requires one block.
---------------------------------------------------------------------------------------------------
(ii)
select * from R2 where c= 2883
Since c is primary key and unique.. we will get only one record. So it takes 1 block.
-------------------------------------------------------------------------------------------
(iii)
select * from R2 where d=521 assuming there are 10 records with d = 521
As each record it takes 2,250 records. For 10 records, it takes 1 block.
----------------------------------------------------------------------------------------------
(iv)
select * from R1 join R2 on R1.c = R2.c using indexed nested loop join
As we are fetching c from R2, this means it was primary and unique it will take 1 block.
------------------------------------------------------------------------------------------------
(v)
select * from R1 join R2 on R1.c = R2.c using block nested join
Using blocks...So In R2 it may be unique, but in R1 it may contain many rows. So may takes
k blocks, it depends upon how many rows it matches.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.