Monday, March 28, 2022

GATE Questions - DBMS - File Structures and Indexing

Previous GATE questions with solutions on DBMS (File Structures and Indexing) - CS/IT

GATE-2013
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.

Ans: option (c)
Explanation :
With a clustered index the physical records (rows) are stored on the disk in the same order as that of index. Hence every table can have exactly one clustered index.

GATE - 2015
2. With reference to the B+ tree index of order 1 shown below, the minimum number of nodes (including the root node) that must be fetched in order to satisfy the following query: “Get all records with a search key greater than or equal to 7 and less than 15” is ________.
Ans: 5
Explanation:
In a B+ tree, data pointers are stored only at the leaf nodes. The leaf nodes contain the value of the search field as well as a pointer to the record/block that contains the record.In order to provide ordered access, the leaf nodes have been linked.
In the above question, in order to fetch all records with key values greater than or equal to 7 and less than 15, we first need to locate the leaf node that stores 7.To reach the node with key 7,we start from root with key value 9,follow left pointer to arrive at a node with key 5 (since search key 7 is less than 9). From node with key 5, we follow the right pointer to reach the leaf node with key value 7. Now we move from 7 till 15 sequentially towards right, thus traversing another 2 nodes.
Thus total number of nodes fetched in order to satisfy the query is 5.

GATE - 2015
3. 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

Ans: option (c)
Explanation:
With a clustered index the physical records (rows) are stored on the disk in the same order as that of index . Hence every table can have exactly one clustered index.

No comments:

Post a Comment