BCNF(Boyce Codd Normal Form)

Boyce-Codd Normal Form (BCNF)

• A relation is in BCNF if every determinant is a candidate key.
• Recall that not all determinants are keys.
• Those determinants that are keys we initially call candidate keys.
• Eventually, we select a single candidate key to be the primary key for the relation.
• Consider the following example:
  Funds consist of one or more Investment Types.
  Funds are managed by one or more Managers
  Investment Types can have one more Managers
  Managers only manage one type of investment.
  
  

  FundID	InvestmentType	Manager
  99	Common Stock	Smith
  99	Municipal Bonds	Jones
  33	Common Stock	Green
  22	Growth Stocks	Brown
  11	Common Stock	Smith

  
  

  FundID, InvestmentType -> Manager
  FundID, Manager        -> InvestmentType
  Manager                -> InvestmentType
  

• In this case, the combination FundID and InvestmentType form a candidate key because we can use FundID,InvestmentType to uniquely identify a tuple in the relation.
• Similarly, the combination FundID and Manager also form a candidate key because we can use FundID, Manager to uniquely identify a tuple.
• Manager by itself is not a candidate key because we cannot use Manager alone to uniquely identify a tuple in the relation.
• Is this relation R(FundID, InvestmentType, Manager) in 1NF, 2NF or 3NF ?
  Given we pick FundID, InvestmentType as the Primary Key: 1NF for sure.
  2NF because all of the non-key attributes (Manager) is dependant on all of the key.
  3NF because there are no transitive dependencies.
• Consider what happens if we delete the tuple with FundID 22. We loose the fact that Brown manages the InvestmentType "Growth Stocks."
• The following are steps to normalize a relation into BCNF:
  1. List all of the determinants.
  2. See if each determinant can act as a key (candidate keys).
  3. For any determinant that is not a candidate key, create a new relation from the functional dependency. Retain the determinant in the original relation.
• For our example:
  Rorig(FundID, InvestmentType, Manager)
  1. The determinants are:
     FundID, InvestmentType
     FundID, Manager
     Manager
  2. Which determinants can act as keys ?
     FundID, InvestmentType YES
     FundID, Manager YES
     Manager NO
  3. Create a new relation from the functional dependency:
     Rnew(Manager, InvestmentType)
     Rorig(FundID, Manager)
     
     In this last step, we have retained the determinant "Manager" in the original relation Rorig.