# DBMS Korth Edition 4 Exercise 12 Question 4 (Page No. 489)

174 views
Is it possible in general to have two primary indices on the same relation for different search keys ? Explain your answer.

No it's not possible to have more than one primary index on same relation. In primary index the search key is the primary key/Alternate key and it is physically ordered in DB, so other keys will automatically be  unordered hence at most 1 primary index is possible for a relation.
If two or more attributes are ordered then yes. Example: Roll Number(unique within institute) and enrollment number(unique within Mumbai University) of relation for an institute.
0
Two primary key in a table?? How will it be possible?? It is not possible I think.
0
But mam, index need not be made on the primary key. Also, a non-primary key can also be ordered.
0

they told

have two primary indices

that means two primary key in the table itself. right??

0

The search key for a primary index need not be the primary key.

Source- 14.7, Database System Concepts, 7th ed by Korth and Sudarshan

So this way two primary indices can be created.

0

I am not sure, though

Roll Number(unique within institute) and enrollment number(unique within Mumbai University) of relation for an institute.

We have read, that a table cannot contain more than one primary or unique key. And in korth they are telling about files, not relation. right??

0
I think by file they mean the relation since the relation is stored in a file on a disk. Also on line 7 they have mentioned that in primary indices the search key need not be primary key.
0

Primary index is defined on an ordered data file. The data file is ordered on a key field. The key field is generally the primary key of the relation

@sakharam check these lines.

mostly two primary indices not possible.

How do u find "different search keys" for a single relation?

0
Yes, the statement says "generally" the primary key, but we can also create on a candidate key. And we can have two search keys(In the book by Navathe there's a topic on having multiple indices on the same relation)

Mam, I thought since there can be multiple indices and since there is a possibility(though rare) that two candidate keys(used as search keys) are ordered in the same way there can be two primary indices on the same relation.
0

@sakharam

Suppose we do primary indices in a student table is Roll number and DOB, where only roll number is primary key.

Now, if we search with key field by DOB , will it give correct answer?? because there is a chance that two students can have same DOB some time.

0
Mam, It will be useful if my search query is select * from Student where DOB= '2000-1-1'

Here the DOB index will be used. And since we want multiple rows this index works.

But yes, DOB can not be a primary index, only Roll no. will be.
0
Atleast DOB cannot be a search key.

right??
0

It can be a search key. The same way in the following example Income was used as a search key for creating index.

Source: Korth

0

@sakharam

Key cannot take duplicate values

right??

Otherwise tell me which key can take duplicate values??

0
Mam, search key need not be a key(primary key/candidate key), right?

Yes mam, I agree about the order thing that generally the order would be different. But I talked about a rare case when order is same.

## Related questions

1
47 views
What is the difference between a primary index and a secondary index ?
Construct a $B^+$-tree for the following set of key values: $(2, 3, 5, 7, 11, 17, 19, 23, 29, 31)$ Assume that the tree is initially empty and values are added in ascending order. Construct B+-trees for the cases where the number of pointers that will fit in one node is as follows: a. Four b. Six c. Eight