Home » DBMS

Difference Between 1NF and 2NF in DBMS

DBMS | First Normal Form (1NF) Vs. Second Normal Form (2NF): In this tutorial, we will learn about the first and second normal forms (1NF and 2NF), and the differences between 1NF and 2NF with the help of examples. By IncludeHelp Last updated : May 29, 2023

What is First Normal Form (1NF)?

Any database table is to be in the first normal form if the table does not contain any multi-valued attribute or composite attribute. So a database table will be in first normal form if it contains only atomic values i.e. the database table must have only single-valued attributes.

Thus a relationship that is in the first normal form must follow the following constraints:

• There must be no repeating elements in the database table. Hence, it should contain only single-valued attributes not multi-valued or composite attributes.
• There must be a unique name to identify each attribute in the database table.

First Normal Form (1NF) Example

Let's consider below mentioned student database table

ST_ID	ST_NAME	ST_PHONE	ST_STATE
101	Ram	8877665544	MP
102	Rex	8574783832	Maharashtra
103	Kapil	7390372389	Delhi

From the above-mentioned student database table, it is depicting that there is not any multivalued or composite attribute. Thus the given table is in first normal form (1NF).

What is Second Normal Form (2NF)?

1NF meets the relationship, and no partial functional dependencies are present. That is, the Primary Key attribute is functionally dependent on each non-key attribute. The primary key attribute must be a composite primary key attribute to produce a Partial Functional Dependency. A Composite Primary Key is a primary key consisting of 2 columns or more.

There are several conditions for keeping the second normal form of the database table. These are the following -

• In the 2NF row, the table must be 1NF.
• In the second normal type, all non-key attributes depending on the primary key are completely functional.
• Hence, there must not be partial functional dependencies.

Second Normal Form (2NF) Example

Let's assume that teachers' table data and the subjects they teach can be stored by a school. A teacher can teach more than one subject in the school.

TEACHER table:

TEACHER_ID	SUBJECT	TEACHER_AGE
T1	Hindi	45
T1	Science	45
T2	Maths	35
T3	English	28
T3	SST	28

The non-prime attribute TEACHER AGE in the provided table is based on TEACHER ID, which is the right subset of a candidate key. That is why it violates the 2NF law.

We split it down into two tables to translate the given table into 2NF:

TEACHER_ID	TEACHER_AGE
T1	45
T2	35
T3	28

TEACHER_SUBJECT table:

TEACHER_ID	SUBJECT
T1	Hindi
T1	Science
T2	Math
T3	English
T3	SST

What are the differences between 1NF and 2NF in DBMS?

Since both 1NF and 2NF are the normalization forms in the database management system still they have differences. The following are the main differences between 1NF and 2NF normalization forms:

S.NO.	1NF	2NF
1	The database table must contain atomic value.	The database table must be in 1 NF.
2	The database table must not contain any composite or multi-valued attributes.	The database table must not contain any partial dependency.
3	The first normal form is used to eliminate the redundant records in a database table.	The second normal form is used to make sure about the data dependencies in a database table.
4	The database table which is in the first normal form may be in 2 normal form.	The database table which is in the second normal form is always be in 2 normal form.
5	In the first normal form, the identification of functional dependence is not required.	In the second normal form, the identification of functional dependence is required.
6	The primary key may be a composite key in the case of the first normal form.	In the second normal form, the primary key will not be a composite key if any partial dependence exists.