Explain normal forms with simple example?

Answer 1

Normal Forms

First Normal Form: Every field of the table must be atomic(indivisible)i.e there must be no composite or multivalued fields.Eg.Consider a table Employee such that it has 5 fields Name,Age,Sex,TelephoneNo,we represent it as

Employee(EmpNo,Name,Age,Sex,Tno).

Empno Name Age Sex Tno

1001 A 24 M 234567,678901

1002 B 25 F 123456

Clearly, the above table is not in 1NF as the field Tno may contain more than one values because each employee may have more than one Telephones.

To make it in 1NF,however, we can do some improvisations,

First Improvisation

Add an extra field "Tno2", our table Employee now looks like Employee(EmpNo,Name,Age,Sex,Tno1,Tno2)

Second Improvisation

Repeate the remaining field values for each Telephone value

Empno Name Age Sex Tno

1001 A 24 M 234567

1001 A 24 M 678901

1002 B 25 F 123456

As you can see both these improvisations have added extra information storage overhead with them. So the most recommended one is the DECOMPOSITION.

Here we will Decompose our erroneous table into more tables.

Employee1(EmpNo,Name,Age,Sex)

Employee2(EmpNo,Tno1,Tno2)

Now our tables will look like this

Employee1

Empno Name Age Sex

1001 A 24 M

1002 B 25 F

Employee2

Empno Tno1 Tno2

1001