15 December 2010

Boyce-Codd normal form

It is slightly higher version Of 3 NF.
A relation is in Boyce-Codd Normal Form (BCNF) if every determinant is a candidate key.

Conisder table TblEmployee with columns(employee_id, first_name, last_name).
In this table, the field employee_id determines first_name and last_name. Similarly, the tuple (first_name, last_name) determines employee_id.


What is normalization?
Normalization is the process of efficiently organizing data in a database. 

There are two goals of the normalization process: 

--->eliminating redundant data

--->storing data in efficient manner

What are different normalization forms?

First Normal Form (1NF)-Basic rules for organizing database
1.Eliminate duplicative columns from the same table.
Consider the table TblDevelopmentTeam with columns-TeamLeader,Developer1,Developer2,Developer3)
but what about the team which have only 2 devloper,3rd column is wasting the size plus if in future what if the number of developer increases.,so thats y we have to modify it as
TblDevelopmentTeam (TeamLeader,Developer)
By this we will get the flexible way for data storage.
2.Each Column must be single valued.
Example Break address as country state city....
3.Each table must have a primary key.

Second Normal Form (2NF)-Eliminate redundant data
Every Non Key attribute must be functionaly dependent upon the primary key.
Example:- Consider The TblCustomer with Columns(CustomerNo,FirstName,LastName,Country,State,City).
In above table if 2 customers reside in the same city Contry,State and city repeats for both with same values,which results redundancy.
we simply know that this 3 things are not depend on the customer,
so remove them from here,create a separate table and create relationship between 2 tables...
It will be like this

Third Normal Form (3NF)-Eliminate redundant data
Every Non Key attribute should be independent of other non-key attribute.
Example:-Consider the table TblCustomer with Columns
(CustomerNo,OrderNo,CityId,Qty,UnitPrice,Total)--Total is depend on UnitPrice and qty. so remove it