Requirements on Decompositions

12.1 Preserving Dependencies

• Decomposition of schemes based on functional dependencies is motivated by separating data into topics.

  St-Name Status Course-Name Course-# Salary Ben senior db 670 500 Ben senior ds 680 500 Dan freshment db 670 400 Dan freshment ds 680 600
  St-Name $\to$ Status
  St-Name Course-Name Course-# Salary Ben db 670 500 Ben ds 680 500 Dan db 670 400 Dan ds 680 600		St-Name Status Ben senior Dan freshment
  Course-# $\to$ Course-Name
  St-Name Course-# Salary Ben 670 500 Ben 680 500 Dan 670 400 Dan 680 600	Course-Name Course-# db 670 ds 680

• 1NF, 2NF, 3NF, BCNF relate to integrity constraints verifiable within a schema

• Consider FD over, but not across, schema

  Eyes SSN ColorName Red Green Blue 123-45-6789 brown 165 42 42 234-56-7890 green 0 255 0

  SSN $\to$ ColorName ColorName $\to$ Red Green Blue

  Bad: Decomposition on SSN $\to$ ColorName

  ByName SSN ColorName 123-45-6789 brown 234-56-7890 green	SSN $\to$ ColorName
  ByRGB SSN Red Green Blue 123-45-6789 165 42 42 234-56-7890 0 255 0	SSN $\to$ Red Green Blue

  • Natural join: Gives back the original data
  • Lost ‘topic’: ColorName $\to$ Red Green Blue

  Good: Decomposition on ColorName $\to$ Red Green Blue

  ByName ColorName Red Green Blue brown 165 42 42 green 0 255 0	ColorName $\to$ Red Green Blue
  ByRGB SSN ColorName 123-45-6789 brown 234-56-7890 green	SSN $\to$ ColorName

• Decompositions should ensure representation of each functional dependency in the decomposed relations

  $\underset{¯}{A}$ B C D E
  $\underset{¯}{A}$ B C D	$\underset{¯}{C}$ $\underset{¯}{D}$ E

  Given:

  A $\to$ BCDE
  CD $\to$ E
  CE $\to$ B

  Normalized:

  A $\to$ BCD
  CD $\to$ E

  CE $\to$ B fails: B is not in $\left\{\right.$CE${\left.\right\}}^{+}$

  Note: The dependencies are preserved for a decomposition to R1=(A,C,D) and R2=(B,C,D,E)

• Objective: Introduce a dependency-preserving 3NF decomposition algorithm
• The subset $F_X$ of the closure $F^{+}$ which uses only attributes of $X$ is the projection of F on X
• A decomposition of $R$ into $R\left(X\right)$ and $R\left(Y\right)$ is dependency preserving if $F^{+}={\left(F_X\cup F_Y\right)}^{+}$

Minimal Cover

$F$ is a minimal set of FDs if each $X\to Y$ is

1. Canonical: $\left|Y\right|=1$
2. Left-reduced: $X$ can’t be replaced by a subset
3. Non-redundant: $X\to Y$ can’t be removed

Note

• $\alpha \to \beta$ may be replaced by $\alpha \to \left\{\beta -A\right\}$, if $\beta$ is still in the closure of $\left\{\beta -A\right\}$
• $\alpha \to \beta$ may be replaced by $\left\{\alpha -A\right\}\to \beta$, if $\beta$ is still in the closure of $\left\{\alpha -A\right\}$

A Dependency-Preserving 3NF Decomposition Algorithm

• Find minimal cover
• Put FDs agreeing on the left-hand-side in the same schema
• Have extra schema for unaccounted attributes

A synthesis algorithm for defining schemas directly from the FDs.

12.2 Lossless (Nonadditive) Joins

• Decompositions are projections of relational schemas

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

  ${\pi }_{A,B}$ A B a1 b1 a2 b2 a3 b1

  ${\pi }_{B,C}$ B C b1 c1 b2 c2 b1 c3

• Old tables should be derivable from the newer ones through the natural join operation

  ${\pi }_{A,B}\left(R\right)\bowtie {\pi }_{B,C}\left(R\right)$

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

  The decomposition is improper as the natural join introduces extra improper records.

• $R_1$, $R_2$ is a lossless join decomposition of $R$ iff the attributes common to $R_1$ and $R_2$ contain a key for at least one of the involved relations

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

  ${\pi }_{A,B}$ A B a1 b1 a2 b2 a3 b1

  ${\pi }_{B,C}$ B C b1 c1 b2 c2

  ${\pi }_{A,B}\left(R\right)\cap {\pi }_{B,C}\left(R\right)=B$

Testing

Given:
Decomposition $D=\left\{R_1,...,R_m\right\}$ of $R$
Attributes $\left\{A_1,...,A_n\right\}$
FDs $F=\left\{F_1,...,F_r\right\}$

• Create $m\times n$ matrix $S$
• Initialize
  $S\left[i,j\right]$ := $a$, if $A_j$ is in $R_i$
  $S\left[i,j\right]$ := $b_i$, otherwise
• If $S\left[k\right]=S\left[t\right]$ on $X$, for $X\to Y$,
  adjust the rows to agree also on corresponding attributes of $Y$
  • Propogate $a$’s, when possible
  • Propogate any $b_i$’s, if no $a$ is available

• A lossless join decomposition iff there a exist a row of $a$’s

A Dependency-Preserving Lossless-Join 3NF Decomposition Algorithm

• Find minimal cover
• Put FDs agreeing on the left-hand-side in the same schema
• Have extra schema for a key, if none of the above schemas contain a key

12.3 Life is Not Easy

• Schemas can always employ lossless-join dependency-preserving decompositions to achieve 3NF
• Determining whether a relationship schema satisfies 3NF is NP-complete. Hence, good design can’t rely too heavily on automated normalization.
• Not all violations to BCNF can be resolved through dependency-preserving decompositions

  MANAGER PROJECT DEPT

  $\left\{\right.$PROJECT, DEPT$\left.\right\}$ $\to$ MANAGER
  MANAGER $\to$ DEPT

  • Doesn’t satisfy BCNF
  • Decompositions can’t preserve dependencies
  • 3NF is satisfied

Reference: Ch. 11.1-11.2 in textbook.