In this problem, you have to calculate the number of disk accesses required in the worst case for different index organizations. The data base keeps track of different pieces of information for a student: name (20 bytes), idnumber (20 byes), gpa (15 bytes) and address (100 bytes). There are four hundred thousand students (it is a large university!). Each block can store 1500 bytes of information. A block pointer requires 12 bytes. The data file is sorted by the primary key, the student name. The idnumber is a secondary key. How many disk accesses are required if:

(a) access is through student name and there is no index

(b) access is through student name and there is a primary index on student name (similar to the index in Figure 17.1) which also has to be kept on disk.

(c) access is through student name and there is a primary index on student name (similar to the index in Figure 17.1). You can assume that the index is small enough to be kept in RAM, and is already in RAM.

(here i am providing sample example please do in that way...

[EN] Eg 1: Primary Index Record size R- 100 bytes, block size B-1024 bytes, r 30000 records For data file, blocking factor Bfr # records in a block ? For data file. Bfr # records in a block- LB div R J-L 1024 / 100 J-10 Number of data file blocks b-? Number of data file blocks b- (r/Bfr -(30000/10) 3000 blocks If no index, how many block accesses for search by ordering field? If no index, bin. search needs log b Hlog 3000 1 13 block accesses Indexing field 9 bytes, block pointer 6 bytes.If sparse primary index (on disk) like Figure 17.1, how many block accesses? Index entry size-? Index entry size (9+6)15bytes For index file, # records in a block-? For index file. Bfr: # records in a block-LB div R J-L 1024 div 15 J-68 Total # index entries ? Total # index entries # data blocks 3000, # index file blocks ? # index file blocks-C300068)-45 blocks. # block accesses to search ? Binary search : |log 45 1-7 block accesses. Plus need one more. Why? To get the data block. Total # block accesses 7 + 1-8 [EN] Eg 1: Primary Index Record size R- 100 bytes, block size B-1024 bytes, r 30000 records For data file, blocking factor Bfr # records in a block ? For data file. Bfr # records in a block- LB div R J-L 1024 / 100 J-10 Number of data file blocks b-? Number of data file blocks b- (r/Bfr -(30000/10) 3000 blocks If no index, how many block accesses for search by ordering field? If no index, bin. search needs log b Hlog 3000 1 13 block accesses Indexing field 9 bytes, block pointer 6 bytes.If sparse primary index (on disk) like Figure 17.1, how many block accesses? Index entry size-? Index entry size (9+6)15bytes For index file, # records in a block-? For index file. Bfr: # records in a block-LB div R J-L 1024 div 15 J-68 Total # index entries ? Total # index entries # data blocks 3000, # index file blocks ? # index file blocks-C300068)-45 blocks. # block accesses to search ? Binary search : |log 45 1-7 block accesses. Plus need one more. Why? To get the data block. Total # block accesses 7 + 1-8