Appendix B Advanced Relational Database Design from our banking example.We can define a relation loan_info (Loan info_schema)as the set of all tuples on Loan_info schema such that The loan represented by loan number is made by the branch named branch name. The loan represented by loan-number is made to the customer named customer 1a1e. The loan represented by loan number is in the amount given by amount. The preceding definition of the loan_info relation is a conjunction of three predi- cates:one on loan number and branch name,one on loan number and customer name, and one on loan_number and amount.Surprisingly,it can be shown that the preced- ing intuitive definition of loan_info logically implies the join dependency *((loan number,branch-name),(loan-number,customer name),(loan_number,amount)). Thus,join dependencies have an intuitive appeal and correspond to one of our three criteria for a good database design. For functional and multivalued dependencies,we were able to give a system of inference rules that are sound and complete.Unfortunately,no such set of rules is known for join dependencies.It appears that we must consider more general classes of dependencies than join dependencies to construct a sound and complete set of inference rules.The bibliographical notes contain references to research in this area. B.2.2 Project-Join Normal Form Project-join normal form (PINF)is defined in the same way as BCNF and 4NF,except that join dependencies are used.A relation schema R is in PJNF with respect to a set D of functional,multivalued,and join dependencies if,for all join dependencies in D+of the form*(R1,R2....,R),where each Ri Rand R R1 UR2U...U R, at least one of the following holds: *(R1,R2,....Rn)is a trivial join dependency. Every Ri is a superkey for R. A database design is in PINF if each member of the set of relation schemas that constitutes the design is in PJNF.PJNF is called fifth normal form(5NF)in some of the literature on database normalization. Consider again our banking example.Given the join dependency *((loan number,branch name),(loan number,customer name),(loan number,amount)),Loan info schema is not in PJNF.To put Loan info schema into PINF,we must decompose it into the three schemas specified by the join dependency:(loan umber,branch name),(loan number,customer name),and (loan number,amount). Because every multivalued dependency is also a join dependency,it is easy to see that every PINF schema is also in 4NF.Thus,in general,we may not be able to find a dependency-preserving decomposition into PINF for a given schema.8 Appendix B Advanced Relational Database Design from our banking example. We can define a relation loan info (Loan info schema) as the set of all tuples on Loan info schema such that • The loan represented by loan number is made by the branch named branch name. • The loan represented by loan number is made to the customer named customer name. • The loan represented by loan number is in the amount given by amount. The preceding definition of the loan info relation is a conjunction of three predi￾cates: one on loan number and branch name, one on loan number and customer name, and one on loan number and amount. Surprisingly, it can be shown that the preced￾ing intuitive definition of loan info logically implies the join dependency *((loan number, branch name), (loan number, customer name), (loan number, amount)). Thus, join dependencies have an intuitive appeal and correspond to one of our three criteria for a good database design. For functional and multivalued dependencies, we were able to give a system of inference rules that are sound and complete. Unfortunately, no such set of rules is known for join dependencies. It appears that we must consider more general classes of dependencies than join dependencies to construct a sound and complete set of inference rules. The bibliographical notes contain references to research in this area. B.2.2 Project-Join Normal Form Project-join normal form (PJNF) is defined in the same way as BCNF and 4NF, except that join dependencies are used. A relation schema R is in PJNF with respect to a set D of functional, multivalued, and join dependencies if, for all join dependencies in D+ of the form *(R1, R2,..., Rn), where each Ri ⊆ Rand R = R1 ∪ R2 ∪ ... ∪ Rn, at least one of the following holds: • *(R1, R2,..., Rn) is a trivial join dependency. • Every Ri is a superkey for R. A database design is in PJNF if each member of the set of relation schemas that constitutes the design is in PJNF. PJNF is called fifth normal form (5NF) in some of the literature on database normalization. Consider again our banking example. Given the join dependency *((loan number, branch name), (loan number, customer name), (loan number, amount)), Loan info schema is not in PJNF. To put Loan info schema into PJNF, we must decompose it into the three schemas specified by the join dependency: (loan number, branch name), (loan number, customer name), and (loan number, amount). Because every multivalued dependency is also a join dependency, it is easy to see that every PJNF schema is also in 4NF. Thus, in general, we may not be able to find a dependency-preserving decomposition into PJNF for a given schema