What is Second Normal Form with examples | 2NF with Examples

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.

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 No	Department No	Employee Name	Department
1	101	Ram	ORACLE
2	102	Rajiv	SQL
3	101	Shiva	PL 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 No	Department No	Employee Name
1	101	Ram
2	102	Rajiv
3	101	Shiva

Table 2:Department table

Department No	Department
101	ORACLE
102	SQL

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.

Amit S

Oracle Consultant with vast experience in Oracle BI and PL/SQL Development. Amiet is the admin head of this website who contributes by preparing tutorials and articles related to database technologies. He is responsible to manage the content and front-end of the website.