Introduction

On Monday, we began talking about functional dependencies (FDs) between attributes of a relational database.
A functional dependency (FD) on a relation `R` is a statement of the form "If two tuples of `R` agree on all of the attributes `A_1, A_2, ..., A_n`, then they will agree on the attributes `B_1, ..., B_m`" and is written as `A_1, ..., A_n -> B_1, ..., B_m`.
We said we were going to use FDs to determine if a decomposition of the data to be stored in a database into relations was reasonable or not.
We talked about how superkey and key constraints can be defined in terms of functional dependencies, and we said what it meant for one set of FDs to imply another set and for two sets of FDs to be equivalent.
We said the FD derivation from the FDs `A -> B` and `B ->C`, that `A ->C` follows is called the transitive rule.
We looked at rules for deriving FDs from an existing set of FDs base on splitting and combining as well as eliminating trivial dependencies to simplify rules.
Today, we start by reviewing the algorithm mentioned from the very end of last day to compute the closure of a set of attributes under a set of FDs.

Computing the closure of Attributes under FDs

Given a set of attributes `{A_1, ..., A_n}` and a set of FDs `S`, for what attributes `B` can we derive the FD `A_1, ..., A_n -> B`?
We call this set of attributes the closure of `{A_1, ..., A_n}` under `S` and denote this set by the notation `{A_1, ..., A_n}^+.`
Since `A_1, ..., A_n -> A_i` is trivial, we have that `{A_1, ..., A_n} subseteq {A_1, ..., A_n}^+`.
The following algorithm computes `{A_1, ..., A_n}^+`:
INPUT: A set of attributes `{A_1, ..., A_n}` and a set of FD's `S`.
OUTPUT: The closure `{A_1, ..., A_n}^+` .
1. For each FD's of `S`, if it has more that one attribute on its right hand side, replace it in the set `S` using the splitting rule with its equivalent set of rules each with only one item on the right handle side.
2. Let `X:={A_1, ..., A_n}`, `X_{old} := emptyset`.
3. While `X != X_{old}`:
  - `X_{old} := X`.
  - For each FD, if it is of the form `B_1,..., B_m -> C` such that all `B_i` are in `X`, but `C` is not:
    - Add `C` to `X`.
4. Return `X` as the closure.
Since there are only finitely many distinct attributes, say `t`, after after at most `t` times through the while loop the above algorithm must terminate.

Example of Computing the Closure

Suppose we have a relation with attributes `A, B, C, D, E`, `F` and we have the FDs `AB ->C`, `BC->AD`, `D->E`, and `CF->B`.
What is the closure of `{A,B}` under these FD's?
After step 1 of our algorithm we would have rewritten our FDs as `AB ->C`, `BC->A`, `BC->D`, `D->E`, and `CF->B`.
Since `{A,B} != emptyset` we execute the while loop at least once, and set `X_{old} = {A, B}`.
The first time through the while, we would execute the for each and first note that for the FD `AB ->C`, `A,B in {A,B}`, so we'd add `C` to `X` obtaining `X = {A,B,C}`.
The next time through the for each the rule `BC->A` doesn't change `X`, the third time, however, the rule `BC->D` allows us to get `D`, and so `X` will be `X = {A,B,C,D}`.
The fourth time through the for each, we consider `D->E` and get `X ={A,B,C,D,E}`.
The fifth time through the for each doesn't change `X`.
Since `X={A,B,C,D,E}` and `X_{old} = {A,B}`, we would then execute the while loop another time.
However, this time through the while there would be no change, and so `X={A,B,C,D,E}` would be eventually output as the value of `{A,B}^+`.

In-Class Exercise

What would be the effect of ordering the FDs in our last example as: `BC->D`, `BC->A`, `D->E`, `CF->B`, and `AB ->C`?
Show how the algorithm would compute the closure of `{A, D, F}` using the same FDs.
Post your solutions to the Sep 18 In-Class Exercise Thread.

Proof that the Closure Algorithm Works

To show our closure algorithm works, we need to show:
1. If `B` is derived by our algorithm that `A_1, ..., A_n ->B` holds in any relation that satisfies `S`.
2. If `A_1, ..., A_n ->B` is derivable from `S` that `B` will be in the closure derived by our algorithm.

Proof of (1)

The proof is by induction `t`, where `t` is the number of times through the while loop `w` times the number of FDs plus the number of times through the foreach on last time through. That is, `t =w *|S| + f`.
BASE CASE Suppose `t = w*|S| + f = 0`. Then if `B` is `X`, `B` must be one of our attributes `{A_1, ..., A_n}`, and so `A_1, ..., A_n -> B` will hold as a trivial dependency.

INDUCTION STEP: Suppose `B` is derived by our algorithm in the `t+1`st step. Then it must have been derived using some FD in `S`, `B_1, ...,B_m -> B`. This means that each of the `B_j` must be in `X` and had to have been derived by at most step `t`. So by our induction hypothesis, for each `B_j`, `A_1, ..., A_n ->B_j`. So if two relations agree on `A_1, ..., A_n`, then will also agree on `B_1, ..., B_m`, and hence by the FD `B_1, ...,B_m -> B`, they will agree on `B`. Thus the induction holds, complete the Proof of 1.

Proof of (2)

Suppose `A_1, ..., A_n ->B` is derivable from `S`, but `B` is not derived by our algorithm.
We want to show that these together imply `A_1, ..., A_n ->B` isn't derivable from `S` to get a contradiction.
To do this, we show there is at least one relation instance that satisfies all the `FD`'s in `S`, and yet does not satisfy `A_1, ..., A_n ->B`.
Let `X` be the set of attributes produced by the end of our algorithm. Let `I` be the relation instance consisting of two tuples `t` and `s`:
```
        X       All other Attributes not in X
t: 1 1 1...1 1   0 0 0 ... 0 0
s: 1 1 1...1 1   1 1 1 ... 1 1
```
From our assumption `B` will be an attribute not in `X`.
Now suppose `C_1,..., C_k ->D` is in `S`, but is not satisfied by `I`.
Since `I` only has two tuples, then those tuples must violate `C_1,..., C_k ->D`.
So `t` and `s` must agree on `{C_1,..., C_k}`, but disagree on `D`.
Since the only attributes on which `t` and `s` agree are those of `X`, we have `{C_1,..., C_k} subseteq X` and `D` is in all other attributes.
But this means, we should have derived `D` using our algorithm when we went through the foreach.
Therefore, a rule `C_1,..., C_k ->D` is in `S`, but not satisfied by `I` cannot exist.
This in turn implies `I` does not satisfy `A_1, ..., A_n ->B` as for sure `t` and `s` agree on the `A_i`'s.
We are assuming `B` is not in `X`, so `B` is one of the attributes of `I` on which `t` and `s` disagree. Thus, `I` does not satisfy `A_1, ..., A_n ->B`.

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 schemes 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}`.

Functional Dependencies and Attribute Closures

Outline

Introduction

Computing the closure of Attributes under FDs

Example of Computing the Closure

In-Class Exercise

Proof that the Closure Algorithm Works

Proof of (1)

Proof of (2)

Armstrong's Axioms

Minimal Basis of FDs

Projecting FDs

Example Projecting FDs