June 16, 2014

What is database normalization?

Normalization

Normalization is the process of efficiently organizing data in a database and is used for eliminating redundant data and ensuring data dependencies make sense (only storing related data in a table).

Normalization Forms

First Normal Form (1NF)
A database is said to be in First Normal Form when all entities have a unique identifier or key, and when every column in every table contains only a single value and doesn't contain a repeating group or composite field.
e.g
------------------------------
Genre Movies
------------------------------
Action A1, A2, A3
Romance R1, R2
------------------------------

We rearrange it to convert it in First Normal Form
------------------------------
Genre Movies
------------------------------
Action A1
Action A2
Action A3
Romance R1
Romance R2
------------------------------

Second Normal Form
A database is in Second Normal Form when it is in First Normal Form plus every non-primary key column in the table must depend on the entire primary key, not just part of it, assuming that the primary key is made up of composite columns.

That is, every non primary column should be fully functionally dependent on primary key column. When a non primary key column is partially dependent on primary key then it is known as partial dependency. In 2nd normal form this partial dependency is removed.
e.g
------------------------------------------------------------------------------
Employee_Name Project_Type Employee_ID Project_ID
------------------------------------------------------------------------------

We rearrange it to convert it in Second Normal Form

table 1:
------------------------------------------
Employee_Name Employee_ID
-----------------------------------------
table 2:
----------------------------------
Project_ID Project_Type
---------------------------------


Third Normal Form
A database is in Third Normal Form when it is in Second Normal Form and each column that isn't part of the primary key doesn't depend on another column that isn't part of the primary key. This is known as Transitive Dependency, which is a situation where a non-key column of a table is dependent on another non-key column.
e.g
------------------------------------------------------------------------------------
Student_ID Student_Name Course_Name Course_Duration
------------------------------------------------------------------------------------

>table 1
------------------------------------------------------------
Student_ID Student_Name Course_Id
------------------------------------------------------------

>table 2
------------------------------------------------------------
Course_Id Course_Name Course_Duration
------------------------------------------------------------

-K Himaanshu Shukla...

No comments:

Post a Comment