Boycee-Codd Normal Form (BCNF)

The original definition of 1NF was inadequate in some situations. It was not satisfactory  for the tables:

• That had multiple candidate keys.
• Where the multiple candidate keys were composite
• Where the multiple candidate keys overlapped  (had atleast one attribute to common)

  Therefore, a new normal form, the BCNF was introduced. You must understand that in tables where the above three condidtions do not apply, you can stop at the third normal form. In such cases, the third NF is the same as the BCNF.

A relation is in the BCNF  if and only if every determinant is a candidate key.

Consider the following PROJECT table.

This table has redundancy.if the name of an employee is modified, the change will have to be made consistent across the table, otherwise there will be inconsistencies.

ECODE + PROJCODE is the primary key. You will notice that NAME + PROJCODE could be chosen as the primary key and hence, is a candidate key.

• HOURS is functionally dependent on ECODE + PROJCODE
• HOURS is also functionally dependent on NAME + PROJCODE
• NAME is functionally dependent on ECODE
• ECODE is functionally dependent on NAME

 You will notice that this table has :

• Multiple candidate keys, that is ECODE + PROJCODE and NAME + PROJCODE
• The candidate keys are composite.
• The candidate keys overlap sicne the attribute PROJCODE is common

 This is a situation that requires conversion to BCNF. The table is essentially in the third NF. The only non-key item is HOURS, which is dependent on the whole key, that is, ECODE + PROJCODE or NAME + PROJCODE.

ECODE and NAME  are determinants sicne they are fucntionally dependent on each other. However, they are not candidate keys by themselves. As per BCNF, the determinants have to be candidate keys.