Question

Consider a disk with block size = 4096 bytes. A block pointer is 6 bytes and a record pointer is 8 bytes long. A file has 100,000 records of fixed length. Each record has the following fields and byte size: Name (30), Ssn (9), Dept (9), Address (40), Phone (10), Bdate (8), Sex (1), Job (4) and Salary (4). An additional byte is used as a deletion marked.

a. Calculate the record size R in bytes
b. Calculate the blocking factor and the number of file blocks needed to store the data, assuming an unspanned organization.

Assume the file is ordered by the key field Ssn. You need to create a B+ tree primary index.
c. If each node is a the size of a disk block, how many key pointers fit into an node?
d. Calculate the total number of blocks needed to store this index.

Answer 1

Ans

1=Record size=(30+9+9+40+10+8+1+4+4+1)=116 byte

2=Blocking factor=floor(block size/record size)=floor(4096/116)=35 records per block

Number of block needed=ceiling(number of records/blocking factor)=ceiling(100000/35)=2858 blocks

If file is ordered by key field SSn

3=key pointer fit in a node(of block size)=floor(node size/(key field size+block pointer)

=floor(4096/(9+6)=273 entries/node

4=No of blocks needed for this index=ceiling(no of blocks needed for file/number of key pointer fit in an node)

=ceiling(2858/273)

=11 blocks