Normal Forms

Normal Forms:

There are types of normal forms as below:

  • First Normal Form(1NF)
  • Second Normal Form(2NF)
  • Third Normal Form(3NF)
  • Boyce-codd Normal Form(BCNF)

First Normal Form(1NF)

For a table to be in the First Normal Form, it should follow the following rules:

  • An attribute (column) of a table cannot hold multiple values, It should hold atomic value.
  • Values stored in a column should be of the same domain
  • All the columns in a table should have unique names.

Example: Suppose a company wants to store the names and contact details of its employees.

It creates a table that looks like this:

Customer:

cust_id cust_name cust_address cust_mobile
101 Ram New Delhi 8916312390
102 Lucky Kanpur 8813121212

9900512222

103 Bharat Chennai 7778881212
104 Shatru Bangalore 9990030123

8123450987

This table is not in 1NF as the rule says �An attribute (column) of a table cannot hold multiple values�, the cust_mobile values for customer Lucky & Shatru violates that rule.

To make the table complies with 1NF we should have the data like this:

Customer:

cust_id cust_name cust_address cust_mobile
101 Ram New Delhi 8916312390
102 Lucky Kanpur 8813121212
102 Lucky Kanpur 9900512222
103 Bharat Chennai 7778881212
104 Shatru Bangalore 9990030123
104 Shatru Bangalore 812345098

Second Normal Form(2NF)

•  A relation R is in second normal form (2NF) if and only if it is in 1NF and

every non-prime attribute of relation is fully dependent on the primary key.

Example:

Student(stud_id, stud_name, facultyId, faculty_name, grade)

The attributes stud_id and facultyId are the identification keys.
All attributes a single valued (1NF).

The following functional dependencies exist:

1. The attribute faculty Name is functionally dependent on attribute facultyId (facultyId –> faculty_name)

2. The attribute Student Name is functionally dependent on student id (stud_id –> Stud_name)

3. The attribute grade is fully functional dependent on stud_id and facultyId ((stud_id, facultyId –> grade)

Student:

stud_id stud_name facultyId faculty_name grade
1 Sachin 3 Kapil 4
2 Rahul 2 Ravi 5
3 Saurav 1 Dilip 6

Result After Normalization

Student

stud_id stud_name
1 Sachin
2 Rahul
3 Saurav

Faculty

facultyId faculty_name
1 Dilip
2 Ravi
3 Kapil

Grade

stud_id facultyId grade
1 3 4
2 2 5
3 1 6

Third normal form (3NF)

A relation is in third normal form if it is in 2NF and no non key attribute is transitively dependent on the primary key.

Vendor(ID, Name, Account_No, Bank_Code_No, Bank)

The attribute ID is the identification key. All attributes are single valued (1NF). The table is also in 2NF.

The following dependencies exist:

  • Name, Account_No, Bank_Code_No are functionally dependent on ID (ID –> Name, Account_No, Bank_Code_No)
  • Bank is functionally dependent on Bank_Code_No (Bank_Code_No –> Bank)

Example:

Vendor

ID Name Account_No Bank_Code_No Bank

Result After Normalization

Vendor

ID Name Account_No Bank_Code_No

Bank

Bank_Code_No Bank

Here

Bank_Code_No is not the primary key of this relation. To get to the third normal form (3NF),

we have to put the bank name in a separate table together with the clearing number to identify it.

Leave a Reply

Your email address will not be published. Required fields are marked *