skip to main content
research-article

Composite Object Normal Forms: Parameterizing Boyce-Codd Normal Form by the Number of Minimal Keys

Published: 30 May 2023 Publication History

Abstract

We parameterize schemata in Boyce-Codd Normal Form (BCNF) by the number n of minimal keys they exhibit. We show that n quantifies a trade-off between access variety and update complexity. Indeed, access variety refers to the number of different ways by which every entity over the schema is represented uniquely, while update complexity refers to the number of attribute sets for which uniqueness needs to be preserved during updates. As normalization aims at minimizing the level of effort required to preserve data consistency during updates, we establish an algorithm that returns a lossless, dependency-preserving 3NF decomposition where the subset of output schemata not in BCNF is minimized and redundant BCNF schemata are eliminated from the highest to the lowest n exhibited. In particular, if a lossless, dependency-preserving BCNF decomposition exists, our algorithm returns one where the maximum n across all output schemata is minimized. Experiments with synthetic and real-world data quantify the impact of n on the update and query performance over schemata in BCNF with n minimal keys, and show insight into the efficacy of our algorithm suite.

Supplemental Material

MP4 File
Presentation video

References

[1]
Marcelo Arenas. 2006. Normalization theory for XML. SIGMOD Rec., Vol. 35, 4 (2006), 57--64.
[2]
William Ward Armstrong. 1974. Dependency Structures of Data Base Relationships. In Information Processing, Proceedings of the 6th IFIP Congress 1974, Stockholm, Sweden, August 5--10, 1974. 580--583.
[3]
Catriel Beeri and Philip A. Bernstein. 1979. Computational Problems Related to the Design of Normal Form Relational Schemas. ACM Trans. Database Syst., Vol. 4, 1 (1979), 30--59.
[4]
Catriel Beeri, Philip A. Bernstein, and Nathan Goodman. 1978. A Sophisticate's Introduction to Database Normalization Theory. In Fourth International Conference on Very Large Data Bases, September 13--15, 1978, West Berlin, Germany. 113--124.
[5]
Catriel Beeri, Martin Dowd, Ronald Fagin, and Richard Statman. 1984. On the Structure of Armstrong Relations for Functional Dependencies. J. ACM, Vol. 31, 1 (1984), 30--46.
[6]
Philip A. Bernstein. 1976. Synthesizing Third Normal Form Relations from Functional Dependencies. ACM Trans. Database Syst., Vol. 1, 4 (1976), 277--298.
[7]
Philip A. Bernstein and Nathan Goodman. 1980. What does Boyce-Codd Normal Form Do?. In Sixth International Conference on Very Large Data Bases, October 1--3, 1980, Montreal, Quebec, Canada, Proceedings. 245--259.
[8]
Joachim Biskup. 1989. Boyce-Codd Normal Form and Object Normal Forms. Inf. Process. Lett., Vol. 32, 1 (1989), 29--33.
[9]
Joachim Biskup. 1995. Achievements of Relational Database Schema Design Theory Revisited. In Semantics in Databases, Selected Papers from a Workshop, Prague, Czech Republic, 1995. 29--54.
[10]
Joachim Biskup, Umeshwar Dayal, and Philip A. Bernstein. 1979. Synthesizing Independent Database Schemas. In Proceedings of the 1979 ACM SIGMOD International Conference on Management of Data, Boston, Massachusetts, USA, May 30 - June 1. 143--151.
[11]
Joachim Biskup, Já nos Demetrovics, Leonid Libkin, and Ilya B. Muchnik. 1991. On Relational Database Schemes Having Unique Minimal Key. J. Inf. Process. Cybern., Vol. 27, 4 (1991), 217--225.
[12]
Joachim Biskup, David W. Embley, and Jan-Hendrik Lochner. 2008. Reducing inference control to access control for normalized database schemas. Inf. Process. Lett., Vol. 106, 1 (2008), 8--12.
[13]
E. F. Codd. 1974. Recent Investigations in Relational Data Base Systems. In Information Processing, Proceedings of the 6th IFIP Congress 1974, Stockholm, Sweden, August 5--10, 1974. 1017--1021.
[14]
Claude Delobel. 1978. Normalization and Hierarchical Dependencies in the Relational Data Model. ACM Trans. Database Syst., Vol. 3, 3 (1978), 201--222.
[15]
Já nos Demetrovics and Vu Duc Thi. 1994. Normal Forms and Minimal Keys in the Relational Datamodel. Acta Cybern., Vol. 11, 3 (1994), 205--215.
[16]
Ronald Fagin. 1977. Multivalued Dependencies and a New Normal Form for Relational Databases. ACM Trans. Database Syst., Vol. 2, 3 (1977), 262--278.
[17]
Ronald Fagin. 1981. A Normal Form for Relational Databases That Is Based on Domains and Keys. ACM Trans. Database Syst., Vol. 6, 3 (1981), 387--415.
[18]
Dimitrios Gunopulos, Roni Khardon, Heikki Mannila, Sanjeev Saluja, Hannu Toivonen, and Ram Sewak Sharm. 2003. Discovering all most specific sentences. ACM Trans. Database Syst., Vol. 28, 2 (2003), 140--174.
[19]
I. J. Heath. 1971. Unacceptable File Operations in a Relational Data Base. In Proceedings of 1971 ACM-SIGFIDET Workshop on Data Description, Access and Control, San Diego, California, USA, November 11--12, 1971, E. F. Codd and A. L. Dean (Eds.). 19--33.
[20]
Christian S. Jensen, Richard T. Snodgrass, and Michael D. Soo. 1996. Extending Existing Dependency Theory to Temporal Databases. IEEE Trans. Knowl. Data Eng., Vol. 8, 4 (1996), 563--582.
[21]
Henning Kö hler. 2006. Finding Faithful Boyce-Codd Normal Form Decompositions. In Algorithmic Aspects in Information and Management, Second International Conference, AAIM 2006, Hong Kong, China, June 20--22, 2006, Proceedings. 102--113.
[22]
Henning Kö hler and Sebastian Link. 2010. Armstrong axioms and Boyce-Codd-Heath Normal Form under bag semantics. Inf. Process. Lett., Vol. 110, 16 (2010), 717--724.
[23]
Henning Kö hler and Sebastian Link. 2016. SQL Schema Design: Foundations, Normal Forms, and Normalization. In Proceedings of the 2016 International Conference on Management of Data, SIGMOD Conference 2016, San Francisco, CA, USA, June 26 - July 01, 2016. 267--279.
[24]
Henning Kö hler and Sebastian Link. 2018. SQL schema design: foundations, normal forms, and normalization. Inf. Syst., Vol. 76 (2018), 88--113.
[25]
Solmaz Kolahi. 2007. Dependency-preserving normalization of relational and XML data. J. Comput. Syst. Sci., Vol. 73, 4 (2007), 636--647.
[26]
Warren-Dean Langeveldt and Sebastian Link. 2010. Empirical evidence for the usefulness of Armstrong relations in the acquisition of meaningful functional dependencies. Inf. Syst., Vol. 35, 3 (2010), 352--374.
[27]
Carol Helfgott LeDoux and Douglas Stott Parker Jr. 1982. Reflections on Boyce-Codd Normal Form. In Eigth International Conference on Very Large Data Bases, September 8--10, 1982, Mexico City, Mexico, Proceedings. 131--141.
[28]
Mark Levene and George Loizou. 1999. A guided tour of relational databases and beyond. Springer.
[29]
Mark Levene and Millist W. Vincent. 2000. Justification for Inclusion Dependency Normal Form. IEEE Trans. Knowl. Data Eng., Vol. 12, 2 (2000), 281--291.
[30]
Sebastian Link and Henri Prade. 2019. Relational database schema design for uncertain data. Inf. Syst., Vol. 84 (2019), 88--110.
[31]
Sebastian Link and Ziheng Wei. 2021. Logical Schema Design that Quantifies Update Inefficiency and Join Efficiency. In SIGMOD '21: International Conference on Management of Data, Virtual Event, China, June 20--25, 2021. 1169--1181.
[32]
Claudio L. Lucchesi and Sylvia L. Osborn. 1978. Candidate Keys for Relations. J. Comput. Syst. Sci., Vol. 17, 2 (1978), 270--279.
[33]
Johann A. Makowsky and Elena V. Ravve. 1998. Dependency Preserving Refinements and the Fundamental Problem of Database Design. Data Knowl. Eng., Vol. 24, 3 (1998), 277--312.
[34]
Heikki Mannila and Kari-Jouko R"a ih"a. 1986. Design by Example: An Application of Armstrong Relations. J. Comput. Syst. Sci., Vol. 33, 2 (1986), 126--141.
[35]
Wai Yin Mok. 2016. Utilizing Nested Normal Form to Design Redundancy Free JSON Schemas. Int. J. Recent Contributions Eng. Sci. IT, Vol. 4, 4 (2016), 21--25.
[36]
Wai Yin Mok, Yiu-Kai Ng, and David W. Embley. 1996. A Normal Form for Precisely Characterizing Redundancy in Nested Relations. ACM Trans. Database Syst., Vol. 21, 1 (1996), 77--106.
[37]
Sylvia L. Osborn. 1979. Testing for Existence of a Covering Boyce-Codd Normal Form. Inf. Process. Lett., Vol. 8, 1 (1979), 11--14.
[38]
Thorsten Papenbrock and Felix Naumann. 2016. A Hybrid Approach to Functional Dependency Discovery. In SIGMOD. 821--833.
[39]
Thorsten Papenbrock and Felix Naumann. 2017. Data-driven Schema Normalization. In Proceedings of the 20th International Conference on Extending Database Technology, EDBT 2017, Venice, Italy, March 21--24, 2017. 342--353.
[40]
Zahir Tari, John Stokes, and Stefano Spaccapietra. 1997. Object Normal Forms and Dependency Constraints for Object-Oriented Schemata. ACM Trans. Database Syst., Vol. 22, 4 (1997), 513--569.
[41]
Don-Min Tsou and Patrick C. Fischer. 1982. Decomposition of a relation scheme into Boyce-Codd Normal Form. SIGACT News, Vol. 14, 3 (1982), 23--29.
[42]
Millist W. Vincent. 1997. A Corrected 5NF Definition for Relational Database Design. Theor. Comput. Sci., Vol. 185, 2 (1997), 379--391.
[43]
Millist W. Vincent. 1999. Semantic Foundations of 4NF in Relational Database Design. Acta Informatica, Vol. 36, 3 (1999), 173--213.
[44]
Millist W. Vincent, Jixue Liu, and Chengfei Liu. 2004. Strong functional dependencies and their application to normal forms in XML. ACM Trans. Database Syst., Vol. 29, 3 (2004), 445--462.
[45]
Ziheng Wei and Sebastian Link. 2019. Discovery and Ranking of Functional Dependencies. In 35th IEEE International Conference on Data Engineering, ICDE 2019, Macao, China, April 8--11, 2019. 1526--1537.
[46]
Ziheng Wei and Sebastian Link. 2021. Embedded Functional Dependencies and Data-completeness Tailored Database Design. ACM Trans. Database Syst., Vol. 46, 2 (2021), 7:1--7:46.

