GATE CSE 2015 Set 1 | Question: 24

Question

A file is organized so that the ordering of the 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 

Comments

This is definitely cluster index, read explanation below, but many are confused why not option a and b see actually clustered index is both sparse and dense at same time

Dense because for every unique key there is entry which is definition of dense

Sparse because for every repeating key in cluster there is no entry which makes it satisfy the definition that there is entry for some of the keys in index then sparse index

So it is Dense and Sparse Index at same time and also Cluster index so in option only C matches

---

noo clustered index is both dense as well as sparse so this option is incorrect it has to be dense index!!

---

I wrongly interpreted the question.

close to the ordering of data entries in some index

In this line i thought by some index they meant some rows in index. So that kind of messed up my brain for a bit, but now i understood they are referring to the some unknown index and we have to tell what that unknown index is.

Answer 1

Clustered- this is the definition of clustered indexing and for the same reason a table can have only one clustered index.

http://www.ece.rutgers.edu/~yyzhang/spring03/notes/7-B+tree.ppt

Correct Answer: C

Comments

Yes True, that was misguiding one.

---

@Arjun Sir , why “a table will have only one clustered index”. Cant’t there be two attributes which are non-key and ordered such that we can use both for different clusterd index.

---

@pandaanwesh33 no a relation can’t be sorted on more than 1 field at a time. Therefore there can be atmost 1 clustered index, in a relation at a time.

Source: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver16

Answer 2

Lots Of Confused Guys here.

First of All Clustering Index is done on a Field Which is NON KEY & ORDERED!

Because it is Non Key , there can be DUPLICATES, so we can’t have an Index Entry for the 1^st element of the Block,we need to have an INDEX entry for each element. (For Duplicates , only one is enough).

So if Main File has DUPLICATES,then Clustered Index is also Sparse Index , bcoz we are not having index entries for the Duplicates. 

Now Answer can’t be sparse Index because if we have an INDEX Entry for every record in main File(assuming No Duplicates) , it is NOT a Sparse Index.

IN SHORT : Question says “data records is the same as or close to the ordering of data entries in some index”.

Now these data records can have duplicates ot it might not have.

If there are Duplicates, then it can’t be Dense index, else not.

If there are No duplicates , then it can’t be Sparse Index.

So only Clustering Index can be the answer.

Answer 3

Answer is C..

 

Dense index  means: you must maintain a index for every record( read agian .. it is not or evry search key) ..

1 a

1 b

2 b  

Total number of records={3}

Total number of search keys={2}

Dense contains index for 1,1,2 ..

Cluster can be either sparse or dense…

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

Answer 4

The answer is CLUSTERED not sparse because in sparse an entry is created only for some search key, only those which are first in a block while is clustered an entry is created for every distinct value.

So, clustered can give same or close to more often than sparse.