Outline

• Armstrong's Axioms
• Closing and Projecting sets of FDs
• Quiz
• Designing Relational Schemas
• Anomalies to Avoid in DB Design
• BCNF

Introduction

• Last week, we began look at functional dependencies, `A_1, ..., A_n -> B_1, ..., B_m`.
• Such an FD holds on a relation `R` if knowing the values of all the attributes `A_1, ..., A_n` for a tuple uniquely fixes the possible values of `B_1, ..., B_m` for that tuple.
• We said FDs will be useful in understanding whether or not a particular decomposition of a set of database attributes into relations makes sense.
• We studies different kinds of functions dependencies such a trivial dependencies, `A_1, ..., A_n -> B_1, ..., B_m` where `{B_1, ..., B_m} subseteq {A_1, ..., A_n}` and looked at various rules for deriving new dependencies from a set of starting dependencies.
• Using the splitting rule, we said we can always find a set of FDs equivalent to some set of FDs `S` consisting of FDs of the form: `A_1, ..., A_n -> B`. I.e., only one attribute on the right hand side of a rule.
• We then looked at algorithm to compute the closure of a set of attributes under a set of function dependencies.
• Today, we continue to look at some rules for FDs as we work towards a condition on relation decompositions that will tell us we did a good job designing our database.

Armstrong's Axioms

• We can use our attribute closure algorithm to determine if one FD follows from some given set `S` of FDs.
• (Armstrong 1974) showed that there is a set of rules from which it is possible to derive any FD that follows from a given set `S` of FDs.
• Although we won't prove they are necessary and sufficient, the following Armstrong's Axioms should seem plausible from our work last week and how the attribute closure algorithm operates:
  1. Reflexivity. If `{B_1, ..., B_m} subseteq {A_1, ..., A_n}` then we can derive
     `A_1, ..., A_n -> B_1, ..., B_m.`
     I.e., we can derive any trivial dependency.
  2. Augmentation (sometimes called Weakening). If `A_1, ..., A_n -> B_1, ..., B_m` then we can derive:
     `A_1, ..., A_n, C_1, ..., C_k -> B_1, ..., B_m, C_1, ..., C_k`
     and we are allowed to delete repeated attributes on either side (Contraction).
  3. Transitivity (sometimes called Mix). If `A_1, ..., A_n -> B_1, ..., B_m` and `B_1, ..., B_m -> C_1, ..., C_k`, then we can derive:
     `A_1, ..., A_n -> C_1, ..., C_k`.
• A proof of a function dependency `FD_t` from such axioms and a starting set `S` of FDs is a sequence of FDs, `FD_1`, ..., `FD_t`, such that each `FD_i` is either in `S`, or follows from earlier `FD_j`'s using one of the three rules above.
• For example, if `S={AB->C, C->D}`, then we can derive `AB -> AD` with the proof: `AB -> C` (from `S`), `C->D` (from `S`), `AB->D` (Transitivity), `A\A\B -> AD = AB->AD` (Augmentation).

Minimal Basis of FDs

• Sometimes we have a choice of which FDs we use to represent the full set of FDs for a relation.
• If we are given a set of FDs `S`, then any set of FDs equivalent to `S` is said to be a basis for `S`.
• If we have any basis for `S`, we can apply splitting rules to make the right hand sides singletons.
• A minimal basis for a relation is a basis `B` that satisfies:
  1. All the FDs in `B` have singleton right sides.
  2. If any FD is removed from `B`, the results is no longer a basis.
  3. If for any FD `F` in `B` we remove one or more attributes from the left hand side of `F`, the result is no longer a basis.
• Notice (2) prevents trivial dependencies from being in minimal bases.
• For example, suppose we had the relation `R(A, B, C)` such that each attribute functionally determines the other two. A full set of FDs with one attribute RHSs would include `A->B`, `A->C`, `B->A`, `B->C`, `C->A`, `C->B`, and the derived FDS `AB ->C`, `AC->B`, `BC->A`.
• There are several minimal bases for this, one of which is `{A->B, B->A, B->C, C->B}`.

Projecting FDs

• To study the design of relation schems with respect to FDs, we need to be able to answer: Given `R` with set of FDs `S`, if we compute `R_1 = pi_L(R)`, what FDs hold for `R_1`.
• The answer is given by the projection of the functional dependencies of `S`, all FDs that
  
  1. Follow from `S`, and
  2. Involve only attributes of `R_1`.
• The following algorithm computes these FDs.
  Algorithm to Compute a Set of FDs
  INPUT: A relation `R` and a second relation `R_1` computed by the projection `R_1 = pi_L(R)`. A set of FD's `S` that hold in `R`.
  OUTPUT: The set of FD's that hold in `R_1`.
  METHOD:
  
  1. Set `T=emptyset`.
  2. For each set of attributes `X` that is a subset of the attributes of `R_1` , compute `X^+`. This computation is performed with respect to the set of FD's `S` , and may involve attributes that are in the schema of `R` but not `R_1`. Add to `T` all nontrivial FD's `X -> A` such that `A` is both in `X^+` and an attribute of `R_1`.
  3. Now, `T` is a basis for the FD's that hold in `R_1`, but may not be a minimal basis. We next construct a minimal basis from `T` as follows:
     • If there is an FD `F` in `T` that follows from the other FD's in `T` , remove F from `T`. To do this for each FD `F:= vec{A} -> B`, if `{vec{A}}^+` computed with respect to `T - {F}` contains `B` we can remove `F` from `T`.
     • Let `Y -> B` be an FD in `T` , with at least two attributes in `Y`, and let `Z` be `Y` with one of its attributes removed. If `Z -> B` follows from the FD's in `T` (including `Y -> B` ), then replace `Y -> B` by `Z -> B` .
     • Repeat the above steps in all possible ways until no more changes to `T` can be made.

