Skip to main content
SpringerLink
Log in

Schema evolution and foreign keys: a study on usage, heartbeat of change and relationship of foreign keys to table activity

  • Published:
Computing Aims and scope Submit manuscript

Abstract

In this paper, we study the evolution of foreign keys in the context of schema evolution for relational databases. Specifically, we study the schema histories of a six free, open-source databases that contain foreign keys. Our findings verify previous results that schemata grow in the long run in terms of tables. To our surprise, we discovered that foreign keys appear to be fairly scarce in the projects that we have studied and they do not necessarily grow in sync with table growth. In fact, we have observed different “cultures” for the handling of foreign keys, ranging from treating foreign keys as an indispensable part of the schema, in full sync with the growth of tables, to the unexpected extreme of treating foreign keys as an optional add-on that twice resulted in their full removal from the schema of the database. Apart from the behavior of entire schemata, we have also studied the behavior of individual tables. We model the schema of any version of the history as a graph, with tables being nodes and foreign keys being edges. The union of these graphs is called the diachronic graph of the schema and contains all the tables and foreign keys that ever appeared in the schema history. The study of the total degree of tables at the diachronic graph, reveals several patterns. The population of tables with total degree in the range of [0–2] includes almost all the tables that were eventually removed from the schema, as well as the vast majority of survivor tables. These low-degree tables (especially the dead ones) tend to be mostly with zero or very few internal updates in their entire history. At the same time, the few tables with degree higher than 2 are typically born very early in the life of the schema, overwhelmingly survivors, and, unusually active, typically undergoing medium or high update activity.

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

Access this article

Log in via an institution

Price excludes VAT (USA)
Tax calculation will be finalised during checkout.

Instant access to the full article PDF.

Institutional subscriptions

Fig. 1
Fig. 2
Fig. 3
Fig. 4
Fig. 5
Fig. 6
Fig. 7
Fig. 8
Fig. 9
Fig. 10
Fig. 11

Similar content being viewed by others

