CS 320: Database Management Systems

Fourth Normal Form

Ge (Frank) Xia, CS, Lafayette College

Adapted from lecture notes by Prof. Ullman @ Stanford

Contents

• • Multivalued Dependencies (MVD)
• • Fourth Normal Form

Definition of MVD

• • A multivalued dependency (MVD) on R , X ->- > Y , says that if two tuples of R agree on all the attributes of X , then their components in Y may be swapped, and the result will be two tuples that are also in the relation.
• • i.e., for each value of X , the values of Y are independent of the values of R - X - Y .

Example

• • Drinkers(name, addr, phones, beersLiked)
  • » A drinker's phones are independent of the beers they like. name->->phones and name ->->beersLiked
  • » Thus, each of a drinker's phones appears with each of the beers they like in all combinations.
  • » This repetition is unlike FD redundancy. name->addr is the only FD.

Tuples Implied

• • Tuples Implied by name->->phones
• 

Picture of MVD X ->-> Y

MVD Rules

• • Every FD is an MVD ( promotion ).
  • » If X -> Y , then swapping Y 's between two tuples that agree on X doesn't change the tuples.
  • » Therefore, the "new" tuples are surely in the relation, and we know X ->-> Y .
• • Complementation : If X ->-> Y , and Z is all the other attributes, then X ->-> Z .
  
  

Splitting Doesn't Hold

• • Like FD's, we cannot generally split the left side of an MVD.
• • But unlike FD's, we cannot split the right side either --- sometimes you have to leave several attributes on the right side.

Example

• • Drinkers(name, areaCode, phone, beersLiked, manf)
  • » A drinker can have several phones, with the number divided between areaCode and phone (last 7 digits).
  • » A drinker can like several beers, each with its own manufacturer.

Example, Continued

• • Since the areaCode-phone combinations for a drinker are independent of the beersLiked- manf combinations, we expect that the following MVD's hold:
  • 1. name ->-> areaCode phone
  • 2. name ->-> beersLiked manf
    
    

Example Data

Fourth Normal Form

• • The redundancy that comes from MVD's is not removable by putting the database schema in BCNF.
• • There is a stronger normal form, called 4NF, that (intuitively) treats MVD's as FD's when it comes to decomposition, but not when determining keys of the relation.
  
  

4NF Definition

• • A relation R is in 4NF if: whenever X - >-> Y is a nontrivial MVD, then X is a superkey.
• • Nontrivial MVD means that:
  • 1. Y is not a subset of X , and
  • 2. X and Y are not, together, all the attributes.
• • Note that the definition of "superkey" still depends on FD's only.
  

BCNF Versus 4NF

• • Remember that every FD X -> Y is also an MVD, X ->-> Y .
• • Thus, if R is in 4NF, it is certainly in BCNF.
  • » Because any BCNF violation is a 4NF violation (after conversion to an MVD).
• • But R could be in BCNF and not 4NF, because MVD's are "invisible" to BCNF.

Decomposition and 4NF

• • If X ->-> Y is a 4NF violation for relation R, we can decompose R using the same technique as for BCNF.
  • 1. XY is one of the decomposed relations.
  • 2. All but Y -X is the other.

Example

• • Drinkers( name , addr, phones , beersLiked )
  • » FD: name -> addr
  • » MVD's: name ->-> phones, name ->-> beersLiked
• • Key is {name, phones, beersLiked} .
• • All dependencies violate 4NF.

Example, Continued

• • Decompose using name -> addr :
• 1. Drinkers1( name , addr)
  • » In 4NF; only dependency is name -> addr
• 2. Drinkers2( name , phones , beersLiked )
  • » Not in 4NF. MVD's name ->-> phones and name ->-> beersLiked apply.
  • » No FD's, so all three attributes form the key.

Example, Continued

• • Further decompose Drinkers2( name , phones , beersLiked )
• • Either MVD name ->-> phones or name ->-> beersLiked tells us to decompose to:
  • » Drinkers3( name , phones )
  • » Drinkers4( name , beersLiked )

The result

• • The resulting decomposition of Drinkers( name , addr, phones , beersLiked ) into:
  • 1. Drinkers1( name , addr )
  • 2. Drinkers3( name , phones )
  • 3. Drinkers4( name , beersLiked )