Multivalued Dependencies

Posted By on October 10, 2014

Download PDF
Boyce-Codd Normal Form (BCNF)
Fourth Normal Form (4NF)

Multivalued Dependencies

  1. Functional dependencies rule out certain tuples from appearing in a relation.If A tex2html_wrap_inline1526 B, then we cannot have two tuples with the same A value but different B values.
  2. Multivalued dependencies do not rule out the existence of certain tuples.Instead, they require that other tuples of a certain form be present in the relation.
  3. Let R be a relation schema, and let tex2html_wrap_inline1732 and tex2html_wrap_inline1734 .The multivalued dependencydisplaymath1886

    holds on R if in any legal relation r(R), for all pairs of tuples tex2html_wrap_inline1906 and tex2html_wrap_inline1908 in r such that tex2html_wrap_inline1912 , there exist tuples tex2html_wrap_inline1914 and tex2html_wrap_inline1916 in r such that:






  4. Figure                                           shows a tabular representation of this. It looks horrendously complicated, but is really rather simple. A simple example is a table with the schema (name, address, car), as shown in Figure .  figure583
    Figure 7.5:   Tabular representation of tex2html_wrap_inline1970 .  figure599
    Figure 7.6:   (name, address, car) where tex2html_wrap_inline1974 and tex2html_wrap_inline1976 .

    • Intuitively, tex2html_wrap_inline1970 says that the relationship between tex2html_wrap_inline1740 and tex2html_wrap_inline1982 is independent of the relationship between tex2html_wrap_inline1740 and tex2html_wrap_inline1986 .
    • If the multivalued dependency tex2html_wrap_inline1970 is satisfied by all relations on schema R, then we say it is a trivial multivalued dependency on schema R.
    • Thus tex2html_wrap_inline1970 is trivial if tex2html_wrap_inline1738 or tex2html_wrap_inline1998 .
  5. Look at the example relation bc relation in Figure .  figure612
    Figure 7.7:   Relation bc, an example of redundancy in a BCNF relation.

    • We must repeat the loan number once for each address a customer has.
    • We must repeat the address once for each loan the customer has.
    • This repetition is pointless, as the relationship between a customer and a loan is independent of the relationship between a customer and his or her address.
    • If a customer, say “Smith”, has loan number 23, we want all of Smith’s addresses to be associated with that loan.
    • Thus the relation of Figure is illegal.
    • If we look at our definition of multivalued dependency, we see that we want the multivalued dependency
       cname  tex2html_wrap_inline2000  street ccity

      to hold on BC-schema.

    Figure 7.8:   An illegal bc relation.

  6. Note that if a relation r fails to satisfy a given multivalued dependency, we can construct a relation r‘ that does satisfy the multivalued dependency by adding tuples to r.
Boyce-Codd Normal Form (BCNF)
Fourth Normal Form (4NF)

Download PDF

Posted by Akash Kurup

Founder and C.E.O, World4Engineers Educationist and Entrepreneur by passion. Orator and blogger by hobby