Normal Forms in DATABASE

FIRST Normal Form

Every value in a cell of an Attribute should be an ATOMIC, which means that an attribute cell can contain only single data values.

There should be no repeating Columns.

Every Value in a column should be of the same data type, In The below students table the “” First_Name” is of datatype Varchar, so we should make sure that only the datatype of varchar should be used.

The Below table is not in First Normal Form as a single Cell of a column is holding multiple values, For “Student_id”  = 1  the Course column has comma-separated values (SQL, JAVA) which is not allowed.

To rectify this issue we can create a separate row for Each course as shown below, so we can say that the below table is in its First Normal form.

SECOND Normal Form

The table should be in its First Normal Form.

If there are any redundant data / repeating Rows then those rows should be moved to another table.

There should not be any Partial Dependency 

If there is an Attribute that is dependent on a part of a Candidate key. then the table is not in Second Normal Form.

In the below example, a combination of (STUDENT_ID + Course) is a candidate key and a table is in 2 Normal Form if there is no Partial Dependency, So any Non-Prime attribute / Column should not depend on a part of the Candidate Key, But Column “Course_Price” is Dependent on “Course“, as if we know the value of Course then we can search the value of Course_Price.

This is a case of Partial Dependency as the column “Course_Price” is dependent on part of the Candidate Key “Course” instead of Dependant of the whole candidate key “STUDENT_ID + Course”

To rectify this issue We need to create a Separate Table for Course and Course_Price as shown in the tables below.

THIRD Normal Form

The table should be in Second Normal Form.

There Should not be any Transitive Dependency, Transitive Dependency means Suppose we have 3 attributes in tables A, B & C, If “A” can search the value of “B” and “B” Inturn can search the value of “C” then this type of Dependency is called Transitive dependency.

The Problem arises when “B” is Non-Prime and “C” is also a Non-Prime Attribute and a Non-attribute starts to depend on a Non-prime Attribute

A non-Prime Attribute Should not find a Non-Prime Attribute. In the below example “Student_id” is a primary key and if we know the value of “Student_id” then we can search the value of “Course”, “First_name” & “Teacher”.

However in this case, if we know the value of “Course” then we can also find the value of “Teacher” but both “Course” and “Teachers” are Non-Prime Attributes, this is a violation of the 3rd normal form.

The solution to this problem is to put “Course” and “Teacher” in a separate table as shown below, now all Non-Prime attributes are directly dependent on the Primary key

BCNF ( Boyce – Codd normal form)

For a table to be in BCNF the tables should be in Third Normal Form. When a Non-Prime/Prime attribute starts to find a Prime attribute or a part of a Prime Attribute.

In the below example (“Student_id” + “Course“) is a PRIME Attribute and “Teacher” is a non-prime attribute, but if we see closely if we know the value of “Teacher” then we can tell the “Course” is a teacher teaches, means if we know the name of the teacher we call to search the course a teacher teaches.

Here a non-prime attribute “Teacher” can search the value of a part of the candidate key “Course” hence this is a violation of BCNF.

To Rectify this anomaly we can create a separate table for Teachers and their respective courses.

FORTH Normal Form

For a table to be in Forth Normal form the table should already be in BCNF

and there should not be any Multi-level dependency

FIFTH Normal Form

The table is in the Fifth normal form if it’s already in the Forth Normal form.

And there should be Lossless decomposition.