Second Normal Form (2NF)

Second Normal Form (2NF) :-A table is said to be in its Second Normal Form if it satisfied the following conditions:-

1) It satisfies the condition for the First Normal Form (1NF),

2) It do not includes any partial dependencies where a column is dependent only a part of a primary key.

For example suppose we have a table EmpProjDetail, which contains the employee details and its project details like projected, project name and durations in terms of days on which he/she is allocated to the project.

Table Name:-EmpProjDetail

Primary Key :- EmpId + projectid

EmpId ProjectId EmpName ProjectName Days
1 1 Vivek Abc Bank 35
2 2 Sudeep AbeBook 10

In this table, the primary key is composition of two columns EmpId and ProjectId. Now this table is in 1NF but it is not in the 2NF since the column EmpName can be depended on the column EmpId and the column ProjectName can be depended on the column ProjectId which violates the second condition for the 2NF.

We can break this table into three different tables to convert it into the 2NF. These tables are given below:-

Table name:- EmpDetails

Primary Key: – EmpId

EmpId EmpName
1 Vivek
2 Sudeep

Table name:- ProjDetails

Primary Key: – ProjectId

ProjectId ProjectName
1 Abc Bank
2 AbeBook

Table name:- EmpProjdetails

Primary Key: – empId + ProjectId

EmpId ProjectId Days
1 1 35
2 2 10

Now all the three tables are in 1NF and all the columns of these tables are fully depended on their respective primary keys.

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in SQL Normalization, SQL Server and tagged , , . Bookmark the permalink.

36 Responses to Second Normal Form (2NF)

  1. Anonymous says:

    Thank you for your simple and clear examples. Studied this in college a long time ago, this is a good refresher.

  2. Anonymous says:

    why the sound clip is in hurry way

  3. swapna jain says:

    vry easy example.
    Thanks

  4. Anonymous says:

    I have a doubt in 2NF it is said that "All attributes of an entity should be fully dependent on the whole primary key" . Then how come third table is said to be 2NF because it depends on composite key (combination of both empId + ProjectId) . Days allocated to a particular project need not depend fully on emp id it can also depend only on project id.can u please clarify by giving so more detailed explaination.I am newbie in SQL and now Iam still more confused

  5. Anonymous says:

    I have a doubt in 2NF it is said that "All attributes of an entity should be fully dependent on the whole primary key" . Then how come third table is said to be 2NF because it depends on composite key (combination of both empId + ProjectId) . Days allocated to a particular project need not depend fully on emp id it can also depend only on project id.can u please clarify by giving so more detailed explaination.I am newbie in SQL and now Iam still more confused

  6. Pingback: natural remedies

  7. Pingback: Get More Information

  8. Pingback: 바카라싸이트

  9. Pingback: xem keo online

  10. Pingback: https://biznesblog.biz.pl/forum/marketing-f5/opinie-o-funkymedia-t64.html

  11. Pingback: ww88

  12. Pingback: какво е лазерна епилация

  13. Pingback: cutting dies

  14. Pingback: buy credit cards online now

  15. Pingback: hemp oil

  16. Pingback: ÔÑßÇÊ ÔÍä ÚÝÔ Ýí ÇáØÇÆÝ

  17. Pingback: cbd

  18. Pingback: El día que mataron a Monseñor Romero

  19. Pingback: cornhole board decals

  20. Pingback: Dream Market Support

  21. Pingback: Vertical Roller Mill Market Poised for an Explosive Growth 2024

  22. Pingback: m88bet

  23. Pingback: Slager hengelo

  24. Pingback: megapoker

  25. Pingback: indo qq

  26. Pingback: www.jaguarqq1.site

  27. Pingback: Beard Oil

  28. Pingback: www.1lapakqq.site

  29. Pingback: warnetqq

  30. Pingback: www.2015louisvuittonoutlet.net

  31. Pingback: pressure washer to wash car

  32. Pingback: rajaqq

  33. Pingback: power washing md

  34. Pingback: เมไหน ไฟแรงเฟอร์ ซูม

Leave a Reply