CPSC-310 Database Systems

1. CPSC-310 Database Systems Professor Jianer Chen Room 315C HRBB Lecture #8

2. BCNF and 3NF

3. BCNF and 3NF 3NF BCNF BCNF  3NF

4. BCNF and 3NF 3NF BCNF BCNF  3NF

5. BCNF and 3NF 3NF BCNF BCNF  3NF

6. BCNF and 3NF 3NF BCNF BCNF  3NF

7. BCNF and 3NF 3NF BCNF BCNF  3NF

8. BCNF and 3NF 3NF BCNF BCNF  3NF Would BCNF be sufficient to eliminate other kind of redundancies?

9. Example of a bad relation in BCNF

10. Example of a bad relation in BCNF • Suppose that we have a relation that lists the teacher- student relationship for each person. That is, for a person p, the relation gives all teachers who ever taught p and all students who were ever taught by p. For example, suppose that David and Jeff taught Kevin, while Kevin taught Michael, Jason, and Tom. Then the relation should contain tuples:

11. Example of a bad relation in BCNF • Suppose that we have a relation that lists the teacher- student relationship for each person. That is, for a person p, the relation gives all teachers who ever taught p and all students who were ever taught by p. For example, suppose that David and Jeff taught Kevin, while Kevin taught Michael, Jason, and Tom. Then the relation should contain tuples:

12. Example of a bad relation in BCNF • Suppose that we have a relation that lists the teacher- student relationship for each person. That is, for a person p, the relation gives all teachers who ever taught p and all students who were ever taught by p. For example, suppose that David and Jeff taught Kevin, while Kevin taught Michael, Jason, and Tom. Then the relation should contain tuples: • There are some obvious redundancies: suppose Kevin has n teachers and m students, then there will be n*m tuples for Kevin! On the other hand, it indeed needs no more than n+m (smaller) tuples to record all information about Kevin’s teachers and students.

13. Example of a bad relation in BCNF • Suppose that we have a relation that lists the teacher- student relationship for each person. That is, for a person p, the relation gives all teachers who ever taught p and all students who were ever taught by p. For example, suppose that David and Jeff taught Kevin, while Kevin taught Michael, Jason, and Tom. Then the relation should contain tuples: • There are some obvious redundancies: suppose Kevin has n teachers and m students, then there will be n*m tuples for Kevin! On the other hand, it indeed needs no more than n+m (smaller) tuples to record all information about Kevin’s teachers and students. • On the other hand, the relation is in BCNF: the only key is • {person, teacher, student} • so there is no BCNF violators.

14. Definition of MVD • MVD captures redundancy that FD’s can’t

15. Definition of MVD • MVD captures redundancy that FD’s can’t • A multivalued dependency (MVD) X↠Y on a relation R says that if two tuples of R agree on all the attributes of X, then by swapping their components in Y, we still get tuples in R, i.e., for each value of X, the values of Y are independent of the values of R\(X∪Y).

16. Definition of MVD • MVD captures redundancy that FD’s can’t • A multivalued dependency (MVD) X↠Y on a relation R says that if two tuples of R agree on all the attributes of X, then by swapping their components in Y, we still get tuples in R, i.e., for each value of X, the values of Y are independent of the values of R\(X∪Y).

17. Definition of MVD • MVD captures redundancy that FD’s can’t • A multivalued dependency (MVD) X↠Y on a relation R says that if two tuples of R agree on all the attributes of X, then by swapping their components in Y, we still get tuples in R, i.e., for each value of X, the values of Y are independent of the values of R\(X∪Y).

18. Definition of MVD • MVD captures redundancy that FD’s can’t • A multivalued dependency (MVD) X↠Y on a relation R says that if two tuples of R agree on all the attributes of X, then by swapping their components in Y, we still get tuples in R, i.e., for each value of X, the values of Y are independent of the values of R\(X∪Y).

19. Definition of MVD • MVD captures redundancy that FD’s can’t • A multivalued dependency (MVD) X↠Y on a relation R says that if two tuples of R agree on all the attributes of X, then by swapping their components in Y, we still get tuples in R, i.e., for each value of X, the values of Y are independent of the values of R\(X∪Y).

20. Definition of MVD • MVD captures redundancy that FD’s can’t • A multivalued dependency (MVD) X↠Y on a relation R says that if two tuples of R agree on all the attributes of X, then by swapping their components in Y, we still get tuples in R, i.e., for each value of X, the values of Y are independent of the values of R\(X∪Y). Thus,person ↠ teacher

