Transcript Multivalued Dependency (MVD) & Join Dependency (JD) •

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)
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] = t2[course] = COMP171
Also, t5 [dept] = t6 [dept] = t7 [dept] = t8 [dept] = 2
t7 [course] = t5 [course] = ELEC102
t8 [course] = t6 [course] = ELEC151
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
Multivalued Dependency (MVD)
pattern
student
1001
1001
1002
1002
1003
1003
1004
1004
dept
1
1
1
1
2
2
2
2
course
COMP104
COMP171
COMP104
COMP171
ELEC102
ELEC151
ELEC102
ELEC151
Two students in
the same
department take
the same courses
Multivalued Dependency (MVD)
•
Example (cont’d)
– 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.
Join Dependency (JD)
•
•
•
•
JD is the general version of MVD
If the schema can be broken up into n (n≥1)
or more schema losslessly, the schema
obeys JD.
particularly, when n=1, it is the trivial case.
when n=2, it is actually MVD.
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
Join Dependency (JD)
–
Decompose the relation enrollment into 3
relations as follows
Student
Course
Course
Lecturer
1001
COMP104
COMP104
1
1001
COMP171
COMP104
1002
COMP104
1002
Student
Lecturer
1001
1
2
1001
3
COMP171
3
1002
2
COMP171
ELEC102
4
1002
3
1003
ELEC102
ELEC151
5
1003
4
1003
ELEC151
ELEC151
6
1003
5
1004
ELEC102
1004
4
1004
ELEC151
1004
6
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
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.