NORMALIZATION IN DATABASE MANAGEMENT SYSTEM

NORMALIZATION:

 Normalization is the process of decomposing a relation(table) based on functional dependency and primary key.

A series of normal form tests can be carried out on individual relation and relations are decomposed into smaller relations to achieve degree of normalization.

Desirable Properties of decomposition…

• 1.       Lossless-join Decomposition

⇒When we decompose a relation into a number of smaller relations, then decide whether decomposition will be lossless or not.

   ⇒ Let R be a relation schema. Let F be a set of functional dependency on R.

    ⇒The decomposition is a lossless join decomposition of R at least one of the following functional dependencies are in F+:

• R1 intersection R2 -> R1
• R1 intersection R2 -> R2

1. 2.       Dependency Preservation

⇒Another property in database design is dependency preservation.

⇒Let F be a set of Functional Dependencies on schema R.

⇒Let (R1, R2, ……Rn) be a decomposition from of R.

⇒The restriction of F to Ri is that the set of all functional dependencies in F+ that includes only attributes of Ri, where F+ is the function Dependency closure that includes all possible set of function dependencies.

 

First Normal Form

⇒A relation is in 1 NF if the values in the domain of each attribute of the relation are atomic.

⇒Each cell of table must have single value, no two rows in a table may be identical.

 

Second Normal Form

⇒A relation R is aid to be 2NF if it is in 1NF and there should not be any partial dependency.

⇒Here, all non-key attributes are dependent on the key alone. No attribute is depending upon a part of the key. Any relation having a key with single attribute is in 2NF.

 

Third Normal Form

⇒A relation R is in 3NF if it is in 2 NF and has no transitive dependency.

⇒Here, all the non-key attributes are depend on the key alone, there should not be any dependency among the non-key attributes.

⇒A relation R is in 3 NF if it is holds for X ->A FD in R, either

-          X is a super key of R.

-          A is a prime attribute or key attribute of R.

It disallows transitive dependency.

 

BOYCE-CODD Normal Form (BCNF)

⇒A relation R is in BCNF if every determinant is a candidate key.

⇒Let X ->A is a funcational dependency, in relation schema R is in 3NF if X is a superkey of R.

⇒This is more restrictive than 3 NF.

⇒While decomposing relation to make them in BCNF we may lose some dependencies.

3NF is better than BCNF.

 

Fourth Normal Form

⇒A relation is in 4 NF if it is in BCNF and has no multivalued dependency,

 

Fifth Normal Form

⇒It deals with the join dependency. A relation R is in 5NF if it has no join dependency.

⇒Lossless join dependency; when we join the decomposed relation then we must get the original relation without any loss.

I hope you like our "http://studiousjyoti.blogspot.com/" collection. If it is helpful please share and do comments to let us know your opinion.