DBMS: Normalization

Normalization:

• Normalization is the process of transformation of the conceptual schema of the database into a computer represent table form.
• Normalization is the process of removing the redundancies from incoming data. 
• Normalization is a technique to which helps the user to group the data and place the data in a table. 

Normalization is a process which ensure the inconsistencies are not introduced into the database.
Need of Normalization: we know with the time, most of databases grow time to time by adding new relations and relationships, the data may be used in different ways. Regularly the information may undergo series of updations in such situations, the performance of a database is entirely dependent upon its design.

    

A bad  database design  may lead to certain undesirable things:

• Repetition of information
• Inability to represent certain information 
• Loss of information

Uses of Normalization:

• When data is large and scattered
• There is no defined group of data 
• Data is too complicated

Types of Normalization:

• 1 NF
• 2 NF
• 3NF
• BCNF

First Normal form: A relation schema is said to be 1 NF if every value is atomic present in the table.

This relation (Student) IS not 1NF because SUBJECT is not an atomic attribute as below:

STUDENT

ROLL NUMBER	NAME	SUBJECT
001	RAM	DBMS, DAA
002	SHYAM	DBMS, DAA
003	VISHNU	DAA
004	KRISHNA	CN

 

 

This relation STUDENT which is in 1NF is shown below alongwith the redundancy in ROLL NUMBER attribute:

ROLL NUMBER	NAME	SUBJECT
001	RAM	DBMS
001	RAM	DAA
001	RAM	OOPS
002	SHYAM	DBMS
002	SHYAM	DAA
003	VISHNU	DAA
004	KRISHNA	CN

Second Normal Form: A relational schema is said to be in second Normal form if it is in the  first Normal form and if if all non key attributes are fully functionally dependent on the primary key. 

2 NF summarised as:

• In 1 NF
• Includes no partial dependencies
• Still possible to show transitive dependencies

Before we know about the partial dependency we must know what candidate key, prime attribute and non prime attribute.

Prime attribute: prime attributes are the attributes which are the parts of candidate key.

Non Prime Attributes: non prime attribute are the attributes which are not the part of candidate key.

Candidate key: candidate key is the set of one or more attributes which can  determine all the attributes of given table. For example:

We have relation r = (ABCDEF) with FD's (functional dependency)

1. A => BCDEF

2. B => ADEF

3. B => F

4. D => E

The candidate key of this relation are:

[ A+] = ABCDEF

[ BC+] = ABCDEF

[B+] = BF

[D+] = DF

Here A closure [A+] and BC closure [BC+] are only candidate key of this relation R as only they can determine all the attributes of the relation.

Here we have dependency B=> F. It is a partial dependency because F is determined by B only which is a prime attribute but not by complete candidate key BC , so it is partially dependent.

To remove the partial dependency of the above relation R we will break this relation into two relations R1 and R2.

R1 (A,B,C,D,E)                     R2 (B,F)

Now R1 and R2 both are in 2 NF.

FD'S in R1.                           FD's in R2
A => BCDE                             B=> F
BC=> ADE
D => E

Third Normal Form: relation is in 3nf if it is in 2nf and Must not contain transitivity or
Should not have trivial functional dependencies.
Example:
We have relation (ABCDEF) with FD's
1. A=> BCDE
2. BC=> ADE
3. D =>E
find whether this relation is in 3 NF or not if not then convert it into 3 NF.
We first find super key of the relation:

[A+] => { ABCDE}
[ BC+] => { ABCDE}
[D+] => {DE}

Hair A and BC are super Key.
For FD, D=>E neither D is super Key nor it is a prime attitude. So the condition for 3 Nf is violated because of E. To convert into 3 NF we divide R into two relations separating  E from R.with E take D also because E  is dependent upon D, so the relation are:

R1 (A,B,C,D).                          R2 ( D,E)
FD's in R1                               FD's in R2
A=> BCD.                                 D=> E
BC=> AD                               

Boyce codd normal form: relation is in BCNF if it follows the following conditions:

• Should be in 2NF
• No transitive functional dependency
• Non trivial functional dependency
• If functional dependency is non trivial and is in form of

X => A

• Then X is a super key or candidate key

Example:
Suppose a relation R =( ABC ) has following FD's;
1. AB => C
2. C => B
Candidate key of the above relation are:
(AB+) => ABC
(AC+) => ABC

Here the functional dependency AB => C is not a problem because AB is prime attribute and C is non prime attribute. But in the dependency C=> B C is non prime attribute but b is is prime attribute which is not possible in BCNF. If C will be super Key then it will be in BCNF. Hence the given relation is not BCNF to make this relation BCNF we divide it into Two relation R1 and R2.

R1 = (CB)                                     R2 = (AC)
FD's in R1.                                  FD's in R2
C => B.                                         A =>C
C is superkey now.                   A is superkey now
So, R1 is BCNF.                          So, R2 is in BCNF