
Quick Contact
DBMS Tutorial
- DBMS Tutorial
- What is Database Management System (DBMS)?
- Components of DBMS
- Applications of DBMS
- Three Schema DBMS Architecture
- Difference between DBMS and RDBMS?
- Difference between File Oriented System and DBMS
- Types of Data Models
- DBMS Schema and Instances
- Data Independence and Data Abstraction
- Database Users and Administrator
- DBMS Languages and Interfaces
DBMS ER Model
DBMS Relational Data Model
DBMS Normalization
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 R2 from R is created due to the functional dependency
B→D, R2, which contains B and D as attributes. B, the determinant, becomes the key to R2.
Step2: Minimize the original relation R by eliminating the attribute on the right-hand side of B→D from it. The minimized relation R1 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
EMP-NO | EMP-NAME | PROJ-ID | PROJ-NAME | PROJ-LOC | HRS-WORKED |
---|---|---|---|---|---|
001 | Rishabh | P-01 | HMS | Mumbai | 20 |
002 | Jyoti | P-02 | ERP | Delhi | 22 |
003 | Sourav | P-01 | HMS | Mumbai | 20 |
004 | Gautam | P-03 | SIS | Noida | 17 |
005 | Rohan | P-02 | ERP | Delhi | 22 |
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.
EMP-NO | EMP-NAME | PROJ-ID | PROJ-NAME | PROJ-LOC | HRS-WORKED |
---|---|---|---|---|---|
001 | Rishabh | P-01 | HMS | Mumbai | 20 |
002 | Jyoti | P-02 | ERP | Delhi | 22 |
003 | Sourav | P-01 | HMS | Mumbai | 20 |
004 | Gautam | P-03 | SIS | Noida | 17 |
005 | Rohan | P-02 | ERP | Delhi | 22 |
006 | Rajiv | ? | ? | ? | ? |
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.
EMP-NO | EMP-NAME | PROJ-ID | PROJ-NAME | PROJ-LOC | HRS-WORKED |
---|---|---|---|---|---|
Deletion Anomaly | |||||
002 | Jyoti | P-02 | ERP | Delhi | 22 |
Deletion Anomaly | |||||
004 | Gautam | P-03 | SIS | Noida | 17 |
005 | Rohan | P-02 | ERP | Delhi | 22 |
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         PRIMARY KEY
FD2:EMP-NO→EMP-NAME                             Partial Dependency
FD3:PROJ-ID→PROJ-NAME,PROJ-LO                 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-NO | PROJ-ID | HRS-WORKED |
---|
EMP: Considering FD2
EMP-NO | EMP-NAME |
---|
PROJECT: Considering FD3
PROJ-ID | PROJ-NAME | PROJ-LOC |
---|
The source relation is divided into three relations and every resultant relation is no longer dependent on partial key dependencies.
ASSIGN
EMP-NO | PROJ-ID | HRS-WORKED |
---|---|---|
001 | P-01 | 20 |
002 | P-02 | 22 |
003 | P-01 | 20 |
004 | P-03 | 17 |
005 | P-02 | 22 |
EMP
EMP-NO | EMP-NAME |
---|---|
001 | Rishabh |
002 | Jyoti |
003 | sourav |
004 | Gautam |
005 | Rohan |
PROJECT
PROJ-ID | PROJ-NAME | PROJ-LOC |
---|---|---|
P-01 | HMS | Mumbai |
P-03 | ERP | Delhi |
P-03 | SIS | Noida |
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