# Ace Test Series: DBMS - File Indexing

681 views

edited
0
99 ?
0
shouldn't it be 137?
0
Its 99, normally we take <search key,block pointer> but here its dense index so we need to point to individual record so <search key,record pointer>
0
Even then it will be 100 right, assuming that the blocks are unspanned.
0
Assuming unspanned strategy, it would be 99, not 100.
0
solution?
2

$\frac{1024}{10} = 102 .4$ = 102 [due to unspanned]

$\frac{10000}{102} = 98.03$  = 99 blocks needed

0

No of record per block $\frac{1024}{100}=10$

No. of such block present $\frac{10000}{10}=1000$

Now blocking factor$\frac{1024}{6+2}=128$

So, in secondary indexing $\frac{10000}{128}=78$

@Magma chk once

ans is 78

0
in secondary indexing and first level $\frac{10000}{128}=78$

if asking for in secondary indexing and second level $\frac{78}{128}=1$
0
mam we construct secondary index for given data...not primary index

that's Y we didn't include 6B (block index )

only we have to include  ( record pointer + key)  = 10B
0
where have I taken primary index?

I just took blocking factor
0
blocking factor takes block pointer+key

It is not Btree

right?
0
mam it's given that file is not order  by key field....that's Y we apply blocking factor of secondary index

right ??
0
blocking factor cannot be change for primary or secondary index
0

@srestha why you are using blocking factor ??

Here we are doing indexing on records directly i.e. in the index file we'll be having entry as <Search Key,Record pointer> not block pointer had it been sparse indexing there we first convert records into blocks by placing them within and calculate the blocking factor

0
is it sparse or dense? not mentioned na?
0

Exactly but you see we are doing indexing on SSN and the records aren't ordered by SSN, that means we cant do sparse indexing on it because the order might be different and eventually we'll endup searching entirely, so its must to do dense indexing. And we know that secondary index must be dense only.

0

@Na462

but how they got 10?

it should be divided by 6+2=8

1

@srestha

See Mam :

Suppose i have a records as shown but the records when stored in file are stored using the  FIRST attribute (The numbers here :) )

Now if i want to do indexing on Name attribute i cant do sparse why ? because as u can see above records are stored using numbers (orderly as both are ordered index) so if i search for 142 i can simply create a sparse index on blocks <Search Key , Block Pointer > and i can do a binary search to go to the record entry <= 142 from there i can do linear scan easily.

But here we are doing indexing on Name attribute imagine i want to search for 'Bart' can i do similar binary search on Sparse index record file (if i do sparse indexing on Name ) to go to 'Bart' , NO ? Why because even if i scan lexicographically, you can see that name follows very different order.

Hence its must to do indexing on every record.

That's why its secondary index as its giving the order of records different from one stored in the files.

So Dense indexing :

I will keep an index corresponding to every Record :

* In one index record i will keep <Search Key , Record Pointer> as You can see above  so size                                         of one entry = 8 + 2 = 10 ( Record pointer + Search Key here its SSN)

* Now how many such entries i can have in a block : 1024/10 = 102, means in one block i can                                           store 102 record index. We need to store for 10,000 records = 10000/102 = 98.02 = 99 blocks

* Now if i again do indexing it will be Sparse , NOTE : Always the first level is sparse or                                                 dense and after then every indexing is sparse Why ? Because now we have blocks to                                           deal with because we have already converted every thing to blocks :)

Had it been Sparse:

As you can see in figure they converted first in blocks, ALWAYS REMEMBER THAT SPARSE INDEX IS ONE PER BLOCK.

Hence Number of blocks to store entire records :-

How many records i can store in one block = 1024/100 = 10

I have total 10,000 records so 10000/10 = 1000 blocks

Now in sparse index file as you can see i store <Search key , Block pointer > (Here block pointer pointing to block and also contains offset within the block to record > , So size of one entry = 6 + 2 = 8 (Block pointer , Search Key)

How many index i can have = 1024/8 = 128

Hence number of blocks on first level = 1000/128 = 78.

Hope you get now MAM

0
Nice explanation sir.
0
Block size = 1024 B

Size of index = 8 + 2 = 10 B.

Number of indices in a block = 1024/10 = 102.

Since in secondary indexing there will be a index for each data record in the database file.It’s dense indexing. Therefore number of indices in  first level = number of data records = 10000.

So, number of blocks in first level = total number of indices/ number of indices in a single block.

number of block in first level = 10000/102 = 98.039.

Since 98 block is sufficient so we will take 99 blocks.

## Related questions

1
218 views
Here the indexing is done on ordered field so which to use Dense or Sparse because both can be done here ?