Database reverse engineering: From requirements to CARE tools

Vincent Englebert

1996, Automated Software Engineering

Abstract

This paper analyzes the requirements that CASE tools should meet for effective database reverse engineering (DBRE), and proposes a general architecture for data-centered applications reverse engineering CASE environments. First, the paper describes a generic DBMS-independent DBRE methodology, then it analyzes the main characteristics of DBRE activities in order to collect a set of desirable requirements. Finally, it describes DB-MAIN, an operational CASE tool developed according to these requirements. The main features of this tool that are described in this paper are its unique generic specification model, its repository, its transformation toolkit, its user interface, the text processors, the assistants, the methodological control and its functional extensibility. Finally, the paper describes five real-world projects in which the methodology and the CASE tool were applied.

Figures (12)

The real scope of database reverse engineering has sometimes been misunderstood, and presented as
merely redrawing the data structures of a database into some DBMS-independent formalism. Many
early scientific proposals, and most current CASE tools are limited to the translation process
illustrated in Figure 1. In such situations, some elementary translation rules suffice to produce a
tentative conceptual schema. — The real scope of database reverse engineering has sometimes been misunderstood, and presented as merely redrawing the data structures of a database into some DBMS-independent formalism. Many early scientific proposals, and most current CASE tools are limited to the translation process illustrated in Figure 1. In such situations, some elementary translation rules suffice to produce a tentative conceptual schema.

Figure 3. Main components of the generic DBRE methodology. Quite naturally, this reverse methodology is to be read
from right to left, and bottom-up !
2.1 Data Structure Extraction — Figure 3. Main components of the generic DBRE methodology. Quite naturally, this reverse methodology is to be read from right to left, and bottom-up ! 2.1 Data Structure Extraction

Figure 4. Simplified DBRE methodology proposed by most current CARE tools.

Figure 5. A typical data structure schema during reverse engineering. This schema includes conceptualized objects
(PRODUCT, CUSTOMER, ACCOUNT, of), logical objects (record type ORDER, with single-valued and multivalued
foreign keys) and physical objects (access keys ORDER.ORD-ID and ORDER.ORIGIN; file DSK: MGT-03) — Figure 5. A typical data structure schema during reverse engineering. This schema includes conceptualized objects (PRODUCT, CUSTOMER, ACCOUNT, of), logical objects (record type ORDER, with single-valued and multivalued foreign keys) and physical objects (access keys ORDER.ORD-ID and ORDER.ORIGIN; file DSK: MGT-03)

Figure 6. Transforming a relationship type into an entity type, and conversely.

Figure 8. Transformation of an attribute into an entity type : (a) by explicit representation of its instances, (b) by
explicit representation of its distinct values.
DB-MAIN proposes a three-level transformation toolset that can be used freely, according to the
skill of the user and the complexity of the problem to be solved. These tools are neutral and generic,
in that they can be used in any database engineering process. As far as DBRE is concerned, they are
mainly used in the Data Structure Conceptualization (Section 2.2). — Figure 8. Transformation of an attribute into an entity type : (a) by explicit representation of its instances, (b) by explicit representation of its distinct values. DB-MAIN proposes a three-level transformation toolset that can be used freely, according to the skill of the user and the complexity of the problem to be solved. These tools are neutral and generic, in that they can be used in any database engineering process. As far as DBRE is concerned, they are mainly used in the Data Structure Conceptualization (Section 2.2).

Figure 7. Relationship-type R is represented by foreign key B1, and conversely

Figure 9. The dialog box of the Split/Merge transformation through which the analyst can either extract some
components from the master entity type (left), or merge two entity types, or migrate components from an entity type to
another.
With these tools, the user keeps full control of the schema transformation. Indeed, similar
situations can often be solved by different transformations; e.g. a multivalued attribute can be
transformed in a dozen ways. Figure 9 illustrates the dialog box for the Split/Merge of an entity
type. The current version of DB-MAIN proposes a toolset of about 25 elementary
transformations. — Figure 9. The dialog box of the Split/Merge transformation through which the analyst can either extract some components from the master entity type (left), or merge two entity types, or migrate components from an entity type to another. With these tools, the user keeps full control of the schema transformation. Indeed, similar situations can often be solved by different transformations; e.g. a multivalued attribute can be transformed in a dozen ways. Figure 9 illustrates the dialog box for the Split/Merge of an entity type. The current version of DB-MAIN proposes a toolset of about 25 elementary transformations.

Figure 12. Control panel of the Transformation assistant. The left-side area is the problem solver, which presents a
catalog of problems (1st column) and suggested solutions (2nd column). The right-side area is the script manager. The
worksheet shows a simplified script for conceptualizing relational databases. — Figure 12. Control panel of the Transformation assistant. The left-side area is the problem solver, which presents a catalog of problems (1st column) and suggested solutions (2nd column). The right-side area is the script manager. The worksheet shows a simplified script for conceptualizing relational databases.

DB-MAIN provides a set of built-in standard functions that should be sufficient to satisfy most
basic needs in database engineering. However, no CASE tool can meet the requirements of all users
in any possible situation, and specialized operators may be needed to deal with unforeseen or
marginal situations.
10. Functional Extensibility — DB-MAIN provides a set of built-in standard functions that should be sufficient to satisfy most basic needs in database engineering. However, no CASE tool can meet the requirements of all users in any possible situation, and specialized operators may be needed to deal with unforeseen or marginal situations. 10. Functional Extensibility