Cited By

View all
  • (2025)Third and Boyce–Codd normal form for property graphsThe VLDB Journal — The International Journal on Very Large Data Bases10.1007/s00778-025-00902-234:2Online publication date: 1-Mar-2025
  • (2024)Compositional Dependencies and High-Order Relations Formulas at Relational Data ModelsAutomatic Documentation and Mathematical Linguistics10.3103/S000510552470031658:5(320-332)Online publication date: 1-Oct-2024
  • (2024)Mixed Covers of Keys and Functional Dependencies for Maintaining the Integrity of Data under UpdatesProceedings of the VLDB Endowment10.14778/3654621.365462617:7(1578-1590)Online publication date: 30-May-2024
  • Show More Cited By

Index Terms

  1. Composite Object Normal Forms: Parameterizing Boyce-Codd Normal Form by the Number of Minimal Keys

      Recommendations

      Comments

      Information & Contributors

      Information

      Published In

      cover image Proceedings of the ACM on Management of Data
      Proceedings of the ACM on Management of Data  Volume 1, Issue 1
      PACMMOD
      May 2023
      2807 pages
      EISSN:2836-6573
      DOI:10.1145/3603164
      Issue’s Table of Contents
      Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than the author(s) must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected].

      Publisher

      Association for Computing Machinery

      New York, NY, United States

      Publication History

      Published: 30 May 2023
      Published in PACMMOD Volume 1, Issue 1

      Permissions

      Request permissions for this article.

      Author Tags

      1. functional dependency
      2. key
      3. normal form
      4. normalization
      5. query
      6. update

      Qualifiers

      • Research-article

      Contributors

      Other Metrics

      Bibliometrics & Citations

      Bibliometrics

      Article Metrics

      • Downloads (Last 12 months)51
      • Downloads (Last 6 weeks)0
      Reflects downloads up to 17 Feb 2025

      Other Metrics

      Citations

      Cited By

      View all
      • (2025)Third and Boyce–Codd normal form for property graphsThe VLDB Journal — The International Journal on Very Large Data Bases10.1007/s00778-025-00902-234:2Online publication date: 1-Mar-2025
      • (2024)Compositional Dependencies and High-Order Relations Formulas at Relational Data ModelsAutomatic Documentation and Mathematical Linguistics10.3103/S000510552470031658:5(320-332)Online publication date: 1-Oct-2024
      • (2024)Mixed Covers of Keys and Functional Dependencies for Maintaining the Integrity of Data under UpdatesProceedings of the VLDB Endowment10.14778/3654621.365462617:7(1578-1590)Online publication date: 30-May-2024
      • (2024)ETC: Efficient Training of Temporal Graph Neural Networks over Large-Scale Dynamic GraphsProceedings of the VLDB Endowment10.14778/3641204.364121517:5(1060-1072)Online publication date: 2-May-2024
      • (2024)CTuner: Automatic NoSQL Database Tuning with Causal Reinforcement LearningProceedings of the 15th Asia-Pacific Symposium on Internetware10.1145/3671016.3674809(269-278)Online publication date: 24-Jul-2024
      • (2024)SIMPLE: Efficient Temporal Graph Neural Network Training at Scale with Dynamic Data PlacementProceedings of the ACM on Management of Data10.1145/36549772:3(1-25)Online publication date: 30-May-2024
      • (2024)Parallelization of butterfly counting on hierarchical memoryThe VLDB Journal — The International Journal on Very Large Data Bases10.1007/s00778-024-00856-x33:5(1453-1484)Online publication date: 7-Jun-2024
      • (2024)Lero: applying learning-to-rank in query optimizerThe VLDB Journal — The International Journal on Very Large Data Bases10.1007/s00778-024-00850-333:5(1307-1331)Online publication date: 25-Apr-2024
      • (2023)Normalizing Property GraphsProceedings of the VLDB Endowment10.14778/3611479.361150616:11(3031-3043)Online publication date: 1-Jul-2023
      • (2023)A Factor Marginal Effect Analysis Approach and Its Application in E-Commerce Search SystemInternational Journal of Intelligent Systems10.1155/2023/69688542023Online publication date: 1-Jan-2023
      • Show More Cited By

      View Options

      Login options

      Full Access

      View options

      PDF

      View or Download as a PDF file.

      PDF

      eReader

      View online with eReader.

      eReader

      Figures

      Tables

      Media

      Share

      Share

      Share this Publication link

      Share on social media