EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials DBMS Tutorial DBMS Multivalued Dependency
Secondary Sidebar
DBMS Tutorial
  • DBMS
    • What is DBMS?
    • Introduction To DBMS
    • DBMS ER Diagram
    • DBMS_RANDOM
    • DBMS_XPLAN
    • DBMS join
    • DBMS Functions
    • Data Administrator in DBMS
    • Checkpoint in DBMS
    • DBMS Table
    • Mapping Constraints in DBMS
    • DBMS Canonical Cover
    • DBMS Log-Based Recovery
    • DBMS Multivalued Dependency
    • View Serializability in DBMS
    • DBMS Concepts
    • DBMS Constraints
    • DBMS_Scheduler
    • B+ Tree in DBMS
    • DBMS_LOB
    • dbms entity
    • DBMS Foreign Key
    • DBMS Users
    • DBMS_Metadata.get_ddl
    • Relational Algebra in DBMS
    • DBMS Components
    • DBMS Features
    • DBMS Models
    • DBMS Relational Model
    • Hashing in DBMS
    • DBMS network model
    • Relationship in DBMS
    • ER Model in DBMS
    • Data Models in DBMS
    • Static Hashing in DBMS
    • Advantages of DBMS
    • dbms_output.put_line
    • DBMS Data Dictionary
    • dbms_xplan.display_cursor
    • Normal Forms in DBMS
    • DBMS helps achieve
    • DBMS 3 tier Architecture
    • Relational Calculus in DBMS
    • Serializability in DBMS
    • File Organization in DBMS
    • DBMS Transaction Processing
    • States of Transaction in DBMS
    • Functional Dependency in DBMS
    • Generalization in DBMS
    • Data Independence in DBMS
    • Lock Based Protocols in DBMS
    • Deadlock in DBMS
    • Integrity Constraints in DBMS
    • Concurrency Control in DBMS
    • Validation Based Protocol in DBMS
    • DBMS Locks
    • Normalization in DBMS
    • Transaction Property in DBMS
    • Specialization in DBMS
    • Aggregation in DBMS
    • Types of DBMS

DBMS Multivalued Dependency

By Aanchal SharmaAanchal Sharma

DBMS Multivalued Dependency

Definition of DBMS Multivalued Dependency

DBMS Multivalued Dependency is a conditional property which states that when two table attributes are self-determining to each other although both be governed by a third attribute. In DBMS, Multivalued Dependency comprises of minimum two attributes that are reliant on a third attribute therefore it continuously needs three attributes. Hence, a Multivalued Dependency is stated as a whole constraint between two groups of attributes in a relationship but it needs definite tuples to be available in a relation. In DBMS Normalization process, the Fourth Normal Form (4NF) rule should not contain any Multivalued Dependency present in a single table to satisfy its conditions. Because due to multivalued dependency, it may lead to the un-essential recurrence of data and other variances as well.

Syntax:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

When there exists of one or more table rows in a database that implies one or multiple other table rows present in the identical table so that here the Multivalued dependencies occur.

If the table consists of A, B, and C attributes, then B and C will be denoted as multi-valued facts of A. It is signified by the double arrow shown as below:

For illustration,

A B

A C

In this type of case, the DBMS Multivalued Dependency occurs only if B and C are independent attributes.

But a table having multivalued dependency will violate the rule of 4NF. Here, 4NF is a kind of Normalization: Normalization is denoted as a technique to organize the data in the server database that supports a user to escape data redundancy, operations like insertion, deletion, and update inconsistency. This is a method of analyzing the relation schemas on the basis of their diverse functional dependencies and the primary key.

Therefore, Normalization is integral to relational database theory. It may hold the consequence of duplicating the identical data within the server database that may affect in the creation of extra tables.

4NF should hold BCNF i.e. Boyce-Codd Normal Form. The redundancy which derives from MVD’s is not transferrable by pushing the database schema in BCNF. There is a tougher normal form known as 4NF which delights MVD’s as FD’s if it derives to disintegration, but not when defining keys of the relation.

