Third Normal Form 3 NF Third Normal Form

Third Normal Form (3 NF)

Third Normal Form (3 NF) • A relation is said to be in 3 NF if it is already in 2 NF and it has no transitive dependency. • A functional dependency X Y in a relation schema r is a transitive dependency if there is a set of attributes Z that is neither a candidate key nor a subset of any key of the relation and both X Z and Z Y holds.

Example The following employee table is not in 3 NF because hourly rate is determined by the group and so there is transitive dependency between two non-key attributes Normalized Employee table – Employee table in 3 NF. The employee table is split into emp 1 and emp 2 in 3 NF Emp 2 Emp 1 Rate_group Hourly_Rate Emp_id Employee_name Rate_group A $1000 100 Charles A B $500 101 James B 102 Jannifer A

3 NF? PERSON ENO Name Dno Dept. Na me E 001 Somchai D 01 Physic E 003 Somchay D 02 Computer Science E 004 Som. Siri D 02 Computer Science Answer is No Because Dept. Name is dependent on Dno (has transitive dependent on key)

Solution • Remove the offending attributes • Take the determinant along

Result Department PERSON ENO Name Dno E 001 Somchai D 01 E 003 Somchay D 02 E 004 Som. Siri D 02 Dno Dept. Na me D 01 Physic D 02 Computer Science

A Relation in 2 NF but Not in 3 NF • Identify primary key (PK) and Look for transitive dependence Transitive dependency 8

Transforming to 3 NF • Move the attributes involved in transitive dependency to another relation Order Customer 9