First Normal Form (1NF)

1NF requires that each column in a table contains atomic values and that each
row is uniquely identified. This means that a table cannot have repeating groups
or arrays as columns, and each row must have a unique primary key.

Example

A table is in 1NF if each column contains atomic values and each row is uniquely
identified. For example, a table that lists customers and their phone numbers −

Customer ID Name Phone Numbers

1 John 555-1234, 555-5678

2 Jane 555-9876

3 Michael 555-5555

This violates 1NF because the Phone Numbers column contains repeating
groups.

To normalize this table to 1NF, we can split the Phone Numbers column into
separate rows and add a separate primary key column −

Customer ID Name Phone Number

1 John 555-1234

1 John 555-5678

2 Jane 555-9876

3 Michael 555-5555
First Normal Form (1NF)
First Normal Form (1NF) is the most basic level of normalization in a
DBMS. The rules for achieving 1NF are as follows:

1. Each table should have a primary key, which uniquely identifies

each record in the table.
2. Each column in the table should contain only atomic values,
which means that a single cell should contain a single value and
not a list of values.
3. There should be no repeating groups of data.

For example, let’s say we have a table named “Students” that stores
information about students in a school. A table that is not in 1NF might
look like this:

StudentID Name Subject Grade

1 John Smith Math, Science A

2 Jane Doe English, History B

StudentID Name Subject Grade

1 John Smith Math A

1 John Smith Science A

2 Jane Doe English B

2 Jane Doe History B

Second Normal Form (2NF)
Second Normal Form (2NF) builds upon the rules of First Normal Form
(1NF) by addressing the issue of partial dependencies. In 2NF, a table !
Cartial dependency exists when a non-primary key column depends on
only part of a composite primary key.

For example, let’s say we have a table named “Orders” that stores
information about customer orders. A table that is not in 2NF might
look like this:

OrderID CustomerID Product Quantity Price

1 1 A 2 10

2 1 B 1 20

3 2 A 3 10

In this table, the Price column depends on the Product column and the
primary key is composed of OrderID and CustomerID.

The table is not in 2NF because the Price column is functionally

dependent on only part of the primary key (Product) and not on the
whole primary key (OrderID and CustomerID).

To bring this table to 2NF, we need to separate the table into two
separate tables: one for the Orders and one for the Products.

Table: Orders

OrderID CustomerID Product Quantity

1 1 A 2

2 1 B 1

3 2 A 3
Table: Products

Product Price

A 10

B 20
Now, the Price column is dependent on the primary key of the Products
table (Product) and the Orders table has no partial dependencies. This
design is now in 2NF.

2NF eliminates partial dependencies and improves the data integrity

by reducing the data anomalies. However, it’s not enough to ensure
the data consistency and to avoid data anomalies, so it’s necessary to
move to the next normalization forms.

Third Normal Form (3NF)

3NF builds on 2NF by requiring that each non-primary key column in a table is
not transitively dependent on the primary key. This means that a table should
not have transitive dependencies, where a non-primary key column depends on
another non-primary key column.

Example
To explain 3NF further, let's consider an example of a table that lists customer
orders −

Order Customer Customer Customer Order Order

ID ID Name City Date Total

100 John Smith New York 2022-01- 100

1
01

101 Jane Doe Los Angeles 2022-01- 200

2
02

102 Bob Johnson San 2022-01- 300

3
Francisco 03

In this example, the non-primary key column "Customer City" is transitively

dependent on the primary key. That is, it depends on "Customer ID", which is
not part of the primary key, instead of depending directly on the primary key
"Order ID". To bring this table to 3NF, we can split it into two tables −

Table 1: Customers

Customer ID Customer Name Customer City

100 John Smith New York

101 Jane Doe Los Angeles

102 Bob Johnson San Francisco

Table 2: Orders

Order ID Customer ID Order Date Order Total

1 100 2022-01-01 100

2 101 2022-01-02 200

3 102 2022-01-03 300

Now, the "Customer City" column is no longer transitively dependent on the

primary key and is instead in a separate table that has a direct relationship with
the primary key. This makes the table 3NF-compliant.

o A relation will be in 3NF if it is in 2NF and not contain any transitive

partial dependency.
o 3NF is used to reduce the data duplication. It is also used to achieve
the data integrity.
o If there is no transitive dependency for non-prime attributes, then the
relation must be in third normal form.

A relation is in third normal form if it holds atleast one of the following

conditions for every non-trivial function dependency X → Y.

1. X is a super key.
2. Y is a prime attribute, i.e., each element of Y is part of some candidate
key.

Example:

EMPLOYEE_DETAIL table:

EMP_ID EMP_NAME EMP_ZIP EMP_STATE EMP_CITY

222 Harry 201010 UP Noida

 333 Stephan 02228 US Boston

444 Lan 60007 US Chicago

555 Katharine 06389 UK Norwich

666 John 462007 MP Bhopal

Super key in the table above:

1. {EMP_ID}, {EMP_ID, EMP_NAME}, {EMP_ID, EMP_NAME, EMP_ZIP}....so

on

Candidate key: {EMP_ID}

Non-prime attributes: In the given table, all attributes except

EMP_ID are non-prime.

Here, EMP_STATE & EMP_CITY dependent on EMP_ZIP and EMP_ZIP

dependent on EMP_ID. The non-prime attributes (EMP_STATE,
EMP_CITY) transitively dependent on super key(EMP_ID). It violates the
rule of third normal form.

That's why we need to move the EMP_CITY and EMP_STATE to the new
<EMPLOYEE_ZIP> table, with EMP_ZIP as a Primary key.

EMPLOYEE table:

EMP_ID EMP_NAME EMP_ZIP

222 Harry 201010

333 Stephan 02228

444 Lan 60007

555 Katharine 06389

666 John 462007

EMPLOYEE_ZIP table:
EMP_ZIP EMP_STATE EMP_CITY

201010 UP Noida

02228 US Boston

60007 US Chicago

06389 UK Norwich

462007 MP Bhopal