- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
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
Explain 3NF with an example in DBMS
A relation is in 3NF when it is in 2NF and there is no transitive dependency or a relation is in 3NF, when it is in 2NF and all non-key attributes directly depend on candidate key.
Example
Consider a relation student (rollno, game, feestructure)
Rollno | Game | Feestructure |
---|---|---|
1 | Basketball | 500 |
2 | Basketball | 500 |
3 | Basketball | 500 |
4 | Cricket | 600 |
5 | Cricket | 600 |
6 | Cricket | 600 |
7 | Tennis | 400 |
F − {rollno -> game, rollno -> feestructure, game -> fee} Rollno+= {rollno, game, feestructure} => rollno is primary key
The above student table is in 1NF because there are no multivalue attributes.
Student table is also in 2NF because all non-key attributes are fully functional dependent on the primary key (rollno). But the table is not in 3NF because there is transitive dependency i.e. game-> feestructure.
Feestructure has transitive/indirect dependency on rollno via game.
Anomalies
The above student table is also suffering from all three anomalies −
Insertion anomaly − A new game can't be inserted into the table unless we get a student to play that game.
Deletion anomaly − If rollno 7 is deleted from the table we also lost the complete information regarding tennis.
Updation anomaly −To change the fee structure for basketball we need to make changes in more than one place.
So, now to convert the above student table into 3NF first we need to decompose the table as follows −
Decomposition for 3NF
To overcome these anomalies, the student table should be divided into smaller tables.
If X->Y is transitive dependency then divide R into R1(X+) and R2(R-Y+).
Game->feestructure is a transitive dependency [since neither game is a key nor fee is a key attribute]
R1=game+=(game, feestructure)
R2=(student-feestructure+) = (rollno,game)
So divide the student table into R1(game, feestructure) and R2 (rollno, game).
R1
Rollno | Game |
---|---|
1 | Basketball |
2 | Basketball |
3 | Basketball |
4 | Cricket |
5 | Cricket |
6 | Cricket |
7 | tennis |
R2
Game | Feestructure |
---|---|
Basketball | 500 |
Cricket | 600 |
Tennis | 400 |
The above two tables are free from all three anomalies.
- Related Articles
- Explain BCNF with an example in DBMS
- Explain about 2NF with an example in DBMS
- Explain serial execution or transaction with an example(DBMS)
- Explain the concept of key attributes with an example in DBMS?
- Explain join operations with the help of an example in DBMS
- Explain the concept of primary key with an example (DBMS)?
- Check which FD violates 3NF in a given relation and decompose R into 3NF(DBMS)
- Explain trial balance with an example
- Explain asynchronous functions in JavaScript with an example
- Explain hard reset with an example in Git
- Explain mixed reset with an example in Git
- Explain soft reset with an example in Git
- Explain 5NF with examples in DBMS
- Is RowSet Scrollable? Explain with an example?
- What is Symbiosis? Explain with an example.
