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 start today by looking at rules for deriving FDs from an existing set of FDs...

Splitting Combining Rule

The splitting rule says that we can replace an FD `A_1, ..., A_n -> B_1, ..., B_m` by a set of FDs `A_1, ..., A_n -> B_i` for each `i`.
The combining rules says we can replace a set of FDs `A_1, ..., A_n -> B_i` for each `i` with the single FD `A_1, ..., A_n -> B_1, ..., B_m`.

As an example, using the splitting and combining rules, the set of FDs:

title year -> length 
title year -> genre 
title year -> studioName

is equivalent to the FD:

title year -> length genre studioName

Note the splitting combining rules do not apply to left hand sides of rules. For example,
```
title year -> length 
```
is not equivalent to
```
title -> length
year -> length
```

Trivial Functional Dependencies

A constraint of any kind on a relation is said to be trivial if it holds for every instance of the relation regardless of what other constraints are assumed.
An FD constraint will be trivial if it is of the form `A_1, ..., A_n -> B_1, ..., B_m` and `{B_1, ..., B_m} subseteq {A_1, ..., A_n}`.
This kind of constraint roughly says that if I know the value of some `B_i` then I know the values of that `B_i`.
For example,
```
title year -> title
and
title -> title
```
are both trivial.
Using the splitting and combining rule and the notion of trivial dependency, we can derive the related trivial dependency rule:
- The FD `A_1, ..., A_n -> B_1, ..., B_m` is equivalent to the FD `A_1, ..., A_n -> C_1, ..., C_k`, where `{C_1, ..., C_k} = {B_1, ..., B_m} - {A_1, ..., A_n}`.

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`, `D->E`, `AB ->C`, `BC->A`, and `CF->B`?
Show how the algorithm would compute the closure of `{C, D, F}` using the same FDs.
Post your solutions to the Sep 19 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`.

Functional Dependencies and Attribute Closures

Outline

Introduction

Splitting Combining Rule

Trivial Functional Dependencies

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)