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

Question 1

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?

• 1
• 2
• 3
• 4

Question 2

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 ____________.

• 99.55 to 99.65
• 100.5 to 101.4
• 97.2 to 98.5
• 89.1 to 91.2

Question 3

In a B+ tree, if the search-key value is 12 bytes long, the block size is 1024 bytes and the block pointer is 6 bytes, then the maximum number of keys that can be accommodated in each non-leaf node of the tree is _____ .

• 57
• 54
• 58
• 56

Question 4

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

• Dense
• Sparse
• Clustered
• Unclustered

Question 5

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  

• 0

• 30400

• 38400

• 798400

Question 6

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?

• Age

• Name

• Occupation

• Category

Question 7

A B+ tree of order d is a tree in which each internal node has between d and 2d key values. An internal node with M key values has M+1 children. The root (if it is an internal node) has between 1 and 2d key values. The distance of a node from the root is the length of the path from the root to the node. All leaves are at the same distance from the root. The height of the tree is the distance of a leaf from the root. 

a). What is the total number of key values in the internal nodes of a B+ tree with l leaves (l≥2)? 
b). What is the maximum number of internal nodes in a B+ tree of order 4 with 52 leaves? 
c). What is the minimum number of leaves in a B+ tree of order d and height h(h≥1)?

• a)L+1 b)8 c)2(d+1)^h−1

• a)L-1 b)13 c)2(d+1)^h−1

• a)L-1 b)17 c)2(d-1)^h−1

• a)L-1 b)9 c)2(d-1)^h+1

Question 8

Consider the following query :

SELECT E.eno, COUNT(*)
FROM Employees E
GROUP BY E.eno

If an index on eno is available, the query can be answered by scanning only the index if

• the index is only hash and clustered
• the index is only B+tree and clustered
• index can be hash or B+ tree and clustered or non-clustered
• index can be hash or B+ tree and clustered

Question 9

Trigger is

• Statement that enables to start any DBMS
• Statement that is executed by the user when debugging an application program
• The condition that the system tests for the validity of the database user
• Statement that is executed automatically by the system as a side effect of a modification of the database

Question 10

The order of a leaf node in a B+ tree is the maximum number of (value, data record pointer) pairs it can hold. Given that the block size is 1K bytes, data record pointer is 7 bytes long, the value field is 9 bytes long and a block pointer is 6 bytes long, what is the order of the leaf node?

• 63
• 64
• 67
• 68