Question Consider Disk Block Size 2048 Bytes Block Pointer P 5 Bytes Long Record Pointer Q34834419

Question . Consider a disk with block size =2048 bytes. A block pointer is P = 5 bytes long, and arecord pointer is PR = 6 bytes long. Afile has r = 50,000 EMPLOYEE records of fixed length. Eachrecord has the following fields: Ssn, 11 bytes; Last_name, 18bytes; First_name, 18 bytes; Middle_init, 1 byte; Birth_date, 8bytes; Address, 35 bytes; Phone, 12 bytes; Supervisor_ssn, 11bytes; Department_code, 5 bytes; Job_code, 5 bytes; deletionmarker, 1 byte.

Qa) Suppose that the file isordered by the key field Ssn and we want toconstruct a primary index on Ssn. Calculate (i) the indexblocking factor (which is also the index fan-out ); (ii) the numberof first-level index entries and the number of first-level indexblocks; (iii) the number of levels needed if we make it into amultilevel index; (iv) the total number of blocks required by themultilevel index; and (v) the number of block accesses needed tosearch for and retrieve a record from the file — given its Ssnvalue — using the primary index

Q.b) . Suppose that the file is not ordered bythe nonkey field Department_code and we want to construct asecondary index on Department_code, using Option 3 introduced inSection 17.1.3 of the textbook, which is to keep the index entriesthemselves at a fixed length and have a single entry for each indexfield value, but to create an extra level of indirection to handlethe multiple pointers. Assume there are 5,000 distinct values ofDepartment_code and that the EMPLOYEE records are evenlydistributed among these values. Calculate (i) the index blockingfactor (which is also the index fan-out ); (ii) the number ofblocks needed by the level of indirection that stores recordpointers; (iii) the number of first-level index entries and thenumber of first-level index blocks; (iv) the number of levelsneeded if we make it into a multilevel index; (v) the total numberof blocks required by the multilevel index and the blocks used inthe extra level of indirection; and (vi) the approximate number ofblock accesses needed to search for and retrieve all records in thefile that have a specific Department_code value, using theindex.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *