Dynamic program analysis for database reverse engineering

The maintenance and evolution of data-intensive systems should ideally rely on a complete and accurate database documentation. Unfortunately, this documentation is often missing, or, at best, outdated. Database redocumentation, a process also known as database reverse engineering, then comes to the rescue. This process typically involves the elicitation of implicit schema constructs, that is, data structures and constraints that have been incompletely translated into the operational database schema. In this context, the SQL statements executed by the programs may be a particularly rich source of information. SQL APIs come in two variants, namely static and dynamic. The latter is intensively used in objectoriented and web applications, notably through ODBC and JDBC APIs. While the static analysis of SQL queries has long been studied, coping with automatically generated SQL statements requires other weapons. This tutorial provides an in-depth exploration of the use of dynamic program analysis as a basis for reverse engineering relational databases. It describes and illustrates several automated techniques allowing to capture the trace of the SQL-related events occuring during the execution of data-intensive programs. It then presents and evaluates several heuristics and techniques supporting the automatic recovery of implicit schema constructs from SQL execution traces. Other applications of SQL execution trace analysis are also identified. © Springer-Verlag Berlin Heidelberg 2013.