[Solved]-Q2 Index File Given Following Data File Employee Employee Name 30 Bytes Ssn 9 Bytes Dept C Q37227946
please help me in database. thank you
Q2: Index file Given the following data file for EMPLOYEE EMPLOYEE [ NAME (30 bytes), SSN (9 bytes), DEPT CODE (9 bytes), ADDRESS (40 bytes), PHONE(10 bytes), DOB (8 bytes), SEX (1 byte), JOB CODE (4 bytes), SALARY (4 bytes) ] Suppose that block size B-512 bytes, a block pointer P- 6 bytes, a record point PR-6, and the number of records r 30,000. The records are fixed length and unspanned. A lepotCinceo ,i uimeruo o,s, and the index blocking factor bfr” i.e., fan-out fo. Ri- bfr- (7 points) Suppose that the file is ordered by the key field, i.e, SSN, and construct a primary index on SSN. B-1. Calculate the number of first-level index entries Li B. B-2. Calculate the number of first-level index blocks bui (i.e, using single-level index) B-3. Calculate the number of block accesses needed to run the following query using the primary index. You can use a binary search to access the first-level index. SELECT FROM EMPLOYEE WHERE SSN “123456789”: # of blocks- C. (7 points) Suppose that the file is not ordered by the key field SSN, and construct a secondary index on SSN. Note that the secondary index is a single level. B-1. Calculate the number of first-level index entries B-2 Calculate the number of first-level index blocks (ie, using the secondary index) B-3. Calculate the number of block accesses needed to run the following query using the secondary index. You can use a binary search to access the first-level index. SELECT FROM EMPLOYEE WHERE SSN123456789″ # of blocks Show transcribed image text Q2: Index file Given the following data file for EMPLOYEE EMPLOYEE [ NAME (30 bytes), SSN (9 bytes), DEPT CODE (9 bytes), ADDRESS (40 bytes), PHONE(10 bytes), DOB (8 bytes), SEX (1 byte), JOB CODE (4 bytes), SALARY (4 bytes) ] Suppose that block size B-512 bytes, a block pointer P- 6 bytes, a record point PR-6, and the number of records r 30,000. The records are fixed length and unspanned. A lepotCinceo ,i uimeruo o,s, and the index blocking factor bfr” i.e., fan-out fo. Ri- bfr- (7 points) Suppose that the file is ordered by the key field, i.e, SSN, and construct a primary index on SSN. B-1. Calculate the number of first-level index entries Li B. B-2. Calculate the number of first-level index blocks bui (i.e, using single-level index) B-3. Calculate the number of block accesses needed to run the following query using the primary index. You can use a binary search to access the first-level index. SELECT FROM EMPLOYEE WHERE SSN “123456789”: # of blocks- C. (7 points) Suppose that the file is not ordered by the key field SSN, and construct a secondary index on SSN. Note that the secondary index is a single level. B-1. Calculate the number of first-level index entries B-2 Calculate the number of first-level index blocks (ie, using the secondary index) B-3. Calculate the number of block accesses needed to run the following query using the secondary index. You can use a binary search to access the first-level index. SELECT FROM EMPLOYEE WHERE SSN123456789″ # of blocks
Expert Answer
Answer to Q2: Index file Given the following data file for EMPLOYEE EMPLOYEE [ NAME (30 bytes), SSN (9 bytes), DEPT CODE (9 bytes)… . . .
OR
