# How are tables normalized and what are 1NF, 2NF and 3NF ?

816    Asked by ROHITJhadav in SQL Server , Asked on Dec 28, 2019
Answered by ROHIT Jhadav

Consider a Guest house for working professionals, where a database is used to maintain the Tenant information.

Below table shows data without normalization implemented.

Tenant Name

Gender

Age

Location

Stay

Food Service

Add ons

JAY

MALE

25

MUMBAI

15 Days

No

AC, Refrigerator, Cupboard

ARYAN

MALE

32

PUNE

45 Days

Yes

Refrigerator, cupboard

KABIR

MALE

29

KOLKATA

30 Days

No

Cupboard

JAY

MALE

25

MUMBAI

90 Days

Yes

AC, Refrigerator, Cupboard

MAYA

FEMALE

24

CHENNAI

120 Days

No

Refrigerator, cupboard

Now, in order to normalize the table there are certain rules imposed.

1NF (First Normal Form)

Each record must be unique

Only a single value is allowed on each cell of a table.

The above given table consists of multiple values for the column "Add ons". Thus, we need to normalize the table as per 1NF to derive the First Normal Form.

Tenant Name

Gender

Age

Location

Agreement

Food Service

Add ons

JAY

MALE

25

MUMBAI

15 Days

No

AC

JAY

MALE

25

MUMBAI

15 Days

No

Refrigerator

JAY

MALE

25

MUMBAI

15 Days

No

Cupboard

ARYAN

MALE

32

PUNE

45 Days

Yes

Refrigerator

ARYAN

MALE

32

PUNE

45 Days

Yes

Cupboard

Accordingly, the table is normalized to achieve uniqueness of each record and removing multiple values on any cell.

2NF (Second Normal Form)

All in 1NF

Single column Primary Key

Tenant ID

Tenant Name

Gender

Age

Location

Agreement

Food Service

1

JAY

MALE

25

MUMBAI

15 Days

No

2

ARYAN

MALE

32

PUNE

45 Days

Yes

3

KABIR

MALE

29

KOLKATA

30 Days

No

4

JAY

MALE

25

MUMBAI

90 Days

Yes

5

MAYA

FEMALE

24

CHENNAI

120 Days

No

Table 1.

Tenant ID

Asset

1

Refrigerator

1

Cupboard

1

AC

2

Refrigerator

2

Cupboard

Table 2.

Here, we have divided the larger table into smaller ones and related them with column "Tenant ID", which is primary key for Table 1.

3NF (Third Normal Form)

All in 2NF

NO Transitive Functional dependency

If a value changed for a non-key column may result in change of value on another non-key column, the behavior is known as transitive functional dependency.

In the above example, if the tenant name is changed, it may result in the change of Gender value as well.

Tenant ID

Tenant Name

Gender ID

Age

Location

Agreement

1

JAY

1

25

MUMBAI

15 Days

2

ARYAN

1

32

PUNE

45 Days

3

KABIR

1

29

KOLKATA

30 Days

4

JAY

1

25

MUMBAI

90 Days

5

MAYA

2

24

CHENNAI

120 Days

Gender ID

Gender

1

MALE

2

FEMALE

3

Transgender

Similarly, the tables can be further normalized as per the need. Usually, tables are normalized upto 3NF