GATE CSE 2008 | Question: 16, ISRO2016-60

Question

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

Comments

if file records are physically ordered on a nonkey field- which does not have a distinct value for each record-that field is called the clustering field. We can create a different type of index, called a clustering index. This differs from a primary index, which requires that the ordering field of the data file have a distinct value for each record.

---

Primary indexing – key , ordered

Secondary indexing – Key , Unordered

Clustering Indexing – Non-key , Ordered

Secondary non key indexing – Non key , Unordered 

---

Truth Table for this

K.   O          PK

K    U          SK(ordered)

NK O          CLUSTERED

NK U          SK (unordered)

---

Refer : Fundamentals of Database Systems 6th edition chapters 17 and 18.

$$
\renewcommand{\arraystretch}{1.3}
\begin{array}{|l|l|l|l|l|}
\hline
\textbf{Index Type} & \textbf{File Order} & \textbf{Attribute / Characteristics} & \textbf{Index Density} & \textbf{Index Entry / Pointer Meaning} \\
\hline
\text{Primary Index} & \text{Ordered} & \text{Primary key} & \text{Dense} & \text{Points to each record} \\
\hline
\text{Primary Index} & \text{Ordered} & \text{Primary key} & \text{Sparse} & \text{Points to block anchor (first record or last record of the block)} \\
\hline
\text{Clustering Index} & \text{Ordered} & \text{Non-key} & \text{Sparse} & \text{Points to first block of each distinct value} \\
\hline
\text{Secondary Index} & \text{Unordered} & \text{Candidate key} & \text{Dense} & \text{Can be block pointers or record pointers} \\
\hline
\text{Secondary Index} & \text{Unordered} & \text{Non-key} & \text{Sparse} & \text{Duplicate index entries having same }K(i)\text{ but different record addresses} \\
\hline
\text{Secondary Index} & \text{Unordered} & \text{Non-key} & \text{Sparse} & \text{Variable length record (length varies according to number of record pointers for }K(i)\text{)} \\
\hline
\text{Secondary Index} & \text{Unordered} & \text{Non-key} & \text{Sparse} & \langle K(i),P(i)\rangle,\ \text{where }P(i)\text{ is pointer to block containing record pointers having same }K(i) \\
\hline
\end{array}
$$

 

Answer 1

There are several types of ordered indexes. A primary index is specified on the ordering key field of an ordered file of records. Recall from Section 17.7 that an ordering key field is used to physically order the file records on disk, and every record has a unique value for that field. If the ordering field is not a key field- that is, if numerous records in the file can have the same value for the ordering field— another type of index, called a clustering index, can be used. The data file is called a clustered file in this latter case. Notice that a file can have at most one physical ordering field, so it can have at most one primary index or one clustering index, but not both. 

Reference -> Database Systems book BY Navathe, $6^{th}$ Edition, 18.1 Types of Single- Level Ordered Indexes Page no. 632.

Answer should be A.

Answer 2

A clustered index can be created on any attribute set, which is ordered (but maximum one for a table). By default, it is created for the primary key. We can change this to any non-key also. So, (A) and (C) can have clustered index. I guess the question assumes primary index as not being called clustered index, so answer should be A. 

http://msdn.microsoft.com/en-us/library/ms186342.aspx

Comments

yes. Unless otherwise told. 

http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key

---

 If file records are physically ordered on a nonkey field—which does not have a distinct value for each record—that field is called the clustering field and the data file. We can create a different type of index, called a clustering
index, to speed up retrieval of all the records that have the same value for the clustering field. This differs from a primary index, which requires that the ordering field
of the data file have a distinct value for each record.
Page No 636, Navathe 6th Edition

---

This is from Navathe book too ! So does it make option A stronger ?

---

There are several types of ordered indexes. A primary index is specified on the
ordering key field of an ordered file of records. Recall from Section 17.7 that an
ordering key field is used to physically order the file records on disk, and every record
has a unique value for that field. If the ordering field is not a key field—that is, if
numerous records in the file can have the same value for the ordering field—
another type of index, called a clustering index, can be used. The data file is called a
clustered file in this latter case. Notice that a file can have at most one physical
ordering field, so it can have at most one primary index or one clustering index, but
not both.
Ref -> Database Systems book BY Navathe, 6th Edition , 18.1 Types of Single-Level Ordered Indexes Page no 632

As per this book Primary Index is not clustering index !

Answer 3

Clustering index is an index whose search key also defines the sequential order of the file. Clustering indices are also called primary indices. The primary index may appear to denote the index of an primary key; but can be built on any search key. The serach key of an clustering index is also called primary key;although not necessarily ... so search key can be non key value and it works on sequential manner means ordered ... so option A ..

Comments

By definition given in KORTH, ANSWER is both A) and C).

But by the definition given in Navathe,it is option A)

Answer 4

answer is A

Comments

@Poshita Shrivastava any explanation ?

Answer 5

This is from Navathe 18.1