A multivalued dependency prevents the normalization standard fourth normal form. Relational databases follow five normal forms that represent guidelines for record design. They prevent update anomalies and inconsistencies in the data. The fourth normal form deals with many-to-one relationships in a database.
Functional Dependency vs. Multivalued Dependency
To understand a multivalued dependency, it is helpful to revisit what a functional dependency is. If an attribute X uniquely determines an attribute Y, then Y is functionally dependent on X. This is written as X -> Y. For example, in the Students table below, the Student_Name determines the Major: If you want the database to also track the sports these students take, you might think the easiest way to do this is to just add another column titled Sport: This table has introduced a multivalued dependency because the major and the sport are independent of one another but both depend on the student. This is a simple example and easily identifiable, but a multivalue dependency could become a problem in a large, complex database. A multivalued dependency is written X ->-> Y. In this case: Student_Name ->-> MajorStudent_Name ->-> Sport This is read as “Student_Name multidetermines Major” and “Student_Name multidetermines Sport.” A multivalued dependency always requires at least three attributes because it consists of at least two attributes that are dependent on a third.
Multivalued Dependency and Normalization
A table with a multivalued dependency violates the normalization standard of fourth normal form because it creates unnecessary redundancies and can contribute to inconsistent data. To bring this up to 4NF, it is necessary to break this information into two tables. The table below now has a functional dependency of Student_Name -> Major, and no multivalued dependencies:
