Multivalued Dependencies

Multivalued Dependencies

A "multivalued dependency" is an assertion that two attributes or sets of attributes are independent of one another. This condition is, as we shall see, an overview of the notion of a functional dependency, in the sense that every FD implies a corresponding multivalued dependency.  However, there are some situations involving independence of attribute sets that cannot be explained as FD's. In this section we shall explore the cause of multivalued dependencies and see how they can be used in database schema design.

Attribute Independence and Its Consequent Redundancy

There are infrequent situations where we plan a relation schema and find it is in BCNF, yet the relation has a kind of redundancy that is not related to FD's. The most general source of redundancy in BCNF schemas is an attempt to put two or more many-many relationships in a single relation.

Example : In this example, we shall suppose that stars may have several addresses. We shall also break addresses of stars into street and city components. Along with star names and their addresses, we shall contain in a single relation the usual Stars-in information about the titles and years of movies in which the star appeared. Then the following figure is a typical example of this relation.

Sets of addresses independent from movies

We focus in above figure on Carrie Fisher's two hypothetical addresses and three popular movies. There is no reason to link an address with one movie and not another. Therefore, the only way to express the fact that addresses and movies are independent properties of stars is to have each address appear with each movie. But when we repeat address and movie facts in all combinations, there is apparent redundancy. For  example, the above figure repeats each of Carrie Fisher's addresses three times (once for each of her movies) and each movie twice (once for each address).

Yet there is no BCNF violation in the relation suggested by above figure. There are, in reality, no nontrivial FD's at all. For instance, attribute city is not functionally determined by the other four attributes. There might be a star with two homes that had the same street address in different cities. Then there would be two tuples that agreed in all attributes but city and disagreed in city. Therefore,

    name street title year  city

is not a FD for our relation. We leave it to the reader to check that none of the five attributes is functionally determined by the other four. Since there are no nontrivial FD's, it follows that all five attributes form the only key and that there are no BCNF violations.