Formal semantics of SQL queries

The documentation of a database includes its conceptual schema, that formalizes the semantics of the data, and its logical schema that translates the former according to an operational database model. Important engineering processes such as database and program evolution rely on a complete and accurate database documentation. In many cases, however, these schemas are missing, or, at best, incomplete and outdated. Their reconstruction, a process called database reverse engineering, requires DDL code analysis but, more important, the elicitation of implicit constructs (data structures and constraints), that is, constructs that have been incompletely translated into the operational database schema. The most powerful discovery technique of these implicit constructs is the static analysis of application program source code, and, in particular of embedded SQL statements. Unfortunately, the increasing use of dynamic SQL in business applications development makes such techniques helpless, so that modern web software systems can no longer be correctly redocumented through current reverse engineering techniques. This paper introduces and evaluates dynamic SQL capturing and analysis techniques and shows that they can replace and even improve upon pure static analysis. A real world case study is presented and discussed.