
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
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.
A | B | C |
---|---|---|
a1 | b1 | c1 |
a2 | b1 | c1 |
a3 | b2 | c2 |
Check for these redundancies in the relation. As the dependency ,B→C holds, we know from the first tuple that when B is b1,and C is c1. Therefore, we can say that this information b1,c_1is repeated in the second tuple. These repetitions had to be made to provide information a1,b1 and a2,b1.
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 R1 (A,B) and R2 (B,C). Now, the given instance will become
A | B |
---|---|
a1 | b1 |
a2 | b1 |
a3 | b2 |
With A as the candidate key and
B | C |
---|---|
b1 | c1 |
b2 | c2 |
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
EMP-NO | EMP-NAME | DESG | SALARY | DEPT-NO | DEPT-NAME |
---|---|---|---|---|---|
001 | Roshni | Manager | 25000 | D01 | Administration |
002 | Gaurav | Programmer | 15000 | D03 | Project |
003 | Raman | Finance Officer | 12000 | D02 | Personnel |
004 | Amit | System Analyst | 20000 | D03 | Project |
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.
EMP-NO | EMP-NAME | DESG | SALARY | DEPT-NO | DEPT-NAME |
---|---|---|---|---|---|
001 | Roshni | Manager | 25000 | D01 | Administration |
002 | Gaurav | Programmer | 15000 | D03 | Project |
003 | Raman | Finance Officer | 12000 | D02 | Personnel |
004 | Amit | System Analyst | 20000 | D03 | Project |
005 | Janvi | Programmer | 22000 | NULL | NULL |
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.
EMP-NO | EMP-NAME | DESG | SALARY | DEPT-NO | DEPT-NAME |
---|---|---|---|---|---|
001 | Roshni | Manager | 25000 | D01 | Administration |
002 | Gaurav | Programmer | 15000 | D03 | Project |
003 | Raman | Finance Officer | 12000 | D02 | Personnel |
004 | Amit | System Analyst | 20000 | D03 | Project |
NULL | NULL | NULL | NULL | D01 | Sales |
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”.
EMP-NO | EMP-NAME | DESG | SALARY | DEPT-NO | DEPT-NAME |
---|---|---|---|---|---|
001 | Roshni | Manager | 25000 | D01 | Administration |
002 | Gaurav | Programmer | 15000 | D03 | Project |
004 | Amit | System Analyst | 20000 | D03 | Project |
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