Multivalued Dependency MVD Join Dependency JD The third

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. • • • t 1 [dept] = t 2 [dept] = t 3 [dept] = t 4 [dept] = 1 t 3 [course] = t 1 [course] = COMP 104 t 4 [course] = t 2[course] = COMP 171 Also, t 5 [dept] = t 6 [dept] = t 7 [dept] = t 8 [dept] = 2 t 7 [course] = t 5 [course] = ELEC 102 t 8 [course] = t 6 [course] = ELEC 151

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 COMP 104 1 COMP 171 2 ELEC 102 2 ELEC 151

Multivalued Dependency (MVD) pattern student 1001 1002 dept course 1 COMP 104 1 COMP 171 1 COMP 104 1002 1003 1004 1 2 2 COMP 171 ELEC 102 ELEC 151 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 COMP 104 and COMP 171 – and those (1003, 1004) in the Electronic Eng. department take ELEC 102 and ELEC 151.

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 COMP 104 1 1001 COMP 171 3 1002 COMP 104 2 1002 COMP 171 3 1003 ELEC 102 4 1003 ELEC 151 5 1004 ELEC 102 4 1004 ELEC 151 6

Join Dependency (JD) – Decompose the relation enrollment into 3 relations as follows Student Course 1001 COMP 104 1 1001 COMP 171 COMP 104 1002 Lecturer Student Lecturer 1001 1 2 1001 3 COMP 171 3 1002 2 COMP 171 ELEC 102 4 1002 3 1003 ELEC 102 ELEC 151 5 1003 4 1003 ELEC 151 6 1003 5 1004 ELEC 102 1004 4 1004 ELEC 151 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 COMP 104 1 1001 COMP 104 2 1001 COMP 171 3 1002 COMP 104 1 1002 COMP 104 2 1002 COMP 171 3 1003 ELEC 102 4 1003 ELEC 151 5 1003 ELEC 151 6 1004 ELEC 102 4 1004 ELEC 151 5 1004 ELEC 151 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.