How to find the candidate keys? Another way to find candidate keys in normalization process / Easy way to find key of relational table in DBMS
Another way to find candidate keys
When we try to find the candidate keys
we always start with the attributes that are in the left hand side and we try
to find the closure of Left Hand Side (LHS) attributes. In many cases, there
are possibilities such that the attributes that are on both sides may also
contribute in forming a candidate keys. Hence, we would use the following
method to find all the candidate keys.
We separate the attributes from the
given set of FDs as follows;
LHS only attributes (L)

Not listed attributes (N)

RHS only attributes (R)

Both LHS and RHS (B)

The attributes that are on the LHS
must present in a key. That is, L ⊇ Key (L is
subset or equal to a key).
The attributes that are not part of
any given FDs must be a part of a candidate key. That is, N ⊃ Key (N is
a subset of a key).
The attributes that are on the RHS
cannot be a part of key. That is, R ⊅ Key (R
cannot be part of a key).
The attributes that are present on
both sides of different functional dependencies may contribute in formation of
a candidate key. That is, R ⊇ Key (R may
be part of key).
Example 1:
Let us try this with an example;
Consider a relation R = ABCDEF with
set F of functional dependencies, F = {A > B, B > D, C > D, E >
F}; Find the candidate keys of R.
Using the set F, let us draw the
table;
LHS only attributes (L)

Not listed attributes (N)

RHS only attributes (R)

Both LHS and RHS (B)

A, C, E



D, F

B

As per the theory, LHS contributes in
forming a key. Hence, let us find the closure of LHS attributes first.
A+ = ABD
C+ = CD
E+ = EF
(AC)+ = ABCD
(AE)+ = ABDEF
(CE)+ = CDEF
(ACE)+ = ABCDEF
Hence, (ACE) is a candidate key.
We don’t need to find the closure for
RHS only attributes D and F.
Both side attribute may contribute. But
in the given example, B is the attribute available on both side but it already
can be determined by A. Hence, it may not contribute in forming the key.
So, only candidate key is (ACE).
Example 2:
Let us try another example;
Consider a relation R = (ABCDEF) with
set F as F = {DF > C, BC > F, E > A, ABC > E}. Find all the
candidate keys.
LHS only attributes (L)

Not listed attributes (N)

RHS only attributes (R)

Both LHS and RHS (B)

D, B





A, C, E, F

Let us find the closure of LHS
attributes.
D+ = D
B+ = B
(DB)+ = DB
Now we need the both sides attributes for
finding the candidate key. But they can form the key in association with LHS
attributes as LHS is must in a key.
A+ = A
C+ = C
E+ = AE
F+ = F
…
…
(DBA)+ = ABD
(DBAC)+ = ABCDEF – One candidate key
(DBEF)+ = ABCDEF – One candidate key
(ACEF)+ = ACEF
Hence, candidate keys are (ABCD) and
(BDEF).
Go to normalization solved exercises page
Go to How to find closure of an attributes(s) page
No comments:
Post a Comment