How are tables normalized and what are 1NF, 2NF and 3NF ?
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