Designing Databases

Aim of database design is to get relations that are free of

• redundancy
• anomalies on
  • insertion
  • update
  • deletion

Design Methods

Two ways to design relations.

1. Put everything in one giant table, then normalize.
2. Use high-level design tool like E/R diagram.

Functional Dependencies

Let \(A_1,\dots,A_n,B_1,\dots,B_m\) be attributes of a relation \(R\). The functional dependency (FD) \[A_1 \dots A_n \to B_1 \dots B_m\] means

for any two tuples of any instance of \(R\), if they agree on all attributes in \(\{ A_1, \dots, A_n \}\) then they also agree on all attributes in \(\{ B_1, \dots, B_m \}\).

In such a case, we say that \(\{A_1, \dots, A_n\}\) functionally determines \(\{B_1, \dots, B_m\}\).

FD Satisfaction

• Let R be a relation and let F be an FD. We say that R satisfies F if F holds in every instance of R.
• Let R be a relation and let S be a set of FD’s. We say that R satisfies S if R satisfies F for every F in S.

Observations on FD’s

1. Splitting/Combining Rule \[A_1 \dots A_n \to B_1\dots B_m\] is equivalent to the conjunction of all these \(m\) FD’s \[A_1\dots A_n \to B_1\] \[\vdots\] \[A_1\dots A_n \to B_m\]

2. Trivial Dependency An FD \(A_1\dots A_n \to B_1\dots B_m\) is said to be trivial if \(\{ B_1,\dots, B_m\} \subseteq \{A_1,\dots, A_n\}\). Every trivial dependency is true.

An Example

In the relation

Movies1(title, year, length, genre, studioName, starName)

we have

title year \(\to\) length genre studioName

However, it is false that

title, year \(\to\) starName

Keys

• A superkey for a relation \(R\) is a set of attributes \(\{ A_1, \dots, A_n \}\) of \(R\) that functionally determines all other attributes of \(R\).
• A key is a minimal superkey, i.e., it is a superkey but no proper subset of it is a superkey.
• Exercise: Show that { title, year, starName } is a key for the Movies1 relation.

Primary Key

• A primary key of a relation \(R\) is some chosen key when \(R\) has more than one key.
• Some authors call the different keys candidate keys.

Definitions

Let \(S\) and \(T\) be sets of FD’s on the same relation \(R\).

• We say that \(S\) follows from \(T\) if every instance of \(R\) that satisfies \(T\) also satisfies \(S\).
• We say that \(S\) and \(T\) are equivalent if \(S\) follows from \(T\) and \(T\) follows from \(S\).

Trivial-Dependency Rule

The FD \(A_1 \dots A_n \to B_1\dots B_m\) is equilvalent to the FD \(A_1\dots A_n \to C_1\dots C_k\), where the \(C\)’s are all of \(B\)’s that are not in \(A\).

Closure

Let \(A = \{ A_1, \cdots, A_n \}\) be a subset of attributes of a fixed relation \(R\), and let \(S\) be a set of FD’s that \(R\) satisfies. The closure of \(A\) with respect to \(S\) is a set of all attributes \(B\) such that the FD \(A_1\dots A_n \to B\) follows from \(S\). It is denoted \(A^+\).

Closure Algorithm

Input: 1. Attributes \(A_1,\dots,A_n\) of relation \(R\)
2. a set \(S\) of FD’s on \(R\)
Return: The positive closure of \(\{A_1,\dots,A_n\}\) with respect to \(S\).

    split S;
    X := { A1,..., An };
    repeat
        if there exists a subset { B1,...,Bn } of X
        having B1...Bn → C in S with C not in X then
            X := X ∪ {C}
    until (no such subset is found);
    return X;

Example 3.8

Problem: Consider the relation \(R(A, B, C, D, E, F)\) and set \(S = \{ AB\to C,\ BC\to AD,\ D\to E,\ CF\to B \}\) of FD’s. Compute \(\{A,B\}^+\).

Transitivity Rule

The Transitivity Rule states that

If \[A_1\dots A_n \to B_1\dots B_m\] and \[B_1\dots B_m \to C_1\dots C_k\] then \[A_1\dots A_n \to C_1\dots C_k.\]

