Database Normalization : 1NF, 2NF, 3NF, BCNF, 4NF

What is normalization?

Breaking down a table into functionally independent tables

Follows three-step procedures {first normal form, second NF, third NF}

Why normalization?

Reduce data redundancy, and thus data anomalies

1NF

1. All the key attributes are defined
2. There are no repeating groups in the table
3. All attributes are dependent on the primary key

2NF

1. It is in 1NF
2. It includes no partial dependencies

Partial dependency: attributes are dependent on only a part of a composite PK

Fig: Dependency Diagram

3NF

1. It is in 2NF
2. It includes no transitive dependencies

Transitive dependency: nonkey (nonprime) attributes are dependent on another nonkey attribute

BCNF

A Table is in BCNF when it is in 3NF and contains no non-key attribute that determines a key attribute.

– Convert to BCNF by exchanging the determinate with the key attribute and then resolving the partial dependency with a new table.

4NF

A Table is in 4NF when it is in 3NF table with no multi-valued dependencies : 

– Convert to 4NF by splitting into two tables to remove multi-valued dependencies

Examples of multi-valued dependencies

– StudentID,StName,Phones(Home,Work,Cell,Fax)

– StudentID,Addresses(permanent, mailing, current)

• Convert multi-valued phones into another table in 3NF & remove the phone data from the original.

Student(StudentID, StName,………..)

StuPhones(StudentID, PhoneType, Phone#)

• Reduce redundant descriptive data by creating a third table.

Phones(PhoneType, Description)