Database Reverse Engineering

Introduction

Database reverse engineering consists of recovering the abstract descriptions of files and databases of legacy information systems. A legacy information system can be defined as a “data-intensive application, such as [a] business system based on hundreds or thousands of data files (or tables), that significantly resists modifications and changes” (Brodie & Stonebraker, 1995). The objective of database reverse engineering is to recover the logical and conceptual descriptions, or schemas, of the permanent data of a legacy information system, that is, its database, be it implemented as a set of files or through an actual database management system.

The logical schema is the technology-dependent (e.g., relational) description of the database structures while the conceptual schema is the abstract, technology-independent description of their semantics.

Database reverse engineering often is the first steps of broader engineering projects. Indeed, rebuilding the precise documentation of a legacy database is an absolute prerequisite before migrating, reengineering, maintaining or extending it, or merging it with other databases.

The current commercial offering in CASE tools poorly supports database reverse engineering. Generally, it reduces to the straightforward derivation of a conceptual schema such as that of Figure 1 from the following DDL code.

Figure 1.

A naive view of data reverse engineering

Unfortunately, actual database reverse engineering often is closer to deriving the conceptual schema of Figure 2 from the following sections of COBOL code, using meaningless names that do not declare compound fields or foreign keys.

Figure 2.

A more realistic view of data reverse engineering

Getting such a result obviously requires additional sources of information, which may prove more difficult to analyze than mere DDL statements. Untranslated (implicit) data structures and constraints, empirical implementation approaches and techniques, optimization constructs, ill-designed schemas, and, above all, the lack of up-to-date documentation are some of the difficulties that the analysts will face when trying to understand existing databases.

The goal of this article is to describe the problems that arise when one tries to rebuild the documentation of a legacy database and the methods, techniques, and tools through which these problems can be solved. A more in-depth analysis can be found in Hainaut (2002).

Background: State Of The Art And Key Problems

Database reverse engineering has been recognized to be a specific problem in the ’80s, notably in Casanova and Amaral De Sa (1984), Davis and Arora (1985), and Navathe (1988). These pioneering approaches were based on simple rules such as those illustrated in Figure 1, which work nicely with databases designed in a clean and disciplined way. A second generation of methodologies coped with physical schemas resulting from empirical design. More complex design rules were identified and interpreted (Blaha & Premerlani, 1995), structured and comprehensive approaches were developed (Edwards & Munro, 1995; Hainaut, Chandelon, Tonneau, & Joris, 1993), and the first industrial tools appeared (e.g., Bachman’s Reengineering Tool). Many contributions were published in the ’90s, addressing practically all the legacy technologies and exploiting such sources of information as application source code, database contents, or application user interfaces. Among synthesis publications, let us mention Davis and Aiken (2000), the first tentative history of this discipline.