This tutorial is written for students who still have some notions about Functional Dependencies and Normal Forms.
Thus I will not explain what these concepts means, because you can find their definition in books and on the web.
I write this article only for the ones that have to deal with Normal Forms exercises and want a quick, fast and easy way to understand what is the Highest Normal Form of a specific Relational Schema.
HOW TO CHECK THE HIGHEST NORMAL FORM:
I have created this simple empirical algorithm after a lot of exercise about Normal Forms, so all the knowledge I have in this topic are “zipped” inside of it.
The Algorithm is divided in 4 Steps, every Step is used to find a grade of Normal Form.
In every step you have to ask yourself the written question, in this way you understand if it is in a specific Normal Form.
Remember only a thing: Every NF (Normal Form) is a subset of a NF with higher grade, so that, if a Table is in 2NF, then it is also in 1NF. Another example, if a Table is in BCNF, then it is also in 3NF, 2NF and 1NF. But if you have studied you know it.
So, let’s start.
STEP 1 | Check 1NF
Are all Attributes values Atomic?
In 99% of the cases, the answer is YES.
If YES, go to Step 2.
If NO, the Table is NOT in 1NF.
STEP 2 | Check 2NF
*Non-Key-Attribute = Attribute that is not inside the Candidate Key (e.g., CK = AB, NKA = C)
**Partial Candidate Key = A set of attribute that are subset of a Candidate Key (e.g., CK = AB, PCK = A)
Is there a Non-Key-Attribute* that depends from a Partial Candidate Key?**
If NO, go to Step 3.
If YES, the Table is in 1NF.
STEP 3 | Check 3NF
Depends every Non-Key-Attribute from the Candidate Key?
If YES, go to Step 4.
If NO, the Table is in 2NF.
STEP 4 | Check Boyce Codd NF (BCNF)
Depends every Attributes (including Key-Attributes) from the whole Candidate Key?
If YES, the Table is in BCNF.
If NO, the Table is in 3NF.
The Algorithm is finish. You can now try it on your exercises. Thank you for your attention. 🙂
For student who have some problems, I will also post below some examples of Relational Schemas that are in some Normal Forms.
- Candidate Key = BD
- R = (A,B,C,D)
- B -> C
- D -> A
- Candidate Key = A
- R = (A,B,C)
- A – > B
- B -> C
- Candidate Keys = JK and JL
- R = (J,K,L)
- JK -> L
- L -> K
- Candidate Key = AB
- R = (A,B,C,D)
- AB -> C
- AB -> D
I hope you like this article, please Share it, Comment it and Follow my Facebook Page 🙂