Outline

• Defining keys using FDs
• Types of FDs
• Quiz
• Closing Attributes under FDs

Introduction

• Last week, 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.
• Today, we start by looking at how superkey and key constraints can be defined in terms of functional dependencies, and what it meant for one set of FDs to imply another set and for two sets of FDs to be equivalent.

Keys of a Relation

• Given the notion of an FD, we can give a more precise definition of key.
• We say a set of attributes `{A_1, ..., A_n}` is a key for a relation `R` if:
  1. It is a superkey. That is, `A_1,...,A_n -> B` is satisfied for any column `B` in R.
  2. No proper subset of `{A_1, ..., A_n}` is a superkey. I.e., `{A_1, ..., A_n}` is a minimal superkey.
• For example, {title, year, starName} form a key for the relation Movies1 of the previous slide.
• A relation can have more than one key. Often, we choose one of these to be the primary key. This is the key we will thenceforth use most frequently when talking about a key for that relation.

Reasoning About Functional Dependencies

• We now look at a couple of different ways we can infer new functional dependencies given that we know some dependencies already hold.
• For example, suppose we know `R(A,B,C)` satisfies `A -> B` and `B -> C`. Then we also know if we fix column `A` we must also fix column `C`. i.e., `A->C`.
• This shows that we might not need to list all the FDs on a table, it suffices to list a subset from which all other FDs can be derived.
• To reason about this we introduce the following definitions:
  • We say two sets of FDs `S` and `T` are equivalent if the set of relation instances satisfying `S` is exactly the same as the set of relation instances satisfying `T`.
  • We say a set of `FD`'s `S` follows from a set of FD's `T` if every relation instance satisfying all the FDs in `T` also satisfies those of `S`.

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}^+`.

Quiz

Which of the following is true?

1. A relational algebra selection operation applied to a relation might output a relation with fewer rows.
2. It is impossible to define theta-join using the other relational algebra operations.
3. Expressing relational algebra constraints using the form `R = emptyset` allows one to express a wider range of constraints than expressing constraints in the form `R subseteq S`.

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