NORMALIZATION

NORMALIZATION:

Normalization is a process of organize data in database.

To remove data redundancy we use normalization.

NORMALIZATION FORMS:-

• First Normal Form(1NF)
• Second Normal Form(2NF)
• Third Normal Form(3NF)

First Normal Form(1NF):-

• In the First Normal Form first rule is to break data into smaller logical pieces.

I have created the student table which stores the student data like Roll_No, Name, etc.

STUDENT TABLE

I will find out those names whose surname is Sharma.

QUERY AND RESULT

Also, I will find out those names whose surname is Sharma but don’t have a middle name.

It is difficult to find out the result because of the database design. so we apply 1NF i.e. name is broken into F_Name, M_Name, and Surname.

1st Rule of 1NF

After applying the first rule searching is faster and a query is simpler.

QUERY AND RESULT

• The second rule of 1NF is to Avoid duplicate data.

In this query, I will display those names whose Class is 10th.

It will display only one record but it doesn’t display another record because of the data entry problem. By avoiding duplicate data we apply the second rule of 1NF.

RESULT

We can create another table that is Class. And create a relationship between the class table and the student table via the foreign key.

CREATE A CLASS TABLE

CLASS TABLE

APPLY FOREIGN KEY

2nd Rule of 2NF

After applying the second rule remove duplicate records.

QUERY AND RESULT

Second Normal Form (2NF):-

• In the second Normal Form, the first rule is table is a must in the First Normal Form.
• The Second rule is all column data should depend on the primary key.

If added extra subject in the subject column then it will be updated to a per-student it is not good. This problem to solve by applying 2NF.

The subject is not directly connected to a student. So I will create three tables i.e. Syllabus table, Subject table, and Subject_Syllabus table.

In database design shown the relationship between the tables.

DATABASE DESIGN

The 2NF second rule is the subject is dependent on the syllabus, not a roll number.

QUERY AND RESULT

Third Normal Form (3NF):-

• In the Third Normal Form, the first rule is table is a must in the Second Normal Form.
• The second rule of 3NF is No column should depend on other columns.

TABLE

If we implement the 3NF we can delete the Average column because the Average depends on the marks and subject.

3NF

If you are new to database learning-SQL Server recommended is the following must-watch video: -