Skip to main content
SpringerLink
Log in

Schema Optimisation Instead of (Local) Normalisation

  • Conference paper
  • First Online:
Foundations of Information and Knowledge Systems (FoIKS 2020)

Part of the book series: Lecture Notes in Computer Science ((LNISA,volume 12012))

Abstract

Classical normalisation theory has a number of lacunas although it is commonly and widely accepted and it is the basis for database theory since the 80ies. Most textbooks and monographs still follow this approach despite the good number of open problems. Today, modern object-relational DBMS offer far better capabilities than the systems that have been built in the past based on the strict relational paradigm. Constraint maintenance has been oriented on transformation of structures to structures that are free of functional dependencies beside key constraints. The maintenance of coherence constraints such as two-type inclusion constraints has been neglected although this maintenance might be the most expensive one. In reality normalisation is local optimisation that exclusively considers functional dependency maintenance.

We thus need a different normalisation approach. This paper develops an approach towards optimisation of schemata and global normalisation. This approach results in a denormalisation and object-relational database schemata.

This is a preview of subscription content, log in via an institution to check access.

Access this chapter

Log in via an institution
Chapter
USD 29.95
Price excludes VAT (USA)
  • Available as PDF
  • Read on any device
  • Instant download
  • Own it forever
eBook
USD 39.99
Price excludes VAT (USA)
  • Available as EPUB and PDF
  • Read on any device
  • Instant download
  • Own it forever
Softcover Book
USD 54.99
Price excludes VAT (USA)
  • Compact, lightweight edition
  • Dispatched in 3 to 5 business days
  • Free shipping worldwide - see info

Tax calculation will be finalised at checkout

Purchases are for personal use only

Institutional subscriptions

Notes

  1. 1.

    This mechanism has been already used for fragmentation techniques for distributed databases. A similar approach has been proposed by Date [10] for handling NULL-polluted classes by rigid horizontal normalisation into NULL-free fragments (see also [25, 40]).

  2. 2.

    We restrict the citations to the most essential ones for this paper and restrain to give a full survey of the research.

  3. 3.

    We forbear from postulating these observations as theorems. They are rather simple and easy to check statements.

  4. 4.

    We note that a database schema is typically not a database model. The schema must be enhanced by views to become a database model [36]. Since we have to use anyway views then we should better extensively use horizontal decomposition beside vertical decomposition.

  5. 5.

    If we require that all inclusion dependencies are referential integrity constraints then we need 12 types for normalisation that results in a foreign-key-faithful decomposition: \(R_1[\underline{A}], R_1[\underline{I}], R_1[\underline{A},I], R_1[\underline{A},G], R_2[\underline{F}], R_2[A,\underline{I,F}], R_3[\underline{B}], R_3[\underline{A,B}], R_3[\underline{A,B},D], \) \( R_4[\underline{D}], R_4[\underline{G}], R_4[\underline{D},F,G]\) where the key of each new type \(R_i[X] := \pi _X[R_i]\) is underlined.

  6. 6.

    We know so far only less than a handful books that do not require such.

  7. 7.

    Many constraints can be omitted since integrity is also often managed through proper interfacing and exchange procedures without a chance for inconsistency as long as the data modification is exclusively based on interface or exchange view data. The development of a theory for this approach is one of the lacunas of database theory.

  8. 8.

    We avoid the exponential size trap for sets of functional dependencies with this toleration of incompleteness of constraint sets. We consider only essential ones and completely or partially neglect others. This approach can be extended to a theory of robust normalisation.

  9. 9.

    The validity of pairwise inclusion constraints is also neglected in this case.

  10. 10.

    https://www.dama.org.

  11. 11.

    With 21 open problems from which 13 are not yet solved.

