Introduction

On Monday, we were looking at techniques for splitting a big relation/table with attributes/columns for each type of data we need to stort into a DB into a relational schemas/ set of tables.
One reason to perform such a split is to prevent redundant data, update, and deletion anomalies.
We said how to analyze this problem in terms of functional dependencies (FDs) `A_1,...,A_n -> B_1,...,B_m` and came up with the Boyce Codd Normal Form (BCNF) condition for relations in a relational schema:
- 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`.
We also said how to determine the FDs which result when the FDs of the original table are applied to decomposed tables.
We start today by looking at how to decompose a relation into BCNF relations.

Decomposition into BCNF

Given a relation `R` not in BCNF we want to split it into smaller relations `R_1, ..., R_m` such that:
1. Each of these `R_j` is in BCNF.
2. The data in the original relation is represented faithfully by the data in the relations that result of the decomposition.
It is condition (2) that we will see prevents us from splitting `R` into relations which just consist of two or one attributes.
Our decomposition strategy will be to look for a nontrivial FD `A_1, ..., A_n -> B_1, ..., B_m` that violates BCNF, i.e., `{A_1, ...,A_n}` is not a superkey.
We then create a new relation consisting of the attributes `{A_1, ...,A_n}` together with all the attributes functionally determined by `{A_1, ...,A_n}`. The remaining attributes together with `{A_1, ...,A_n}`, we also put into a different relation.
These two new tables will now not have that FD as a violator of BCNF. We can now repeat the process on each of these tables if there are still nontrivial violators of BCNF.

Example

Recall 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

`mbox(title year) -> mbox(length genre studioName)` violates BCNF, so we make a table out of it.
The process we just described splits this into two relations Movies2(title, year, length, genre, studioName) and Movies3(title, year, starName).
We argued last day that these two tables are now in BNCF.

Algorithm for BCNF Decomposition

Input. A relation `R_0` with a set of FDs `S_0`.

Output. A decomposition of `R_0` into a collection of relations, all of which are in BCNF.

Method. The following steps are applied recursively to any relation `R` and set of FDs `S`. Initially, set `R = R_0`, `S= S_0`.

Check whether `R` is in BCNF. If so, nothing more needs to be done. Return `{R}` as the answer.
If there are BCNF violations, let one be `X -> Y` . Compute `X^+` using our algorithm from last Wednesday. Choose `R_1 = X^+` as one relation schema and let `R_2` have attributes `X` and those attributes of `R` that are not in `X^+`.
Use the algorithm from Monday to compute the sets of FD's for `R_1` and `R_2` ; let these be `S_1` and `S_2`, respectively.
Recursively decompose `R_1` and `R_2` using this algorithm. Return the union of the results of these decompositions.

In-Class Exercise

Consider the relation `R(A,B,C,D,E)` with FD `A->BC`, `B->D`, `D->E`.
Use the BCNF Algorithm to decompose this relation into relations which satisfy BCNF.
Post your solution to the Sep 26 In-Class Exercise Thread.

More on "Good" Relation Schemas

Let's revisit what we mean when we say a relation schema is "good".
So far, we have mainly only said that if it has anomalies it is "bad".
We now consider three properties we'd like to have:
1. Elimination of Anomalies. We've already discussed this.
2. Recoverability of Information. Can we recover the original relation from the tuples in the decomposition?
3. Preservation of Dependencies. If we check the projected FD's in the relation of the decomposition, can we be sure that when we reconstruct the original relation from the decomposition by joining, that the result will satisfy the original FDs?

The Importance of Joins

If we naively, split a relation into a set of two attribute sub relations it will be in BCNF, so why bother with our complicated algorithm?
We would like it to be the case that if we natural join the sub-relations we get back the original relation. Such a join is a called a lossless join.
This is not always the case with such a naive splitting. For example, consider the relation R(A,B,C):
```
A B C
-----
1 2 3
4 2 5
```
Notice neither `B->A` nor `B->C` holds.
If we project this table on to AB and BC we get respectively the tables:
```
A B
---
1 2
4 2
```
and
```
B C
---
2 3
2 5
```
Joining these together gives the table:
```
A B C
-----
1 2 3
1 2 5
4 2 3
4 2 5
```
which is not the same as the original table.
On the other hand, our algorithm relies on the fact that if `Y->Z` holds in `R`, whose attributes are `X cup Y cup Z`, then one can show $R = \pi_{X \cup Y}(R) \bowtie \pi_{Y \cup Z}(R)$.

Chase Test for Lossless Join - Making a Tableaux

We will see in a bit that BCNF decomposition is perhaps a little too aggressive, so we might want to consider weaker decompositions.
We would like to be able to check for a decomposition of `R` into `S_1, ..., S_k` whether $R = \pi_{S_1}(R) \bowtie \pi_{S_2}(R) \bowtie \cdots \bowtie \pi_{S_k}(R)$.
Notice natural join is associative and commutative, we also always have $R \subseteq \pi_{S_1}(R) \bowtie \pi_{S_2}(R) \bowtie \cdots \bowtie \pi_{S_k}(R)$, so to show a join is lossless it suffice to come up with a test that every $t \in \pi_{S_1}(R) \bowtie \pi_{S_2}(R) \bowtie \cdots \bowtie \pi_{S_k}(R)$ belongs to `R`.
A `t` in $\pi_{S_1}(R) \bowtie \pi_{S_2}(R) \bowtie \cdots \bowtie \pi_{S_k}(R)$ must come from joining the projections onto `S_i` of some tuples `t_1, ..., t_k in R`
The chase test involves creating a picture of what we know. Suppose `R` has attributes `A,B, ...` then we use `a, b,... ` for the component of a `t` in the join.
We make a table of rows `t_1, ..., t_k`. We use the same letter as `t` in the components that are in `S_i`, but we subscript with `i` those components not in `S_i`.
For example, if our relation was `R(A,B,C,D)` and we decomposed it into `S_1 = {A,D}`, `S_2 = {A,C}`, and `S_3 = {B,C,D}`, then our tableau would look like:
```
A  B  C  D
-----------
a  b1 c1 d
a  b2 c  d2
a3 b  c  d
```

Chase Test for Lossless Join - Performing a Chase

We now try to use our FDs to show the rows in our tableau can derive back `t` in the join. This will show `t` is really in `R`.
To do this, we "chase" the tableau by applying the FDs in `F` to equate symbols whenever we can.
Whenever we have an FD `X->Y`, we look for rows where there `X` components agree, and then we set there `Y` components to be the same. To keep things simple, if we have two values `b_i` and `b_j` that need to be equated then we replace them with the smaller indexed valued or just `b` if one of the component is `b`.
For example, suppose we have the relation of the last slide and FDs `A->B`, `B->C`, and `CD->A`. Applying the chase to our relation for the FD `A->B`, yields:
```
A  B  C  D
-----------
a  b1 c1 d
a  b1 c  d2
a3 b  c  d
```

Next applying the chase for `B->C` yields:

A  B  C  D
-----------
a  b1 c d
a  b1 c  d2
a3 b  c  d

Applying the chase for `CD->A` yields:

A  B  C  D
-----------
a  b1 c  d
a  b1 c  d2
a  b  c  d

Notice in applying the chase for these rules, the last row has become `a b c d`. This tells us the chase has proven that `t` is in `R` and so we have a lossless join.
If we hadn't had this we would continue to cycle through the FDs again until we got no change in the rows, if we never derive `a b c d` then the join isn't lossless.

BCNF and Lossless Join Algorithms

Outline