4NF

1
4NF Multivalued Dependency

• By Kristina Miguel

2
Review

• Superkey a set of attributes which will
  uniquely identify each tuple in a relation
• Candidate key a minimal superkey
• Primary key a chosen candidate key
• Secondary key all the rest of candidate keys
• Prime attribute an attribute that is a part of
  a candidate key (key column)
• Non-prime attribute a non-key column

3
Review (cont.)

• 1NF
• Eliminate repeating groups.
• Make a separate table for each set of related
  attributes, and give each table a primary key.
• 2NF
• Eliminate redundant data.
• Each attribute must be functionally dependent on
  the primary key.
• If an attribute depends on only part of a
  multi-valued key, remove it to a separate table.
• 3NF
• Eliminate columns not dependent on key.
• If attributes do not contribute to a description
  of the key, remove them to a separate table.
• Any transitive dependencies are moved into a
  smaller table.
• BCNF
• Every determinant in the table is a candidate
  key.
• If there are non-trivial dependencies between
  candidate key attributes, separate them out into
  distinct tables.
• All normal forms are additive, in that if a model
  is in 3NF, it is by definition also in 2NF and
  1NF.

4
Multivalued Dependency (MVD)

• A MVD X??Y,
• Holds for some relation R, so that when you fix
  the values for one set of attributes, then the
  values in certain other attributes are
  independent of the values of all the other
  attributes in the relation.
• Is an assertion that two attributes or sets of
  attributes are independent of one another.
• For each value of X, the values of Y are
  independent of the values of R-X-Y.

5
MVD (cont.)

• More precisely, for MVD A??B
• For each pair of tuples t and u of relation R
  that agree on all the As, we can find in R some
  tuple v that agrees
• With both t and u on the As,
• With t on the Bs, and
• With u on all attributes of R that are not among
  the As or Bs.

6
MVD (cont.)

• Representation of X??Y

X Y others equal exchange
7
MVD Example

• Drinkers(name, addr, phones, beersLiked)
• A drinkers phones are independent of the beers
  they like.
• name??phones and name??beersLiked.
• Thus, each of a drinkers phones appears with
  each of the beers they like in all combinations.

8
MVD Example (cont.)

• Tuples Implied by name??phones
• If we have tuples
• Then these tuples must also be in the relation.

name addr phones beersLiked sue a p1
b1 sue a p2 b2
sue a p2 b1 sue a p1 b2
9
4NF

• Definition
• A relation R is in 4NF if and only if, for every
  one of its non-trivial multivalued dependencies
  X??Y, X is a superkeythat is, X is either a
  candidate key or a superset thereof.
• Nontrivial MVD means that
• Y is not a subset of X, and
• X and Y are not, together, all the attributes.

10
Decomposition into 4NF

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

11
Decomposition into 4NF Method

• Find a 4NF violation in R, say A??B, where A is
  not a superkey.
• If there is such a 4NF violation, break the
  schema for the relation R that has the 4NF
  violation into two schemas.
• R1, whose schema is As and Bs.
• R2, whose schema is the As and all attributes of
  R that are not among the As or Bs.
• Find the FDs and MVDs that hold in R1 and R2.
  Recursively decompose R1 and R2 with respect to
  their projected dependencies.

12
4NF Decomposition Example

• Drinkers(name, addr, phones, beersLiked)
• FD name?addr
• MVDs name??phones
• name??beersLiked
• Key is name, phones, beersLiked.
• All dependencies violate 4NF.

13
4NF Decomposition Example (cont.)

• Decompose using name?addr
• Drinkers1(name, addr)
• In 4NF only dependency is name?addr.
• Drinkers2(name, phones, beersLiked)
• Not in 4NF. MVDs name??phones and
  name??beersLiked apply. No FDs, so all three
  attributes form the key.

14
4NF Decomposition Example (cont.)

• Decompose Drinkers2
• Either MVD name -gt-gt phones or name -gt-gt
  beersLiked tells us to decompose to
• Drinkers3(name, phones)
• Drinkers4(name, beersLiked)

15
Summary

• A multivalued dependency is a statement that two
  sets of attributes in a relation have sets of
  values that appear in all possible combinations.
• If a relation is in 4NF, then every nontrivial
  MVD is really an FD with a superkey on the left.

16
References

• http//www.datamodel.org/NormalizationRules.html
• http//www.almaden.ibm.com/cs/people/fagin/tods77.
  pdf
• http//www.bkent.net/Doc/simple5.htm
• http//infolab.stanford.edu/ullman/dscb.html