Explain 3NF with an example in DBMS

DBMSDatabaseBig Data Analytics

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)

RollnoGameFeestructure
1Basketball500
2Basketball500
3Basketball500
4Cricket600
5Cricket600
6Cricket600
7Tennis400
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

RollnoGame
1Basketball
2Basketball
3Basketball
4Cricket
5Cricket
6Cricket
7tennis

R2

GameFeestructure
Basketball500
Cricket600
Tennis400

The above two tables are free from all three anomalies.

raja
Published on 06-Jul-2021 12:12:38
Advertisements