Question

Assume a relation is structured with a sequential file organization and has fixed length records. Also assume that after processing, the relation is fully "sequentially ordered" and that no subsequent changes have been made. Assume a disk block has 10240 bytes, a record is 1000 bytes, and an index is 16 bytes. The indices are "sparse" and at the bottom level, point to the first record of the pertinent disk block. How many disk blocks are needed to account for the records and indices? Show all work and note any assumptions.

Answer 1

Let us assume that there are a total of N records.

Firstly, number of records that can be stored in a block

= floor ( (Size of 1 block in bytes) / (Size of 1 record in bytes) )

= floor ( 10240 / 1000 )

= 10

Thus, number of blocks required to store all N records

= ceiling ( (Number of records) / (Number of records per block) )

= ceiling ( N / 10 )

$= \color{green}\left \lceil \frac{N}{10} \right \rceil$

Secondly, number of index-entries that can be stored in a block

= floor ( (Size of 1 block in bytes) / (Size of 1 index-entry in bytes) )

= floor ( 10240 / 16 )

= 640

Now, in a sparse index, there is one index-entry for each data-block.

Thus, number of blocks required to store all index-entries

= ceiling ( (Number of index-entries) / (Number of index-entries per block) )

= ceiling ( (Number of data-blocks) / (Number of index-entries per block) )

$=\color{green}\left \lceil \frac{\left \lceil \frac{N}{10} \right \rceil}{640} \right \rceil$

In summary, for a total of N records,

• Number of data-blocks required
  • $= \color{green}\left \lceil \frac{N}{10} \right \rceil$

• Number of index-blocks required
  • $=\color{green}\left \lceil \frac{\left \lceil \frac{N}{10} \right \rceil}{640} \right \rceil$

• Total number of blocks required
  • $=\color{green}\left \lceil \frac{N}{10} \right \rceil + \left \lceil \frac{\left \lceil \frac{N}{10} \right \rceil}{640} \right \rceil$