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.