What is the difference between 1NF and 2NF in DBMS?

Here, we are going to learn about the First normal form (1NF), Second normal form (2NF), and the difference between the First normal form (1NF) and Second normal form (2NF).
Submitted by IncludeHelp, on November 20, 2020

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.

For example - let's consider below mentioned student database table

ST_ID ST_NAME ST_PHONE ST_STATE
101Ram8877665544MP
102Rex8574783832Maharashtra
103Kapil7390372389Delhi

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

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.

For 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_IDSUBJECTTEACHER_AGE
T1Hindi45
T1Science45
T2Maths35
T3English28
T3SST28

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_IDTEACHER_AGE
T145
T235
T328

TEACHER_SUBJECT table:

TEACHER_IDSUBJECT
T1Hindi
T1Science
T2Math
T3English
T3SST

Difference between 1NF and 2NF

S.NO.1NF2NF
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.





Comments and Discussions

Ad: Are you a blogger? Join our Blogging forum.





Languages: » C » C++ » C++ STL » Java » Data Structure » C#.Net » Android » Kotlin » SQL
Web Technologies: » PHP » Python » JavaScript » CSS » Ajax » Node.js » Web programming/HTML
Solved programs: » C » C++ » DS » Java » C#
Aptitude que. & ans.: » C » C++ » Java » DBMS
Interview que. & ans.: » C » Embedded C » Java » SEO » HR
CS Subjects: » CS Basics » O.S. » Networks » DBMS » Embedded Systems » Cloud Computing
» Machine learning » CS Organizations » Linux » DOS
More: » Articles » Puzzles » News/Updates


© https://www.includehelp.com some rights reserved.