A relation **R** contains 1,200,000 tuples, each tuple occupying 150 bytes. The disk block size is 4,096 bytes.

(a) How many disk blocks are required to store **R**?

(b) An index on attribute **A** is implemented as a B+‑tree of order 100 (each node can hold at most 99 keys). The index is dense, i.e., every tuple has a leaf entry. Assuming the index height is the number of levels from the root to a leaf, determine the height of the B+‑tree.

(c) A selection $\sigma_{A1000}(R)$ is expected to retrieve about 5 % of the tuples. Estimate the number of disk I/O operations required to evaluate the selection using the index (count one I/O for each block read). Compare this with a full table scan and state how many block reads are saved.

Question

A relation **R** contains 1,200,000 tuples, each tuple occupying 150 bytes. The disk block size is 4,096 bytes.

(a) How many disk blocks are required to store **R**?

(b) An index on attribute **A** is implemented as a B+‑tree of order 100 (each node can hold at most 99 keys). The index is dense, i.e., every tuple has a leaf entry. Assuming the index height is the number of levels from the root to a leaf, determine the height of the B+‑tree.

(c) A selection $\sigma_{A>1000}(R)$ is expected to retrieve about 5 % of the tuples. Estimate the number of disk I/O operations required to evaluate the selection using the index (count one I/O for each block read). Compare this with a full table scan and state how many block reads are saved.

Accepted Answer

Index method saves 42,219 block reads (2,226 I/Os vs 44,445 I/Os)

City Guilds Model Questions Vol1 2021 Information Technology Question 48

Answer Options

About This Question

Master the Exam!