Notes

  1. All our data sets and software are openly available at our group’s Github site (https://github.com/DAINTINESS-Group).

  2. https://github.com/DAINTINESS-Group/ParmenidianTruth.

References

  1. Boslaugh S (2012) Statistics in a nutshell—a desktop quick reference. O’Reilly, Sebastopol

    Google Scholar 

  2. Cleve A, Brogneaux A, Hainaut J (2010) A conceptual approach to database applications evolution. In: 29th International conference on conceptual modeling (ER 2010), Vancouver, BC, Canada, November 1–4, 2010, pp 132–145

    Chapter  Google Scholar 

  3. Cleve A, Gobert M, Meurice L, Maes J, Weber JH (2015) Understanding database schema evolution: a case study. Sci Comput Program 97:113–121

    Article  Google Scholar 

  4. Curino C, Moon HJ, Tanca L, Zaniolo C (2008) Schema evolution in wikipedia: toward a web information system benchmark. In: Proceedings of ICEIS 2008, Citeseer

  5. Curino C, Moon HJ, Deutsch A, Zaniolo C (2013) Automating the database schema evolution process. VLDB J 22(1):73–98

    Article  Google Scholar 

  6. Decan A, Goeminne M, Mens T (2015) On the interaction of relational database access technologies in open source java projects. In: Post-proceedings of the 8th seminar on advanced techniques and tools for software evolution, Mons, Belgium, July 6–8, 2015, pp 26–35

  7. Decan A, Goeminne M, Mens T (2017) On the interaction of relational database access technologies in open source java projects. CoRR abs/1701.00416. arXiv:1701.00416

  8. García-García J, Ordonez C (2010) Extended aggregations for databases with referential integrity issues. Data Knowl Eng 69(1):73–95

    Article  Google Scholar 

  9. Herrmann K, Voigt H, Behrend A, Lehner W (2015) Codel: a relationally complete language for database evolution. In: 19th East European conference on advances in databases and information systems (ADBIS 2015), Poitiers, France, September 8–11, 2015, pp 63–76

    Chapter  Google Scholar 

  10. Kagdi HH, Collard ML, Maletic JI (2007) A survey and taxonomy of approaches for mining software repositories in the context of software evolution. J Softw Maint 19(2):77–131

    Article  Google Scholar 

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

    Article  Google Scholar 

  12. Lin DY, Neamtiu I (2009) Collateral evolution of applications and databases. In: Proceedings of the joint international and annual ERCIM workshops on principles of software evolution (IWPSE) and software evolution (Evol) workshops, IWPSE-Evol ’09, pp 31–40

  13. Manousis P, Vassiliadis P, Papastefanatos G (2013) Automating the adaptation of evolving data-intensive ecosystems. In: Proceedings of 32th international conference on conceptual modeling (ER 2013), Hong-Kong, China, November 11–13, 2013, pp 182–196

    Chapter  Google Scholar 

  14. Meurice L, Ruiz FJB, Weber JH, Cleve A (2014) Establishing referential integrity in legacy information systems—reality bites! In: 30th IEEE international conference on software maintenance and evolution, Victoria, BC, Canada, September 29–October 3, 2014, pp 461–465

  15. Meurice L, Nagy C, Cleve A (2016) Static analysis of dynamic database usage in java systems. In: Proceedings of 28th international conference on advanced information systems engineering , CAiSE 2016, Ljubljana, Slovenia, June 13–17, 2016, pp 491–506

  16. Papenbrock T, Ehrlich J, Marten J, Neubert T, Rudolph J, Schönberg M, Zwiener J, Naumann F (2015) Functional dependency discovery: an experimental evaluation of seven algorithms. PVLDB 8(10):1082–1093

    Google Scholar 

  17. Qiu D, Li B, Su Z (2013) An empirical analysis of the co-evolution of schema and code in database applications. In: Proceedings of the 2013 9th joint meeting on foundations of software engineering, ESEC/FSE 2013, pp 125–135

  18. Sjøberg D (1993) Quantifying schema evolution. Inf Softw Technol 35(1):35–44

    Article  Google Scholar 

  19. Skoulis I, Vassiliadis P, Zarras A (2014) Open-source databases: within, outside, or beyond Lehman’s laws of software evolution? In: 26th International conference on advanced information systems engineering (CAiSE 2014), Thessaloniki, Greece, June 16–20, 2014

  20. Skoulis I, Vassiliadis P, Zarras AV (2015) Growing up with stability: How open-source relational databases evolve. Inf Syst 53:363–385

    Article  Google Scholar 

  21. Teorey TJ, Yang D, Fry JP (1986) A logical design methodology for relational databases using the extended entity-relationship model. ACM Comput Surv 18(2):197–222

    Article  Google Scholar 

  22. Vassiliadis P (2017) Schema evolution and gravitation to rigidity: a tale of calmness in the lives of structured data. In: Proceedings of 7th international conference model and data engineering—MEDI 2017, Barcelona, Spain, October 4–6, 2017, pp 18–23

    Chapter  Google Scholar 

  23. Vassiliadis P, Zarras AV (2017) Survival in schema evolution: putting the lives of survivor and dead tables in counterpoint. In: 29th International conference on advanced information systems engineering (CAiSE 2017), Essen, Germany, June 12–16, 2017, pp 333–347

    Chapter  Google Scholar 

  24. Vassiliadis P, Zarras AV, Skoulis I (2015) How is life for a table in an evolving relational schema? Birth, death and everything in between. In: Proceedings of 34th international conference on conceptual modeling (ER 2015), Stockholm, Sweden, October 19–22, 2015, pp 453–466

    Chapter  Google Scholar 

  25. Vassiliadis P, Kolozoff M, Zerva M, Zarras AV (2017a) Schema evolution and foreign keys: birth, eviction, change and absence. In: Proceedings of 36th international conference on conceptual modeling (ER 2017), Valencia, Spain, November 6–9, 2017, pp 106–119

    Chapter  Google Scholar 

  26. Vassiliadis P, Zarras AV, Skoulis I (2017b) Gravitating to rigidity: patterns of schema evolution—and its absence—in the lives of tables. Inf Syst 63:24–46

    Article  Google Scholar 

  27. Wu S, Neamtiu I (2011) Schema evolution analysis for embedded databases. In: Proceedings of the 2011 IEEE 27th international conference on data engineering workshops, ICDEW ’11, pp 151–156

  28. Zhang M, Hadjieleftheriou M, Ooi BC, Procopiuc CM, Srivastava D (2010) On multi-column foreign key discovery. PVLDB 3(1):805–814

    Google Scholar 

Download references

Acknowledgements

We thank the reviewers of both the current and previous papers for their comments that helped us clarify better key points of interest.

Author information

Authors and Affiliations

  1. Department of Computer Science and Engineering, University of Ioannina, Ioannina, Greece

    Panos Vassiliadis & Apostolos V. Zarras

  2. Advantage FSE, Athens, Greece

    Michail-Romanos Kolozoff

  3. P&I AG, Ioannina, Greece

    Maria Zerva

Authors
  1. Panos Vassiliadis
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Michail-Romanos Kolozoff
    View author publications

    You can also search for this author in PubMed Google Scholar

  3. Maria Zerva
    View author publications

    You can also search for this author in PubMed Google Scholar

  4. Apostolos V. Zarras
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to Panos Vassiliadis.

Additional information

Michail-Romanos Kolozoff and Maria Zerva: work done while in the University of Ioannina.

Electronic supplementary material

Below is the link to the electronic supplementary material.

Supplementary material 1 (pdf 1243 KB)

Rights and permissions

Reprints and permissions

About this article

Check for updates. Verify currency and authenticity via CrossMark

Cite this article

Vassiliadis, P., Kolozoff, MR., Zerva, M. et al. Schema evolution and foreign keys: a study on usage, heartbeat of change and relationship of foreign keys to table activity. Computing 101, 1431–1456 (2019). https://doi.org/10.1007/s00607-019-00702-x

Download citation

  • Received:

  • Accepted:

  • Published:

  • Issue Date:

  • DOI: https://doi.org/10.1007/s00607-019-00702-x

Keywords

Search

Navigation