Abstract Supporting database schema evolution represents a long-standing challenge of practical and theoretical importance for modern information systems. In this paper we describe techniques and systems for automating the critical tasks of migrating the database and rewriting the legacy applications. In addition to labor saving, the benefits delivered by these advances are many, and include reliable prediction of outcome, minimization of down-time, system-produced documentation, and support for archiving, historical queries and provenance. The PRISM/PRISM++ system delivers these benefits, by solving the difficult problem of automating the migration of databases and the rewriting of queries and updates. In this paper we present the PRISM/PRISM++ system, and the novel technology that made it possible. In particular we focus on the difficult, and previously unsolved problem of supporting legacy queries and updates under schema and integrity constraints evolution.
The PRISM/PRISM++ approach consists in providing the users with a set of SQL-based Schema Modification Operators (SMOs) which describe how the tables in the old schema are modified into those in the new schema. In order to support updates, SMOs are extended with Constraints Modification Operators (ICMOs). By using recent results on schema mapping, the paper (i) characterizes the impact on integrity constraints of structural schema changes, (ii) devises representations that enable the rewriting of updates, and (iii) develop a unified approach for query and update rewriting under constraints.
We complement the system with two novel tools: the first automatically collects and provides statistics on schema evolution histories, whereas the second derives equivalent sequences of SMOs from the migration scripts that were used for schema upgrades. These tools were used to produce an extensive testbed containing 15 evolution histories of scientific databases and web information systems, providing over 100 years of aggregate evolution histories and almost 2000 schema evolution steps.
To appear in VLDB Journal “Best of VLDB 2011”