**Source:** Wikipedia: Candidate Key

Candidate key

From Wikipedia, the free encyclopedia

Jump to:navigation, search

Merge-arrows.svg

It has been suggested that this article or section be merged with Unique key. (Discuss)

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

1. the relation does not have two distinct tuples with the same values for these attributes (which means that the set of attributes is a superkey)

2. there is no proper subset of these attributes for which (1) holds (which means that the set is minimal).

Since a relation contains no duplicate tuples, the set of all its attributes is a superkey if NULL values are not used. 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.

Contents

[hide]

* 1 Example

* 2 Determining candidate keys

* 3 References

* 4 External links

* 5 See also

[edit] Example

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 determiy 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}.

[edit] References

* Date, Christopher (2003). "5: Integrity". An Introduction to Database Systems. Addison-Wesley. pp. 268–276. ISBN 978-0321189561.

[edit] External links

* Relation Database terms of reference, Keys: An overview of the different types of keys in an RDBMS

[edit] See also

* Alternate key

* Compound key

* Database normalization

* Primary key

* Relational database

* Superkey

[hide]

v • d • e

Database management systems

Database models · Database normalization · Database storage · Distributed DBMS · Federated database system · Referential integrity · Relational algebra · Relational calculus · Relational database · Relational DBMS · Relational model · Object-relational database · Transaction processing

Concepts

Database · ACID · CRUD · Null · Candidate key · Foreign key · Primary key · Superkey · Surrogate key · Armstrong's axioms

Objects

Relation (Table) · View · Transaction · Log · Trigger · Index · Stored procedure · Cursor · Partition

Components

Concurrency control · Data dictionary · JDBC · ODBC · Query language · Query optimizer · Query plan

Database products: Object-oriented (comparison) · Relational (comparison) · Document-oriented

Retrieved from "http://en.wikipedia.org/wiki/Candidate_key"

Categories: Database management systems | Data modeling