Introduction

Before the Midterm, we were looking at algorithms that allow us to go from a set of attributes and functional dependencies on those attributes to a "reasonable" relation schema.
Here "reasonable" meant the relations in the schema were not susceptible to redundancy, update or deletion anomalies.
Here "reasonable" also meant that we had a lossless join decomposition. That is, if we natural join our relations together into a big relation, we get no more rows than would have been present in the original data.
We said that if the relations in the schema were in Boyce Codd Normal Form (BCNF) (Boyce-Codd 1975, Heath 1971), that is, if a relation in the schema has a nontrivial FD on it, then the left hand side of the FD is a superkey; then our schema would be largely free of anomalies.
We then gave algorithms to check if a set of relations had the lossless join decomposition property and to convert a set of relations into a set in BCNF with the lossless join decomposition (LJDP) property .
We start today by looking at some drawbacks of BCNF.

Dependency Preservation

Besides LJD property, another property that we might want for our relations is dependency preservation (DP).
A schema has the DP Property if we check the projected FDs in the relations of the decomposition, we can be sure that when we reconstruct the original relation via joining, the result will satisfy the original FDs.
It turns out that one can't always decompose into BCNF so that the decomposed tables satisfy both the LJD and DP properties.

As an example, suppose we have the following relation:
```
Bookings(title, theater, city)
```
subject to the FDs:
```
theater -> city
title city -> theater.
```
A tuple in bookings means movie title is booked into a given theater in a given city.
The second FD means for small cities we don't want the same movie booked into two theaters in the same city.
Neither title, theater, nor city, are keys by themselves. There are in fact two possible keys:
{title, city} and {theater, title}.
So Bookings violates BCNF because of theater -> city as theater is not a superkey.

Using our method of splitting a relation according to an FD to make sure that FD isn't a BCNF violation, we can decompose Bookings into the tables:
```
{theater, city}
{theater, title}
```

Consider the following instances of these two tables:

theater | city
--------------
Guild   | Menlo Park
Park    | Menlo Park

theater | title
---------------
Guild   | Antz
Park    | Antz

On the other hand, if we join them, we get the table:

theater | city       | title
----------------------------
Guild   | Menlo Park | Antz
Park    | Menlo Park | Antz

which violates: title city -> theater.

Which of the following is true?

A functional dependency is a kind of deletion anomaly.
The BCNF decomposition algorithm relies on being able to compute the closure of a set of attributes by a set of FDs.
The chase test was used to test if a table was in BCNF.

We can slightly weaken BCNF to get a normal form which largely prevents anomalies and does have the LJD and DP properties.
We say a relation is in third normal form (3NF) if:
- Whenever `A_1,...A_n -> B_1, ..., B_m` is a nontrivial FD, either
  `{A_1,...A_n}`
  is a superkey, or those of `B_1, ..., B_m` that are not among the `A's` are each a member of some key (not necessarily the same).
- An attribute that is a member of some key is often said to be prime.
- So 3NF can also be defined as "for each nontrivial FD, either the left hand side is a superkey or the right hand side consists of prime attributes only."
- Bookings is an example of a 3NF relation as although in theater -> city, theater is not a superkey, city is part of a key.

You might wonder what 1NF and a 2NF are giving that we just defined 3NF.
1NF and 2NF were defined in the same 1971 paper as 3NF.
A table is in first normal form (1NF) says that every component of every tuple is atomic. That is, we can't split it into sub-components.
For example, one could view a field NAME as not in 1NF because it can be split into first and last name. Similarly, storing semi-structured data into a table column might be viewed as violating 1NF.
A table is in second normal form (2NF) if it is in 1NF and no non-prime attribute is dependent on any proper subset of any candidate key of the relation.
For example, consider the relation `R(A,B,C,D)` on which we have the FDs `A->C`, `B->D`. The only key is `AB`. So `C`, which is non-prime, depends on only `A` not both `A` and `B`. So `R` is not in 2NF.
If either condition holds for 3NF, we won't have a violation of 2NF, we have that any 3NF schema will also be 2NF.

Input

A relation `R` and a set of FDs `F` that hold on `R`.

Output

A decomposition of `R` into relations, each of which is in 3NF. Further, the decomposition satisfies the LJD and DP properties.

Method

Perform the following steps:

Find a minimal basis for `F`, say `G`.
For each FD `X->A` in `G`, use `XA` as the schema of one of the relations in the decomposition.
If none of the relation schema from Step 2 is a superkey for `R`, add another relation whose schema is a key for `R`.

Consider the relation `R(A,B,C,D,E)` with FDs `AB->C`, `C->B`, and `A->D`.
To start, notice the FDs are already minimal (can't eliminate any FDs from set or shrink num attributes on left hand side of existing FDs).
Step 2 of the synthesis algorithm, then gives the relations `R_1(A,B,C)`, `R_2(B, C)`, and `R_3(A,D)`.
As the attributes of `R_2` are a subset of those of `R_1`, we can safely delete `R_2` from our list (even though not said in algorithm).
`R` has two keys `{A,B,E}` and `{A,C,E}` neither of which is contained in the attributes of `R_1` or `R_3`. So we add a table `R_4(A,B,E)`.
We thus obtain the final decomposition `R_1(A,B,C)`, `R_3(A,D)`, and `R_4(A,B,E)`.

To show the algorithm works we need to show the resulting relations have LJD and DP properties and the result is in 3NF:
1. Lossless Join. Let `K` be the attributes of a relation in the decomposition whose attributes form a superkey. Consider the sequence of FDs used in our algorithm to compute `K^+`. Since `K` is a superkey `K^+` will be all attributes. The same sequenc eof FDs in when applied in the lossless join chase algorithm will cause the subscripted symbols in the row corresponding to `K` to be equated to unsubscripted symbols in the same order as attributes were added to the closure with eventually all attributes become unsubscripted. Thus, it has the LJD property.
2. Dependency Preservation. Each FD of the minimal basis has all its attributes in some relation of the decomposition. Therefore, each dependency can be checked in the decomposed relations.
3. 3NF. If we have a relation whose schema is a key, then this relation will be in 3NF. This is because all attributes of this relation are prime, so won't cause a 3NF violation. For relations whose schemas are from FDs of a minimal basis, we skip the argument, but as a hint we want to show a 3NF violation in them implies that the basis is not minimal.