Managing structural and behavioral evolution in relational database: Application of Software engineering techniques


Views, stored procedures, triggers are mostly considered as text by DB tools. Consequently, change impact analysis, refactoring and re-architecting implying such entities is badly or not managed at all.


Adopting a Software Engineering approach to manage DB evolution.


Relational databases have been at the core of many information systems for decades and continue to be used in new software development. Many of these databases reflect human or societal activities, for example, processes related to human re- sources, insurances, banks, etc. Reflecting such activities induce frequent evolu- tions of both the software system and the relational database. Relational databases do not only store and ensure data consistency, they can also define behavior taking the form of views, stored procedures, triggers, etc.

Implementing behavior directly inside a database has the advantage to prevent code duplication when multiple programs using it perform similar tasks. However, the evolution of such database becomes complex and few approaches in the lit- erature address this problem. Most of the literature addressing relational database evolution focus either on the evolution of the database schema or on its co-evolution with software interacting with it. Approaches to reverse-engineer and evolve both structural and behavioral entities of relational databases are missing.

In this thesis, we address this gap in the literature with four main contributions: (i) we report our observation of a relational database evolution made by an architect and identify problems from our observations; (ii) we propose a meta-model repre- senting both structural and behavioral parts of a database and simplifying depen- dencies analysis; (iii) we propose a tool to find quality issues in a database schema; and (iv) we propose a semi-automatic approach to evolve a relational database (its structural and its behavioral parts) based on recommendations that can be compiled into a SQL script.
The results of the research reported in this thesis provide building bricks for the development of behavior-aware integrated development environment for relational databases.

Open-source tools developed

  • build

    A tool to manage connections to databases.

  • thumbs_up_down

    A tool to check that a database schema respects a list of predefined properties.

  • build

    A tool to build a model of the database under analysis using the meta-data tables of PostgreSQL

  • search
    PostgreSQL parser

    A parser for PostgreSQL allowing to build an AST to perform static analysis.

  • build
    SQL Meta-model

    A meta-model for relational databases.