1

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.
GATE CS 2013     File structures (sequential files, indexing, B and B+ trees)    
Discuss it


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?
A
1
B
2
C
3
D
4
GATE CS 2010     File structures (sequential files, indexing, B and B+ trees)    
Discuss it


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
A
non-key and ordering
B
non-key and non-ordering
C
key and ordering
D
key and non-ordering
File structures (sequential files, indexing, B and B+ trees)    GATE CS 2008    
Discuss it


Question 3 Explanation: 
A clustering index as the name suggests is created when the data can be grouped in the form of clusters. yashika 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 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 ____________.
A
99.55 to 99.65
File structures (sequential files, indexing, B and B+ trees)    GATE-CS-2014-(Set-2)    
Discuss it


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 :
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
File structures (sequential files, indexing, B and B+ trees)    GATE-CS-2002    
Discuss it


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.
 
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
File structures (sequential files, indexing, B and B+ trees)    GATE-CS-2015 (Set 1)    
Discuss it


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
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
File structures (sequential files, indexing, B and B+ trees)    Gate IT 2005    
Discuss it


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.
 
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
File structures (sequential files, indexing, B and B+ trees)    Gate IT 2005    
Discuss it


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.
 
Please comment below if you find anything wrong in the above post.
There are 8 questions to complete.
1

GATE CS Corner


See Placement Course for placement preparation, GATE Corner for GATE CS Preparation and Quiz Corner for all Quizzes on GeeksQuiz.