B.2 Join Dependencies 1.4NF 2.Dependency preservation 3.Lossless join If all we have are functional dependencies,then the first criterion is just BCNF. We have seen also that it is not always possible to meet all three of these criteria.We succeeded in finding such a decomposition for the bank example,but failed for the example of schema R =(A,B,C,G,H,I). When we cannot achieve our three goals,we have to compromise on 4NF or dependency preservation. B.2 Join Dependencies We have seen that the lossless-join property is one of several properties of a good database design.Indeed,this property is essential:Without it,information is lost. When we restrict the set of legal relations to those satisfying a set of functional and multivalued dependencies,we are able to use these dependencies to show that certain decompositions are lossless-join decompositions. Because of the importance of the concept of lossless join,it is useful to be able to constrain the set of legal relations over a schema R to those relations for which a given decomposition is a lossless-join decomposition.In this section,we define such a constraint,called a join dependency.Just as types of dependency led to other normal forms,join dependencies will lead to a normal form called project-join normal form(PJNF). B.2.1 Definition of Join Dependencies Let R be a relation schema and R1,R2.....R be a decomposition of R.The join dependency *(R1,R2,...,R)is used to restrict the set of legal relations to those for which Ri,R2.....Rn is a lossless-join decomposition of R.Formally,if R R1U R2 U...U Rn,we say that a relation r(R)satisfies the join dependency *(R1,R2,,R)if r=ΠR1(r)凶ΠR2(r)·凶ΠR(r) A join dependency is trivial if one of the R;is R itself. ABCG HI a1 b1 c1 81 h1 i1 a201 C2 82 h2i2 Figure B.2 A relation r(R)that does not satisfy BHI.B.2 Join Dependencies 5 1. 4NF 2. Dependency preservation 3. Lossless join If all we have are functional dependencies, then the first criterion is just BCNF. We have seen also that it is not always possible to meet all three of these criteria. We succeeded in finding such a decomposition for the bank example, but failed for the example of schema R = (A, B, C, G, H, I). When we cannot achieve our three goals, we have to compromise on 4NF or dependency preservation. B.2 Join Dependencies We have seen that the lossless-join property is one of several properties of a good database design. Indeed, this property is essential: Without it, information is lost. When we restrict the set of legal relations to those satisfying a set of functional and multivalued dependencies, we are able to use these dependencies to show that certain decompositions are lossless-join decompositions. Because of the importance of the concept of lossless join, it is useful to be able to constrain the set of legal relations over a schema R to those relations for which a given decomposition is a lossless-join decomposition. In this section, we define such a constraint, called a join dependency. Just as types of dependency led to other normal forms, join dependencies will lead to a normal form called project-join normal form (PJNF). B.2.1 Definition of Join Dependencies Let R be a relation schema and R1, R2,..., Rn be a decomposition of R. The join dependency *(R1, R2,..., Rn) is used to restrict the set of legal relations to those for which R1, R2,..., Rn is a lossless-join decomposition of R. Formally, if R = R1 ∪ R2 ∪ ... ∪ Rn, we say that a relation r(R) satisfies the join dependency *(R1, R2,..., Rn) if r = R1 (r) ✶ R2 (r) ✶ ··· ✶ Rn (r) A join dependency is trivial if one of the Ri is R itself. A B C G H I a1 b1 c1 g1 h1 i i 1 a2 b1 c2 g2 h2 2 Figure B.2 A relation r(R) that does not satisfy B →→ HI.