SQL Normalization| SQL Normalization with 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 Constraints

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 IDEmployee NameCityDepartment
1AnthonyMarkhamOracle,SQL
2RajuSydeneyOBIEE
3RonyNYCSIEBEL
3PeterChicagoSQL

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 NoSalary Slip NoEmployee NameSalary
10001Anthony50000
20002Raju40000
30003Rony57000

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 NoSalary Slip NoEmployee Name
10001Anthony
20002Raju
30003Rony

Create table Salary

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

Insert data

Select * from Salary;

Salary Table:

Salary Slip NoSalary
000150000
000240000
000357000

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.

2 Replies to “SQL Normalization| SQL Normalization with Examples”

  1. I cant understand Third Normal Form : Normalization Step 3 with example of Employee table. because this table also contains this Employee No, Salary Slip No,Employee Name columns which is transitively dependent.

    1. Hello Vishal,

      thanks for question.

      If you check the table all values are not transitive dependent to each other.So that it is in third normal form. You can follow the steps. If you have still issues with the same kindly send mail on compexsql@gmail.com

Leave a Reply

Your email address will not be published. Required fields are marked *