Outline

• Multivalued Dependencies
• In-Class Exercise
• High Level Database Models

Introduction

• On Monday, we continued our discussion of what makes a set of relations a "reasonable" relational schema for one's data.
• We said BCNF can be too restrictive in that it is possible for a decomposition into BCNF to not be dependency preserving.
• We weakened BCNF to get 3NF which supports lossless joins, is dependency preserving, and minimizes anomalies.
• Today, we look at a kind of redundancy anomaly that can't necessarily be handled even if the table is in BCNF.
• We give a normal form to handle this kind of redundancy.
• We then switch topics and look at higher level data models which might be useful for reasoning about data before trying to create a relational schema at all.

Multivalued Dependencies

• Consider the relation below which models where stars have lived and the movies they have been in:

  name      | street        | city      | title               | year
  C. Fisher | 123 Maple St. | Hollywood | Star Wars           | 1977
  C. Fisher | 5 Locust Ln.  | Malibu    | Star Wars           | 1977
  C. Fisher | 123 Maple St. | Hollywood | Empire Strikes Back | 1980
  C. Fisher | 5 Locust Ln.  | Malibu    | Empire Strikes Back | 1980
  C. Fisher | 123 Maple St. | Hollywood | Return of the Jedi  | 1983
  C. Fisher | 5 Locust Ln.  | Malibu    | Return of the Jedi  | 1983
  

• There are no nontrivial FDs in the above. No attribute is determined by the other four. So the above is in BCNF.
• For example, a star might have two homes with the same street address, but different cities, so city is not determined by the four other attributes.
• On the other hand there is obviously some kind of redundancy in the above table: If we know the star, then there is only a fixed set of addresses they can live at.
• So rather than name fixing a single possible address, it is fixing a fixed possible list of addresses.
• We call such a dependency, a multi-valued dependency (MVD), and write `X ↠ Y` to say `X` multivalue determines `Y`.
• In the above, we have `n\a\m\e ↠ s\t\r\e\e\t \ \ c\i\t\y`

Multivalued Dependencies

• We can more formally say the MVD
  `A_1A_2...A_n ↠ B_1...B_m`
  holds on `R` if for each pair of tuples `t` and `u` of relation `R `that agree on all the `A_i`s, we can find in `R` some tuple `v` that agrees:
  1. With both `t` and `u` on the `A_i`s,
  2. With `t` in the `B_j`'s, and
  3. With `u` on all attributes of `R` that are not among the `A_i`s or `B_j`s.
• As an example, let `t` and `u` be the tuples:

  name      | street        | city      | title               | year
  C. Fisher | 123 Maple St. | Hollywood | Star Wars           | 1977
  C. Fisher | 5 Locust Ln.  | Malibu    | Empire Strikes Back | 1980
  

• Then `n\a\m\e ↠ s\t\r\e\e\t \ \ c\i\t\y` asserts that there is a tuple with street and city given by 123 Maple St. Hollywood and whose title and year are Empire Strikes Back and 1980. This row in fact does exist, it is the third row in our table.

In-Class Exercise

• Consider the relation `R(A,B,C)`.
• Come up with instances of this relation in which `A ↠ B` is an MVD, and yet `A->B` is not an FD.
• Post your solutions to the Oct 2 In-Class Exercise Thread.

Reasoning about MVDs

• There are a number of rules about MVDs that are similar to those we had for FDs:
  • Trivial MVDs. The MVD
    `A_1A_2...A_n ↠ B_1...B_m`
    holds in any relation if `{B_1, ..., B_m} subseteq {A_1,A_2,...,A_n}`.
  • Transitive MVDs. If the MVDs
    `A_1A_2...A_n ↠ B_1...B_m` and `B_1...B_m ↠ C_1...C_k`
    hold, then `A_1A_2...A_n ↠ C_1...C_k` will hold. Here any `C_t`s which are also `A_i`'s must be deleted from the right side.
  • FD Promotion. Every FD is an MVD.
  • Complementation Rule. Given a relation `R(A_1,A_2,...,A_n, B_1...B_m, C_1...C_k)`, if `A_1A_2...A_n ↠ B_1...B_m` then `A_1A_2...A_n ↠ C_1...C_k`.
  • More Trivial MVDs. Given a relation `R(A_1,A_2,...,A_n, B_1...B_m)` with all attributes listed, then `A_1A_2...A_n ↠ B_1...B_m` holds in `R`.

Fourth Normal Form

• We now modify BCNF to get a normal form on MVDs which avoids MVD-style redundancy.
• We say a relation is in fourth normal form (4NF) if whenever
  `A_1A_2...A_n ↠ B_1...B_m`
  is a nontrivial MVD, `{A_1,A_2,...,A_n}` is a superkey.
• In our table earlier `\n\a\m\e` wasn't a superkey, but `n\a\m\e ↠ s\t\r\e\e\t \ \ c\i\t\y` was non-trivial, so that relation was not in 4NF.
• Since every FD is an MVD, if one is in `4NF` one will be in `BCNF`.

4NF Decomposition Algorithm

We can modify our BCNF decomposition algorithm to get a 4NF decomposition algorithm as follows:

Input:

A relation `R_0` with a set of FDs and MVDs

Output:

A decomposition of `R_0` into relations all of which are in 4NF. The decomposition has the lossless join property.

Method:

Do the following steps, with `R = R_0` and `S= S_0`:

1. Find a 4NF violation in R, say `A_1A_2...A_n ↠ B_1...B_m`, where
   `{A_1,A_2,...,A_n}`
   is not a superkey. If there is none, return; R by itself as a decomposition.
2. If there is such a 4NF violation, break the schema for the relation `R` into two schemas:
   • `R_1`, whose schema is `A_i`s and `B_j`s.
   • `R_2`, whose schema is the `A_i`s together with all the attributes that are not `A_i`'s or `B_j`'s.
3. Find the FDs and MVDs that hold in `R_1` and `R_2`. The book shows how to modify the chase algorithm to do this, but we are going to skip it. Recursively, apply the algorithm to each of `R_1` and `R_2`.

In the case of our earlier example table. The above algorithm would split the table because of `n\a\m\e ↠ s\t\r\e\e\t \ \ c\i\t\y` and decompose it into two tables `R_1`(name, street, city) and `R_2`(name, title, year).

High Level Database Models

• We are now going to look at the process in which a database such as our movie database might be created.
• This might look like:
  Ideas `->` High-Level Design `->` Relational Database Schema `->` Relational DBMS.
• You might think the design phase should use the relational model as well.
• In practice, it is often easier to start with a higher-level model and then convert the design to the relational model.
• The problem with the relational model is that it has only one concept, the relation, rather than several complementary concepts more closely related to real-world situations.
• We also want to leverage our minds innate ability to process images.
• So we are now going to begin describing several diagramming techniques for modeling databases beginning with one of the oldest, E/R diagrams (Entity/Relationship Diagrams).

Entity-Relationship Model

• In the Entity-relationship model (E/R model), the structure of data is represented graphically as an entity-relationship diagram. This has three principal parts, which we will go over next day:
  1. Entity Sets
  2. Attributes
  3. Relationships