Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What is 4NF in DBMS?
A relation R is in 4NF if it is in BCNF and there is no non-trivial multivalued dependency.
For a dependency A->B, if for a single value of A, multiple values of B exist, then the relation will be a multi-valued dependency.
Example
Consider the following table
| Regno | Phoneno | Qualification |
|---|---|---|
| 1 | P1 | Diploma |
| 1 | P1 | B.Tech |
| 1 | P1 | M.Tech |
| 1 | P2 | Diploma |
| 1 | P2 | B.Tech |
| 1 | P2 | M.Tech |
Here,
regno->-> phoneno
regno->-> qualification.
Both are non trivial MVD
The given relation is in BCNF [since no functional dependency exists]. But the above table is not in 4NF [since there is a non trivial MVD].
Anomalies
It also suffers with anomalies which are as follows −
Insertion anomaly: If we want to insert a new phoneno for regno3 then we have to insert 3 rows, because for each phoneno we have stored all three combinations of qualification.
Deletion anomaly: If we want to delete the qualification diploma, then we have to delete it in more than one place.
Updation anomaly: If we want to update the qualification diploma to IT, then we have to update in more than one place.
4NF decomposition
If R(XYZP) has X->->Y and X->->Z then, R is decomposed to R1(XY) and R2(XZP).
=> R(regno, phoneno, qualification) is decomposed to R1(regno, phoneno) and R2(regno, qualification).
R1
| Regno | Phoneno |
|---|---|
| 1 | P1 |
| 1 | P2 |
R2
| Regno | Qualification |
|---|---|
| 1 | Diploma |
| 1 | B.Tech |
| 1 | M.Tech |
All the anomalies discussed above are removed. The above two relations are in 4NF.
Now, regno->->phoneno is trivial MVD (since {regno} U {phoneno}=R1)
=>R1 is in 4NF.
Regno->-> qualification is trivial MVD (since {regno} U {qualification} =R2)
=> R2 is in 4NF.
5K+ Views