Example Projecting FDs

• Suppose we want to compute the FDs on `R_1 = pi_{A,C,D}(R(A,B,C,D))` where we have the following FDs, `S` on `R`: `A->B`, `B->C`, and `C->D`.
• To simplify the process, we notice:
  • Closing the empty set and the set of all attributes cannot yield a nontrivial FD.
  • If we already know that the closure of some set `X` is all attributes, then we cannot discover any new FDs by closing supersets of `X`.
• So we start by considering singleton subset of `{A,C,D}` and move onto doubleton sets as necessary.
• For each closure of a set `X`, we add the FD `X->E` for attribute `E` in `(X^+ cap R_1) - X`.
• As `{A}^+ = {A,B,C,D}`, we add `A->C`, `A->D` to `T`.
• As `{C}^+ = {C, D}`, we add `C->D` to `T`.
• Finally, `{D}^+ = {D}`, so add no FDs.
• Since `{A}^+` contains all attributes of `R_1` there is no point in considering any superset of it as they will only add FDs that already follow.
• Next, we consider `{C,D}^+ = {C,D}`, which adds nothing.
• Currently, we have `T = {A->C, A->D, C->D}`. The minimization steps would then reduce this to `T = {A->C, C->D}`.

Quiz

Which of the following is true?

1. Any relational constraint of the form `R = \emptyset` can be rewritten as a constraint of the form `R subseteq S` for some relation `S`.
2. Constraints of the form `R subseteq S` cannot express relational integrity constraints.
3. `{A_1, ..., A_n}^+` adds to the list of attributes in `{A_1, ..., A_n}` only those implied by trivial FDs.

Design of Relational DB Schemas

• We break design problem of good relation schemas into the following stages:
  1. Determine what problems can arise when our schema is poorly designed
  2. Carefully define the notion of "decomposition" of a relation schema.
  3. Introduce Boyce Codd Normal Form a condition which eliminates the poor design problems we will discuss.
  4. Give a technique for computing BCNF given a set of FDs and a starting relations.

Anomalies to Avoid in DB Design

• Problems which occur when we try to cram too much information into a single relation are called anomalies.
• To illustrate the anomalies we have, consider the following table of the Movies1 relation:

  title             year length genre   studioName  starName
  -----------------------------------------------------------
  Star Wars          1977 124    SciFi  Fox         Carrie Fisher
  Star Wars          1977 124    SciFi  Fox         Mark Hamill
  Star Wars          1977 124    SciFi  Fox         Harrison Ford
  Gone With the Wind 1939 231    drama  MGM         Vivien Leigh
  Wayne's World      1992 95     comedy Paramount   Dana Carvey 
  Wayne's World      1992 95     comedy Paramount   Mike Meyers
  

• The three most common kinds of anomalies are:
  1. Redundancy. Information is repeated unnecessarily in several tuples. For example, the length of Star Wars is repeated several times above, but is fixed once we know title and year.
  2. Update Anomalies. We change information in one tuple but leave the same information unchanged in another. For example, if there was an error in the length of Star Wars, say it was 125 minutes, then we might accidentally change the first row only and not the second and third causing the data to be in an inconsistent state (the same movie can't have two different lengths).
  3. Deletion Anomalies. If a set of values is empty, we may lose other information as a side effect. For example, suppose Vivien Leigh didn't want her data in our DB. If we delete her, we might also delete the whole movie Gone With the Wind, so we would the info that it was drama and 231 minutes long.

Decomposing Relations

• The accepted way to eliminate these anomalies is to decompose relations.
• Given a relation `R(A_1, ..., A_n)`, we may decompose it into two relations `S(B_1,..., B_m)` and `T(C_1,...,C_k)` such that:
  1. `{A_1, ..., A_n} = {B_1,..., B_m} cup {C_1,...,C_k}.`
  2. `S = pi_{B_1,..., B_m}(R)`.
  3. `T = pi_{C_1,..., C_k}(R)`
• For example, we can decompose Movies1 into two relations with schemas Movies2(title, year, length, genre, studioName) and Movies3(title, year, starName) yielding the relations:

  title             year length genre   studioName
  ------------------------------------------------
  Star Wars          1977 124    SciFi  Fox
  Gone With the Wind 1939 231    drama  MGM
  Wayne's World      1992 95     comedy Paramount
  
  and
  
  title             year   starName
  ---------------------------------
  Star Wars          1977  Carrie Fisher
  Star Wars          1977  Mark Hamill
  Star Wars          1977  Harrison Ford
  Gone With the Wind 1939  Vivien Leigh
  Wayne's World      1992  Dana Carvey 
  Wayne's World      1992  Mike Meyers
  

• Notice the redundant repetition of movie length is eliminated, as well as the issue of update anomalies for length of movies, and deletion anomalies for Gone With the Wind.

Boyce-Codd Normal Form (BCNF)

• Our goal of decomposition is to replace a relation by several that do not exhibit anomalies,
• One condition that can assure that the three anomalies we just described won't occur is Boyce Codd Normal Form.
• A relation `R` is in Boyce Codd Normal Form (BCNF) if and only if: whenever there is a nontrivial FD `A_1, ..., A_n -> B_1, ..., B_m` for `R`, it is the case that `{A_1, ..., A_n}` is a superkey of `R`.
• For example, Movies1 is not in BCNF as it has the nontrivial dependency `mbox(title year) -> mbox(length)`, but `mbox(title, year)` is not a superkey.
• On the other hand, the decomposition into Movies2 and Movies3 is in BCNF.