• Tweet
• Sumo

• Tweet

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 :
ABCD
AB → CD

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 :
ABCDE
AB → CD
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 :
ABCD
AB → CD

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 :
ABCDE
AB → CD
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:
ABCD
ABC → D
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. 😀