Explain BCNF with example

Mumbai University > Computer Engineering > sem 4> database management system

Marks: 5M

Year: May16

1 Answer

Boyce–Codd Normal Form :

  • Boyce–Codd normal form (or BCNF or 3.5NF) is a normal form used in database normalization.
  • It is a slightly stronger version of the third normal form (3NF).
  • If a relational schema is in BCNF then all redundancy based on functional dependency has been removed, although other types of redundancy may still exist.
  • A relational schema R is in Boyce–Codd normal form if and only if for every one of its dependencies X → Y, at least one of the following conditions hold:

    X → Y is a trivial functional dependency (Y ⊆ X)

    X is a super key for schema R

Normalisation to BCNF - Example :

Patient No Patient Name Appointment Id Time Doctor
1 John 0 9:00 Zorro
2 Kerr 0 9:00 Killer
3 Adam 1 10:00 Zorro
4 Robert 0 13:00 Killer
5 Zane 1 14:00 Zorro

Lets consider the database extract shown above. This depicts a special dieting clinic where the each patient has 4 appointments. On the first they are weighed, the second they are exercised, the third their fat is removed by surgery, and on the fourth their mouth is stitched closed… Not all patients need all four appointments! If the Patient Name begins with a letter before “P” they get a morning appointment, otherwise they get an afternoon appointment. Appointment 1 is either 09:00 or 13:00, appointment 2 10:00 or 14:00, and so on.


Patno -> PatName

Patno,appNo -> Time,doctor

Time -> appNo

Now we have to decide what the primary key of DB is going to be. From the information we have, we could chose: DB(Patno,PatName,appNo,time,doctor)

Example: DB(Patno,PatName,appNo,time,doctor)

  • 1NF Eliminate repeating groups.



  • 2NF Eliminate partial key dependencies



  • 3NF Eliminate transitive dependencies

    None: so just as 2NF

  • BCNF Every determinant is a candidate key



  • Go through all determinates where ALL of the left hand attributes are present in a relation and at least ONE of the right hand attributes are also present in the relation.

  • Patno -> PatName

    Patno is present in DB, but not PatName, so not relevant.

  • Patno,appNo -> Time,doctor

All LHS present, and time and doctor also present, so relevant. Is this a candidate key? Patno,appNo IS the key, so this is a candidate key. Thus this is OK for BCNF compliance.

Time -> appNo

Time is present, and so is appNo, so relevant. Is this a candidate key. If it was then we could rewrite DB as:


This will not work, as you need both time and Patno together to form a unique key. Thus this determinate is not a candidate key, and therefore DB is not in BCNF. We need to fix this.

  • BCNF: rewrite to




time is enough to work out the appointment number of a patient. Now BCNF is satisfied, and the final relations shown are in BCNF

Please log in to add an answer.