Database course

3. (Storage and Indexing) Consider a disk with block size B 512 bytes, where a block pointer is 8 bytes long. A file has r= 90,000 EMPLOYEE records of fixed length. Each record has the following fields Name (20 bytes), SSN (9 bytes), Department (3 bytes), Address (20 bytes), Phone (10 bytes), BirthDate (4 bytes), Sex ( byte), JobCode (4 bytes), and Salary (4 bytes). The records are stored in a file using an unspanned organization a. Calculate (i) the record size R in bytes,ii) the blocking factor bfr, and (iii) the number of file blocks b b. Suppose that the file is ordered by the key field SSN and we decide to construct a primary index on SSN. Calculate(i) the number of records in the index file,i) the blocking factor of the index file,ii) the number of blocks in the index file, and (iv) the number of block accesses needed to search for and retrieve a record from the data file given its SSN using the primary index Also, suppose we were to extend the primary index to construct a multilevel index. Calculate (v) the total number of index levels in the multilevel index and (vi) the total number of blocks in the entire multilevel index c. Suppose that the file is not ordered by the key field SSN (or by any other field) and we decide to construct a secondary index on SSN. Calculate (i) the number of records in the index file, (ii) the blocking factor of the index file, (iii) the number of blocks in the index file, and (iv) the number of block accesses needed to search for and retrieve a record from the data file given its SSN using the secondary index. Also, suppose we were to extend the secondary index to construct a multilevel index. Calculate (v) the total number of index levels in the multilevel index and (vi) the total number of blocks in the entire multilevel index