Define candidate key, alternate key, composite key?
- 30 Answers
- 137442 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 are the constraints on severity level in raiseerror?
The count() function always returns a int value type what should you do if you need to count rows from a query which you know will return a value that is too large for an int value type?
Any sample or model question of punjab national bank for deputy manger IT or any sample of PNB.any1 plz do send on
What are the three types of database design?
Explain what a database is?
What is an application role and explain a scenario when you would use one?
I have a few records all are same structures data, I want to store data in multiple targets how
Explain about query and reporting.
Explain about relational database management systems?
What is database design and development?
How to generate OIDS
Does QTP Support the Propjects which are running in Dot Net? As we are starting with new project in Dot net need to use automation tool?Please advice & let me know what would be the best.
What is the physical representation for a many-to-many relationship?
Write the fastest query to find out how many rows exist in a table?
explain detail about ftp in datastage?
