Data Base Design Techniques I: pp 172-218 | Cite as

# Problems of relational database design

## Abstract

In the logical design of a relational database, the theory of data dependencies has been playing an important role. Data depenencies are integrity constraints concerned with attribute relationships. The theory of functional dependencies (FDs) were successful to capture the important aspects of relational database design: semantics of data and logical scheme design (normalization). Several problems of FDs have been also pointed out, and many kinds of new dependencies have been introduced as the generalizations of FDs. Each of the researches on the theory of the new dependencies, semantics of data and the logical scheme design has made a great progress with few sufficient interactions. This leads to several problems when the data dependency theory is used in the actual relational database design.

In this paper, we identify these important unsolved problems as well as the problems of the FD-based design, and proceed to solve some of them. As for the FD-based design, a new synthetic design procedure is shown. We show that the well known Bernstein's algorithm may not always produce a minimum solution in some cases, and the problem is solved in our procedure. Furthermore, our procedure always produces a set of relations which satisfy the important criterion ‘lossless join property’. We also discuss the decompositions of Boyce-Codd normal form relations by multivalued dependencies. As for the principle of logical scheme design, we discuss the preservability of data dependencies. We mainly discuss what data dependencies can be enforced by a set of relation schemes when each relation is allowed to be updated independently from others. Several important conditions for the preservability are shown. This study aims at the relaxation of so called ‘universal relation assumption’. As for the semantics of data, we discuss the semantic problems of multivalued dependencies and the incomplete information problem.

## Preview

Unable to display preview. Download preview PDF.

## References

