Consider a disk with block size B $=512$ bytes, a block pointer P $=6$ bytes, and a record pointer PR $=7$ bytes. A file contains $30,000$ EMPLOYEE records, with the following fields:

$$ Name $(30$ bytes$)$

$$ Ssn $(9$ bytes$)$

$$ Department$\_$code $(9$ bytes$)$

$$ Address $(40$ bytes$)$

$$ Phone $(10$ bytes$)$

$$ Birth$\_$date $(8$ bytes$)$

$$ Sex $(1$ byte$)$

$$ Job$\_$code $(4$ bytes$)$

$$ Salary $(4$ bytes, real number$)$

$$ Deletion marker $(1$ byte$)$ Tasks:$$

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

b$.$ Calculate the blocking factor bfr and the number of file blocks b$,$ assuming an unspanned organization.

c$.$ Suppose the file is ordered by the key field Ssn and you want to construct a primary index on Ssn$.$

$($i$)$ Calculate the index blocking factor bfri $($also the index fan$-$out fo$).$

$($ii$)$ Calculate the number of first$-$level index entries and the number of first$-$level index blocks.

$($iii$)$ Determine the number of levels needed for a multilevel index.

$($iv$)$ Calculate the total number of blocks required by the multilevel index.

$($v$)$ Calculate the number of block accesses needed to search for and retrieve a record using the primary index.

d$.$ Repeat part $($c$)$ for a secondary index on Ssn$,$ assuming the file is not ordered by Ssn$.$ Compare the results with the primary index.

e$.$ Construct a secondary index on Department$\_$code $($using option $3$ of Section $17\mathrm{.}1\mathrm{.}3)$ with an extra level of indirection that stores record pointers. Assume there are $1,000$ distinct values of Department$\_$code and the records are evenly distributed.

$($i$)$ Calculate the index blocking factor bfri.

$($ii$)$ Calculate the number of blocks needed by the level of indirection.

$($iii$)$ Calculate the number of first$-$level index entries and blocks.

$($iv$)$ Determine the number of levels needed for a multilevel index.

$($v$)$ Calculate the total number of blocks required by the multilevel index and level of indirection.

$($vi$)$ Estimate the number of block accesses to retrieve all records with a specific Department$\_$code.

$$

f$.$ Construct a clustering index on Department$\_$code assuming the file is ordered by this field. Calculate the blocking factor, first$-$level index blocks, levels, total blocks, and block accesses required to retrieve all records for a specific Department$\_$code.