How to Find Candidate Key(s)

As my final exam on Database Management Course closely approaching, I think I’m a bit forced (in a good way) to write some notes on the topics I’ve learned so far. This time it’s the notes on how to find candidate key of a given relation.

Lately after I started post my database notes on conflict and view serializability, and BCNF and 3NF check, I see increased visitors traffic to my blog. Some visitors actually visited my blog to find how to find candidate keys of a relation. I just have the time now to write this post, hopefully it’s not really too late.

Enough for the introduction, get your pencil and paper, let’s find that candidate keys!

Pencil and Paper? Can you just give me an App?

Sure, here it is:
http://www.koffeinhaltig.com/fds/kandidatenschluessel.php

If you don’t need to understand the process behind finding the candidate keys you can stop reading this blog post now. For those of you who still want to know how to find it manually, read on.

How to find it manually

Finding candidate keys is just as simple as applying some algorithm here and there. Here I post examples of different cases.

First example

WHOSE
WH -> S
HOS -> E

Steps:
1. Find the attributes that are neither on the left and right side
> (none)

2. Find attributes that are only on the right side
> E

3. Find attributes that are only on the left side
> WHO

4. Combine the attributes on step 1 and 3
> since step 1 has no attributes, it’s just WHO

5. Test if the closures of attributes on step 4 are all the attributes
> in our case, yes it is. Because with WH we can get S, and by HOS, we can get E.
So we have only one candidate key, that is WHO.

Second example

ABCDEFG
AB -> F
AD -> E
F -> G

Steps:
1. C
2. EG
3. ABD
4. ABCD
5. The closures of ABCD is ABCDEFG, so ABCD is our candidate key this time.

Third example

ABCD
ABC -> D
D -> A

Steps:
1. –
2. –
3. BC
4. BC
5. The closure of BC is only BC, we should find the relation exterior.
6. Find the relation exteriors, that is the attributes not included in step 4 and step 2.
> in this example it is AD
7. Now test the closures of attributes on step 4 + one attribute in step 6 one at a time.
> ABC closures are ABCD, so it is a candidate key
> BCD closures are ABCD, so it is also a candidate key
> so in this case we have two candidate keys, they are ABC and BCD.

Hoahm… these are too easy, harder examples please..

Allright, ready for some harder examples, here are they:

Hard example #1

ABCDEF
DF -> C
BC -> F
E -> A
ABC -> E

Hard example #2

ABCDE
A -> BC
CD -> E
B -> D
E -> A

Conclusion

This is not the only algorithm of finding candidate key. Another way includes to find the attributes that are on both sides (see comment of RandomSpectrum on my other blog post to know about it). I suppose both algorithms are correct, it’s just a matter of personal preference.

I’m not going to post the answers here for the hard examples just to make it a bit harder. You can find it on different post: answer.


Author: Trijito Santoso

I’m Trijito Santoso, a Seventh-Day Adventist, a medical technology graduate, and a software developer. The reason why I shifted from medical technology to computer science is because I love to create things (design, software, articles, anything), and being a software developer allows me to create things everyday. I’m currently studying Master of Science in Computer Science at Northeastern University, Boston. My Google Profile+

39 thoughts on “How to Find Candidate Key(s)”

  1. Thank you a lot!

    I’ve studied with a book by Ulman, and the book never give that how to find candidate key!!!!

    You just save me lol

  2. thanks very very useful
    i have been frustated over BCNF & 3NF for 3 days
    these notes helped me a lot to understand the concept.

  3. i also need to check wheather a relation is in 1NF
    & 2NF .kindly if you can post the solutions of the above.
    i am thankful to you.
    please
    thanking you in advance

  4. Hi can you please mail me how to find the candidate keys for the harder ones?
    I am stuck with the first one.
    And in the second one Step 1 to Step 4 do not have any attributes. How to proceed?
    Why did you make it “harder” ? 🙂
    You should have posted the solutions for harder ones too 🙂

    1. Hi Ankush, I’ve just sent the answer to your email.
      Looking at where you got the problem, you’re doing right so far, you only need to know how to tackle this problems.

      I purposely not posted the solutions so that people will give their try first. 😀

  5. hiiii
    please forward me the tricks for the harder ones.
    i have a exam on 18th.
    and if i get to know the trick … that’ll be great help

    thanks & regards
    soura

  6. Thanks for this explaination, I have my database exam approching and was frustrated with these concepts as I would be confused everytime… U r wonderful….

  7. U r my god!!!! Thanks a ton!!! This approach was very helpful!!! Your posts on Candidate keys and Normal Forms are quite enough for me to crack my final exam!!!

  8. Hi djitz , yr step are better than what my lecturer had taught (exhaustive search). Can i have the answers for the harder question please ?

    1. @sean,
      It’s been awhile since I wrote this note, but I believe if the attributes are only on the right side, then they can’t be part of candidate key(s). 😀

  9. thanks a lot..ur post is really so useful ,tomorrow i have exam and now im really ready for any questions like this..
    harder #1: (DBEC) (DBEF) are candidate keys.
    harder #2: (DA) (DC) (DE) are candidate keys.
    thanks a lot again..

  10. Hiii,

    Very nice way to find out the candidate key…
    Thanks a lot 🙂 😀

    Pls send me the answers for the hard ones …

  11. great work man..!!!! i was jst digging out the internet for a good explanation of finding candidate keys … also on 3rd NF .. nd i got it from .. u !!! thnx frnd ..:)

  12. Sir in ur hardr problem 2nd we hav a A and E as candidate key then why we calculate more candidate key without combination of A and E

Leave a Reply

Your email address will not be published. Required fields are marked *