File structures (sequential files, indexing, B and B+ trees)

Question 1

An index is clustered, if

• A. it is on a set of fields that form a candidate key.
• B. it is on a set of fields that include the primary key.
• C. the data records of the file are organized in the same order as the data entries of the index.
• D. the data records of the file are organized not in the same order as the data entries of the index.

Answer: (C)

File structures (sequential files, indexing, B and B+ trees) 50 DBMS MCQs with Answers
Discuss it

A database index is clustered if physical records on disk follow the index order.

Question 2

Consider a B+-tree in which the maximum number of keys in a node is 5. What is the minimum number of keys in any non-root node?

• A. 1
• B. 2
• C. 3
• D. 4

Answer: (B)

GATE CS 2010 File structures (sequential files, indexing, B and B+ trees)
Discuss it

This solution is contributed by Anil Saikrishna Devarasetty Another one: Since the maximum number of keys is 5, maximum number of children a node can have is 6. By definition of B Tree, minimum children that a node can have would be 6/2 = 3. Therefore, minimum number of keys that a node can have becomes 2 (3-1).

Question 3

A clustering index is defined on the fields which are of type

• A. non-key and ordering
• B. non-key and non-ordering
• C. key and ordering
• D. key and non-ordering

Answer: (A)

GATE CS 2008 File structures (sequential files, indexing, B and B+ trees) 50 DBMS MCQs with Answers
Discuss it

A clustering index as the name suggests is created when the data can be grouped in the form of clusters. For example: A small database file storing roll no and subjects enrolled for a particular student. Here data can be grouped on the basis of Roll No.s. Hence to create such kind of index files, fields could be non-key attributes and which are in ordered form so as to form clusters easily. Hence option (A) – non key and ordering. Additional points for Clustered Indexing: 1. The number of entries in the index file are the unique values of the attribute on which indexing is done. 2. The pointer in the index file will give the base address of the block in which the value appear for the first time. http://quiz.geeksforgeeks.org/indexing-in-databases-set-1/ This solution is contributed by Yashika Arora.

Question 4

A FAT (file allocation table) based file system is being used and the total overhead of each entry in the FAT is 4 bytes in size. Given a 100 x 10⁶ bytes disk on which the file system is stored and data block size is 10³ bytes, the maximum size of a file that can be stored on this disk in units of 10⁶ bytes is ____________.

• A. 99.55 to 99.65
• B. 100.5 to 101.4
• C. 97.2 to 98.5
• D. 89.1 to 91.2

Answer: (A)

GATE-CS-2014-(Set-2) File structures (sequential files, indexing, B and B+ trees)
Discuss it

Here block size is 10^3 B.
No. of entries in the FAT = Disk capacity/ Block size 
                          = 10^8/10^3 
                          = 10^5
Total space consumed by FAT = 10^5 *4B 
                            = 0.4*10^6B
Max. size of file that can be stored = 100*10^6-0.4*10^6
                                     = 99.6*10^6B.
So answer 99.6.

Question 5

In the index allocation scheme of blocks to a file, the maximum possible size of the file depends on :

• A. the size of the blocks, and the size of the ad­dress of the blocks.
• B. the number of blocks used for the index, and the size of the blocks.
• C. the size of the blocks, the number of blocks used for the index, and the size of the address of the blocks.
• D. None of these

Answer: (B)

GATE-CS-2002 File structures (sequential files, indexing, B and B+ trees) 50 DBMS MCQs with Answers
Discuss it

In the index allocation method, an index block stores the address of all the blocks allocated to a file.
When indexes are created, the maximum number of blocks given to a file depends upon the size of the index which tells how many blocks can be there and size of each block(i.e. same as depending upon the number of blocks for storing the indexes and size of each index block).

Thus, option (B) is correct.

Please comment below if you find anything wrong in the above post.

Question 6

A file is organized so that the ordering of data records is the same as or close to the ordering of data entries in some index. Then that index is called

• A. Dense
• B. Sparse
• C. Clustered
• D. Unclustered

Answer: (C)

GATE-CS-2015 (Set 1) File structures (sequential files, indexing, B and B+ trees)
Discuss it

