5,184 views
A data file consisting of $1,50,000$ student-records is stored on a hard disk with block size of $4096$ bytes. The data file is sorted on the primary key $\textrm{RollNo}$. The size of a record pointer for this disk is $7$ bytes. Each student-record has a candidate key attribute called $\textrm{ANum}$ of size $12$ bytes. Suppose an index file with records consisting of two fields, $\textrm{ANum}$ value and the record pointer the corresponding student record, is built and stored on the same disk. Assume that the records of data file and index file are not split across disk blocks. The number of blocks in the index file is ________

1 mark

ANS = 698 .

Index is being built on attribute “ANum” which is Candidate Key, but Given that file is Sorted on Primary Key “Roll No”.

This indicates that The Index must a Secondary Index, (data records not being physically ordered as per the index making a dense record necessary) so “THERE SHOULD EXIST AN INDEX RECORD FOR EVERY RECORD of Original ‘Student Table’ ”.

=> Also this Line: “Assume that Records of data file and index file are not split across disc blocks”.

This indicates UNSPANNED STRATEGY.

With This Knowledge, let’s see the Data given.

→ Record Size in Index $= 12 + 7 = 19 \text{ B}$ (‘ANum’ key size + Record pointer Size), and Block Size $= 4096 \text{ B}$

→ So number of Index records in 1 Block $= \left \lfloor \frac{4096}{19} \right \rfloor= 215$ records in 1 block (Remember again, unspanned strategy).

→ So number of blocks in the Index file $= \dfrac{\text{Total Number of records}}{\text{Records per block}} = \left \lceil \dfrac{1,50,000}{215}\right \rceil = 698.$

(Recall that this is Secondary Index)

by

How are we supposed to approach this question if spanned strategy is given instead of unspanned?
I think in case of spanned strategy the answer would be simply $\frac{19*150000}{4096} = 696$
Yeah In spanned strategy we will get 696 blocks