How to solve blocking factor questions in DBMS

Hello friends! Nice to meet you all again. Today I have brought you a very interesting topic. This is also a kind of difficult lesson. Don’t worry we will discuss everything nicely. In a simple manner. The topic we are going to discuss today is blocking factor questions in DBMS. Sounds not familiar. So many students yet don’t know what is the proper name for this topic. You might have seen questions like asking to calculate the blocking factor for different indexing types. That’s the questions today we are going to discuss. We call it also as numeric in database.

Today we will discuss what is blocking factor in a databases. How to calculate bfr. How to calculate bfr in primary index. How to calculate bfr in secondary index. How to calculate bfr in multi level index. You get answers to all these questions from this post. Overall How to solve blocking factor questions in DBMS

Before starting this chapter I hope you know about indexing theories. Because we need your indexing knowledge for this topic. In order to solve the questions you need to know the Indexing types. We discuss three types of questions here. That is numeric on primary indexing, numeric on secondary indexing, numeric on multi-level indexing. The way we find the blocking factor in those three types are different. So let’s get started. We will learn these things with questions.

How to solve blocking factor questions in DBMS for primary indexing

All records – 300,000

Block size – 4096 bytes

Record length – 100 bytes

Index size – 15 bytes

Here what we are going to find is how many blocks we have to access to find a data. You know for data there will be index file. That index file also stored in the secondary storage. In the secondary storage files stored in blocks. So firstly we have to access some blocks to find the index. Then by index we can find the location of the data. That is also stored in a block. Then we have access those blocks also. All together we can sum index blocks and data blocks to find a data. Firstly we need to find the index blocks. Because number of blocks we need to access to find the index is decide on how many blocks the index files acquired. Therefore we have to find that. Because index file size can vary from the amount of data and the indexing type that we use.

To find how many blocks need to store index file. We firstly need to find how many index records can store in a single block. We call it as blocking factor for index. In other words number of index records per block. We will calculate that. We can calculate from the data we have.

Here we take the lower bound value. Now we know how many records can store in a block. Then we will find how many index entries blocks. Since we are following primary indexing. Total number of index entries = Total number of blocks. Now let’s find how many indexes are there.

How to solve blocking factor question in DBMS for secondary indexing

Secondary indexing blocking factor questions solving is different than primary indexing solving. The difference happens because of the number of index files we are creating. In primary indexing it is a sparse one. There won’t be index for each and every data item in database. Even though in secondary indexing it is dense index. There is an index entry for each and every data item in the database file. If we think about the index file size primary index file size is lesser than secondary index file size. Yet if we compare the access time secondary index can search faster than primary. Because if you remember there is an index entry for each data item.

We will calculate the blocking factor with the same value which we previously use with primary indexing question.

All records – 300,000

Block size – 4096 bytes

Record length – 100 bytes

Index size – 15 bytes

First we need to find blocking factor for index. Then we need to calculate how many index entries are there. Then after that we need to calculate number of block to store the index file. At last we calculate how many blocks we have to search to find a data which referencing through secondary indexing. Let’s get started.

How to solve blocking factor question in DBMS for multi level indexing

Solving multi level indexing question is the easiest question type you get. You know in multi level indexing we create different levels with indexing. Outer level will be sparse index type. Inner level will be a dense index. When searching a data in the multi level index it would equal to the number of levels we have. Outer level will guide us through inner level. Therefore in order to find a data we have to travel through levels. Because of that in this type of questions what we do is finding the number of levels. We will look at a question. The same data we will use.

All records – 300,000

Block size – 4096 bytes

Record length – 100 bytes

Index size – 15 bytes

In this question what we have to calculate as a fact data is only the BFR for index. Let’s look at the solution.

Here we have discussed all the indexing question types. We will discuss more questions from another article. Here why we add one blocks extra is because of the reason I explained earlier. We have to access index entries as well as the data block. Index entries will give us where that data is actually stored. So after knowing we have to read that block to know the data value. Since Total access blocks are one more block extra to the index levels.

Conclusion

As usual we have come to end of the post. As I said we discussed all the theories. Real world exam question we will discuss with another post. If you have doubts you can write to us. We always there to help you. Share this post among with your friends then they also can learn these stuff. Keep stay with myexamnote for learn more things with us. If you have doubt you can comment and let us know. As usual we have provided so many good posts related to the database. You can find some of them in related posts below. Here is one suggestion for you. Database query processing and optimization. We can write a post regarding that and help you to study. We will meet with another good topic. Till then good bye!