Normalization with example

Normalization is the process of breaking a complex huge attributed table into small tables. Normalization is just a database design technique to reduce redundancy and anomalies. The theory of normalization was proposed by the creator of the relational model, Edgar Codd.

Advantages of normalization are:

  • Reduces data redundancy.
  • Easier to maintain the data.
  • Anomalies are eliminated.
  • Database efficiency is increased.

But there are some disadvantages too.

  • Difficult to retrieve the data.
  • Too much normalization can reduce the efficiency.
  • Requires the deep understanding of the business rules.
  • There will be more tables to handle.

Now, let us see an example of unnormalized form to third normal form.

normalization

UNF

Un-Normalized Form is the attributes clustered in one table and no normalization rule is applied. It has attributes with repeating groups.

STUDENT RECORD (Student number, Student name, Contact phone number, Major, {Course number, Course title, Instructor name, Instructor location, Grade})

1NF

Also, First Normal Form is the divided tables from which repeating groups have been removed.

STUDENT (Student number, Student name, Contact phone number, Major)

GRADE (Student number, Course number, Course title, Instructor name, Instructor location, Grade)

2NF

And Second Normal Form is the normalization where partial functional dependencies have been removed.

STUDENT (Student number, Student name, Contact phone number, Major)

GRADE (Student number, Course number, Grade)

COURSE  (Course number, Course title, Instructor name, Instructor location)

 3NF

Third Normal Form has transitive dependencies removed from the second normal form.

STUDENT (Student number, Student name, Contact phone number, Major)

GRADE (Student number, Course number, Grade)

COURSE  (Course number, Course title, Instructor name)

INSTRUCTOR (Instructor name, Instructor location)

Leave a Reply

Your email address will not be published. Required fields are marked *