Web Analytics Made Easy - Statcounter

Technology with Vivek Johari

SQL Normalization SQL Server

Second Normal Form (2NF)

Byvivekjohari

Jan 26, 2010 #Second Normal Form (2NF), #SQL Basic Concepts, #SQL Normalization

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.

Trending
Advantages of SQL Stored Procedure

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.

Discover more from Technology with Vivek Johari

Subscribe to get the latest posts sent to your email.

By vivekjohari

I am currently working as a Senior Database Professional and have around 18 years of experience in database. Degree:- Master Degree in Computer(MCA) Certification course in Data Science & Machine Learning from Indian Institute of Technology (IIT), Delhi Work experience:- Designing of the database. Database Optimization. Writing Complex Stored Procedures,Functions,Triggers etc. Designing and developing SSIS & DTS packages. Designing SQL Reports using SSRS. Database Server Maintenance. Certification:- MCTS: DA-100: Analysing Data with Microsoft Power BI MCTS: DP-300: Administering Relational Databases on Microsoft Azure Microsoft certified Sql DBA in Sql server 2008 (MCTS). Microsoft certified BI professional in Sql server 2008 (MCTS). Oracle certified profession DBA in ORACLE 10g (OCP) certified profession DBA in ORACLE 9i (OCP) My other publication Technical Blog:- Technologies with Vivek Johari Guest Author and Blogger at sqlservercentral.com

Related Post

Miscellaneous SQL Server

🎉✨ Happy New Year 2025! ✨🎉

Jan 1, 2025 vivekjohari
SQL Server

Database Partitioning in SQL Server

Dec 31, 2024 vivekjohari
SQL Server

What are the important python libraries?

Dec 31, 2024 vivekjohari
36 thought on “Second Normal Form (2NF)”

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

    Reply

  4. 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

    Reply

  5. 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

    Reply

  11. … [Trackback]

    […] There you can find 95500 more Information on that Topic: techmixing.com/2010/01/second-normal-form-2nf.html […]

    Reply

Leave a Reply

You missed

Miscellaneous SQL Server

🎉✨ Happy New Year 2025! ✨🎉

January 1, 2025 vivekjohari No Comments
SQL Server

Database Partitioning in SQL Server

December 31, 2024 vivekjohari No Comments
SQL Server

What are the important python libraries?

December 31, 2024 vivekjohari No Comments
SQL Server

Understanding Estimated and Actual Query Plans in SQL Server

December 31, 2024 vivekjohari No Comments

Discover more from Technology with Vivek Johari

Subscribe now to keep reading and get access to the full archive.

Continue reading