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

2 Answers

0 votes
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.
0 votes
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?

https://www.tutorialspoint.com/dbms/dbms_indexing.htm 

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
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

0 votes
1 answer
1
0 votes
0 answers
2
33 views
Since indices speed query processing, why might they not be kept on several search keys ? List as many reasons as possible.
asked Apr 1, 2019 in Databases akash.dinkar12 33 views
0 votes
0 answers
3
52 views
When is it preferable to use a dense index rather than a sparse index ? Explain your answer.
asked Apr 1, 2019 in Databases akash.dinkar12 52 views
1 vote
0 answers
4
153 views
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
asked Apr 1, 2019 in Databases akash.dinkar12 153 views
...