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)

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

R2

The above two tables are free from all three anomalies.

Bhanu Priya

Updated on 06-Jul-2021 12:22:54

• Related Questions & Answers
• 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 primary key 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 trial balance with an example
• Check which FD violates 3NF in a given relation and decompose R into 3NF(DBMS)
• Is RowSet Scrollable? Explain with an example?
• Explain asynchronous functions in JavaScript with an example
• Explain hard reset with an example in Git
• Explain soft reset with an example in Git
• Explain mixed reset with an example in Git
• Explain Deep cloning an object in JavaScript with an example.
• Explain 5NF with examples in DBMS