DBMS Third Normal Form (3NF)

Third Normal Form (3NF)

A relation will be in the DBMS Third Normal Form (3NF) if it is in the 2NF and not include some transitive partial dependency.

3NF is used to manage data integrity and reduce the duplication of data.

A relation is in third normal form if and only if some of the subsequent conditions will satisfy every non-trivial functional dependency P→Q.

• P is a super key or candidate key.
• Q is a prime attribute, i.e., Q is a part of the candidate key.

Consider a relation R (A, B, C) where the given FDs are A→B,B→C. Then, an instance of this relation can be as follows.

Check for these redundancies in the relation. As the dependency ,B→C holds, we know from the first tuple that when B is b₁,and C is c₁. Therefore, we can say that this information b₁,c_1is repeated in the second tuple. These repetitions had to be made to provide information a₁,b₁ and a₂,b₁.

Check out that relation is in 2NF, and both B and C are fully functionally dependent on the candidate key A.

Normalization Procedure

If we decompose the table such that no non-candidate key becomes functionally dependent on another non-candidate key, then the redundancy is removed. So, here we decompose the relation as R₁ (A,B) and R₂ (B,C). Now, the given instance will become

With A as the candidate key and

With B as the candidate key.

Therefore, the redundancy is no longer present, and we say that the relation is in 3NF.

Example

Consider the following EMP relation

UPDATE

Can we change the employee servicing in department “D03” in the PROJECT department. In this case, many employees belong to department “D03” (e.g., Gaurav and Amit). Thus, we must make sure that all tuples are updated, or there will be a problem with database inconsistency.

INSERT

Suppose we want to add a new employee in the EMP relation with EMP-NO as its primary key. The new tuple must also include correct information about the department where he will be working. If not, the data will become inconsistent.

If we want to add a new department and no employees are assigned there, then as we do not know the information about the employees, we will have to put the NULLs as their attributes, including the primary key, which is not permitted.

DELETE

If we want to delete information from the employee whose employee code is “003” in the EMP instance as shown, then we would loss information concerning the department “D02”.

Instance of EMP after deleting the tuple of EMP-“003”

The given an example makes it clear that despite of a relation existing in 2NF, the issue can still occur, and they should be removed. They need to be normalized further, i.e., there is a need for a third normal form to remove these anomalies.

Examine the functional dependencies of the above example, the presence of “transitive” dependencies are evident.

Here, EMP-NO→DEPT-NAME is only a transitive or an indirect dependency. It is treated as indirect as EMP-NO→DEPT-NO and DEPT-NO→DEPT-NAME and thus EMP-NO→DEPT-NAME.

Enroll Yourself in Live Training: DBMS Training