- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Second Normal Form (2NF)
What is 2NF?
The second step in Normalization is 2NF.
A table is in 2NF, only if a relation is in 1NF and meet all the rules, and every non-key attribute is fully dependent on primary key.
The Second Normal Form eliminates partial dependencies on primary keys.
Let us see an example −
Example (Table violates 2NF)
<StudentProject>
StudentID | ProjectID | StudentName | ProjectName |
S89 | P09 | Olivia | Geo Location |
S76 | P07 | Jacob | Cluster Exploration |
S56 | P03 | Ava | IoT Devices |
S92 | P05 | Alexandra | Cloud Deployment |
In the above table, we have partial dependency; let us see how −
The prime key attributes are StudentID and ProjectID.
As stated, the non-prime attributes i.e. StudentName and ProjectName should be functionally dependent on part of a candidate key, to be Partial Dependent.
The StudentName can be determined by StudentID, which makes the relation Partial Dependent.
The ProjectName can be determined by ProjectID, which makes the relation Partial Dependent.
Therefore, the <StudentProject> relation violates the 2NF in Normalization and is considered a bad database design.
Example (Table converted to 2NF)
To remove Partial Dependency and violation on 2NF, decompose the above tables −
<StudentInfo>
StudentID | ProjectID | StudentName |
S89 | P09 | Olivia |
S76 | P07 | Jacob |
S56 | P03 | Ava |
S92 | P05 | Alexandra |
<ProjectInfo>
ProjectID | ProjectName |
P09 | Geo Location |
P07 | Cluster Exploration |
P03 | IoT Devices |
P05 | Cloud Deployment |
Now the relation is in 2nd Normal form of Database Normalization