Types Of Normal Forms : 1NF, 2NF, 3NF, BCNF, 4NF and 5NF

Types of Normal forms : 

Normalization rule are divided into following normal form :

1. First Normal Form (1NF)
2. Second Normal Form (2NF)
3. Third Normal Form (3NF)
4. BCNF
5. Fourth Normal Form (4NF)
6. Fifth Normal Form (5NF)

1. First Normal Form (1NF) :

A Relation is said to be in First Normal form if it satisfy the following conditions :

1. Remove the Redundancy
2. stabelies the Primary Key
3. All the key attributes are defined
4. All the attributes in a table must have atomic domains (or values).

As per First Normal Form, no two Rows of data must contain repeating group of information i.e each set of column must have a unique value, such that multiple columns cannot be used to fetch the same row. Each table should be organized into rows, and each row should have a primary key that distinguishes it as unique. For example suppose an Online shop stores the customers details in a table named customers that has four attributes : cust_id for storing customer's id, cust_name for storing customer's name, cust_address for customer's address, cust_mobile for customer's mobile numbers. 


at the above table two customers John and jack are having two mobile numbers so the table schema stored them in the same field as we can see in the table. So this table is not in 1NF as the rule says 'All the attributes in a table must have atomic domains', the cust_mobile values for customers John and jack violates that rule. To make the table complies with 1NF we should have the data like this :



2. Second normal Form (2NF) :

A Relation is said to be in Second Normal Form if it satisfy the following conditions :

1. Relation must be in First Normal Form.
2. Relation must not contain any partial dependency.

Partial Dependency : When an attribute is dependent partially on a primary key (or condidate key), it is called partial dependency.
Prime attribute : An attribute, which is a part of the candidate-key, is known as a prime attribute.
Non-prime attribute : An attribute, which is not a part of the prime-key, is said to be a non-prime attribute.

To be in second normal form, a relation must be in first normal form and relation must not contain any partial dependency. A relation is in 2NF, if it has No Partial Dependency, i.e., no non-prime attribute (attributes which are not part of any candidate key) is dependent on any proper subset of any candidate key of the table. For example, consider the below given table 'students' which have four attributes: student_id, student_name, course_name and course_id. Since a student can enrolled for more than one course, so the table can have multiple rows for the same student.

 At the above table

Candidate keys : student_id, course_id
Non-key attributes : student_name, course_name

The table students is in 1NF because each attribute has atomic values. However, it is not in 2NF because according to the rule, non-key attributes, i.e. student_name and course_name must be dependent upon both and not on any of the prime key attribute individually. But we find that student_name can be identified by student_id and and course_name can be identified by course_id independently. This is called partial dependency, which is not allowed in Second Normal Form. To make the table complies with Second Normal Form (or 2NF) we can break it in two tables like this:


Now the tables comply with Second Normal Form (2NF).


3. Third Normal Form (3NF) :

A Relation is said to be in Third Normal Form if it is satisfy the following conditions :

1. It must be in Second Normal Form.
2. No non-prime attribute is transitively dependent on prime key attribute.

Transitive Dependency : A Functional Dependency is said to be transitive if it is indirectly formed by two functional dependencies. For example if X->Y and Y->Z are two FDs (Functional Dependency) then A->C is called transitive dependency.

Third Normal form implies that every non-prime attribute of table must be dependent on primary key, or we can say that, there should not be the case that a non-prime attribute is determined by another non-prime attribute. So this transitive functional dependency should be removed from the table and also the table must be in Second Normal form. For example, consider the below table 'student_details' which have four attributes : student_id, student_name, city, state and zip.


In this table student_id is primary key, but city and state is depends upon Zip. The dependency between zip and other fields is called transitive dependency. Hence to make the table complies with Third Normal Form (or 3NF), we need to move city and state to new table, with Zip as primary key.


Now the tables comply with Third Normal Form (3NF).


4. Boyce Codd Normal Form (BNCF) :

Boyce-Codd Normal Form (BCNF) is an extension of Third Normal Form on strict terms. A table is said to be in Boyce Codd Normal Form or BNCF if it is satisfy the following conditions :

1. It must be in Third Normal Form.
2. For each non-trivial functional dependency, X → Y, X must be a super-key.

