Normalization and it's types in details..

 

Normalization

The process of decomposing large and complex table into the simple and normal table is called normalization. It is a process to present the database in a normal form to avoid undesirable things such as repetition of information, inability of represents information, loss of information etc. it improves performance of database by reducing data redundancy in large extent.

 Concept of normalization

 Data normaliz ation is the process to present database in a normal form to avoid undesirable things such as repetition of information, inability to represent information, loss of informatin etc. it improves performance by reducing data redundancy to a large extent. 

EF C odd has introduced few rules for normalizing the database in 1970 and these rules are known as normal forms. This process minimizes and controls the duplication of data in a database and also provides a rapid search for data from database. 

Basic rule for  normalization

•  The attribute values in a relational table should be functionally dependent on.
•  The primary key value.
• No repeating groups allowed in relational tables. 
• A relational table should not have attributes involved in a with the primary key

 Normalization is used to avoid 

Duplication of data 

Insert anomaly: A record about an entity cannot be inserted into the table without first inse of information about another entity, we cannot enter a customer with a sales order.

Delete anomaly: A record cannot be deleted without deleting a record about a related entity. Eg: we cannot delete a sales order without deleting all of the customers' information.

Update anomaly: cannot update information without changing information in many places. Eg: to update a customer information, it must be updated for each sales order the customer has placed.

 Unnormalized database:

Normalized database:

Advantages of normalization

1. It reduces data redundancy (duplication of data)
2. It improves faster sorting and indexing.
3. It simplifies the structure of the database table.
4. It improves the performance of a system.
5. It avoids loss of information.

Normal Forms

Let us consider a following unnormalized table

1 NF ( First Normal form)

A relation or table is said to be in 1NF if its attributes are atomic. That is, there should not be repeating groups of an attribute. The purpose of 1NF is to eliminate repeating groups of attribute in an entity.

1 NF (First Normal Form) Rules

1. Each table cell should contain a single value.
2. Each record needs to be unique

B) 2NF (Second Normal form)

1. Be in 1NF
2. Single Column Primary Key that does not functionally dependent on any subset of candidate key relation
3. Eliminated partial key dependencies.
4. Functional dependency: the value of one attribute in a table is determined entirely by value of another
5. Parital dependency: A type of functional dependency where an attribute is functional dependent on only part of the primary key

 (primary key must be a composite key)

Hence, above table can be decomposed as:

Name →depends on roll no and class

Subject →only depends on class

Subject marks depends on nam and subject_name.

3NF (Third Normal Form)

1. Rule 1-Be in 2NF
2. Rule 2-Has no transitive functional dependencies
3. Transitive dependencies: A transitive dependencies is one in which, among three attributes, A, B, and C, if A→B, B→C, then C→A.

Author Spotlight

Santosh Chapagain
Gmail: chapagainsantoshcs@gmail.com
Phone no: +977-9863512955