Answered step by step
Verified Expert Solution
Question
00
1 Approved Answer
3. Consider a disk with block size B 1024 bytes. A block pointer is P 6 bytes long. A file has r 60,000 EMPLOYEE records
3. Consider a disk with block size B 1024 bytes. A block pointer is P 6 bytes long. A file has r 60,000 EMPLOYEE records of fixed length. Each record has the following fields: Name(30 bytes), Ssn(9 bytes), Department code(9 bytes), Address(40 bytes), Phone(10 bytes), Birth data(B bytes), Sex(1 byte), Job_code(4 bytes), and Salary (4 bytes, real number) (a) (1%) Calculate the record size R in bytes. (b) (2%) Calculate the blocking factor bfr and the numberof disk blocks , assuming an unspanned organization. (c) (5%) Suppose that the file is ordered by the key field Ssn a nd we want to construct a primary index on Ssn. Calculate () the index blocking factor bfr (which is also the index fan-out fo); (Gi) the numbet of first-level index entries and the number of first-level index blocks: (ii) the number of levels needed if we make it into a multilevel index; (iv) the total number of blocks required by the multilevel index; and (v) the number of block accesses needed to search for and retrieve a record from the file- given its Ssn value-using the primary index. (d) (5%) Suppose that the file is not ordered by the key field Ssn and we want to construct a secondary index on Ssn. Repeat the previous question (part c) for the secondary index. (e) (2%) Specify the SQL for creating a primary index for the EMPLOYEE table on the indexing field Ssn. (f) (2%) Specify the SQL for creating a cluster index for the EMPLOYEE table on the indexing field Name
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started