Introduction

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, when we check the projected FDs in the relations of the decomposition, we can be sure that we can reconstruct the original relation via joining. That is, 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.

If we apply the chase algorithm to check for the lossless join property, does our Bookings table deecomposition have it?
Come up with a scenario where you start with a big table with data, use the BCNF algorithm to decompose it into small tables, then after using the resulting tables you end up with data that is no longer dependency preserving.
Please post your solution to the Sep 27 In-Class Exercise Thread.

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) if 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.
As if either condition holds for 3NF, we won't have a violation of 2NF, we have that any 3NF schema will also be in 2NF.