What is 1NF 2NF and 3NF in RDBMS

2012-02-07T20:02:00.000-08:00

Hi All,
Welcome to the Bhatoa's world of Oracle.
This article will give you walk through pertaining to database design concepts of Normalizations.

What is 1NF?
First normal form:
If you have a table then simply it should not have repeated columns having the same data and it should have primary key.

For Instance:
Table Name=Home
Column Names=Home_primary_key,Table,Chair1,chair2,chair3,chair4,door,window.

If you observe column names ..chair1,chair2,chair3,chair4 are repeated columns
To bring this table to 1NF form we have to have one primary key which is present and should not have repeated columns.
So now our column names would be after applying 1NF.
Home_primary_key,Table,Chairs,Door,Window.

What is 2NF or What is second Normal form?
Now we will discuss normalization with 2nd Normal Form.

2nd NF comprises of following conditions:
1)It should satisfy conditions of 1NF(Which is mentioned above).
2)All the repeating subset of data in the table should be seperated into other table with Primary Key and Foreign Key relationship between the existing table and the new table.
Basically reducing the redundancy and also adding foreign key.

For instance:

Table Name=Home
Column Names=Home_primary_key,Table,Chairs,door,window,zip_code,city,state.

Here zip_code,city,state are the columns which are getting repeated un-necessarily
thus increassing the size of disk and thus we can create a new table with these 3 columns and one
foreign key column.
Which will make this table 2nf.

What is 3NF?
Third Normal Form is very easy to understand if you have understood what is first normal form and what is second normal form.
Apart from satisfying 1nf and 2nf . We have to remove the columns that do not depend on Primary Key of the table.

For Instance:
If we have a table named "Invoice"
and suppose its columns are:
Item_id
item_name
item_cost
item_quantity
discount
Total

Now if we start to analyze the table:
It is having primary key as item_id
It does not have repetition of columns
It does not have subset of data in the table.
But it has one column Total which is not dependent on primary key.
So it can be removed from the table design and can be calculated in the queries itself whereever required.
New table design would be:
Invoice=table name
column names:
Item_id
item_name
item_cost
item_quantity
discount
I hope this artical is little bit helpful to clear your concepts on normalizations.

Normalization of Database Design

What is 1NF 2NF and 3NF in RDBMS