Consider a disk with block size B = 512 bytes. A block pointer is P = 6 bytes long, and a record pointer is PR = 7 bytes long. A le has r = 30000 EMPLOYEE records of xed-length.

Each record has the following elds: NAME (30 bytes), SSN (9 bytes), DEPARTMENT CODE (9 bytes), ADDRESS (40 bytes), PHONE (9 bytes), BIRTHDATE (8 bytes), SEX (1 byte), JOB CODE (4 bytes), SALARY (4 bytes, real number). An additional byte is used as a deletion marker. Assume that unspanned organization is used to store file records.

a. Calculate the record size R in bytes. R = bytes.

b. Calculate the blocking factor bfr= and the number of le blocks b = .

c. Suppose the le is ordered by the key eld SSN and we want to construct a primary index on SSN.

Calculate:

i. The index blocking factor bfri index records per block.

ii. The number of index entries .

iii. The number of index blocks .

iv. The number of block accesses needed to search for and retrieve a record from the file given its SSN value and using the primary index is _________ .

d. Suppose the le is not ordered by the key eld SSN and we want to construct a secondary index on SSN.

Calculate:

i. The index blocking factor bfri .

ii. The number of index entries .

iii. The number of index blocks .

iv. The number of block accesses needed to search for and retrieve a record from the file given its SSN value and using the secondary index (on SSN) is .