Number of records fit into a disk block and number of disk blocks required in DBMS

Find the number of disk blocks to store certain number of records, find the number of records that fit into one block in DBMS,  blocking factor, number of records without spanning, number of records with spanning

Exercise:

Suppose we have blocks of size 2048 bytes that we use to store fixed-length records. Each block has a 32 byte header used to store information including the number of records in the block.

(a) Suppose we have records consisting of a 20 byte header, and 3 columns of size 15 bytes, 6 bytes and 12 bytes respectively. Within each record, fields can start at any byte. How many records can fit in a block?

(b) Suppose that we have records each with a 20 byte header and 500 bytes of data. How many blocks will we need to store 4 records if no spanning is allowed?

 

Solution:

As each block has a 32 byte header, the available space for storing records is,

Available space = Block size – Header size = 2048 – 32 = 2016 bytes

(a) The space required to store each record (fixed length) is the sum of the record’s header and the column sizes.

Record size = Header size + column size = 20 + 15 + 6 + 12 = 53 bytes.

The number of records that can fit in each block is,

Blocking factor = Block size / record size = 2016 / 53 = 38 records

 

Blocking factor is a measure to count the number of records that can fit into a disk block. The block size is divided by the record size in case of fixed length records, to calculate the blocking factor. The average of record size may be used if the records are variable length.

 

(b) If no spanning is allowed, we cannot store partial records into disk blocks. We can only store full length record in any disk block. If a record doesn’t fit entirely into the remaining space of a disk block, we need to move that record into the next block.

Available block space = 2016 bytes

Record size = 20 + 500 = 520 bytes per record.

We like to store 4 such records, hence 520 * 4 = 2080 bytes.

As spanning is not permitted, only 3 records of 520 byte each can fit into a single block. The fourth record has to be stored in the next block. Hence, we need 2 blocks to store 4 records.

 

********************

Related posts:

• Go to Multiple Choice Questions in DBMS home

• Go to Normalization - MCQs page

• Go to General Mixed Quiz in all topics of DBMS page

• Go to Advanced DBMS Concepts page