Multivalued Dependency (MVD)

1
Multivalued Dependency (MVD) Join Dependency
(JD)

• The third type of DD is related to the
  decomposition restriction on a single schema.
• This includes multivalued dependency (MVD) and
  join dependency (JD)

2
Multivalued Dependency (MVD)

• Notation MVD A ?? B
• Example
• In the relation pattern. An MVD dept??course is
  satisfied.
• t1 dept t2 dept t3 dept t4 dept 1
• t3 course t1 course COMP104
• t4 course t2course COMP171
• Also, t5 dept t6 dept t7 dept t8
  dept 2
• t7 course t5 course ELEC102
• t8 course t6 course ELEC151

3
Multivalued Dependency (MVD)
student
sid sname sprogram sdept
1001 stud_A BEng(COMP) 1
1002 stud_B BEng(COMP) 1
1003 stud_C BEng(ELEC) 2
1004 stud_D BEng(ELEC) 2
requirement
dept course
1 COMP104
1 COMP171
2 ELEC102
2 ELEC151



4
Multivalued Dependency (MVD)
pattern
student dept course
1001 1 COMP104
1001 1 COMP171
1002 1 COMP104
1002 1 COMP171
1003 2 ELEC102
1003 2 ELEC151
1004 2 ELEC102
1004 2 ELEC151
Two students in the same department take the same
courses
5
Multivalued Dependency (MVD)

• Example (contd)
• In other words, students in the same department
  follow the same study pattern to take courses.
• Students (1001, 1002) in the Computer Sci.
  department take COMP104 and COMP171
• and those (1003, 1004) in the Electronic Eng.
  department take ELEC102 and ELEC151.

• Example

6
Join Dependency (JD)

• JD is the general version of MVD
• If the schema can be broken up into n (n1) or
  more schema losslessly, the schema obeys JD.
• particularly, when n1, it is the trivial case.
• when n2, it is actually MVD.

7
Join Dependency (JD)

• Example
• In the relation enrollment
• JD ((student, course), (course, lecturer),
  (student, lecturer)) holds,
• but JD ((student,course), (course,lecturer)) does
  not.

enrollment
student course lecturer
1001 COMP104 1
1001 COMP171 3
1002 COMP104 2
1002 COMP171 3
1003 ELEC102 4
1003 ELEC151 5
1004 ELEC102 4
1004 ELEC151 6
8
Join Dependency (JD)

• Decompose the relation enrollment into 3
  relations as follows

Student Lecturer
1001 1
1001 3
1002 2
1002 3
1003 4
1003 5
1004 4
1004 6
Course Lecturer
COMP104 1
COMP104 2
COMP171 3
ELEC102 4
ELEC151 5
ELEC151 6
Student Course
1001 COMP104
1001 COMP171
1002 COMP104
1002 COMP171
1003 ELEC102
1003 ELEC151
1004 ELEC102
1004 ELEC151
9
Join Dependency (JD)

• When the three relations are joined back
  together, it will be the same as before
  decomposing.
• But joining the first two relations ((student,
  course), (course, lecturer)) would generate some
  spurious results.
• The natural join is as shown in the next slide

10
Join Dependency (JD)
Student Course Lecturer
1001 COMP104 1
1001 COMP104 2
1001 COMP171 3
1002 COMP104 1
1002 COMP104 2
1002 COMP171 3
1003 ELEC102 4
1003 ELEC151 5
1003 ELEC151 6
1004 ELEC102 4
1004 ELEC151 5
1004 ELEC151 6
The tuples with red values are spurious. They do
not exist in the original enrollment relation.
Therefore the JD of joining these two relations
does not hold.