Boyce–Codd Normal Form (BCNF)

RDBMSDatabaseMySQL

BCNF is an extension to Third Normal Form (3NF) and is slightly stronger than 3NF.

A relation R is in BCNF, if P -> Q is a trivial functional dependency and P is a superkey for R.

If a relation is in BCNF, that would mean that redundancy based on function dependency have been removed, but some redundancies are still there.

Let us see an example −

<SportsClub>

Ground
Begin_Time
End_Time
Package
G01
07:00
09:00
Gold
G01
10:00
12:00
Gold
G01
10:30
11:00
Bronze
G02
10:15
11:15
Silver
G02
08:00
09:00
Silver


The above relation is in 1NF, 2NF, 3NF, but not in BCNF. Here is the reason −

Functional Dependency {Package->Ground}

It has the determinant attribute Package on which Ground depends on is neither a Candidate Key nor a superset of the candidate key.

<Package>

Package
Ground
Gold
G01
Silver
G02
Bronze
G01


<TomorrowBookings>

Ground
Begin_Time
End_Time
G01
07:00
09:00
G01
10:00
12:00
G01
10:30
11:00
G02
10:15
11:15
G02
08:00
09:00


Now the above tables are in BCNF.

Candidate key for <Package> table are Package and Ground

Candidate key for <TomorrowBookings> table are {Ground, Begin_Time} and {Ground, End_Time}

The anomaly eliminated because we used Package as a key in the <Package> relation.

raja
Published on 04-Jul-2018 09:53:25
Advertisements