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

Marks: 5M

Year: May16

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 :

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.

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

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.

  None

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

• 2NF Eliminate partial key dependencies

  DB(Patno,appNo,time,doctor)

  R1(Patno,PatName)

• 3NF Eliminate transitive dependencies

  None: so just as 2NF

• BCNF Every determinant is a candidate key

  DB(Patno,appNo,time,doctor)

  R1(Patno,PatName)

• 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:

DB(Patno,appNo,time,doctor)

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

  DB(Patno,time,doctor)

  R1(Patno,PatName)

  R2(time,appNo)

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