Normalization and Normal Forms - IBPS IT Officers Preparation Materials - DBMS

sponsored links

The Process of making a table (or often we call it as Record) NORMAL is called Normalization :P Ok in other words, we can say that the Normalization is the process of simplifying the Table :)

Good definitions... Aren't they ??? ;) But you will get Size Zero marks if you write the above definitions in exams. Lets be lil TECHNICAL.

The design method which helps us to MINIMIZE the redundancies of data (repetitions) and reduces the errors / mistakes in the table is called Normalization. 

Note : To perform this Normalization Process, we should analyze the INTER DEPENDENCIES of the attributes in the tables and should take the subsets of the larger tables to form the Smaller tables. 

In this process, we apply various Normal Forms to the given Database (Table). These Normal Forms helps us to break the BIG tables into smaller ones. Now lets have a look at these Normal Forms...

1st Normal Form : 

  * In 1st Normal Form we deal with the REDUNDANCY of ROWS.

In first Normal Form, each ATTRIBUTE must be ATOMIC. There shouldn't be any repetitions in the Rows. and There shouldn't be any multivalued columns. 

The advantage of the 1st Normal Form is "The queries become easier"

The 1NF eliminates GROUPS by keeping them into different (separate) Tables and connecting them with One to Many relationship. 

2nd Normal Form :

* A table or entity should be in the first normal form, if you want to apply the 2nd Normal form on that.

Note : The first normal form deals with the repetition (redundancy) of data in Rows (Horizontal) , where as the 2nd Normal form deals with the redundancy of the data in Columns (Vertical). 

As I already said, A table should be in the First Normal form if you want to make sure that it is in 2nd normal form. Some more additional conditions are,

• It shouldn't have a COMPOSITE primary key. (I mean, the key SHOULD NOT be divided into sub parts (logical entities).
• In 2nd Normal Form, each attribute should FUNCTIONALLY Dependent on the primary key. 
• What is this Functionally Dependence? 
• If one or more attributes determines UNIQUELY the values of other attributes, then that property is called as Functionally Dependence.
• If you find any Non Dependent Attributes, you should move them into Smaller Tables.
• 2NF helps us to improve the data Integrity (Error free)

3rd Normal Form :

* A table or entity should be in the Second Normal Form (so obviously, it should be in the first normal form too :P) if you want to apply 3rd Normal form on that. Some more conditions are,

• If the table is in 3rd Normal Form, then it should be Transitive dependencies free.....
• What is this Transitive Dependence?
• If there exists TWO separate entities in ONE table. Obviously we should move them into smaller tables.
• 3nf also further improves the integrity of the data. 

Boyce - Codd Normal Form : (Not Boys Code as some of us are thinking :P )

• It is the ADVANCED version of the 3rd Normal Form (3NF). 
• A row in a table is in BCNF, if every determinant of it is a Candidate Key. (Most of the entities of 3rd Normal form are already in Boyce - Codd Normal Form)
• Why should we use BCNF if already 3NF is there?
• The 3rd Normal Form misses the inter dependencies between Non Key attributes. 
• Simple we can say that, a 3NF is not in BCNF if,
• There are number of Candidate Keys
• The keys are composed of multiple attributes
• There are more than one attribute between the Keys.......

4th Normal Form :

* ok ok , Obviuosly it should be in the 3NF if you want to call it 4NF. In addition, we should eliminate Trivial Multivalued Dependencies... I mean, our table shouldn't have multiple sets of Multi Valued Dependencies. 

5th Normal Form :

* It should be in the 4th Normal Form...

• In this, we should eliminate the dependencies, not determined by the Keys.
• I mean, every dependency should be the consequence of its Candidate keys...

Thats all for now Friends. Read more materials of IBPS IT Officers Here. All The Best