Multivalued Dependency

Multivalued Dependency Breaks Fourth Normal Form

Format
mla apa chicago
Your Citation
Chapple, Mike. "Multivalued Dependency." ThoughtCo, Dec. 11, 2016, thoughtco.com/multivalued-dependency-1019755. Chapple, Mike. (2016, December 11). Multivalued Dependency. Retrieved from https://www.thoughtco.com/multivalued-dependency-1019755 Chapple, Mike. "Multivalued Dependency." ThoughtCo. https://www.thoughtco.com/multivalued-dependency-1019755 (accessed September 25, 2017).

A multivalued dependency occurs when the presence of one or more rows in a table implies the presence of one or more other rows in that same table. Put another way, two attributes (or columns) in a table are independent of one another, but both depend on a third attribute. A multivalued dependency prevents the normalization standard Fourth Normal Form (4NF).

Functional Dependency vs. Multivalued Dependency

To understand this, let's revisit what a functional dependency is.

Remember that 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: 

Students
Student_NameMajor
RaviArt History
BethChemistry

 

This functional dependency can be written:  Student_Name -> Major. Each  Student_Name determines exactly one Major, and no more.

Now, perhaps we also want to track the sports these students take. We might think the easiest way to do this is to just add another column, Sport:

Students
Student_NameMajorSport
RaviArt HistorySoccer
RaviArt HistoryVolleyball
RaviArt HistoryTennis
BethChemistryTennis
BethChemistrySoccer

 

The problem here is that both Ravi and Beth play multiple sports. We need to add a new row for every additional 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.

Note that this is a very simple example and easily identifiable — but this could become a problem in a large, complex database.

A multivalued dependency is written X ->-> Y. In this case:

Student_Name  ->-> Major
Student_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 (4NK) because it creates unnecessary redundancies and can contribute to inconsistent data. To bring this up to 4NF, we can break this into two tables.

The table below now has a functional dependency of Student_Name -> Major, and no multidependencies:

Students & Majors
Student_NameMajor
RaviArt History
RaviArt History
RaviArt History
BethChemistry
BethChemistry

 

While this table also has a single functional dependency of Student_Name -> Sport:

Students & Sports
Student_NameSport
RaviSoccer
RaviVolleyball
RaviTennis
BethTennis
BethSoccer

 

It's clear that normalization is often addressed by simplifying complex tables so that they contain information related to a single idea or theme, rather than trying to make a single table contain too much disparate information.

 

Format
mla apa chicago
Your Citation
Chapple, Mike. "Multivalued Dependency." ThoughtCo, Dec. 11, 2016, thoughtco.com/multivalued-dependency-1019755. Chapple, Mike. (2016, December 11). Multivalued Dependency. Retrieved from https://www.thoughtco.com/multivalued-dependency-1019755 Chapple, Mike. "Multivalued Dependency." ThoughtCo. https://www.thoughtco.com/multivalued-dependency-1019755 (accessed September 25, 2017).