Second Normal Form | 2NF with Examples | 2nd Normal Form

In my previous article i have given the information about First Normal Form with real life examples.In this article i would like to give you Second Normal Form with real life industry examples. We need to check all the normal forms to make your database normalized.This article gives you information about 2NF in depth.The Second Normal Form data must be in First normal form and all non-key attributes are fully functional dependent on the primary key.

What is Second Normal Form?

In this section I would like to explain the Second Normal Form with real life examples.The data is in 2NF if following conditions are completed by data :

1.The data should be in First Normal Form (1 NF)

2.The data is in 2NF where data does not have any non-prime attribute which is dependent on any of proper subset of any candidate key of the relation.

Normalization is the process of organizing the data in a database so that it will meet following requirements :

1.There is no redundancy of data.

2.Data dependencies are logical.

Normal Forms

2nd Normal Form With Example :

The data is said to be in 2NF If,

1.It is 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.

Example:

Let us consider following table which is in first normal form:

Employee NoDepartment NoEmployee NameDepartment
1101RamORACLE
2102RajivSQL
3101ShivaPL SQL

In above example we can see that department .Here We will see that there is composite key as{ Employee No,Department No}.Employee No is dependent on Employee Name and Department is dependent on Department No.We can split the above table into 2 different tables:

Table 1:Employee_NO table

Employee NoDepartment NoEmployee Name
1101Ram
2102Rajiv
3101Shiva

Table 2:Department table

Department NoDepartment
101ORACLE
102SQL

Now we have simplified the table in to 2NF where each entity of table is functionally dependent on primary key.

I hope you like this article on 2NF. If you like this article or if you have any issues with the same kindly comment in to comments section.

Leave a Reply

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