First Normal Form (1NF)
According to the E.F. Codd, a relation will be in 1NF, if each cell of a relation
contains only an atomic value. This normal form states that an attribute of a
relation cannot hold multiple values. It should hold only single-valued
attributes. Values stored in an attribute should be of the same domain.
Example:
The following student relation is not in 1NF because the Subject attribute
contains multiple values.
Student_id Name Subject
101 Akash Computer Network, JAVA
102 Vikrant Database Management System
103 Amrita Software Engineering, Compiler Design
The below relation student is in 1NF:
Student_id Name Subjects
101 Akshay Computer Network
101 Akshay JAVA
102 Aman Database Management System
103 Anjali Software Engineering
103 Anjali Compiler Design
Relation is in 1NF
Second Normal Form (2NF)
According to the E.F. Codd, a relation is in 2NF, if it satisfies the following
conditions:
A relation must be in 1NF.
And the candidate key in a relation should determine all non-prime
attributes or no partial dependency should exist in the relation.
Example of Second normal form:
Example: Suppose a training institute wants to store the data of student and
the programming_languages they learn. Since a student can learn more
than one programming_language, the relation can have multiple rows for a
same student. Following relation shows the data of the students:
student_id programming_langauges student_age
101 Computer Network 20
101 JAVA 20
102 Database Management System 20
103 Software Engineering 21
103 Compiler Design 21
Candidate Keys: {student_id, programming_language}
Non-prime attribute: student_age
The above relation is in 1 NF because each attribute contains atomic values.
However, it is not in 2NF because a non-prime attribute student_age
is dependent on student_id, which is a proper subset of a candidate key.
This violates the rule for second normal form as a rule says “no non-prime
attribute should be dependent on the part of a candidate key of the relation”.
To make the relation in 2NF, we can break it in two tables like:
Student_details table:
student_id student_age
101 20
101 20
102 20
103 21
103 21
student_programminglangauge table:
student_id programming_langauge
101 Computer Network
101 JAVA
102 Database Management System
103 Software Engineering
103 Compiler Design
Now, both the tables follow 2NF.
Third Normal Form (3NF)
According to the E.F. Codd, a relation is in third normal form (3NF) if it
satisfies the following conditions:
A relation must be in second normal form (2NF).
And there should be no transitive functional dependency exists for non-
prime attributes in a relation.
Example of 3NF:
Suppose a school wants to store the address of each student, they
create a table named student_details that looks like:
Rollno State City
1 Punjab Chandigarh
2 Haryana Ambala
3 Punjab Chandigarh
4 Haryana Ambala
5 Uttar Pradesh Ghaziabad
Candidate Key: {Rollno}
Prime attribute: Rollno
Non-prime attribute: {State, City}
The above relation is not in third normal form, because as a rule says,
there should be no transitive functional dependency in the relation.
Here, City (a non-prime attribute) depends on State (a non-prime
attribute), and State depends on Rollno. The non-prime attributes
(State, City) are transitively dependent on the candidate key(Rollno).
Thus, it violates the rule of third normal form.
To covert the relation in 3NF, you have to decompose the relation as:
Table: Student_state
Rollno State
1 Punjab
2 Haryana
3 Punjab
4 Haryana
5 Uttar Pradesh
Table:Student_city
State City
Punjab Chandigarh
Haryana Ambala
Uttar Pradesh Ghaziabad
Now, both the tables follow the third normal form (3NF).