References

  1. Abiteboul, S., Hull, R., Vianu, V.: Foundations of Databases. Addison-Wesley, Reading (1995)

    MATH  Google Scholar 

  2. Beeri, C., Thalheim, B.: Identification as a primitive of database models. In Proceedings of the FoMLaDO 1998, pp. 19–36. Kluwer, London (1999)

    Chapter  Google Scholar 

  3. Benczúr, A.A., Kiss, A., Markus, T.: On a general class of data dependencies in the relational model and its implication problems. Comput. Math. Appl. 21(1), 1–11 (1991)

    Article  Google Scholar 

  4. Bick, M.: Denormalisierung. Master’s thesis, CAU Kiel, Department of Computer Science (2015)

    Google Scholar 

  5. Biskup, J.: Foundations of Information Systems. Vieweg, Wiesbaden (1995). (in German)

    MATH  Google Scholar 

  6. Buxton, S., et al.: Database Design - Know It All. Morgan Kaufmann, Burlington (2008)

    Google Scholar 

  7. Celko, J.: Joe Celko’s SQL for Smarties - Advanced SQL Programming. Morgan Kaufmann, San Francisco (1995)

    MATH  Google Scholar 

  8. Celko, J.: Joe Celko’s Data and Databases: Concepts in Practice. Morgan Kaufmann, Burlington (1999)

    Google Scholar 

  9. Codd, E.F.: The Relational Model for Database Management (Version 2). Addison-Wesley, Reading (1991)

    MATH  Google Scholar 

  10. Date, C.J.: Database Design and Relational Theory - Normal Forms and All That Jazz. O’Reilly, Sebastopol (2012)

    Google Scholar 

  11. Date, C.J.: Go Faster - The TransRelational Approach to DBMS Implementation. C.J. Date & Ventus Publishing ApS, Frederiksberg (2011)

    Google Scholar 

  12. Demetrovics, J., Molnar, A., Thalheim, B.: Graphical and spreadsheet reasoning for sets of functional dependencies. In: Proceedings of the ER 2004, LNCS, vol. 3255, pp. 54–66 (2004)

    Google Scholar 

  13. Demetrovics, J., Molnar, A., Thalheim, B.: Graphical and spreadsheet reasoning for sets of functional dependencies. Technical Report 0402, Kiel University, Computer Science Institute (2004). http://www.informatik.uni-kiel.de/reports/2004/0402.html

  14. Kiss, A., Markus, T.: Functional and inclusion dependencies and their implication problems. In: 10th International Seminar on DBMS, Cedzyna, Poland, pp. 31–38 (1987)

    Google Scholar 

  15. Klettke, M., Thalheim, B.: Evolution and migration of information systems. In: Embley, D., Thalheim, B. (eds.) The Handbook of Conceptual Modeling: Its Usage and Its Challenges, pp. 381–420. Springer, Berlin (2011). https://doi.org/10.1007/978-3-642-15865-0_12

    Chapter  Google Scholar 

  16. Koehler, H.: Autonomous sets – a method for hypergraph decomposition with applications in database theory. In: Hartmann, S., Kern-Isberner, G. (eds.) FoIKS 2008. LNCS, vol. 4932, pp. 78–95. Springer, Heidelberg (2008). https://doi.org/10.1007/978-3-540-77684-0_8

    Chapter  Google Scholar 

  17. Köhler, H.: Autonomous sets for the hypergraph of all canonical covers. Ann. Math. Artif. Intell. 63(3–4), 257–285 (2011)

    Article  MathSciNet  Google Scholar 

  18. Köhler, H., Link, S.: SQL schema design: foundations, normal forms, and normalization. Inf. Syst. 76, 88–113 (2018)

    Article  Google Scholar 

  19. Leonard, M.: Database Design Theory. MacMillan, Houndsmills (1992)

    Book  Google Scholar 

  20. Lightstone, S., Teorey, T., Nadeau, T.: Physical Database Design. Morgan Kaufmann, Burlington (2007)

    MATH  Google Scholar 

  21. Makowsky, J.A., Ravve, E.V.: Dependency preserving refinements and the fundamental problem of database design. DKE 24(3), 277–312 (1998). Special Issue: ER 1996 (ed. B. Thalheim)

    Article  Google Scholar 

  22. Mannila, H., Räihä, K.-J.: The Design of Relational Databases. Addison-Wesley, Wokingham (1992)

    MATH  Google Scholar 

  23. Paredaens, J., De Bra, P., Gyssens, M., Van Gucht, D.: The Structure of the Relational Database Model. Springer, Berlin (1989). https://doi.org/10.1007/978-3-642-69956-6

    Book  MATH  Google Scholar 

  24. Popkov, G.P., Popkov, V.K.: A system of distributed data processing. Vestnik Buryatskogo Gosudarstvennogo Universiteta 9, 174–181 (2013). (in Russian)

    Google Scholar 

  25. Schewe, K.-D., Thalheim, B.: NULL value algebras and logics. In: Information Modelling and Knowledge Bases, vol. XXII, pp. 354–367. IOS Press (2011)

    Google Scholar 

  26. Shasha, D.E., Bonnet, P.: Database Tuning - Principles, Experiments, and Troubleshooting Techniques. Elsevier, Amsterdam (2002)

    Google Scholar 

  27. Simsion, G., Witt, G.C.: Data Modeling Essentials. Morgan Kaufmann, San Francisco (2005)

    MATH  Google Scholar 

  28. Sörensen, O., Thalheim, B.: Semantics and pragmatics of integrity constraints. In: Schewe, K.-D., Thalheim, B. (eds.) SDKB 2011. LNCS, vol. 7693, pp. 1–17. Springer, Heidelberg (2013). https://doi.org/10.1007/978-3-642-36008-4_1

    Chapter  MATH  Google Scholar 

  29. Steeg, M.: RADD/raddstar - a rule-based database schema compiler, evaluator, and optimizer. Ph.D. thesis, BTU Cottbus, Computer Science Institute, Cottbus, October 2000

    Google Scholar 

  30. Thalheim, B.: Dependencies in Relational Databases. Teubner, Leipzig (1991)

    Book  Google Scholar 

  31. Thalheim, B.: Entity-Relationship Modeling - Foundations of Database Technology. Springer, Berlin (2000). https://doi.org/10.1007/978-3-662-04058-4

    Book  MATH  Google Scholar 

  32. 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). https://doi.org/10.1007/978-3-540-39648-2_29

    Chapter  Google Scholar 

  33. Thalheim, B.: Open problems of information systems research and technology. In: Kobyliński, A., Sobczak, A. (eds.) BIR 2013. LNBIP, vol. 158, pp. 10–18. Springer, Heidelberg (2013). https://doi.org/10.1007/978-3-642-40823-6_2

    Chapter  Google Scholar 

  34. Thalheim, B.: Conceptual models and their foundations. In: Schewe, K.-D., Singh, N.K. (eds.) MEDI 2019. LNCS, vol. 11815, pp. 123–139. Springer, Cham (2019). https://doi.org/10.1007/978-3-030-32065-2_9

    Chapter  Google Scholar 

  35. Thalheim, B.: Semiotics in databases. In: Schewe, K.-D., Singh, N.K. (eds.) MEDI 2019. LNCS, vol. 11815, pp. 3–19. Springer, Cham (2019). https://doi.org/10.1007/978-3-030-32065-2_1

    Chapter  Google Scholar 

  36. Thalheim, B., Tropmann-Frick, M.: The conception of the conceptual database model. In: ER 2015. LNCS, vol. 9381, pp. 603–611. Springer, Berlin (2015)

    Google Scholar 

  37. Tropmann, M., Thalheim, B.: Performance forecasting for performance critical huge databases. In: Proceedings of the EJC 2010, Jyväskylä, pp. 214–233 (2010)

    Google Scholar 

  38. Wang, Q., Thalheim, B.: Data migration: a theoretical perspective. DKE 87, 260–278 (2013)

    Article  Google Scholar 

  39. Webster, B.F.: Pitfalls of Object-Oriented Development: A Guide for the Wary and Entusiastic. M&T Books, New York (1995)

    Google Scholar 

  40. Wei, Z., Link, S.: Embedded functional dependencies and data-completeness tailored database design. PVLDB 12(11), 1458–1470 (2019)

    Google Scholar 

  41. Yang, C.-C.: Relational Databases. Prentice-Hall, Englewood Cliffs (1986)

    Google Scholar 

