Browse By

How To Check if a Relation is in BCNF, 3NF, or Both

Another note of my database lecture class regarding Normalization and checking whether a relation is in BCNF, 3NF, or both.

The textbook that I use is “Database Management System” by Ramakrishnan and Gehrke and though it is a very comprehensive textbook, it is not that easy to understand. The discussion about BCNF, and 3NF was so wordy and has few examples.

So this is my way of making notes that will help myself on the final exam later, and I hope it can help you also understanding the BCNF and 3NF relation.

BCNF Relation

Probably you’ve heard the definition of Boyce-Codd Normal Form, and let’s repeat it again:

A relation in in BCNF if for every non-trivial FD X → A, X is a superkey.

Now, this doesn’t help at all, does it? But an example will surely help:

Example #1 :

The steps are :

  1. Find the candidate keys (We will not discuss how to find candidate keys here).
  2. Check if all the FD satisfies the definition.

For example #1:

  1. The candidate key is only one, that is AB.
  2. Fortunately that AB is also our left hand side FD (AB → CD), so the relation is in BCNF.

Example #2 :
E → A
D → A

  1. Candidate key : BE.
  2. Unfortunately, all the left hand side FDs does not include BE, so the relation is not in BCNF.
3NF Relation

Third Normal Form (3NF) is a bit more relaxed form compared to BCNF. Let’s see the definition:

A relation is in 3NF if for every non-trivial FD X → A, X is a superkey or A is part of some key for R.

Once again, it doesn’t help much until we see the example. So let’s go back to our previous examples.

Example #1 :

The steps are the same with checking for BCNF.

  1. Candidate key is AB, and
  2. Our only FD’s left hand side is equal to that candidate key, so it is in 3NF.

Honestly, you don’t need to check for 3NF for the first example, because all relation that is in BCNF is also in 3NF. 😀

Example #2 :
E → A
D → A

  1. Candidate key : BE.
  2. Let’s check the FDs one by one:
  • E → A : this is not ok, because E is not a candidate key, and A is not part of BE.
Do you have examples of relation that is not in BCNF but it is in 3NF?

Sure I have!

Example #3:
D → A

Let’s check this relation for both BCNF and 3NF.

BCNF check:

  1. Candidate keys : ABC and BCD.
  2. Check the FDs:
  • ABC → D : ABC is a candidate key.
  • D → A : D is not a candidate key, so it is not in BCNF.

3NF check:

  1. Candidate keys : ABC and BCD.
  2. Check the FDs:
  • ABC → D : ABC is a candidate key.
  • D → A : D is not a candidate key, BUT A is part of candidate key (ABC), so it is ok.

Since both are okay, this relation is 3NF.

That’s all there is in regards to determining whether a database relation is in BCNF or 3NF, it’s not that confusing at it appears to after all.

Questions and critics are welcome! And if you have a relation example that you are not sure of, you can ask in the comments section. 😀

30 thoughts on “How To Check if a Relation is in BCNF, 3NF, or Both”

  1. Nirav says:

    hi..its really helpful.. i have doubt in finding out candidate key in following type of relation:

    will you plz tell me how to find out candidate key for above??

    1. djitz says:

      Hi Nirav,

      First, the process to find a candidate key will be a bit long one so I will write it briefly here. I’ll have a separate blog post for finding candidate keys within this week. 😀
      Since you’re having question for an interesting example here, I also suppose that you have an idea about finding a candidate key from the given relation and functional dependencies.

      The candidate keys I got for the relation you posted are : A, E, BC, and CD
      How I got these key? Since all the attributes are on both sides, so we must try each attribute one by one, starting from single attribute.
      For single attributes, we can find that A closures are all attributes, and E closures are all attributes.
      Next for two attributes, we will also find that BC closures are all attributes, and CD closures are also all attributes.

      We don’t need three attributes test because they will contain both the single attribute and two attributes candidate keys.

      I hope it answers your question! 😀

  2. RandomSpectrum says:

    I believe the best method to find the candidate key(s) is by doing the 4 step:

    1. Attribute on no sides
    2. Attribute on right side but not left side.
    3. Attribute on left side but no right side.
    4. Both sides

    Then from 1 and 3, you get the main attributes that your candidate key should have. You try it out (trialKey+) and if it works, that is your only candidate key. If it fails you try add to it the attributes in step 4 one by one and group by group and also removing the attributes in step 2.

    So if your main key derived from 1 and 3 is let’s say AB. And in step 2 you have C and in step four you have DE.

    Then the next tries for your candidate keys would be :

    ABD, ABE, ABDE. You do not include C.

    Anyways, been working on a small application that should work on this algorithm. Make it easier for others.

    1. djitz says:

      hi RandomSpectrum,
      Thank you for your comment on the method you use to find the candidate keys, it’s actually a bit different with the method I know. I will try the method you posted and compare it with some examples. I’ll let you know the result. 😀

  3. NXN says:

    Good job!

    Last year i spent a lot of time to find out EXACTLY the way you wrote above. I wish you had posted this 1 year ago LOL. What a pity !

    To find out the key, my method is testing all the k-combinations , k is the number of element in R.