Normalization is a technique to reduce the data redundancy and organizing the data in the database.

Normalization is a systematic approach of decomposing tables to eliminate data redundancy (repetition) and undesirable characteristics like Insertion, Update and Deletion Anomalies.

Normalization divides larger tables into smaller tables and links them using relationships.

Normalization rules are divided into the following normal forms:

  • First Normal Form

  • Second Normal Form

  • Third Normal Form

  • BCNF

  • Fourth Normal Form

 

First Normal Form (1NF)

Normalization divides larger tables into smaller tables and links them using relationships.

  • It should only have single (atomic) valued attributes/columns.

  • The attribute Domain should not change.

  • All the columns in a table should have unique names.

  • The order of the stored data doesn’t matter.

1NF EXAMPLE

Figure 44.1.EXAMPLE OF 1NF

 

UNDERSTANDING KEY

A KEY is a value used to uniquely identify a record in a table. A KEY could be a single column or combination of multiple columns

Note: The other Columns in a table that are NOT used to identify a record uniquely are called non-key columns.

 

Primary Key

A primary is a single column value used to identify a database record uniquely.

The following are the characteristics of a PRIMARY KEY

  • A primary key value must be unique

  • A primary key cannot be NULL

  • The primary key values should rarely be changed

  • A Primary key should be defined in the initial stages of creating a table

  • We are required to provide a value of the primary key whenever a new record is inserted in the table.

 

COMPOSITE KEY

A composite key is a primary key composed of multiple columns used to identify a record uniquely.

Figure 44.2. COMPOSITE KEY

In our database, we can have many people with the same name, but they live in different places. Hence, we require both Full Name and Address to identify a record uniquely. That is a composite key.

 
 

Foreign Key

Foreign Key references the primary key of another Table! It helps connect our Tables. The following are the characteristics of a Foreign Key

  • A foreign key can have a different name from its primary key

  • It ensures rows in one table have corresponding rows in another

  • Unlike the Primary key, they do not have to be unique.

  • Foreign keys can be null.

Transitive functional dependencies?

A transitive functional dependency is when changing a non-key column, might cause any of the other non-key columns to change

 
 

Second Normal Form (2NF)

The following are the rules for a table to be in the Second Normal Form

  • Rule 1- Be in 1NF

  • Rule 2- Single Column Primary Key

 

2NF EXAMPLE

Figure 44.3.EXAMPLE OF 2NF

We have divided our 1NF table into two tables viz. Table 1 and Table2. Table 1 contains all the travel Information whereas Table 2 contains Travel information based on TravelId.

We have introduced a new column called TravelId which is the primary key for table 1. Records can be uniquely identified in Table 1 using TravelId

Figure 44.4.EXAMPLE OF 2NF (Primary Table Broken down into TravelId & Placetravelled)

 

Third Normal Form (3NF)

A table is said to be in the Third Normal Form when,

  • It is in the Second Normal form.

  • And, it doesn't have Transitive Dependency.

 

3NF EXAMPLE

Figure 44.5.EXAMPLE OF 3NF

We have divided our tables and created a new table which stores Salutations. There are no transitive functional dependencies, and hence our table is in 3NF.

In Table 3 Salutation ID is primary key, and in Table 1 Salutation ID is foreign to primary key in Table 3.

Figure 44.6.EXAMPLE of 3NF (Primary Table Broken down into TravelId & Placetravelled)

Figure 44.7. Salutation Table

 

BCNF (Boyce-Codd Normal Form)

BCNF is also referred as 3.5 Normal Form.

Even when a database is in 3rd Normal Form, still there can be scenarios where anomalies can result if it has more than one Candidate Key.

 

4NF (Fourth Normal Form) Rules

If no database table instance contains two or more, independent and multivalued data describing the relevant entity, then it is in 4th Normal Form.

 

5NF (Fifth Normal Form) Rules

A table is in 5th Normal Form only if it is in 4NF and it cannot be broken down into any number of smaller tables without loss of data.

 

6NF (Sixth Normal Form) Proposed

6th Normal Form is not standardized, yet however, it is being discussed by database experts for some time.

 
 
 
 

©2020 by Pavan Lalwani. 

  • Udemy
  • Facebook
  • Twitter
  • YouTube
  • Linkedin