First Normal Form (1NF)

DBMSDatabaseMySQL

What is 1NF

1NF is First Normal Form in DBMS, applied on an unnormalized table to make it normalized.  An unnormalized table has the following issues we need to avoid to get a perfect database design −

Problems with Unnormalized Table

Data Redundancy

Storing same data item multiple times is known as Data Redundancy.

Let us see an example −

We have an <Employee> table that has a field to store current address of Employees. Some of these employees given a place to live by the company; therefore, they have the same address. The address will be repeated in the database −

Table 1.1

EmpID

EmpName
EmpAddress
001
Amit
11, VA Street, New York
002
Tom
11, VA Street, New York
003
David
11, VA Street, New York
004
Steve
13, HG Block, Philadelphia
005
Jeff
13, HG Block, Philadelphia


To solve it, store the employee address in a separate table and point in the <Employee> table.

Updation Anomaly

It occurs when you face issues while updating a table.

Let us see an example −

Table 1.2

EmpID

EmpName

EmpAddress

EmpDept

001
Amit
11, VA Street, New York
A
002
Tom
11, VA Street, New York
B
003
Tom
11, VA Street, New York
C
004
Steve
13, HG Block, Philadelphia
D
005
Jeff
27, ZR Block, Virginia
E


If we need to update the address of the employee Tom, who is from two departments, then we need to update two rows. If we only update a single row, then Tom would be having two different addresses that would lead to inconsistencies in data.

Deletion Anomaly

Let’s say the company decide to close the Department D, then the deletion of row would also lead the deletion of employee Steve’s data.

Insertion Anomaly

It occurs when you try to insert data in a record that does not exist.

Normalization removes all the above issues/ anomalies and gives you a normalized perfect database design that a database administrator love.

Let us see how to normalize data using First Normal Form (1NF)

Why INF

First Normal Form (1 INF) is useful in removing the data redundancy issue and anomalies of a database as shown in the above section. All attributes in 1NF should have atomic domains. If it is not atomic, then your database is having a bad design. It is the first step in Normalisation.

Therefore, the foremost rule for a table to be in a normalized form is that it should have only atomic values.

Example

The below example has a table with every column consisting of atomic values, except the DeptRole column. Therefore, the DeptRole column violates the atomic value rule for Department A, B, and C.

Table 1.3

DeptId
DeptName
DeptRole
001
A
Finance, HR
002
B
Programming, Networking
003
C
Management, Marketing
004
D
Operations


Let us now correct it and convert to 1NF:

DeptId
Dept Name
DeptRole
001
A
Finance
001
A
HR
002
B
Programming
002
B
Networking
003
C
Management
003
C
Marketing
004
D
Operations


Now the above table is in 1NF and we have atomic values in every column as summarized in the following screenshot −

raja
Updated on 15-Jun-2020 13:01:32

Advertisements