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.

Example:-
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.

Normalization

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
Normalization usually involves dividing a database into two or more tables and defining relationships between the tables.

What are different normalization forms?
The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF). In practical applications, you'll often see 1NF,2NF, and 3NF along with the occasional 4NF. Fifth normal form is very rarely seen.4th and 5th nomal forms won't be discussed in this article.


First Normal Form (1NF)-Basic rules for organizing database
1.Eliminate duplicative columns from the same table.
Example:
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
TblCustomer(CustomerNo,FirstName,LastName,AddressId)
TblAddress(AddressId,Country,State,City).

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