Editing Candidate key (section)

==Determining candidate keys==
The set of all candidate keys can be computed
e.g. from the set of [[Functional dependency|functional dependencies]].
To this end we need to define the attribute closure <math>\alpha^+</math> for an attribute set <math>\alpha</math>.
The set <math>\alpha^+</math> contains all attributes that are functionally implied by <math>\alpha</math>.

It is quite simple to find a single candidate key.
We start with a set <math>\alpha</math> of attributes and try to remove successively each attribute.
If after removing an attribute the attribute closure stays the same,
then this attribute is not necessary and we can remove it permanently.
We call the result <math>\text{minimize}(\alpha)</math>.
If <math>\alpha</math> is the set of all attributes,
then <math>\text{minimize}(\alpha)</math> is a candidate key.

Actually we can detect every candidate key with this procedure
by simply trying every possible order of removing attributes.
However there are many more [[permutation]]s of attributes (<math>n!</math>)
than [[power set|subsets]] (<math>2^n</math>).
That is, many attribute orders will lead to the same candidate key.

There is a fundamental difficulty for efficient algorithms for candidate key computation:
Certain sets of functional dependencies lead to exponentially many candidate keys.
Consider the <math>2\cdot n</math> functional dependencies
<math>\{A_i \rightarrow B_i : i\in\{1,\dots,n\}\} \cup \{B_i \rightarrow A_i : i\in\{1,\dots,n\}\}</math>
which yields <math>2^n</math> candidate keys:
<math>\{A_1, B_1\} \times \dots \times \{A_n, B_n\}</math>.
That is, the best we can expect is an algorithm that is efficient with respect to the number of candidate keys.

The following algorithm actually runs in polynomial time in the number of candidate keys and functional dependencies:<ref>
{{cite journal
 | last1 =L. Lucchesi
 | first1 =Cláudio
 | last2 =Osborn
 | first2 =Sylvia L.
 | title =Candidate keys for relations
 | journal =Journal of Computer and System Sciences
 | volume =17
 | issue =2
 | pages =270–279
 | date =October 1978
 | doi=10.1016/0022-0000(78)90009-0
| doi-access =
 }}
</ref>

 '''function''' find_candidate_keys(A, F)
     /* A is the set of all attributes and F is the set of functional dependencies */
     K[0] := minimize(A);
     n := 1; /* Number of Keys known so far */
     i := 0; /* Currently processed key */
     '''while''' i < n '''do'''
         '''for each''' α → β ∈ F '''do'''
             /* Build a new potential key from the previous known key and the current FD */
             S := α ∪ (K[i] − β);
             /* Search whether the new potential key is part of the already known keys */ 
             found := false;
             '''for''' j := 0 to n-1 '''do'''
                 '''if''' K[j] ⊆ S '''then''' found := true;
             /* If not, add it */
             '''if''' '''not''' found '''then'''
                 K[n] := minimize(S);
                 n := n + 1;
         i := i + 1
     '''return''' K

The idea behind the algorithm is that given a candidate key <math>K_i</math>
and a functional dependency <math>\alpha \rightarrow \beta</math>,
the reverse application of the functional dependency yields
the set  <math>\alpha \cup (K_i \setminus \beta)</math>,
which is a key, too.
It may however be covered by other already known candidate keys.
(The algorithm checks this case using the 'found' variable.)
If not, then minimizing the new key yields a new candidate key.
The key insight is that all candidate keys can be created this way.