Normalization

by E-Computer Concepts November 24, 2019 at 12:45 pm

Normalization is a design technique that is widely used as a guide in designing relational databases. Normalization is essentially a two step process that puts data into tabular form by removing repeating groups and then removes duplicated data from the relational tables.

Normalization theory is based on the concepts of normal forms. A relational table is said to be a particular normal forms that have been defined.

The goal of normalization is to create a set of relational tables that are free of redundant data and that can be consistently and correctly modified. This means that all tables in a relational database should be in the third normal form (3NF).

Normalization is a technique to remove or reduce redundancy from a table

NORMALIZATION

Two Types of Redundancy

There are two types of redundancy in normalization:

1. Row Level Redundancy

IDNAMEAGE
1ANURADHA32
2PREETI27
1ANURADHA32

In this table 1st and 3rd row is same called row level redundancy.

SOLUTION : Row level redundancy is removed by PRIMARY KEY

2. Column Level Redundancy

SIDS_NAMECIDC_NAMEFIDF_NAME
1ANURADHAC1HEALTHCAREF1RAM
2PREETIC2DBMSF2LAXMAN
3KIRANC1MATHF1RAM
4CHANDNIC1MATHF1RAM

Problem Occured in Normalization

  • Insertion Anomaly (special problem occurred in special case)
  • Deletion Anomaly
  • Updation Anomaly

Types of Normal Form

First Normal Form

A relation which contains a repeating group called an unauthorized relation. Removal of repeating groups is most important while trying to remove problems from relations. Relations without repeating groups are said to be in first normal form.

Definition of First Normal Form

A relation is said to be in first normal form (1NF) if it does not contain repeating groups.

In first normal form, table should not contain any multi-valued attribute.

Roll NoNameCourse
1ANURADHAC/C++
2PREETIDBMS
3CHANDNIASP.NET

CONVERTING IT TO 1st NORMAL FORM

Roll NoNameCourse
1ANURADHAC
1ANURADHAC++
2PREETIDBMS
3CHANDNIASP.NET

Second Normal Form

Table must be in 1st normal form.

Table should not contain any partial dependency in relation.

All the non-prime attribute should be fully functional dependent on candidate key.

CUSTOMER_IDSTORE_IDLOCATION
11CHANDIGARH
13MUMBAI
21CHANDIGARH
32BANGLORE
43MUMBAI

CANDIDATE KEY : Customer_ID, Store_ID

PRIME ATTRIBUTE : Customer_ID, Store_ID

NON-PRIME : Location

CONVERTING IT TO 2nd NORMAL FORM

CUSTOMER_IDSTORE_ID
11
13
21
32
43
STORE_IDLOCATION
1CHANDIGARH
2BANGLORE
3MUMBAI

Third Normal Form

Table must be in 2nd normal form

There should be NO TRANSITIVE DEPENDENCY in table.

ROLL NOSTATECITY
1PunjabMohali
2HaryanaAmbala
3PunjabMohali
4HaryanaAmbala
5BiharPatna

CANDIDATE KEY : Roll No

FUNCTIONAL DEPENDENCY : Roll No -> State, State -> City

PRIME ATTRIBUTE : Roll No

NON-PRIME ATTRIBUTE : State, City

Add Comment