- Aho,A.V., Beeri,C. and Ullman,J.D. (1977). The Theory of joins in Relational Data Bases (Extended Abstract). Proc. 18th Annual Symposium on Foundation of Computer Science, October 1977, pp.107–113.Google Scholar
- Aho, A.V., Beeri, C. and Ullman, J.D. (1979). The Theory of Joins in Relational Databases. ACMTODS, Vol.4, No.3, September 1979, pp.297–314.Google Scholar
- Armstrong,W.W. (1974). Dependency Structures of Data Base Relationships. Proc. IFIP 74 Congress, August 1974, pp.580–583.Google Scholar
- Arora,A.K. and Carlson,C.B. (1978). The Information Preserving Properties of Relational Database Transformations. Proc. 4th International Conference on VLDB, September 1978, pp.352–359.Google Scholar
- Beeri,C., Fagin,R. and Howard,J.H. (1977). A Complete Axiomatization for Functional and Multivalued Dependencies in Database Relations. Proc. ACM-SIGMOD 1979 International Conference on Management of Data, August 1977, pp.47–61.Google Scholar
- Beeri,C., Bernstein,P.A. and Goodman,N. (1978). A Sophisticate's Introduction to Database Normalization Theory. Proc. 4th International Conference on VLDB, September 1978, pp.113–124.Google Scholar
- Beeri, C. and Bernstein, P.A. (1979a). Computational Problems Related to the Design of Normal Form Relational Schemas. ACMTODS, Vol.4, No.1, March 1979, pp.30–59.Google Scholar
- Beeri,C., Mendelzon,A.O., Sagiv,Y. and Ullman,J.D. (1979b). Equivalence of Relational Database Schemes. Proc. 11th Annual ACM Symposium on Theory of Computing, April 1979, pp.319–329.Google Scholar
- Beeri,C. (1979c). On the Role of Data Dependencies of Relational Database Schemas. Hebrew Univ., Dept. of Computer Science Report, No.43, January 1979.Google Scholar
- Beeri,C. and Vardi,M.Y. (1979d). On the Properties of Total Join Dependencies. Dept. of Computer Science, The Hebrew University, Res. Rep., November 1979.Google Scholar
- Beeri,C. and Rissanen,J. (1980a). Faithful Representations of Relational Database Schemes. IBM Res. Rep., RJ2722, January 1980.Google Scholar
- Beeri, C. (1980b). On the Membership Problem for Functional and Multivalued Dependencies in Relational Databases. ACMTODS, Vol.5, No.3, September 1980, pp.241–259.Google Scholar
- Bernstein, P.A. (1976). Synthesizing Third Normal Form Relations from Functional Dependencies. ACMTODS, Vol.1, No.4, December 1976, pp.277–298.Google Scholar
- Bernstein,P.A. and Goodman,N. (1980). What Does Boyce-Codd Normal Form Do?. Proc. 6th International Conference on VLDB, October 1980.Google Scholar
- Biskup, J. (1978). On the Complementation Rule for Multivalued Dependencies in Database Relations. Acta Informatica, Vol.10, No.3, 1978, pp.297–305.Google Scholar
- Biskup,J. (1980). Inferences of Multivalued Dependencies in Fixed and Undetermined Universes. Theoretical Computer Science, Vol.10, No.1, January 1980.Google Scholar
- Biskup,J., Dayal,U. and Bernstein,P.A. (1979). Synthesizing Independent Database Schemas. Proc. ACM-SIGMOD International Conference on Management of Data, May 1979, pp.143–151.Google Scholar
- Codd, E.F. (1970). A relational Model of Data for Large Shared Data Banks. Comm. ACM, Vol.13, No.6, June 1970, pp.370–387.Google Scholar
- Codd,E.F. (1971). Further Normalization of the Data Base Relational Model. Proc. Courant Computer Science Symposium 6, Data Base Systems, May 1971, pp.33–64.Google Scholar
- Codd,E.F. (1974). Recent Investigations in Relational Data Base Systems. Proc. IFIP Congress, August 1974, pp.1017–1021.Google Scholar
- Date,C.J. (1977). An Introduction to Database Systems. 2nd ed., Addison-Wesley, 1977.Google Scholar
- Delobel, C. and Casey, R.G. (1973). Decomposition of a Data Base and the Theory of Boolean Switching Functions. IBM J. Res. & Dev., Vol.17, No.5, September 1973, pp.374–386.Google Scholar
- Delobel, C. (1978a). Normalization and Hierarchical Dependencies in the Relational Data Model. ACMTODS, Vol.3, No.3, September 1978, pp.201–222.Google Scholar
- Delobel,C. and Parker,D.S. (1978b). Functional and Multivalued Dependencies in a Relational Database and the Theory of Boolean Switching Functions. Universite de Grenoble, November 1978.Google Scholar
- Delobel, C. and Pichat, E. (1978c). The Design of Relational Information System According to Different Kinds of Dependencies. Proc. 2nd Conference of the Eupopean Cooperation in Informatics, Lecture Notes in Computer Science 65, October 1978, pp.266–290.Google Scholar
- Delobel, C. (1980). An Overview of the Relational Data Theory. Proc. IFIP Congress' 80, October 1980, pp.413–426.Google Scholar
- Fadous,R.Y. (1979). Decomposition of a Relation into Fourth Normal Forms. Proc. 3rd COMPSAC, November 1979, pp.404–408.Google Scholar
- Fagin, R. (1977a). Multivalued Dependencies and a New Normal Form for Relational Databases. ACMTODS, Vol.2, No.3, September 1977, pp.262–278.Google Scholar
- Fagin,R. (1977b). The Decomposition versus the synthetic Approach to Relational Database Design. Proc. 3rd International Conference on VLDB, October 1977, pp.441–446.Google Scholar
- Fagin, R. (1977c). Functional Dependencies in a Relational Data Base and Propositional Logic. IBM J. Res. & Dev., Vol.21, No.6, November 1977, pp.534–544.Google Scholar
- Fagin,R. (1979a). Normal Forms and relational Database Operators. Proc. ACM-SIGMOD International Conference on Management of Data, May 1979, pp.153–160.Google Scholar
- Fagin,R. (1979b). A Normal Form for Relational Databases That is Based on Domains and Keys. IBM Res. Rep., RJ2520, May 1979.Google Scholar
- Fagin,R. (1980). Horn Clauses and Database Dependencies. IBM Res. Rep., RJ2741, March 1980.Google Scholar
- Grant, J. (1977). Null Values in a Relational Data Base. Information Processing Letters, Vol.6, No.5, October 1979, pp.156–157.Google Scholar
- Hagihara, K., Ito, M., Taniguchi, K. and Kasami, T. (1979). Decision Problems for Multivalued Dependencies in Relational Databases. SIAM J. Computing, Vol.8, No.2, May 1979, pp.247–264.Google Scholar
- Honeyman, P., Ladner, R.E. and Yannakakis, M. (1980). Testing the Universal Instance Assumption. Information Processing Letters, Vol.10, No.1, February 1980, pp.14–19.Google Scholar
- Kambayashi,Y., Tanaka,K. and Yajima,S. (1977). A Relational Data Language with Simplified Binary Relation Handling Capability. Proc. 3rd International Conference on VLDB, October 1977, pp.338–350.Google Scholar
- Kambayashi,Y. (1978a). Equivalen Key Problem of the Relational Data Base Model. Proc. International Conference on Mathematical Studies of Information Processing, Lecture Notes in Computer Science 75, August 1978, pp.165–192.Google Scholar
- Kambayashi,Y., Tanaka,K., Imai,Y. and Yajima,S. (1978b). Generalized Null Values in Relational Databases and Their Application to Database Decompositions. (in Japanese). Kansai Branch Joint Convention record of IECE Japan, G6–13, October 1978.Google Scholar
- Kambayashi,Y. (1979a). A New Synthetic Approach for Relational Database Design. presented at AFIPS NCC, June 1979. (Reprints available from the author. Detailed verion is published as a report: Dept. of Information Science, Kyoto University, Yajima Laboratory Research Report, ER78-02, Nov. 1978).Google Scholar
- Kambayashi,Y., Tanaka,K. and Yajima,S. (1979b). Semantic Aspects of Data Dependencies and Their Application to Relational Database Design. Proc. COMPSAC'79, November 1979, pp.398–403.Google Scholar
- Kambayashi,Y. (1981). Database: A Bibliography. To be published by Computer Science Press. 1981.Google Scholar
- Kent,W. (1973). A Primer of Normal Forms. IBM Technical Report, TR02.600, December 1973.Google Scholar
- Klug, A. (1980). Calculating Constraints on Relational Expressions. ACMTODS, Vol.5, No.3, September 1980, pp.260–290.Google Scholar
- Lien,Y.E. (1979). Multivalued Dependencies with Null Values in Relational Data Bases. Proc. 5th International Conference on VLDB, October 1979, pp.61–66.Google Scholar
- Maier, D., Mendelzon, A.O. and Sagiv, Y. (1979a). Testing Implications of Data Dependencies. ACMTODS, Vol.4, No.4, December 1979, pp.455–469.Google Scholar
- Maier,D., Mendelzon,A.O., Sadri,F. and Ullman,J.D. (1979b). Adequacy of Decompositions of Relational Database. Proc. Workshop on Formal Bases of Data Bases, December 1979.Google Scholar
- Mendelzon, A.O. (1979a). On Axiomatizing Multivalued Dependencies in relational Databases. JACM, Vol.26, No.1, January 1979, pp.37–44.Google Scholar
- Mendelzon,A.O. and Maier,D. (1979b). Generalized Mutual Dependencies and the Decomposition of Database Relations. Proc. 5th International Conference on VLDB, October 1979, pp.75–82.Google Scholar
- Nicolas,J.M. (1978a). Mutual Dependencies and Some Results on Undecomposable Relations. Proc. 4th International Conference on VLDB, September 1979, pp.360–367.Google Scholar
- Nicolas,J.M. (1978b). First Order Logic Formalization for Functional, Multivalued and Mutual Dependencies. Proc. ACM-SIGMOD International Conference on Management of Data, May 1979, pp.40–46.Google Scholar
- Paredaens,J. (1979). Transitive Dependencies in a Database Scheme. MBLE Res. Lab., Res. Rep., R387, February 1979.Google Scholar
- Parker,D.S. and Delobel,C. (1979). Algorithmic Applications for a New Result on Multivalued Dependencies. Proc. 5th International Conference on VLDB, October 1979, pp.67–74.Google Scholar
- Parker,D.S.,Jr. and Parsaye-Ghomi,K. (1980). Inferences Involving Embedded Multivalued Dependencies and Transitive Dependencies. Proc. ACM-SIGMOD International Conference on Management of Data, May 1980, pp.52–57.Google Scholar
- Rissanen, J. (1977). Independent Components of Relations. ACMTODS, Vol.2, No.4, December 1977, pp.317–325.Google Scholar
- Rissanen, J. (1978). Theory of Relations for Databases — A Tutorial Survey. Proc. 7th Symposium on Mathematical Foundations of Computer Science, Lecture Notes in Computer Science 64, September 1978, pp.536–551.Google Scholar
- Sadri,F. and Ullman,J.D. (1980a). A Complete Axiomatization for a Large Class of Dependencies in Relational Databases. Proc. ACM Symposium on the Theory of Computing, April 1980, pp.117–122.Google Scholar
- Sadri,F. and Ullman,J.D. (1980b). The Interaction between Functional Dependencies and Template Dependencies. Proc. ACM-SIGMOD International Conference on Management of Data, May 1980, pp.45–51.Google Scholar
- Sagiv,Y. and Fagin,R. (1979a). An Equivalence between Databse Dependencies and a Subclass of Propositional Logic. IBM Res. Rep., RJ2500, March 1979.Google Scholar
- Sagiv,Y. and Walecka,S. (1979b). Subset Dependencies as an Alternative to Embedded Multivalued Dependencies. Dept. of Computer Science, Univ. of Illinois at Urbana-Champaign, UIUCDCD-R-79-980, July 1979.Google Scholar
- Schmid,H.A. and Swenson,J.R. (1975). On the Semantics of the Relational Lata Model. Proc. ACM-SIGMOD International Conference on Management of Data, May 1975, pp.211–223.Google Scholar
- Sciore,E. (1979). A Complete Axiomatization of Full Join Dependencies. Dept. of EECS, Princeton University, Technical Report, TR-279, July 1979.Google Scholar
- Sharman,G.C.H. (1976). A Constructive Definition of Third Normal Form. Proc. ACM-SIGMOD International Conference on Management of Data, June 1976, pp.91–99.Google Scholar
- Smith,J.M. (1978). A Normal Form for Abstract Syntax. Proc. 4th International Conference on VLDB, September 1978, pp.152–162.Google Scholar
- Tanaka, K., Kambayashi, Y. and Yajima, S. (1979a). Representability Problem for Relational Database Design with Multivalued Dependencies. Memoirs of the Research Institute for Mathematical Sciences, Kyoto University, 353, April 1979, pp.1–10.Google Scholar
- Tanaka, K., Kambayashi, Y. and Yajima, S. (1979b). Properties of Embedded Multivalued Dependencies in Relational Databases. Trans. Institute of Electronics and Communication Engineers (IECE) of Japan, Vol.E62, No.8, August 1979, pp.536–543.Google Scholar
- Tanaka,K., Kambayashi,Y. and Yajima,S. (1980). Preservability of Data Dependencies for Relational Database Operations. Proc. JIPDEC Information Systems Seminar on Semantic Aspects of Databases, February 1980, pp.151–174.Google Scholar
- Tanaka,Y. and Tsuda,T. (1977). Decomposition and Composition of a Relational Data Base. Proc. 3rd International Conference on VLDB, October 1977, pp.454–461.Google Scholar
- Vassiliou,Y. (1980). Functional Dependencies and Incomplete Information. Proc. 6th International Conference on VLDB, October 1980.Google Scholar
- Wang,C.P. and Wedekind,H. (1975). Segment Synthesis in Logical Data Base Design. IBM J. Res. & Dev., Vol.20, No.3, May 1976.Google Scholar
- Yannakakis,M. and Papadimitriou,C.H. (1980). Algebraic Dependencies. Proc. 21st Annual Symposium on Foundations of Computer Science, October 1980.Google Scholar
- Zaniolo,C. (1976). Analysis and Design of Relational Schemata for Database Systems. UCLA Computer Methodology Group Report, UCLA-ENG-7669, July 1976.Google Scholar