Multi-valued and Joined Dependencies

MULTIVALUED AND JOIN DEPENDENCY IN DBMS

MULTIVALUED DEPENDENCY

Definition: An MVD exists when a determinant (attribute or set of attributes) is associated with multiple independent sets of values for other attributes. Essentially, for a single value of X, there are multiple possible values for Y and Z, but the sets of Y and Z are independent of each other.

Notation: X ->> Y (read as X multi-valued dependent on Y).

Example: Consider a table with attributes (Student, Project, Hobby).

If a student works on multiple projects and has multiple hobbies, and the projects and hobbies are independent of each other, then we have an MVD

(Student ->> Project) and (Student ->> Hobby).

Multivalued dependency would occur whenever two separate attributes in a given table happen to be independent of each other. And yet, both of these depend on another third attribute.

JOIN DEPENDENCY

A JD exists when a relation can be perfectly reconstructed by joining a set of its projections (smaller relations containing a subset of the original relation's attributes). 

Notation: R (A, B, C, D) satisfies join dependency if it is equal to the join of R1 (A, B) and R2 (B, C, D). 

 

Types of Join Dependency

There are two types of Join Dependencies:

• Lossless Join Dependency: It means that whenever the join occurs between the tables, then no information should be lost, the new table must have all the content in the original table.
• Lossy Join Dependency: In this type of join dependency, data loss may occur at some point in time which includes the absence of a tuple from the original table or duplicate tuples within the database.

Example:

Imagine a relation with attributes (Book, Author, Publisher). If the relation can be losslessly joined from two projections: (Book, Author) and (Author, Publisher), then a join dependency exists. 

 

We can decompose the table given above into these three tables given below. 

<Student_Skills>

<Student_Job>

 

<Job_Skills>

Our Join Dependency would be:

{(Name, Skills ), ( Name, Job), (Skills, Job)}

The relations given above have join dependency. It means that the join relation of the three relations given above is equal to the very original relation <Student>.