Difference between 1NF and 2NF in DBMS

1. First Normal Form (1NF) :
For any relation to be in the first normal form (1NF), the relation should not contain any composite or multi-valued attribute. So a relation will be in first normal form if it contains atomic values. The relation should contain only single valued attributes. Thus a relation that is in the first normal form must follow the following rules:

  • There should be no repeating groups or elements in the relation, i.e., it should contain only single valued attributes.
  • There should be a unique name to be specified for each attribute within the table.
  • It should not contain any composite attributes.

Example:
Consider the following relation:

Roll Number Student Name Marks
1 Abhay 96
2 Amit 78
3 Ayushi 86


This relation is in 1NF as it does not contain any multi-valued or composite attributes.



2. Second Normal Form (2NF) :
The basic concept of second normal form is full functional dependency. It is thus applicable to the relations which contain composite keys(where the primary key consists of more than one attributes).So any relation which contains a single attribute primary key is always in 2NF (second normal form). Thus the relation which contains a composite primary key in order to be in 2NF should not contain any partial dependency. Partial dependency occurs when any non prime attribute is dependent on any of the proper subsets of the candidate key .Thus every non prime attribute should be dependent on whole of the every cadidate key in the relation. Thus a relation is in 2NF if:



  • It is in 1NF(first normal form).
  • It does not contain any partial dependency.

Example:
Consider the functional dependencies for the relation R(X, Y, E, F).

{XY->EF, E->F} 

We thus find the closure of (XY) which is {X, Y, E, F}
Since its closure contains all the attributes in the relation thus XY is the candidate key.For each functional dependency, i.e., XY->EF:
It does not contain any partial dependency as the non prime attributes depend on the whole of candidate key.
E->F: It does not contain any partial dependency as here the non prime attributes depend on each other only.



Difference between 1NF and 2NF :

S.NO. 1NF 2NF
1. In order to be in 1NF any relation must be atomic and should not contain any composite or multi-valued attributes. In order to be in 2NF any relation must be in 1NF and should not contain any partial dependency.
2. The identification of functional dependency is not necessary for first normal form. The identification of functional dependency is necessary for second normal form.
3. First Normal form only deals with the schema of the table and it does not handle the update anomalies. Second normal form handles the update anomalies.
4. A relation in 1NF may or may not be in 2NF. A relation in 2NF is always in 1NF.
5. The primary key in case of first normal form can be a composite key. The primary key in case of second normal form cannot be a composite key in case it arises any partial dependency.
6. The main goal of first normal form is to eliminate the redundant data within the table. The main goal of second normal form is to actually ensure the data dependencies.
7. The first normal form is less stronger than the second normal form. The second normal form is comparatively more strong than first normal form.

Don’t stop now and take your learning to the next level. Learn all the important concepts of Data Structures and Algorithms with the help of the most trusted course: DSA Self Paced. Become industry ready at a student-friendly price.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.