Multivalued Dependency in Databases
Multivalued Dependency in Databases GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > Apps 77 77 people found this article helpful
This functional dependency can be written: Student_Name -> Major. Each Student_Name determines exactly one Major and no more. 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: Student_Name Major Sport Ravi Art History Soccer Ravi Art History Volleyball Ravi Art History Tennis Beth Chemistry Tennis Beth Chemistry Soccer Students The problem here is that both Ravi and Beth play several sports. It is necessary 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. 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 ->-> 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 in Database Design
Multivalued dependency breaks fourth normal form
By Mike Chapple Mike Chapple Writer University of Idaho Auburn University Notre Dame Former Lifewire writer Mike Chapple is an IT professional with more than 10 years' experience cybersecurity and extensive knowledge of SQL and database management. lifewire's editorial guidelines Updated on July 25, 2020 Tweet Share Email Tweet Share Email Apps Best Apps Payment Services In a relational database, a dependency occurs when the information stored in the same database table uniquely determines other information stored in the same table. 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. stevecoleimages/Vetta/Getty Images 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: Student_Name Major Ravi Art History Beth Chemistry StudentsThis functional dependency can be written: Student_Name -> Major. Each Student_Name determines exactly one Major and no more. 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: Student_Name Major Sport Ravi Art History Soccer Ravi Art History Volleyball Ravi Art History Tennis Beth Chemistry Tennis Beth Chemistry Soccer Students The problem here is that both Ravi and Beth play several sports. It is necessary 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. 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 ->-> 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.