Foundations for a Fourth Normal Form over SQL-Like Databases

  • Flavio Ferrarotti
  • Sven Hartmann
  • Henning Köhler
  • Sebastian Link
  • Millist W. Vincent
Part of the Lecture Notes in Computer Science book series (LNCS, volume 7260)


In the relational model of data the Fourth Normal Form condition guarantees the elimination of data redundancy in terms of functional and multivalued dependencies. For efficient means of data processing the industry standard SQL permits partial data and duplicate rows of data to occur in database systems. Here, the combined class of uniqueness constraints, functional and multivalued dependencies is more expressive than the class of functional and multivalued dependencies itself. Consequently, the Fourth Normal Form condition is not suitable for SQL databases. We characterize the associated implication problem of the combined class in the presence of NOT NULL constraints axiomatically, algorithmically and logically. Based on these results we are able to establish a suitable Fourth Normal Form condition for SQL.


Functional Dependency Inference Rule Uniqueness Constraint Relation Schema Propositional Variable 
These keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.


Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.


  1. 1.
    Arenas, M., Libkin, L.: An information-theoretic approach to normal forms for relational and XML data. J. ACM 52(2), 246–283 (2005)CrossRefzbMATHMathSciNetGoogle Scholar
  2. 2.
    Atzeni, P., Morfuni, N.: Functional dependencies and constraints on null values in database relations. Information and Control 70(1), 1–31 (1986)CrossRefzbMATHMathSciNetGoogle Scholar
  3. 3.
    Beeri, C., Fagin, R., Howard, J.H.: A complete axiomatization for fds and mvds in database relations. In: SIGMOD, pp. 47–61. ACM (1977)Google Scholar
  4. 4.
    Codd, E.F.: A relational model of data for large shared data banks. Commun. ACM 13(6), 377–387 (1970)CrossRefzbMATHGoogle Scholar
  5. 5.
    Date, C., Darwen, H.: A guide to the SQL standard. Addison-Wesley Professional, Reading (1997)Google Scholar
  6. 6.
    Demetrovics, J., Katona, G., Miklós, D., Seleznjev, O., Thalheim, B.: Asymptotic properties of keys and functional dependencies in random databases. Theor. Comput. Sci. 190(2), 151–166 (1998)CrossRefzbMATHMathSciNetGoogle Scholar
  7. 7.
    Demetrovics, J., Katona, G.O.H., Miklós, D., Thalheim, B.: On the Number of Independent Functional Dependencies. In: Dix, J., Hegner, S.J. (eds.) FoIKS 2006. LNCS, vol. 3861, pp. 83–91. Springer, Heidelberg (2006)CrossRefGoogle Scholar
  8. 8.
    Demetrovics, J., Molnár, A., Thalheim, B.: Graphical Reasoning for Sets of Functional Dependencies. In: Atzeni, P., Chu, W., Lu, H., Zhou, S., Ling, T.-W. (eds.) ER 2004. LNCS, vol. 3288, pp. 166–179. Springer, Heidelberg (2004)CrossRefGoogle Scholar
  9. 9.
    Demetrovics, J., Molnár, A., Thalheim, B.: Relationship Design Using Spreadsheet Reasoning for Sets of Functional Dependencies. In: Manolopoulos, Y., Pokorný, J., Sellis, T.K. (eds.) ADBIS 2006. LNCS, vol. 4152, pp. 108–123. Springer, Heidelberg (2006)CrossRefGoogle Scholar
  10. 10.
    Fagin, R.: Multivalued dependencies and a new normal form for relational databases. ACM Trans. Database Syst. 2(3), 262–278 (1977)CrossRefGoogle Scholar
  11. 11.
    Galil, Z.: An almost linear-time algorithm for computing a dependency basis in a relational database. J. ACM 29(1), 96–102 (1982)CrossRefzbMATHMathSciNetGoogle Scholar
  12. 12.
    Hartmann, S., Kirchberg, M., Link, S.: Design by example for SQL table definitions with functional dependencies. The VLDB Journal (2011), doi:10.1007/s00778-011-0239-5Google Scholar
  13. 13.
    Hartmann, S., Leck, U., Link, S.: On Codd families of keys over incomplete relations. Comput. J. 54(7), 1166–1180 (2011)CrossRefGoogle Scholar
  14. 14.
    Hartmann, S., Link, S.: Efficient reasoning about a robust XML key fragment. ACM Trans. Database Syst. 34(2) (2009)Google Scholar
  15. 15.
    Hartmann, S., Link, S.: Numerical constraints on XML data. Inf. Comput. 208(5), 521–544 (2010)CrossRefzbMATHMathSciNetGoogle Scholar
  16. 16.
    Hartmann, S., Link, S.: When data dependencies over SQL tables meet the Logics of Paradox and \(\mathcal{S}\)-3. In: PODS, pp. 317–326 (2010)Google Scholar
  17. 17.
    Hartmann, S., Link, S., Schewe, K.-D.: Weak Functional Dependencies in Higher-Order Datamodels. In: Seipel, D., Turull-Torres, J.M. (eds.) FoIKS 2004. LNCS, vol. 2942, pp. 116–133. Springer, Heidelberg (2004)CrossRefGoogle Scholar
  18. 18.
    Imielinski, T., Lipski Jr., W.: Incomplete information in relational databases. J. ACM 31(4), 761–791 (1984)CrossRefzbMATHMathSciNetGoogle Scholar
  19. 19.
    Köhler, H., Link, S.: Armstrong axioms and Boyce-Codd-Heath normal form under bag semantics. Inf. Process. Lett. 110(16), 717–724 (2010)CrossRefzbMATHMathSciNetGoogle Scholar
  20. 20.
    Lien, E.: On the equivalence of database models. J. ACM 29(2), 333–362 (1982)CrossRefzbMATHGoogle Scholar
  21. 21.
    Link, S.: Consistency Enforcement in Databases. In: Bertossi, L., Katona, G.O.H., Schewe, K.-D., Thalheim, B. (eds.) Semantics in Databases 2001. LNCS, vol. 2582, pp. 139–159. Springer, Heidelberg (2003)CrossRefGoogle Scholar
  22. 22.
    Link, S., Schewe, K.-D.: An arithmetic theory of consistency enforcement. Acta Cybern. 15(3), 379–416 (2002)zbMATHMathSciNetGoogle Scholar
  23. 23.
    Paredaens, J., De Bra, P., Gyssens, M., Van Gucht, D.: The Structure of the Relational Database Model. Springer, Heidelberg (1989)CrossRefzbMATHGoogle Scholar
  24. 24.
    Sagiv, Y., Delobel, C., Parker Jr., D.S., Fagin, R.: An equivalence between relational database dependencies and a fragment of propositional logic. J. ACM 28(3), 435–453 (1981)CrossRefzbMATHMathSciNetGoogle Scholar
  25. 25.
    Schaerf, M., Cadoli, M.: Tractable reasoning via approximation. Artif. Intell. 74, 249–310 (1995)CrossRefzbMATHMathSciNetGoogle Scholar
  26. 26.
    Schewe, K.-D., Thalheim, B.: Limitations of rule triggering systems for integrity maintenance in the context of transition specifications. Acta Cybern. 13(3), 277–304 (1998)zbMATHMathSciNetGoogle Scholar
  27. 27.
    Schewe, K.-D., Thalheim, B.: Towards a theory of consistency enforcement. Acta Inf. 36(2), 97–141 (1999)CrossRefzbMATHMathSciNetGoogle Scholar
  28. 28.
    Selesnjev, O., Thalheim, B.: On the numbers of shortes keys in relational databases on non-uniform domains. Acta Cybern. 8, 267–271 (1988)Google Scholar
  29. 29.
    Seleznjev, O., Thalheim, B.: Behavior of keys in random databases. In: SCCC, pp. 171–183 (1998)Google Scholar
  30. 30.
    Thalheim, B.: A compelte axiomatization for full join dependencies in relations. Bulletin of the EATCS 24, 109–114 (1984)Google Scholar
  31. 31.
    Thalheim, B.: Deductive normal forms of relations. In: Mathematical Methods of Specification and Synthesis of Software Systems, pp. 226–230 (1985)Google Scholar
  32. 32.
    Thalheim, B.: Design Tools for Large Relational Database Systems. In: Biskup, J., Demetrovics, J., Paredaens, J., Thalheim, B. (eds.) MFDBS 1987. LNCS, vol. 305, pp. 210–224. Springer, Heidelberg (1988)CrossRefGoogle Scholar
  33. 33.
    Thalheim, B.: Open Problems in Database Theory. In: Biskup, J., Demetrovics, J., Paredaens, J., Thalheim, B. (eds.) MFDBS 1987. LNCS, vol. 305, pp. 241–247. Springer, Heidelberg (1988)CrossRefGoogle Scholar
  34. 34.
    Thalheim, B.: The Higher-Order Entity-Relationship model and (DB)2. In: Demetrovics, J., Thalheim, B. (eds.) MFDBS 1989. LNCS, vol. 364, pp. 382–397. Springer, Heidelberg (1989)CrossRefGoogle Scholar
  35. 35.
    Thalheim, B.: On semantic issues connected with keys in relational databases permitting null values. Elektronische Informationsverarbeitung und Kybernetik 25(1-2), 11–20 (1989)MathSciNetGoogle Scholar
  36. 36.
    Thalheim, B.: Dependencies in relational databases. Teubner (1991)Google Scholar
  37. 37.
    Thalheim, B.: Fundamentals of Cardinality Constraints. In: Pernul, G., Tjoa, A.M. (eds.) ER 1992. LNCS, vol. 645, pp. 7–23. Springer, Heidelberg (1992)CrossRefGoogle Scholar
  38. 38.
    Thalheim, B.: The number of keys in relational and nested relational databases. Discrete Applied Mathematics 40(2) (1992)Google Scholar
  39. 39.
    Thalheim, B.: An overview on database theory. Datenbank Rundbrief 10, 2–13 (1992)Google Scholar
  40. 40.
    Thalheim, B.: Database design strategies. In: CISM, pp. 267–285 (1993)Google Scholar
  41. 41.
    Thalheim, B.: Foundations of Entity - Relationship Modeling. Ann. Math. Artif. Intell. 7(1-4), 197–256 (1993)CrossRefzbMATHMathSciNetGoogle Scholar
  42. 42.
    Thalheim, B.: Entity-Relationship modeling. Springer, Heidelberg (2000)CrossRefzbMATHGoogle Scholar
  43. 43.
    Thalheim, B.: Conceptual Treatment of Multivalued Dependencies. In: Song, I.-Y., Liddle, S.W., Ling, T.-W., Scheuermann, P. (eds.) ER 2003. LNCS, vol. 2813, pp. 363–375. Springer, Heidelberg (2003)CrossRefGoogle Scholar
  44. 44.
    Thalheim, B.: Component development and construction for database design. Data Knowl. Eng. 54(1), 77–95 (2005)CrossRefGoogle Scholar
  45. 45.
    Vincent, M.: Semantic foundation of 4NF in relational database design. Acta Inf. 36, 1–41 (1999)CrossRefMathSciNetGoogle Scholar
  46. 46.
    Vincent, M., Liu, J., Liu, C.: Strong FDs and their application to normal forms in XML. ACM Trans. Database Syst. 29(3), 445–462 (2004)CrossRefGoogle Scholar
  47. 47.
    Zaniolo, C.: Database relations with null values. J. Comput. Syst. Sci. 28(1), 142–166 (1984)CrossRefzbMATHMathSciNetGoogle Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 2012

Authors and Affiliations

  • Flavio Ferrarotti
    • 1
  • Sven Hartmann
    • 2
  • Henning Köhler
    • 3
  • Sebastian Link
    • 4
  • Millist W. Vincent
    • 5
  1. 1.School of Information ManagementVictoria University of WellingtonNew Zealand
  2. 2.Institut für InformatikTechnische Universität ClausthalGermany
  3. 3.N-Squared SoftwarePalmerston NorthNew Zealand
  4. 4.Department of Computer ScienceUniversity of AucklandNew Zealand
  5. 5.School of Computer and Information ScienceUniversity of South AustraliaAustralia

Personalised recommendations