Normalization in Database

In this article we give the information about normalization in a database design technique that organizes tables in a manner that reduce redundancy (useless) and dependency of data.

Normalization in Database:

• Normalization is a database design technique that organizes tables in a manner that reduce redundancy (useless) and dependency of data.
• To avoid insertion, update & deletion anomaly.
• Normalization divides larger tables into smaller table and link them  using relationships. (Primary key and Foreign key)

Anomaly (Insertion, Update, Delete)

Anomaly: Something that is not according to standard or normal

Table Name: student

Insert:

Insert into student (Roll_No, Name, Addrress) values (4, ‘Amol’, ‘Satara’);

Result: student does not allow null values

Update:

Update student set dept=’BCA’ where Roll_No=1;

Result: Update two rows

Delete:

Delete from student where Roll_No=1;

Result: Delete two rows

Normalization Types:

Here are most commonly used normal forms:

1. First Normal Form (1NF)
2. Second Normal Form (2NF)
3. Third Normal Form (3 NF)
4. Boyce & Codd Normal Form (BCNF)
5. Forth Normal Form (4NF)
6. Fifth Normal Form (5NF)

First Normal Form (1NF)

As per the rule of first normal form,

• An attributes (Column) of a table cannot hold multiple values.
• It should hold only atomic (forming a single unit) values.
• Each record needs to be unique.

Atomic value: An atomic value is a value that cannot be divided.

Table Name: student

Above table, column mobile number there are two mobile numbers in rows two and three so there is not an atomic value.

After First Normalization

Table Name: student

Second normal form(2NF):-

1. It should be in the first normal form.
2. All non key attributes are fully functional dependent on the primary key. In simple words it should not have partial dependency

A table or relation is in 2nd normal form when it meets all the requirements of 1st normal form and all non-key attributes are completely dependent on the primary key.

Table Name: Employee

Composite Primary Key: Emp_ID + Qualification

Non Key Attribute: Age

Example:

Q. Find the age of employee where employee ID is 102

SQL> Select * from Employee where emp_id=102;

O/P: 

102      BCom        21

102      MBA          21

here we get the two age values that is not in proper out put.

After Second Normalization

To make the table compiles with 2NF we can break it in two tables like this:

Here,

1st NF: 

• It should hold only atomic values.
• Each record needs to be unique.

2nd NF:

• Non key attribute should be depend on single key (that is primary key)

Third Normal Form (3rd NF):

• It is in second normal form.
• There is no transitive functional dependency.
• A transitive functional dependency is when changing a non-key column, might clause any of the other non-key column to change.

Here,

Emp_ID-> Zip-> City       that is City depend on zip and zip depend on Emp_ID

Emp_ID-> City                  City dependent on Emp_ID

City Indirectly depend on Emp_ID is called transitive functional dependency.

After Third Normalization;

Some More: 

POP- Introduction to Programming Using ‘C’

DS – Data structure Using C

OOP – Object Oriented Programming 

Java Programming

DBMS – Database Management System

RDBMS – Relational Database Management System

Join Now: Data Warehousing and Data Mining