Additional Dependencies and Normalizations

13.1 Multivalued Functional Dependencies (MVDs)

• Not all redundancies are detectable by FDs.
  A schema in BCNF:

  Course Quarter Days 670 Spring TR 670 Autumn MWF 680 Autumn MWF 670 Spring MWF 670 Autumn TR 680 Autumn TR

• Yet they may be still eliminated by decompositions

  Quarters Course Quarter 670 Spring 670 Autumn 680 Autumn

  Days Course Days 670 MWF 670 TR 680 MWF 680 TR

  Days are independent of quarters

• $Y$ is multivalued dependent on $X$ over $R$, denoted $X\to \to Y$, if

  $X$ $Y$ $R-XY$ a b1 c1 a b2 c2

  implies

  $X$ $Y$ $R-XY$ a b1 c2 a b2 c1

• $X\to \to Y$ is trivial if $X$ includes $Y$ or $R-XY$ is empty

Testing Algorithm:

• $X\to \to Y$ iff $R-X=Y\times \left(R-XY\right)$ for each tuple in $X$.

13.2 Inference Rules for MVDs

• FD reflexitivity (Armstrong): $X\to Y$
  if $Y\subseteq X$
• FD augmentation (Armstrong): $XZ\to YZ$
  if $X\to Y$
• FD transitivity (Armstrong): $X\to Z$
  if $X\to Y$ and $Y\to Z$
• MVD augmentation: $XW\to \to YZ$
  if $X\to \to Y$ and $Z\subseteq W$
• MVD transitivity: $X\to \to Z-Y$
  if $X\to \to Y$ and $Y\to \to Z$
• MVD complementation: $X\to \to R-XY$
  if $X\to \to Y$
• Replication: $X\to \to Y$
  if $X\to Y$
  
• Coalescence: $X\to Z$
  if $X\to \to Y$, $W\to Z$, $Z\subseteq Y$, and $W$ and $Z$ are disjoint
  

The set of inference rules is sound and complete.

Given: $A\to BCD$ $B\to \to C$

Implied: $B\to C$

From:

Additional Inference Rules

• MVD union: $X\to \to YZ$
  if $X\to \to Y$, $X\to \to Z$
• MVD pseudotransitivity: $WX\to \to Z-WY$
  if $X\to \to Y$, $WY\to \to Z$
• MV mixed pseudotransitivity: $X\to Z-Y$
  if $X\to \to Y$, $XY\to Z$
• MVD decomposition: $X\to \to Y\cap X$, $X\to \to Y-Z$, $X\to \to Z-Y$
  if $X\to \to Y$, $X\to \to Z$

13.3 Fourth Normal Form

• $X$ is a superkey in each nontrivial $X\to \to Y$
  Course is not a superkey in

  Course Quarter Days 670 Autumn MWF 670 Autumn TR 670 Spring MWF 670 Spring TR 680 Autumn MWF 680 Autumn TR

• If $X$ is a superkey then each partition holds exactly one tuple

Result: The decomposition $X\cup Y$, $R-Y$ is lossless join if $X\to \to Y$.

13.4 Degrees of Decompositions

• Up to 4NF, we considered decompositions replacing a relation by two of its projections

• Some relations require higher degree of decomposition to have lossless join

  • $R=R1\bowtie R2\bowtie R3$

    R A B C a1 b1 c2 a1 b2 c1 a2 b1 c1 a1 b1 c1

    R1 A B a1 b1 a1 b2 a2 b1

    R2 B C b1 c2 b2 c1 b1 c1

    R3 C A c2 a1 c1 a1 c1 a2

  • None of the pairs provide lossless join decomposistion

    $R1\bowtie R2$

    A B C a1 b1 c2 a1 b2 c1 a2 b1 c1 a1 b1 c1 a2 b1 c2

13.5 Join Dependencies (JDs)

• A lossless join decomposition defines a constraint on the entries of the decompompostion
  if <a,b> is a tuple in R1
  and <b,c> is a tuple in R2
  and <c,a> is a tuple in R3
  then <a,b,c> is a tuple in R

• The condition on each projection can be expressed in terms of the original relation
  <a,b> is in R1 iff <a,b,...> is in $R$
  <b,c> is in R2 iff <...,b,c> is in $R$
  <c,a> is in R3 iff <a,...,c> is in $R$

• The above observations imply join dependency constraints
  if <a,b,...>, <...,b,c>, and <a,...,c> are in R
  then <a,b,c> is in R

• The schemas $R_1$, ..., $R_n$ have a join dependency over $R$, if they define a lossles-join decomposition over $R$.
• JDs don’t have a sound and complete set of inference rules.
• A JD is trivial if $R=R_i$ for some i

13.6 Fifth (Project-Join) Normal Form

A relation $R$ is in 5NF iff in every join dependency each $R_i$ is a super key of $R$.

13.7 Assignment #7

Due: Mo, Mar 2

Problems 11.24, 11.29 (page 359 4th ed; 409 5th ed),

Notes

• If you submit your homework electronically, use the departmental submit utility: submit XXX lab7 filename. XXX represents ‘c670aa’ for students of the 3:30 section, and ‘c670ab’ for students of the 5:30 section.
  • Files are restricted to 1,048,576 bytes
  • The submit program issues error messages to the student’s CSE email account

Q&A