This question is related to Database Management please,don\'t answer with handwr

Question

This question is related to Database Management

please,don't answer with handwriting .

Thank

Year 2020, You are hired as a database developer in a new established university. Yoiu are required to computerize the existing students information system. The details are as follows: . Currently enrolled students "r" : 25,000 . Block size "B" of disk to store data is 512 bytes. .Record pointer "P" size is 7 bytes . Each record is of fixed length with following attributes. Attributes Size (in bvtes 30 30 ex 10 Phone Address 30 Your task is to a) Calculate number of file blocks "b" to store data of all the students. b) Calculate umber of index blocks bi , if the file is ordered by StudentId and a primary index is constructed on same field. c) show how binary search algorithm with primary indexing would be a better choice than binary search algorithm without primary indexing, (by compare the average cost in terms of block accesses) [ Note: You may need to calculate some intermediate values to get final results ]

Explanation / Answer

Number of records in the file "r" = 25000
Block Size "B" = 512 bytes
Size of Record Pointer = 7 bytes
Record size "R" = 9+30+30+1+10+9+30+8+1 = 128 bytes
Blocking factor bfr = floor (B/R) = floor(512/128) = 4 records per block

a) Number of file blocks "b" = ceiling(r/bfr) = ceiling(25000/4) = 6250

b) Index record size R1 = Size of StudentID + size of Record Poitner = 9 +7 = 16 bytes
Index Blocking Factor bfri= floor (B/R1) = floor (512/16) = 32

Number of first-level index entries r1 = number of gile blocks = 6250

Number of first-level index blocks b1 = ceiling(r1/bfr1) = Ceiling(6250/32) = 196

the number of levels needed if we make it into a multi-level index;

Number of second-level index entries r2 = number of first-level blocks b1 = 196 entries
Number of second-level index blocks b2 = ceiling(r2/bfri ) = ceiling(196/32) = 7 blocks
Number of third-level index entries r3 = number of second-level index blocks b2 = 7 entries
Number of third-level index blocks b3 = ceiling(r3/bfri ) = ceiling(7/32) = 1

As the third level has only one block, it is the top index level.
Hence, the index has 3 levels

Total number of blocks for the index bi = b1 + b2 + b3 = 196 + 7 + 1 = 204 blocks

c) The binary search without primary indexing would need log2 b = log2 6250 = 13 block access
The binary search with primary indexing would need log2 b = log2 204= 8 block access

Hence, in terms of block access, binary search algorithm with primary indexing need lesser number of block access, and would be a better choice than binary search algorithm without primary indexing.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.