What is SQL Normalization?| SQL Normalization examples

In my previous articles i have given idea about normalization techniques as well as its examples. In this article i would like to give you some examples of SQL Normalization with explanation.In this article i want to explain different techniques of normalization with real world industry examples. The SQL normalization is nothing but technique of organizing unstructured data in structured way.

Few Definitions of SQL Normalization :

In this section i would like to give you some definitions of SQL Normalization.

Definition 1 :

The SQL Normalization technique is organization the data in structured way.

Definition 2 :

The Database Normalization is structured way to decompose the data to eliminate the redundancy and undesirable anomalies.

Definition 3 :

The database normalization is database design technique which is used to eliminate the redundancy and dependency of data.

These are some definitions of this technique.User need to take care of following things in database normalization.

1. To Eliminate the redundant or useless data
2. To Reduce the complexity of the data
3. To Ensure the relationship between tables as well as data in the tables
4. To Ensure data dependencies and data is logically stored.

SQL Normalization

SQL Normalization Examples :

In this section i would like to give you example of this normalization technique.I would like to give this example with SQL Commands so that user can implement it and start applying it.User needs to start with the table which is not normalization.Our motive is to make that table normalized. The actual work in industry is to design the database for specific application in normalized way. The normalization will convert the huge tables in to small tables.The database normalization will create fast,accurate and efficient data.

Step 1 : Create a table and insert data in to table named Employee .

Select * from Employee;

Employee ID	Employee Name	City	Department
1	Anthony	Markham	Oracle,SQL
2	Raju	Sydeney	OBIEE
3	Rony	NYC	SIEBEL
3	Peter	Chicago	SQL

Analysis done on this table :

The first thing is Employee ID column is not primary key. We need to check the Employee ID and update that Employee Id accordingly.We need to convert the data in to unique data. The Anthony is associated with two departments.

First Normal Form : Normalization step 1

The first step is to make the first table in to First Normal Form.To convert it in to first normal form user need to consider following things.

1.The first normal form contains only automic values.

The automic values are nothing but the single value in single cell. In above example Anthony is associated with two departments which are Oracle and SQL as well.

2.The record in table should be unique.Each and every record should be unique.There should not be no repeating group. To normalize the above table in First normal form we need to convert that table in to two different tables.

Create table Employee

(Employee_No Number(2),Employee_Name varchar2(30));

Insert data in to table

Select * from Employee;

Employee Table :

Employee No	Employee Name
1	Anthony
2	Raju
3	Rony
4	Peter

Create table Department

(Employee_No Number(2),Department varchar2(30));

Insert data in table

Select * from department;

Department Table :

Employee No	Department
1	Oracle
1	SQL
2	OBIEE
3	SIEBEL
4	SQL

We have divided the One table in to two different tables to convert it in to first normal form.

Second Formal Form : Normalization Step 2

The second step is to convert the table in to second normal form. Before converting the table in to second normal form in SQL normalization Let us check the following table which is in First Normal Form

Employee No	Department No	Employee Name	Department
1	101	Anthony	Oracle
2	102	Raju	COGNOS
3	103	Rony	SQL
4	101	Peter	Oracle

To convert the first normal form in second normal form user should check the following things,

1.The table should be in first normal form

2.There should not be any partial dependency of any column on primary key.Means the table have concatanated primary key and each attribute in table depends on that concatanated primary key.

3.All Non-key attributes are fully functionally dependent on primary key.If primary is is not composite key then all non key attributes are fully functionally dependent on primary key.

We need to convert the table in to two different tables .

Create table Employee_Number

(Employee_No number(2),Department_No Number(5),Employee_name varchar2(30));

Employee_Number table

Employee No	Department No	Employee Name
1	101	Anthony
2	102	Raju
3	103	Rony
4	101	Peter

Department table

Department No	Department
101	Oracle
102	COGNOS
103	SQL

These tables are created in Second normal form.In next section i would like to give you the examples with third normal form.

Third Normal Form : Normalization Step 3

In this section i would like to give you the Third Normal Form.The following are most important points needs to be considered in normalization.

1.It is in Second normal form

2.There is no transitive functional dependency

Transitive Dependency:

When table 1 is Functionally dependent on table 2. and table 2 is functionally dependent on table 3 then.table 3 is transitively dependent on table 1 via table 2.

Real Life Example :

Lets take following example.We need to convert following table in to third normal form.

Employee No	Salary Slip No	Employee Name	Salary
1	0001	Anthony	50000
2	0002	Raju	40000
3	0003	Rony	57000

1.The above table is in second normal form.

2.In above table Employee No determines the Salary Slip No.And Salary Slip no Determines Employee name.Therefore Employee No determines Employee Name.We have transitive functional dependency so that this structure not satisfying Third Normal Form.

For That we will Split tables into following 2 tables to convert the table in to third normal form :

Create table Employee

(Employee_No number(2),Salary_Slip_No Number(5),Employee_Name varchar2(30));

Insert data

Select * from Employee;

Employee table:

Employee No	Salary Slip No	Employee Name
1	0001	Anthony
2	0002	Raju
3	0003	Rony

Create table Salary

(Salary_Slip_No number(5),Salary Number(6));

Insert data

Select * from Salary;

Salary Table:

Salary Slip No	Salary
0001	50000
0002	40000
0003	57000

Few Advantages of Third Normal Form :

1.Amount of data duplication is removed because transitive dependency is removed in third normal form.

2.Achieved Data integrity

BCNF(Boyce-Codd Normal Form)

BCNF Normal form is higher version of third normal form.This form is used to handle analomies which are not handled in third normal form.BCNF does not allow dependencies between attributes that belongs to candidate keys.It drops restriction of the non key attributes from third normal form.

Third normal form and BCNF are not same if following conditions are true:

1.The table has 2 or more candidate keys

2.At least two of candidate keys are composed of more than 1 attribute

3.The keys are not disjoint.

Example:

Address-> {City,Street,Zip}

Key 1-> {City,Zip}

Key 2->{City,Street}

No non key attribute hence this example is of 3 NF.

{City,Street}->{zip}

{Zip}->{City}

There is dependency between attributes belonging to key.Hence this is BCNF.

These are some most important SQL Normalization examples with its explanation. These normalization techniques are used in SQL to convert simple table in to normalized form.I hope you will get idea about the SQL Normalization techniques.If you like this article or if you have any suggestions with the same kindly comment in comments section.