Home > DeveloperSection > Interviews > What is normalization?

Posted on    September-27-2016 5:07 AM

 Database Database  SQL Server 
Ratings:
 1 Answer(s)
  115  View(s)
Rate this:

Abhishek Srivasatava
Abhishek Srivasatava

Total Post:70

Points:350
Posted on    September-27-2016 5:07 AM

Normalization :

Normalization is a process of effectively organizing the column and table of a database to reduce data redundancy and improve data integrity.

 

Normal form of a table

Prsn_empl_id

Prsn_dpnd_id

Plan opted

669911

123456

Health , Dental

669901

987415

Vision

 

1NF

First Normal Form (1NF)

All attributes in a relation must have atomic domains.

In this scheme there should be no repeated element in single block.

For example:

 

Prsn_empl_id

Prsn_dpnd_id

Plan opted

669911

123456

Health

669911

123456

Dental

669901

987415

Vision

 

2NF

Second Normal Form (2NF)

for implementing this scheme it must satisfied 1st scheme.

Before moving further one should know about the prime attribute and non-prime attribute.

Prime attribute: the entire element which is a part of the prime key.

Non-prime attribute: the entire element which is not part of the prime key.

For this scheme, non prime attribute should be functionally dependent on prime key.


1NF table 

Prsn_empl_id

Prsn_dpnd_id

Plan opted

669911

123456

Health

669911

123456

Dental

669901

987415

Vision

 

Modified to 2NF

Prsn_empl_id

Prsn_dpnd_id

669911

123456

669901

987415

 

 

Prsn_empl_id

Plan opted

669911

Health

669911

Dental

669901

Vision

 

3NF

It must satisfy 2NF criteria and there should no relationship between primary key and non primary element data.

For example we have table like this

Name

Addr_id

Address type

Address

A

10

Permanent

Abc

A

5

Temp

Pqr

B

10

Permanent

Xyz

B

5

temp

Efg

 

We can divide above table in two

Name

Addr_id

Address

A

10

Abc

A

5

Pqr

B

10

Xyz

B

5

Efg

 

Addr_id

Address type

5

Temp

10

Permanent

 

Boyce-Codd Normal Form

It is developed by the Raymond F Boyce.

X Y is a trivial functional dependency (Y X)

X is a super key for schema R

Normal table

Employee_id

Name

DOB

Car

Car_id

price

1001

A

12/01/1993

U

997

11M

1002

B

15/01/1993

X

997

12M

1003

C

17/01/1993

Y

997

13M

1004

D

20/01/1993

Z

997

14M

 

Converting to Boyce-Codd Normal Form

Employee_id

Name

DOB

1001

A

12/01/1993

1002

B

15/01/1993

1003

C

17/01/1993

1004

D

20/01/1993

 

Car

Car_id

price

U

997

11M

X

996

12M

Y

995

13M

Z

994

14M

 

Employee_id

Car_iD

1001

997

1002

996

1003

995

1004

994

 


Modified On Sep-27-2016 05:08:37 AM

Don't want to miss updates? Please click the below button!

Follow MindStick