10 Appendix B Advanced Relational Database Design The domain constraints for Regular acct schema require that the account number does not begin with 9.The resulting design is in DKNF,although the proof of this fact is beyond the scope of this text. Let us compare DKNF to the other normal forms that we have studied.Under the other normal forms,we did not take into consideration domain constraints.We assumed(implicitly)that the domain of each attribute was some infinite domain, such as the set of all integers or the set of all character strings.We allowed key constraints (indeed,we allowed functional dependencies).For each normal form,we allowed a restricted form of general constraint (a set of functional, multivalued,or join dependencies).Thus,we can rewrite the definitions of PINF, 4NF,BCNF,and 3NF in a manner that shows them to be special cases of DKNF. We now present a DKNF-inspired rephrasing of our definition of PINF.Let R =(A1,A2,....A)be a relation schema.Let dom(A)denote the domain of attribute Ai,and let all these domains be infinite.Then all domain constraints D are of the form A dom(A).Let the general constraints be a set G of functional, multivalued,or join dependencies.If F is the set of functional dependencies in G, let the set K of key constraints be those nontrivial functional dependencies in F+ of the form a-R.Schema R is in PJNF if and only if it is in DKNF with respect to D,K,and G. A consequence of DKNF is that all insertion and deletion anomalies are elimi- nated. DKNF represents an"ultimate"normal form because it allows arbitrary con- straints,rather than dependencies,yet it allows efficient testing of these con- straints.Of course,if a schema is not in DKNF,we may be able to achieve DKNF via decomposition,but such decompositions,as we have seen,are not always dependency-preserving decompositions.Thus,although DKNF is a goal of a database designer,it may have to be sacrificed in a practical design. B.4 Summary In this chapter we presented the theory of multivalued dependencies,including a set of sound and complete inference rules for multivalued dependencies. We then presented two more normal forms based on more general classes of constraints.Join dependencies are a generalization of multivalued dependencies, and lead to the definition of PINF.DKNF is an idealized normal form that may be difficult to achieve in practice.Yet DKNF has desirable properties that should be included to the extent possible in a good database design. Exercises B.1 List all the nontrivial multivalued dependencies satisfied by the relation in Figure B.7.10 Appendix B Advanced Relational Database Design The domain constraints for Regular acct schema require that the account number does not begin with 9. The resulting design is in DKNF, although the proof of this fact is beyond the scope of this text. Let us compare DKNF to the other normal forms that we have studied. Under the other normal forms, we did not take into consideration domain constraints.We assumed (implicitly) that the domain of each attribute was some infinite domain, such as the set of all integers or the set of all character strings. We allowed key constraints (indeed, we allowed functional dependencies). For each normal form, we allowed a restricted form of general constraint (a set of functional, multivalued, or join dependencies). Thus, we can rewrite the definitions of PJNF, 4NF, BCNF, and 3NF in a manner that shows them to be special cases of DKNF. We now present a DKNF-inspired rephrasing of our definition of PJNF. Let R = (A1, A2,..., An) be a relation schema. Let dom(Ai) denote the domain of attribute Ai , and let all these domains be infinite. Then all domain constraints D are of the form Ai ⊆ dom(Ai). Let the general constraints be a set G of functional, multivalued, or join dependencies. If F is the set of functional dependencies in G, let the set K of key constraints be those nontrivial functional dependencies in F + of the form → R. Schema R is in PJNF if and only if it is in DKNF with respect to D, K, and G. A consequence of DKNF is that all insertion and deletion anomalies are elimi￾nated. DKNF represents an “ultimate” normal form because it allows arbitrary con￾straints, rather than dependencies, yet it allows efficient testing of these con￾straints. Of course, if a schema is not in DKNF, we may be able to achieve DKNF via decomposition, but such decompositions, as we have seen, are not always dependency-preserving decompositions. Thus, although DKNF is a goal of a database designer, it may have to be sacrificed in a practical design. B.4 Summary In this chapter we presented the theory of multivalued dependencies, including a set of sound and complete inference rules for multivalued dependencies. We then presented two more normal forms based on more general classes of constraints. Join dependencies are a generalization of multivalued dependencies, and lead to the definition of PJNF. DKNF is an idealized normal form that may be difficult to achieve in practice. Yet DKNF has desirable properties that should be included to the extent possible in a good database design. Exercises B.1 List all the nontrivial multivalued dependencies satisfied by the relation in Figure B.7.