In Clustered Index, data blocks are stored in a way to match the index. Therefore, only one clustered index can be created on a given database table. Refer https://msdn.microsoft.com/en-IN/library/ms190457.aspx for more details.

Question 7

Q 82_Part A A database table T1 has 2000 records and occupies 80 disk blocks. Another table T2 has 400 records and occupies 20 disk blocks. These two tables have to be joined as per a specified join condition that needs to be evaluated for every pair of records from these two tables. The memory buffer space available can hold exactly one block of records for T1 and one block of records for T2 simultaneously at any point in time. No index is available on either table. If Nested-loop join algorithm is employed to perform the join, with the most appropriate choice of table to be used in outer loop, the number of block accesses required for reading the data are  

• A. 800000
• B. 40080
• C. 32020
• D. 100

Answer: (C)

Gate IT 2005 File structures (sequential files, indexing, B and B+ trees) 50 DBMS MCQs with Answers
Discuss it

Number of block access = n_r * b_s + b_r where b_r and b_s are number of blocks in relations R and S respectively, and n_r is the number of tuples in relation R.
We select the relation with small number of tuples as outer relation R. So, R is T2.
Number of block access = 400 * 80 + 20 = 32020

Thus, option (C) is correct.

Please comment below if you find anything wrong in the above post.

Question 8

Q82_Part B A database table T1 has 2000 records and occupies 80 disk blocks. Another table T2 has 400 records and occupies 20 disk blocks. These two tables have to be joined as per a specified join condition that needs to be evaluated for every pair of records from these two tables. The memory buffer space available can hold exactly one block of records for T1 and one block of records for T2 simultaneously at any point in time. No index is available on either table. If, instead of Nested-loop join, Block nested-loop join is used, again with the most appropriate choice of table in the outer loop, the reduction in number of block accesses required for reading the data will be  

• A. 0
• B. 30400
• C. 38400
• D. 798400

Answer: (B)

Gate IT 2005 File structures (sequential files, indexing, B and B+ trees)
Discuss it

Number of block access = n_r * b_s + b_r where b_r and b_s are number of blocks in relations R and S respectively, and n_r is the number of tuples in relation R.
We select the relation with small number of tuples as outer relation R. So, R is T2. Number of block access = 400 * 80 + 20 = 32020
The memory buffer space holds one block of records for T1 and one block of records for T2 simultaneously. So, number of block accesses is 80 * 20 + 20 = 1620
So, Total number of block access = 32020 - 1620 = 30400

Thus, option (B) is correct.

Please comment below if you find anything wrong in the above post.

Question 9

Which of the following is correct?

• A. B-trees are for storing data on disk and B+ trees are for main memory.
• B. Range queries are faster on B+ trees.
• C. B-trees are for primary indexes and B+ trees are for secondary indexes.
• D. The height of a B+ tree is independent of the number of records.

Answer: (B)

GATE CS 1999 File structures (sequential files, indexing, B and B+ trees) 50 DBMS MCQs with Answers Top MCQs on B and B+ Trees with Answers
Discuss it

Option (A) is False because both B-trees and B+ trees are for storing data on disk Option (B) is True because By searching leaf level linearly in B+ tree, we can say a node is present or not in B+ tree. But for B tree we have to traverse the whole tree and Most of the database system use indexes built on some form of a B+ tree due to its many advantages, in particular its support for range queries. Leaf nodes are linked together in B+ tree. Hence range queries are faster. Option (C) is False because B tree and B+ tree uses for dynamic multilevel indexes not for primary or secondary index. Option (D) is False because Height depends on number of record as well as max no of keys in each node (i.e. order of tree) .

Question 10

There are 5 records in a database.

Name     Age    Occupation    Category
Rama     27       CON            A
Abdul    22       ENG            A
Jeniffer 28       DOC            B
Maya     32       SER            D
Dev      24       MUS            C

There is an index file associated with this and it contain the values 1, 3, 2, 5 and 4. Which one of the fields is the index built form?

• A. Age
• B. Name
• C. Occupation
• D. Category

Answer: (C)

GATE CS 1998 File structures (sequential files, indexing, B and B+ trees)
Discuss it

The index field associated with the values 1, 3, 2, 5 and 4 is the Occupation field because after sorting the Occupational field lexicographically will give the sequence 1,3,2,5,4.