21. Another Definition of MVDs

22. Another Definition of MVDs Suppose X↠Yis a MVD in a relation R. Let W be the set of attributes of R that are not in X∪Y. Then for each value x of X, there is a set VY of values of Y and a set VW of values of W such that R contains exactly the tuples of the form (x, yi, wk), where yi and wk run over all combinations of the values in VY and VW.

23. Another Definition of MVDs Suppose X↠Yis a MVD in a relation R. Let W be the set of attributes of R that are not in X∪Y. Then for each value x of X, there is a set VY of values of Y and a set VW of values of W such that R contains exactly the tuples of the form (x, yi, wk), where yi and wk run over all combinations of the values in VY and VW. x

24. Another Definition of MVDs Suppose X↠Yis a MVD in a relation R. Let W be the set of attributes of R that are not in X∪Y. Then for each value x of X, there is a set VY of values of Y and a set VW of values of W such that R contains exactly the tuples of the form (x, yi, wk), where yi and wk run over all combinations of the values in VY and VW. VY VW x w1 y1 ⁞ ⁞ ⁞ yp wq

25. Another Definition of MVDs Suppose X↠Yis a MVD in a relation R. Let W be the set of attributes of R that are not in X∪Y. Then for each value x of X, there is a set VY of values of Y and a set VW of values of W such that R contains exactly the tuples of the form (x, yi, wk), where yi and wk run over all combinations of the values in VY and VW. VY VW R x w1 y1 wk w1 . . wq . . y1 . yp . . yi . x x . . . . x ⁞ ⁞ ⁞ yp wq

26. Another Definition of MVDs Suppose X↠Yis a MVD in a relation R. Let W be the set of attributes of R that are not in X∪Y. Then for each value x of X, there is a set VY of values of Y and a set VW of values of W such that R contains exactly the tuples of the form (x, yi, wk), where yi and wk run over all combinations of the values in VY and VW. VY VW R x Look at the example we discussed.

27. Another Definition of MVDs Suppose X↠Yis a MVD in a relation R. Let W be the set of attributes of R that are not in X∪Y. Then for each value x of X, there is a set VY of values of Y and a set VW of values of W such that R contains exactly the tuples of the form (x, yi, wk), where yi and wk run over all combinations of the values in VY and VW. VY VW R Kevin Look at the example we discussed.

28. Another Definition of MVDs Suppose X↠Yis a MVD in a relation R. Let W be the set of attributes of R that are not in X∪Y. Then for each value x of X, there is a set VY of values of Y and a set VW of values of W such that R contains exactly the tuples of the form (x, yi, wk), where yi and wk run over all combinations of the values in VY and VW. VY VW R Kevin David Michael Jeff Jason Tom Look at the example we discussed.

29. Another Definition of MVDs Suppose X↠Yis a MVD in a relation R. Let W be the set of attributes of R that are not in X∪Y. Then for each value x of X, there is a set VY of values of Y and a set VW of values of W such that R contains exactly the tuples of the form (x, yi, wk), where yi and wk run over all combinations of the values in VY and VW. VY VW R Kevin David Michael Jeff Jason Tom Look at the example we discussed.

30. Another Example Consumers(name, addr, phones, beersLiked)

31. Another Example Consumers(name, addr, phones, beersLiked) • A consumer’s phones are independent of the beers they like.

32. Another Example Consumers(name, addr, phones, beersLiked) • A consumer’s phones are independent of the beers they like. • name ↠ phones and name ↠ beersLiked.

33. Another Example Consumers(name, addr, phones, beersLiked) • A consumer’s phones are independent of the beers they like. • name ↠ phones and name ↠ beersLiked. • Thus, each of a consumer’s phones appears with each of the beer they like in all combinations.

34. Another Example Consumers(name, addr, phones, beersLiked) • A consumer’s phones are independent of the beers they like. • name ↠ phones and name ↠ beersLiked. • Thus, each of a consumer’s phones appears with each of the beer they like in all combinations. • This repetition is unlike FD redundancy.

35. Another Example Consumers(name, addr, phones, beersLiked) • A consumer’s phones are independent of the beers they like. • name ↠ phones and name ↠ beersLiked. • Thus, each of a consumer’s phones appears with each of the beer they like in all combinations. • This repetition is unlike FD redundancy. • name → addr is the only FD.

