Database Reverse Engineering

Database Reverse Engineering

Jean-Luc Hainaut (University of Namur, Belgium), Jean Henrard (REVER s.a., Belgium), Didier Roland (REVER s.a., Belgium), Jean-Marc Hick (REVER s.a., Belgium) and Vincent Englebert (University of Namur, Belgium)
DOI: 10.4018/978-1-60566-242-8.ch021
OnDemand PDF Download:
$30.00
List Price: $37.50

Abstract

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.
Chapter Preview
Top

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).

Top

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.

Key Terms in this Chapter

Data Structure Extraction: The process within reverse engineering that attempts to recover the implicit constructs of a database.

Implicit Construct: A data structure or an integrity constraint that holds, or should hold, among the data, but that has not been explicitly declared in the DDL code of the database. Implicit compound and multivalued fields as well as implicit foreign keys are some of the most challenging constructs to chase when recovering the logical schema of a database

Legacy Information System: “[A] data-intensive application, such as [a] business system based on hundreds or thousands of data files (or tables), that significantly resists modifications and change” (Brodie & Stonebraker, 1995)

Data Structure Conceptualization: The process within reverse engineering that aims at deriving a plausible conceptual schema from the logical schema of a database.

System Empirical Design: A way to build a system that does not rely on a strict methodology but rather on the experience and the intuition of the developer. An empirically designed database often includes undocumented idiosyncrasies that may be difficult to understand when attempting to reverse engineer it.

Optimization Schema Construct: Any data structure or constraint that appears in a logical or physical schema and that conveys no information (does not appear in the conceptual schema). Its aim is to increase the performance of the database and of its application programs. Unnormalized tables, redundancies, and artificially split or merged tables are some popular examples

Database Reverse Engineering: The process through which the logical and conceptual schemas of a legacy database, or of a set of files, are recovered, or rebuilt, from various information sources such as DDL code, data dictionary contents, database contents, or the source code of application programs that use the database

Complete Chapter List

Search this Book:
Reset