Data Modeling - Normalization process (1NF,2NF,3NF)

Data Modeling - Normalization process (1NF,2NF,3NF)

What is Normalization ?
Normalization is the process of putting things right.

First normal form (1NF):
------------------------
Table faithfully represents a relation and has no "repeating groups"

Second normal form (2NF):
-------------------------
No non-prime attribute in the table is functionally dependent
on a part (proper subset) of a candidate key

Third normal form (3NF):
------------------------
Every non-prime attribute is non-transitively dependent on every key of the table

----------------------------------------------------------------------------------------

First Normal Form (1NF)
First normal form (1NF) sets the very basic rules for an organized database:

* Eliminate duplicative columns from the same table.
* Create separate tables for each group of related data and
identify each row with a unique column or set of columns (the primary key).


Second Normal Form (2NF)
Second normal form (2NF) further addresses the concept of removing duplication data:

* Meet all the requirements of the first normal form.
* Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
* Create relationships between these new tables and their predecessors through the use of foreign keys.

Third Normal Form (3NF)
Third normal form (3NF) goes one large step further:

* Meet all the requirements of the second normal form.
* Remove columns that are not dependent upon the primary key.

-----------------------------------------------------------------------------------------------------

Example:
-----------
Bookshelf: Title, Author,Publisher,Category

Create a Entity called BOOKSHELF, it consits of following columns: Title,Author1,Author2,Auther3,Publisher,Category1,Category2,Category3

BOOKSHELF: TITLE,PUBLISHER,CATEGORY1,CATEGORY2,CATEGORY3,RATING,RATING_DESC
BOOKSHEL_AUTHOR: TITLE,AUTHOR_NAME
AUTHOR: AUTHOR_NAME , Comments

Above step refers to 1NF. regrouping of repeated groups into respective table.

Create a RATING Table with RATING,RATING_DESC , this step is called 2NF.

Remove category1,Category2,Category3 to seperate table called Category Table.

Create CATEGORY table with CATEGORY_NAME,PARENT_CATEGORY,SUBCATEGORY , this step is called 3NF.