Download references

Author information

Authors and Affiliations

  1. Department of Computer Science, Christian-Albrechts University at Kiel, 24098, Kiel, Germany

    Bernhard Thalheim

Authors
  1. Bernhard Thalheim
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to Bernhard Thalheim .

Editor information

Editors and Affiliations

  1. CNRS, University of Toulouse, Toulouse, France

    Andreas Herzig

  2. University of Helsinki, Helsinki, Finland

    Juha Kontinen

Rights and permissions

Reprints and permissions

Copyright information

© 2020 Springer Nature Switzerland AG

About this paper

Check for updates. Verify currency and authenticity via CrossMark

Cite this paper

Thalheim, B. (2020). Schema Optimisation Instead of (Local) Normalisation. In: Herzig, A., Kontinen, J. (eds) Foundations of Information and Knowledge Systems. FoIKS 2020. Lecture Notes in Computer Science(), vol 12012. Springer, Cham. https://doi.org/10.1007/978-3-030-39951-1_17

Download citation

  • DOI: https://doi.org/10.1007/978-3-030-39951-1_17

  • Published:

  • Publisher Name: Springer, Cham

  • Print ISBN: 978-3-030-39950-4

  • Online ISBN: 978-3-030-39951-1

  • eBook Packages: Computer ScienceComputer Science (R0)

Publish with us

Policies and ethics

Search

Navigation