Panos Vassiliadis, Michail-Romanos Kolozoff, Maria Zerva, Apostolos V. Zarras. Schema evolution and foreign keys: a study on usage, heartbeat of change and relationship of foreign keys to table activity. Computing , 2019
Panos Vassiliadis, Michail-Romanos Kolozoff, Maria Zerva, Apostolos V. Zarras. Schema Evolution and Foreign Keys: Birth, Eviction, Change and Absence. 36th International Conference on Conceptual Modeling (ER 2017), pp. 106-119, Nov. 6th-9th, 2017, Valencia Spain
In this line of research, 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.
The full version is the paper at Computing, whereas the ER'17 paper presents the results only at the schema level, and not the individual tablesPlz., refer to our Schema biographies page for a general overview of our research program.
Panos Vassiliadis, Michail-Romanos Kolozoff, Maria Zerva, Apostolos V. Zarras. Schema Evolution and Foreign Keys: Birth, Eviction, Change and Absence. 36th International Conference on Conceptual Modeling (ER 2017), pp. 106-119, Nov. 6th-9th, 2017, Valencia Spain
[Local copy of the paper at ER 2017 (PDF)]
Panos Vassiliadis, Michail-Romanos Kolozoff, Maria Zerva, Apostolos V. Zarras. Schema evolution and foreign keys: a study on usage, heartbeat of change and relationship of foreign keys to table activity. Computing , 2019
Long version of the ER'17 paper. [Official page by Springer] [Open by Springer (only for online reading)] [Local copy and supplementary material]
The following code and data are presented on-line to allow the reproduction of results by others. We would like to to clearly state that we simply cannot support any requests for the maintenance of the code, or clarifications, explanations etc. Moreover, we do not assume any responsibility for any side effects of the code (although we cannot think of, or have ever encountered, any). You are free to reuse the following code and data for academic purposes, provided you give the appropriate citation:
Panos Vassiliadis, Michail-Romanos Kolozoff, Maria Zerva, Apostolos V. Zarras. Schema Evolution and Foreign Keys: Birth, Eviction, Change and Absence. 36th International Conference on Conceptual Modeling (ER 2017), pp. 106-119, Nov. 6th-9th, 2017, Valencia Spain. Source code, datasets, presentations available at http://www.cs.uoi.gr/~pvassil/publications/2017_ER
(and, yes, academic honesty rules impose that this includes student projects too ;) )
Input: Database history versions (Raw input for extracting transitions)
Code: Source code for Hecate. Java (Eclipse).
Code: Source code for Parmenidian Truth. Java(Eclipse).