36. Another Example Consumers(name, addr, phones, beersLiked) • A consumer’s phones are independent of the beers they like. • name ↠ phones and name ↠ beersLiked. • Thus, each of a consumer’s phones appears with each of the beer they like in all combinations. • This repetition is unlike FD redundancy. • name → addr is the only FD.

37. Another Example Consumers(name, addr, phones, beersLiked) • A consumer’s phones are independent of the beers they like. • name ↠ phones and name ↠ beersLiked. • Thus, each of a consumer’s phones appears with each of the beer they like in all combinations. • This repetition is unlike FD redundancy. • name → addr is the only FD. Then these tuples must also be in the relation.

38. Another Example Consumers(name, addr, phones, beersLiked) • A consumer’s phones are independent of the beers they like. • name ↠ phones and name ↠ beersLiked. • Thus, each of a consumer’s phones appears with each of the beer they like in all combinations. • This repetition is unlike FD redundancy. • name → addr is the only FD. If X ↠ Y X Y others Then these tuples must also be in the relation.

39. Another Example Consumers(name, addr, phones, beersLiked) • A consumer’s phones are independent of the beers they like. • name ↠ phones and name ↠ beersLiked. • Thus, each of a consumer’s phones appears with each of the beer they like in all combinations. • This repetition is unlike FD redundancy. • name → addr is the only FD. If X ↠ Y X Y others equal Then these tuples must also be in the relation.

40. Another Example Consumers(name, addr, phones, beersLiked) • A consumer’s phones are independent of the beers they like. • name ↠ phones and name ↠ beersLiked. • Thus, each of a consumer’s phones appears with each of the beer they like in all combinations. • This repetition is unlike FD redundancy. • name → addr is the only FD. If X ↠ Y X Y others equal exchange Then these tuples must also be in the relation.

41. MVD Rules • Every FD is an MVD (promotion).

42. MVD Rules • Every FD is an MVD (promotion). * If X→Y, then swapping Y’s between two tuples that agree on X doesn’t change the tuples.

43. MVD Rules • Every FD is an MVD (promotion). * If X→Y, then swapping Y’s between two tuples that agree on X doesn’t change the tuples. * Therefore, the “new” tuples are surely in the relation, and we know X ↠ Y.

44. MVD Rules • Every FD is an MVD (promotion). * If X→Y, then swapping Y’s between two tuples that agree on X doesn’t change the tuples. * Therefore, the “new” tuples are surely in the relation, and we know X ↠ Y. • Transitiveness: If X↠Y, and Y↠Z, then X↠Z.

45. MVD Rules • Every FD is an MVD (promotion). * If X→Y, then swapping Y’s between two tuples that agree on X doesn’t change the tuples. * Therefore, the “new” tuples are surely in the relation, and we know X ↠ Y. • Transitiveness: If X↠Y, and Y↠Z, then X↠Z. • Complementation: If X↠Y, and Z is all the other attributes, then X↠Z.

46. However, Splitting Doesn’t Hold • Like FD’s, we cannot generally split the left side of an MVD.

47. However, Splitting Doesn’t Hold • Like FD’s, we cannot generally split the left side of an MVD. • But unlike FD’s, we cannot split the right side either --- sometimes you have to leave several attributes on the right side.

48. However, Splitting Doesn’t Hold • Like FD’s, we cannot generally split the left side of an MVD. • But unlike FD’s, we cannot split the right side either --- sometimes you have to leave several attributes on the right side. • Example Consumers(name, areaCode, phone, beersLiked, manf)

49. However, Splitting Doesn’t Hold • Like FD’s, we cannot generally split the left side of an MVD. • But unlike FD’s, we cannot split the right side either --- sometimes you have to leave several attributes on the right side. • Example Consumers(name, areaCode, phone, beersLiked, manf) * A drinker can have several phones, with the number divided between areaCode and phone (last 7 digits).

50. However, Splitting Doesn’t Hold • Like FD’s, we cannot generally split the left side of an MVD. • But unlike FD’s, we cannot split the right side either --- sometimes you have to leave several attributes on the right side. • Example Consumers(name, areaCode, phone, beersLiked, manf) * A drinker can have several phones, with the number divided between areaCode and phone (last 7 digits). * A drinker can like several beers, each with its own manufacturer.