Boyce-Codd Normal Form

• • We say a relation R is in BCNF if whenever X -> A is a nontrivial FD that holds in R , X is a superkey.
  • » Remember: nontrivial means A is not a member of set X .
  • » Remember, a superkey is any superset of a key (not necessarily a proper superset).

Example

• • Drinkers( name, addr, beersLiked, manf, favBeer)
  • » FD's: name->addr favBeer, beersLiked->manf
  • » In each FD, the left side is not a superkey.
  • » Any one of these FD's shows Drinkers is not in BCNF

Another Example

• • Beers( name , manf, manfAddr)
  • » FD's: name->manf , manf->manfAddr
  • » Only key is {name} .
  • » name->manf does not violate BCNF, but manf->manfAddr does.

Decomposition into BCNF

• • Given: relation R with FD's F .
• • Look among the given FD's for a BCNF violation X ->B .
  • » If any FD following from F violates BCNF, then there will surely be an FD in F itself that violates BCNF.
• • Compute X ⁺ .
  • » Not all attributes, or else X is a superkey.

Decompose R Using X ->B

• • Replace R by relations with schemas:
  • 1. R_1 = X ⁺ .
  • 2. R_2 = R - ( X ⁺ - X ).
• • Project given FD's F onto the two new relations.
  
  

Decomposition Picture

Example

• • Drinkers( name, addr, beersLiked, manf, favBeer)
  • » FD's: name->addr, name-> favBeer, beersLiked->manf
  • » Pick BCNF violation name->addr .
  • » Find the closure of the left side:
  •     {name}⁺ = {name, addr, favBeer}.
  • » Decomposed relations:
    • 1. Drinkers1( name , addr, favBeer)
    • 2. Drinkers2( name , beersLiked , manf)

Example, Continued

• • We are not done; we need to check Drinkers1 and Drinkers2 for BCNF.
• • Projecting FD's is easy here.
• • For Drinkers1( name , addr, favBeer) , relevant FD's are name->addr and name->favBeer .
  • » Thus, {name} is the only key and Drinkers1 is in BCNF.
    
    

Example, Continued

• • For Drinkers2( name , beersLiked , manf) , the only FD is beersLiked->manf , and the only key is {name, beersLiked} .
  • » Violation of BCNF.
• • beersLiked⁺ = {beersLiked, manf}
• • So we decompose Drinkers2 into:
  • 1. Drinkers3( beersLiked , manf)
  • 2. Drinkers4( name , beersLiked )
    
    

Example, Concluded

• • The resulting decomposition of Drinkers :
  • 1. Drinkers1( name , addr, favBeer)
  • 2. Drinkers3( beersLiked , manf)
  • 3. Drinkers4( name , beersLiked )
• • This makes sense, because:
  • »Drinkers1 tells us about drinkers,
  • »Drinkers3 tells us about beers, and
  • »Drinkers4 tells us the relationship between drinkers and the beers they like.
    
    

3rd Normal Form - Motivation

• • There is one structure of FD's that causes trouble when we decompose.
• • AB -> C and C -> B .
  • » Example: A = street address, B = city, C = zip code.
• • There are two keys, { A , B } and { A , C } .
• • C ->B is a BCNF violation, so we must decompose into AC , BC .
  

We Cannot Enforce FD's

• • The problem is that if we use AC and BC as our database schema, we cannot enforce the FD   AB ->C by checking FD's in these decomposed relations.
• • Example with A = street, B = city, and C = zip on the next slide.

An Unenforceable FD

3NF Let's Us Avoid This Problem

• • 3 rd Normal Form (3NF) modifies the BCNF condition so we do not have to decompose in this problem situation.
• • An attribute is prime if it is a member of any key.
• • X ->A violates 3NF if and only if X is not a superkey, and also A is not prime.

Example

• • In our problem situation with FD's AB -> C and C -> B , we have keys AB and AC .
• • Thus A , B , and C are each prime.
• • Although C -> B violates BCNF, it does not violate 3NF.

What 3NF and BCNF Give You

• • There are two important properties of a decomposition:
  • 1. Recovery : it should be possible to project the original relations onto the decomposed schema, and then reconstruct the original.
  • 2. Dependency Preservation : it should be possible to check in the projected relations whether all the given FD's are satisfied.

Algorithm for 3NF

• • Given a set F of FD's, find a minimal basis of F:
  • - Basis : a basis of F is a set of FD's that is equivalent to F.
  • - Minimal basis : a basis G where the right side of each FD is a singleton, and G is no longer a basis if any FD is removed or if any attribute is removed from a FD in G.
• • For each FD X->A in G, use XA as the schema of one of the relations in the decomposition.
• • If none of the relation schemas from Step 2 is a superkey for R, add another relation whose schema is a key for R.

3NF & BCNF, Continued

• • We can get (1) with a BCNF decomposition.
  • » Explanation needs to wait for relational algebra.
• • We can get both (1) and (2) with a 3NF decomposition.
• • But we can't always get (1) and (2) with a BCNF decomposition.
  • » street-city-zip is an example.