GATE CSE 2013 | Question: 15

Question

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

Comments

Similar question
https://gateoverflow.in/8222/gate2015-1-24

---

Clustering index is applied on a ordered file and can contain duplicate values.
Kind of like a group of values in which the values are same for that one index entry and same for another group of same values and so on...

---

$\text{Clustered Indexing}$

• Relation is physically sorted on search key, but that search key is not a $key$ of the relation
• hence forming clusters of same values

\[
\begin{array}{c|c|c|c}
\text{Record} & \text{Student\_ID} & \text{Name} & \text{Age} \\ \hline
R_1 & 101 & A & 18 \\
R_2 & 105 & E & 18 \\
R_3 & 103 & C & 19 \\
R_4 & 107 & G & 19 \\
R_5 & 102 & B & 20 \\
R_6 & 108 & H & 20 \\
R_7 & 104 & D & 21 \\
R_8 & 106 & F & 22
\end{array}
\]

 

\[
\begin{array}{c|c}
\text{Age (Search Key)} & \text{Pointer} \\ \hline
18 & \rightarrow R_1 \\
19 & \rightarrow R_3 \\
20 & \rightarrow R_5 \\
21 & \rightarrow R_7 \\
22 & \rightarrow R_8
\end{array}
\] 

Answer 1

Answer is C).

Index can be created using any column or combination of column which need not be unique. So, A, B are not the answers.

Indexed column is used to sort rows of table.Whole data record of file is sorted using index so, C is correct option. (Simple video explains this).

Comments

I thought clustered indexes have to be unique so why not option A?

---

No clustered index only require it to be build on the ordering key of the record which is not the candidate key i.e not unique, it just takes the block pointer to the first block of the repeated values for each distinct value.

Answer 2

Clustered Index is created on data file whose file recordrds are physically ordered on a non key field which does not have distinct value for each record .

Option A ) Candidate Key

Option B) Primary Key

Option D ) File NOT ordered

Best choice  is Option C

Option C ) Data records are ordered

Comments

@pC 

I think, answer is correct but explanation isnt.

What you have explained is the definition of clustering index not clustered index.

As per my knowledge clustered index means  the index and the data on which index is have the same order so it doesn't matter whether selected attribute is key or non-key .

https://gateoverflow.in/8222/gate2015-1-24

( i might be wrong but you can verify once)

---

what does the term cluster signify?

---

The correct defination is clustered index is an index which is generally on non key,

but if we have a primary key in the table. then automatically we get CLUSTERED UNIQUE index, which is on primary key

 

Arjun sir, this is answer to your query

Answer 3

Answer is C

Watch this video: https://www.youtube.com/watch?v=vjrHiaIfOl8

Answer 4

a

Answer 5

Just adding up more points:

Option A): Indexing based on Candidate keys can be done using Primary index or Secondary Index given ordered/unordered data records

Option B): Indexing based on primary key also a candidate key so the same logic applies as option A

Option C): Indexing on ordered/organized data records over non key value attribute is mandatory for clustering index

Option D) Indexing on Unordered data records over key/non key attribute is for secondary indexing 

Answer C.

For more on Cluster indexing: https://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean