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

Question 1
An index is clustered, if
Cross
it is on a set of fields that form a candidate key.
Cross
it is on a set of fields that include the primary key.
Tick
the data records of the file are organized in the same order as the data entries of the index.
Cross
the data records of the file are organized not in the same order as the data entries of the index.


Question 1-Explanation: 
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?
Cross
1
Tick
2
Cross
3
Cross
4


Question 2-Explanation: 
\"anil_ds_18\" 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

Tick

non-key and ordering

Cross

non-key and non-ordering

Cross

key and ordering

Cross

key and non-ordering



Question 3-Explanation: 

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/ 

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 106 bytes disk on which the file system is stored and data block size is 103 bytes, the maximum size of a file that can be stored on this disk in units of 106 bytes is ____________.
Tick
99.55 to 99.65
Cross
100.5 to 101.4
Cross
97.2 to 98.5
Cross
89.1 to 91.2


Question 4-Explanation: 
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 :

Cross

the size of the blocks, and the size of the ad­dress of the blocks.

Tick

the number of blocks used for the index, and the size of the blocks.

Cross

the size of the blocks, the number of blocks used for the index, and the size of the address of the blocks.

Cross

None of these



Question 5-Explanation: 


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. 

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
Cross
Dense
Cross
Sparse
Tick
Clustered
Cross
Unclustered


Question 6-Explanation: 
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

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  

Cross

800000

Cross

40080

Tick

32020

Cross

100



Question 7-Explanation: 


Number of block access = nr * bs + br where br and bs are number of blocks in relations R and S respectively, and nr 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. 

Question 8

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  

Cross

0

Tick

30400

Cross

38400

Cross

798400



Question 8-Explanation: 


Number of block access = nr * bs + br where br and bs are number of blocks in relations R and S respectively, and nr 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. 

Question 9
Which of the following is correct?
Cross
B-trees are for storing data on disk and B+ trees are for main memory.
Tick
Range queries are faster on B+ trees.
Cross
B-trees are for primary indexes and B+ trees are for secondary indexes.
Cross
The height of a B+ tree is independent of the number of records.


Question 9-Explanation: 
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?

Cross

Age

Cross

Name

Tick

Occupation

Cross

Category



Question 10-Explanation: 


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.
 

There are 36 questions to complete.

  • Last Updated : 09 Oct, 2019

Share your thoughts in the comments
Similar Reads