Non-trivial functional dependency : If a functional dependency X->Y holds true where Y is not a subset of X then this dependency is called Non-trivial Functional Dependency.
Super-key :  A super-key is a set of one or more attributes that collectively identifies an entity in an entity set.

BCNF is stricter than Third Normal Form. A table complies with BCNF if it is in Third Normal Form and for every functional dependency X->Y, X should be the super key of the table. For example, consider the below table 'student_details' which have five attributes : stud_id, stud_name, course, course_type and stud_grade_on_course.


Now the functional dependencies in the above table are :

        stud_id -> stud_name
        course -> { course_type, stud_grade_on_course } 


And the candidate keys on the above tables are stud_id and course. The table is not in BCNF because neither stud_id nor course alone are keys. So to make the table comply with BNCF we can break the table into three tables :


Now the functional dependencies are :

In first table :       stud_id -> stud_name
In second table :  course -> course_type
in Third table :    stud_id -> stud_grade_on_course  

And the Candidate keys are :

In first table :       stud_id
in second table :  course
in third table :     {stud_id, course} 

Now in all functional dependencies left side part is a key, hence above tables complies with Boyce Codd Normal Form or BNCF.


5. Fourth Normal Form (4NF) :

A Relation is said to be in Fourth Normal Form if it is satisfy the following conditions :

1. It should meet all the requirement of BNCF (Boyce Codd Noraml Form).
2. It does not contain more than one multi-valued dependency.

Fourth Normal Form is builds on the first three normal forms (1NF, 2NF and 3NF) and the Boyce-Codd Normal Form (BCNF). It states that, in addition to a database meeting the requirements of BCNF, it must not contain more than one multi-valued dependency. To understand it clearly, consider a table course_details:


at the above table, there are three attributes : course, instructor and textbook. If we observe the data in the table above it satisfies Third Normal Form. But instructor and textbooks are two independent entities here. There is no relationship between instructor and textbook. In the above example, either Josiah white or Brian moore can be the instructor for Programming course, and Programming course instructor can refer either "Programming Book 1" or "Programming Book 2".  i.e.

           course ->> instructor
           course ->> textbook

This is a multi-valued dependency on SUBJECT. If we need to select both instructor and textbook recommended for any of the course, it will show up (instructor, textbook) combination, which implies lecturer who recommends which book. This is not correct. To eliminate this dependency, we divide the table into two as below:


Now if we want to know the instructor name and textbook recommended for any of the subject, we will fire two independent queries. Hence it removes the multi-valued dependency and confusion around the data. Thus the table is in 4NF.


6. Fifth Normal Form (5NF) :

A Relation is said to be in Fifth Normal Form if it is satisfy the following conditions :

1. It should meet all the requirement of Fourth Normal Form (4NF).
2. If an attribute is multi-valued attribute, then it must be taken out as a separate entity.

Fifth Normal Form is also knows as Project-Join Normal Form (PJ/NF). It is designed for reducing the redundancy in relational databases. A table is in 5th normal form if it cannot be made into any smaller tables with different keys (most tables can obviously be made into smaller tables with the same key!). For example, consider the below table course_details :


At the above table 1st Batch has taught about Programming, Data Structure and Operating Systems and 2nd Batch has only taught about Data Structure. In above table, Ethon hunt takes both Programming and Data Structure classes for 1st Batch, but he does not take Data Structure class for 2nd Batch.  In this case, combination of all these 3 fields is required to identify a valid data. Imagine we want to add a new Batch - 3rd Batch, but do not know which course and who will be instructing that course. We would be simply inserting a new entry with Batch as 3rd Batch and leaving course and instructor as NULL. As we discussed above, it's not a good to have such entries. Moreover, all the three columns together act as a primary key, we cannot leave other two columns blank!

Hence we have to decompose the table in such a way that it satisfies all the rules till 4NF and when join them by using keys, it should yield correct record. Here, we can represent each Instructor's Course area and their Batches in a better way. We can divide above table into three - (Course, Instructor), (Instructor, Batch), (Course, Batch).


Now, each of combinations is in three different tables. If we need to identify who is Instructing which Course to which Batch, we need join the keys of each table and get the result. Hence there is no lose or new data, thus the table is in 5NF.


No comments:

Post a Comment