Interested to Buy Any Domain ? << Click Here >> for more details...
Define candidate key, alternate key, composite key?
- 30 Answers
- 144487 Views
- IAS, IBM, I also Faced
- E-Mail Answers
Answer Posted / p govind rao
In the relational model of databases, a candidate key of a
relation is a minimal superkey for that relation; that is,
a set of attributes such that
the relation does not have two distinct tuples with the
same values for these attributes
there is no proper subset of these attributes for which (1)
holds.
Since a relation contains no duplicate tuples, the set of
all its attributes is a superkey. It follows that every
relation will have at least one candidate key.
The candidate keys of a relation tell us all the possible
ways we can identify its tuples. As such they are an
important concept for the design database schema.
For practical reasons RDBMSs usually require that for each
relation one of its candidate keys is declared as the
primary key, which means that it is considered as the
preferred way to identify individual tuples. Foreign keys,
for example, are usually required to reference such a
primary key and not any of the other candidate keys.
The definition of candidate keys can be illustrated with
the following (abstract) example. Consider a relation
variable (relvar) R with attributes (A, B, C, D) that has
only the following two legal values r1 and r2:
r1
A B C D
a1 b1 c1 d1
a1 b2 c2 d1
a2 b1 c2 d1
r2
A B C D
a1 b1 c1 d1
a1 b2 c2 d1
a1 b1 c2 d2
Here r2 differs from r1 only in the A and D values of the
last tuple.
For r1 the following sets have the uniqueness property,
i.e., there are no two tuples in the instance with the same
values for the attributes in the set:
{A,B}, {A,C}, {B,C}, {A,B,C}, {A,B,D}, {A,C,D}, {B,C,D},
{A,B,C,D}
For r2 the uniqueness property holds for the following sets;
{B,D}, {C,D}, {B,C}, {A,B,C}, {A,B,D}, {A,C,D}, {B,C,D},
{A,B,C,D}
Since superkeys of a relvar are those sets of attributes
that have the uniqueness property for all legal values of
that relvar and because we assume that r1 and r2 are all
the legal values that R can take, we can determine the set
of superkeys of R by taking the intersection of the two
lists:
{B,C}, {A,B,C}, {A,B,D}, {A,C,D}, {B,C,D}, {A,B,C,D}
Finally we need to select those sets for which there is no
proper subset in the list, which are in this case:
{B,C}, {A,B,D}, {A,C,D}
These are indeed the candidate keys of relvar R.
We have to consider all the relations that might be
assigned to a relvar to determine whether a certain set of
attributes is a candidate key. For example, if we had
considered only r1 then we would have concluded that {A,B}
is a candidate key, which is incorrect. However, we might
be able to conclude from such a relation that a certain set
is not a candidate key, because that set does not have the
uniqueness property (example {A,D} for r1). Note that the
existence of a proper subset of a set that has the
uniqueness property cannot in general be used as evidence
that the superset is not a candidate key. In particular,
note that in the case of an empty relation, every subset of
the heading has the uniqueness property, including the
empty set.
[edit] Determining candidate keys
The previous example only illustrates the definition of
candidate key and not how these are in practice determined.
Since most relations have a large number or even infinitely
many instances it would be impossible to determine all the
sets of attributes with the uniqueness property for each
instance. Instead it is easier to consider the sets of real-
world entities that are represented by the relation and
determine which attributes of the entities uniquely
identify them. For example a relation Employee(Name,
Address, Dept) probably represents employees and these are
likely to be uniquely identified by a combination of Name
and Address which is therefore a superkey, and unless the
same holds for only Name or only Address, then this
combination is also a candidate key.
In order to determine correctly the candidate keys it is
important to determine all superkeys, which is especially
difficult if the relation represents a set of relationships
rather than a set of entities. Therefore it is often useful
to attempt to find any "forgotten" superkeys by also
determining the functional dependencies. Consider for
example the relation Marriage(Husband, Wife, Date) for
which it will trivially hold that {Husband, Wife, Date} is
a superkey. If we assume that a certain person can only
marry once on a given date then this implies the functional
dependencies {Husband,Date}→Wife and {Wife,Date}→Husband.
From this then we can derive more superkeys by applying the
following rule:
if S is a superkey and X→Y a functional dependency
then (S-Y)+X is also a superkey
where '-' is the set difference and '+' the set union. In
this case this leads to the derivation of the superkeys
{Husband, Date} and {Wife, Date}.
| Is This Answer Correct ? | 16 Yes | 8 No |
Post New Answer View All Answers
What you can do to remove data from the cache and query plans from memory for testing the performance of a query repeatedly?
Explain about post-relational databases?
How to replace not in with not exist?
Write short notes on manual refreshes.
How to use timestamp datatypes
fact table and dimension table containg one to many relationship or many to one relastionship
What is an application role and explain a scenario when you would use one?
How to use online Backups?
How to customize error conditions.
How to read and create .LDC data files which are used by linguatic dictionary.
What is database design and development?
What is the database development process?
Why does this query return 0?
On friday, you issued several insert statements using query analyzer. You then verified the data had been correctly entered with a select statement on monday, your users report that the data is not there what happened?
Explain about query and reporting.
