Difference between 2NF and 3NF | Fourth Semester | BSc.CSIT (TU)

Download our Android App from Google Play Store and start reading Reference Notes Offline.

difference between 2NF and 3NFDifference between 2 Normal Form(NF) and 3 Normal Form(NF) | BSc.CSIT
Fourth Semester | Second Year | Tribhuvan University (TU)
Subject: System Analysis and Design (SAD)

Difference among 2NF and 3NF
A relation is in 2NF if every non-primary key attribute is functionally dependent on the whole primary key. Thus no primary key is functionally dependent on part, but not all of primary key.

Satisfied if any 1 conditions apply

  1. Primary key consist of any 1 attribute.
  2. Non primary key attribute exist in the relation.
  3. Every non-primary key attribute is functionally dependent on full set of primary key attributes.

For example; EMPLOYEE (emp-ID, Name, dept, salary, course, Date-completed)
Above relation is not in 2NF because of functional dependencies as follows;
Emp_ID -> name, Dept, Salary
Emp_ID , course -> Date- Completed

To convert to 2NF, decompose relation into new relation using attributes called determinants and determine other attributes. Determinants are primary key of these relations. For 2NF decompose above relation into as follows;
EMPLOYEE(Emp-ID, Name, Dept, Salary)
EMPLOYEE(Emp-ID, Course, Date-completed)

A relation is in 3NF if it is in 2NF and there are no functional dependencies between two or more non primary key attributes. Functional dependency between non-primary key attributes is known as transitive dependency.
For example: SALES(Customer-ID, Customer-name, Salesperson, Region)
Above relation is not in 3NF because of functional dependencies as follows;
Customer_ID -> Customer-name, Salesperson, Region
Salesperson -> Region

Above example has following problems;

  • New salesperson can’t be assigned until customer is assigned
  • If customer deleted, salesperson assignment to region into deletes
  • If sales person assigned to different region, several rows must be changed to reflect the fact.

This can be avoided by decomposing SALES into 2 relations based on 2 determinants as follows;
SALES1(customer-ID, Customer-Name, Salesperson)
SPERSON(Salesperson, Region)

Here Salesperson act as foreign key in relation SALES1.

(Visited 691 times, 1 visits today)

Posted By : Digvijay | Comment RSS | Category : Fourth Semester
Tag :

Post a Comment

Your email is never published nor shared. Required fields are marked *


Wordpress DMCA
Community | Toolbar | Android App | Founder/Developer : Hari Prasad Chaudhary | CSIT Portal Manager : Digvijay Chaudhary