It can be proved by considering \(\{A_1,\dots,A_n\}^+\).

Key and Closure

• To test whether a set of attributes \(A = \{A_1,\dots,A_n\}\) of a relation \(R\) is a key,
  1. compute \(A^+\)
  2. if \(A^+\) equals the set of all attributes of \(R\), then it is a superkey
  3. check minimality of the superkey

Armstrong’s Axioms

Armstrong’s Axioms is a set of inference rules that can be used to derive any FD that follows from a given set of FD’s. There are three axioms in the set.

1. Reflexivity. If \(\{B_1,\dots,B_m\}\subseteq\{A_1,\dots,A_n\}\), then \(A_1\dots A_n\to B_1\dots B_m\)
2. Augmentation. If \(A_1\dots A_n\to B_1\dots B_m\), then \(A_1\dots A_nC_1\dots C_k\to B_1\dots B_mC_1\dots C_k\) for any set of attributes \(\{C_1,\dots,C_k\}\).
3. Transitivity. If \(A_1\dots A_n\to B_1\dots B_m\) and \(B_1\dots B_M\to C_1\dots C_k\), then \(A_1\dots A_n\to C_1\dots C_k\).

Closure and Equivalence of sets of FD’s

• Given a set of FD’s \(S\), and given an FD \(A_1, \dots, A_n \to B\). We can test whether or not this FD follows from \(S\) by computing the closure of \(\{ A_1, \dots, A_n \}\) and check if \(B\) is in this closure or not.
• Given 2 sets of FD’s \(S\) and \(T\), we can check if \(T\) follows from \(S\) by checking whether every FD in \(T\) follows from \(S\).
• So we can use closure to test for equivalence of two sets of FD’s.

Bases

Let \(S\) be a set of FD’s.

• A basis for \(S\) is any set of FD’s that is equivalent to \(S\).
• A basis \(B\) for \(S\) is a minimal basis if it satisfies all these conditions:
  1. All the FD’s in \(B\) have singleton right sides.
  2. If any FD is removed from \(B\), the result is not a basis for \(S\).
  3. If for any FD \(F\) in \(B\) we remove one or more attributes from the left side of \(F\), the result is no longer a basis for \(S\).

Example 3.11

Problem: Let \(R(A,B,C)\) be a relation satisfying the set of FD’s \(S = \{\) \(A\to B\), \(A\to C\), \(B\to A\), \(B\to C\), \(C\to A\), \(C\to B\) \(\}\).

1. Show that \(S_1 = \{A\to B,\ B\to A,\ B\to C,\ C\to B\}\) is a minimal basis for \(S\).
2. Show that \(S_2 = \{A\to B,\ B\to C,\ C\to A\}\) is also a minimal basis for \(S\).
3. Can you characterize the minimal basis for \(S\)?
4. Find the remaining minimal bases for \(S\).

Projecting Functional Dependencies

• Problem: Let \(R\) be a relation satisfying the set of FD’s \(S\), and let \(L\) be a list of attributes of \(R\). Define \(R_1 := \pi_L(R)\). Determine the FD’s that hold in \(R_1\).
• A projection of functional dependencies \(S\) is the set of all FD’s that
  1. Follows from \(S\), and
  2. Involves only attributes of \(R_1\)

Projection Algorithm

Input: Relations \(R\) and \(R_1 = \pi_L(R)\) and a set of FD’s on \(R\).
Return: A minimal basis for the set of all FD’s that hold in \(R_1\) as a result of FD’s on \(R\).

    T := empty set;
    for each subset of attributes X of R_1 do {
        compute X+;
        add to T all nontrivial FD's X → A such that
        A is both in X+ and an attribute of R_1;
    }
    repeat
      if there's an FD F in T that follows from
      the other FD's in T then remove F from T;
      let Y → B be an FD in T with # attributes in Y ≥ 2
      and let Z be Y with one of the attributes removed;
      if Z → B follows from the FD's in T then
          replace Y → B by Z → B;
    until (no more changes to T can be made);
    return T;

Example 3.13

Problem: Let \(R(A,B,C,D)\) be a relation that satisfies a set of FD’s \(S = \{ A\to B,\ B\to C,\ C\to D \}\). Find a projection of \(S\) onto \(R_1(A,C,D)\).