DBMS Second Normal Form (2NF)

Second Normal Form (2NF)

Fully Functional Dependency forms the basis of the DBMS second normal form (2NF). If each non-prime attribute in relation is dependent functionally on the candidate key of the relation, a relational schema is said to be in 2NF. Therefore, if each non-prime attribute in relation is not partially dependent on any key of the relation, the relation schema is in 2NF.

Normalization Procedure

Test left-hand side attributes as a component of the main key. If an attribute is dependent on parts of the multi-valued key, transfer it to various tables.

Where the composite attribute (A, B) is the main key. Assume that the following functional dependencies exist:

(A,B)→C

i.e., complete functional dependence on the composite keys (A,B).

B→D

i.e., part functional dependence on the composite keys (A,B).

The following steps are:

Step1: A new relation R₂ from R is created due to the functional dependency
B→D, R₂, which contains B and D as attributes. B, the determinant, becomes the key to R₂.

Step2: Minimize the original relation R by eliminating the attribute on the right-hand side of B→D from it. The minimized relation R₁ thus, has all the original attributes. However, D is absent.

Step3: If one relation contains a similar determinant with the other relation, the dependent attributes of the relation to be non-key attributes are placed in the other relation for whom the determinant is a key.

Thus, “A relation R is in 2NF if it is in 1NF and every non-key attribute is fully functionally dependent on primary key”.

Reduction of 1NF to 2NF can be represented diagrammatically as follows.

Example

Consider the following relation

EMP

INSERT

We introduce a new employee who has not been assigned to any project yet. We cannot do so unless he/she has been assigned to a project.

Entity Integrity Violation: PROJ-ID is a part of Primary Key

DELETE

If the data about a project is deleted, the information about the employees who worked on that project is also deleted. If we delete the data about the project “P01”, information about employees E001 and E003 will be deleted.

UPDATE

If the project location for a particular project has been changed and we have updated the change only in a single tuple, this will lead to an update anomaly due to partial update resulting in data inconsistencies.

Although it is normalized to 1NF, the EMP relation still contains storage anomalies. Partial dependency on the primary key leads to violations of the database integrity and consistency rules.

The determinant (EMP-NO, PROJ-ID) is the composite key of the EMP relation, and its value will establish the value of other non-key attributes in a tuple of the relation uniquely. Note that while Hrs_worked is fully functionally dependent on all of the (EMP-NO, PROJ-ID),EMP-NAME is partially functionally dependent on the composite key (as each of them depends on the EMP-NO part of the key only but not on PROJ-ID). PROJ-NAME and PROJ-LOC are only partially functionally dependent on the composite key (as they each depend on the PROJ-ID part of the key but not on EMP-NO).

Functional Dependencies

FD1:{EMP-NO,PROJ-ID}→HRS-WORKED &nbsp &nbsp &nbsp &nbsp PRIMARY KEY

FD2:EMP-NO→EMP-NAME &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp Partial Dependency

FD3:PROJ-ID→PROJ-NAME,PROJ-LO &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp Partial Dependency

These issues can be kept away by removing partial key dependence in place of full functional dependence, and this can be done by making dependencies separate.

This is as follows

ASSIGN: Considering FD1

EMP: Considering FD2

PROJECT: Considering FD3

The source relation is divided into three relations and every resultant relation is no longer dependent on partial key dependencies.

ASSIGN

EMP

PROJECT

There are two relations in the second normal form now. Test these operations on the resultant relations.

Updation Anomaly

Redundant/duplicate tuples are not present in the relation, and the updates are completed in one place without worrying for inconsistencies in the database.

Insertion Anomaly

Adding a new employee can be done in the EMP relation without being concerned with whether he/she is assigned to a project or not.

Deletion Anomaly

When a tuple is deleted in PROJECT, it does not lead to information loss on the details of all the employees.

Enroll Yourself in Live Training: DBMS Training