How Multivalued Dependency works in DBMS?

In DBMS, MVD or say Multivalued Dependency defines that for one value of attribute ‘x’ multiple values of attribute ‘y’ be existent. We will write this as:

X Y

This is read as; x is multi-valued dependent on b.

In DBMS, a table is defined to hold Multivalued dependency when the succeeding conditions follow to be true:

  1. For a dependency XY, if for the only value of X, many values of B occurs, then the table may hold multivalued dependency.
  2. Besides, a table should include minimum 3 table columns for it to have a multivalued dependency.
  3. Finally, for a relation R(X, Y, Z), if there exists a multivalued dependency between X and Y, so Y and Z should have to be independent of each other.

Thus, when all these above conditions are satisfied for any relation in a table then it is defined to have a multivalued dependency.

In comparison to the functional dependency, the DBMS Multivalued Dependency needs few tuples to be existent in a relation. So, this Multivalued Dependency is said to be an exceptional instance of tuple-generating dependency and plays an important role in Database Normalization (4NF).

Multivalued Dependency is also a special example of a Join Dependency, having only two groups of values contained i.e. can be said as a binary join dependency.

A Multivalued dependency occurs when there exist a minimum of three attributes such as A, B, C in a relation where for a value of A there is a well-stated group of values of B and a well-stated group of values of C. Still, the set of values of B is independent of set C and follows vice versa.

Examples

Let us study the DBMS Multivalued Dependency with certain examples for reference explained below:

Suppose there exists a company for car manufacturing that generates two different colors of car models every year. These colors can be denoted as blue and black.

select * from ‘multivalue’

DBMS Multivalued Dependency 1

From the above table information, we can define that the columns Colour and Manuf_Year are reliant on Model_Num and at the same time self-governing of each other also. So, in this case, we can declare that the two columns are Multivalued Dependent on the column named Model_Num.

We can view the illustration of these dependencies below:

Model_Num Manuf_Year

Model_Num Colour

The above presentation can be defined as Model_Num multi-determined Manuf_Year and Model_Num multi-determined Colour.

But this condition or situation violates the 4NF rule in the DBMS Normalization process.

Also, you can see that Model_Num 205 has more than one color model in the year 2015, where color and manuf_year are independent but dependent on model_num.

For correcting it and removing multivalued dependency, we can split in two distinct tables and break the dependency. One table will include Model_Num and Colour and the other will contain Model_Num and Manuf_Year.

DBMS Multivalued Dependency 2

DBMS Multivalued Dependency 3

This process will break the multivalued dependency and so now we can view two functional dependencies:

Model_Num Colour

Model_Num Manuf_Year

Functional Dependency in DBMS defines the relation of an attribute to another attribute in a database schema which thus provides benefit to support the quality of data records in the server. As you can the Functional Dependency is denoted by a single arrow whereas Multivalued Dependency is denoted by two arrows.

Also, using Functional Dependency a user can get the difference between decent and corrupt designs of a database in DBMS.

Conclusion

A multivalued dependency on a relation R, suppose AB, concludes that if the two tuples of R approve on entire attributes of A, formerly their components in B might be exchanged and thus the outcome will be two tuples which are in the relation also. We can now say that for every value of A, the values of B are independent of the values of R-A-B.

Recommended Articles

This is a guide to DBMS Multivalued Dependency. Here we discuss the definition, syntax, How Multivalued Dependency works in DBMS? along with the examples respectively. You may also have a look at the following articles to learn more –

  1. Checkpoint in DBMS
  2. Integrity Constraints in DBMS
  3. File Organization in DBMS
  4. ER Model in DBMS
Popular Course in this category
SQL Training Program (10 Courses, 8+ Projects)
  10 Online Courses |  8 Hands-on Projects |  80+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more