Reverse Engineering of Relational Database Physical Schema

This paper seeks to present and illustrate essential aspects of database physical de-optimisation within the context of a reverse engineering method, called MeRCI. The latter provides a framework allowing us to achieve a reverse engineering of a relational database into an Extended Entity-Relationship schema. In this paper we focus mainly on a critical step of MeRCI leading to schema de-optimization that removes the non-semantic aspects of the physical and logical schemas. To achieve such a result, we reverse engineer the physical structures obtained by a database designer when using restructuring operations. Removing optimization structures requires to reverse the effect of both relational operators (join, restrict, project, etc) and non-relational operators such as the flattening operator. The de-optimization process requires the analysis of both the Data Description Language (DDL) and the Data Manipulation Language (DML) specifications and to perform a data mining process, thus allowing us to recover the structure of a database conceptual schema. The paper illustrates the de-optimization process by applying it to various examples. Finally, we discuss implications for further research pertaining to the logic and development of an efficient and complete reverse engineering method eliciting the semantics of a relational database.

[1]  Veda C. Storey,et al.  Reverse Engineering of Relational Databases: Extraction of an EER Model from a Relational Database , 1994, Data Knowl. Eng..

[2]  Erik G. Nilsson.  The Translation of a Cobol Data Structure to an Entity-Relationship Type Conceptual Schema , 1985, ER.

[3]  Veda C. Storey,et al.  Performance Evaluation of Reverse Engineering Relational Databases into Extended Entity-Relationship Models , 1993, ER.

[4]  Margaret H. Dunham,et al.  Join processing in relational databases , 1992, CSUR.

[5]  W. Alex Gray,et al.  An Approach to Eliciting the Semantics of Relational Databases , 1992, CAiSE.

[6]  Peter M. G. Apers,et al.  Reverse Engineering of Relational Database Applications , 1995, OOER.

[7]  Roger H. L. Chiang,et al.  A knowledge-based system for performing reverse engineering of relational databases , 1995, Decis. Support Syst..

[8]  Hausi A. Müller,et al.  Domain-retargetable reverse engineering , 1993, 1993 Conference on Software Maintenance.

[9]  William J. Premerlani,et al.  An approach for reverse engineering of relational databases , 1993, [1993] Proceedings Working Conference on Reverse Engineering.

[10]  Katalin Kalman,et al.  Implementation and Critique of an Algorithm which Maps a Relational Database to a Conceptual Model , 1991, CAiSE.

[11]  Marco A. Casanova,et al.  Mapping Uninterpreted Schemes into Entity-Relationship Diagrams: Two Applications to Conceptual Schema Design , 1984, IBM J. Res. Dev..

[12]  Shamkant B. Navathe,et al.  Conceptual Database Design: An Entity-Relationship Approach , 1991 .

[13]  Jean-Luc Hainaut,et al.  Schema Transformation Techniques for Database Reverse Engineering , 1993, ER.

[14]  Martin Andersson.  Extracting an Entity Relationship Schema from a Relational Database through Reverse Engineering , 1994, ER.

[15]  Kathi Hogshead Davis,et al.  Software Reverse Engineering from a Currently Existing IMS Database to an Entity-Relationship Model , 1990, ER.

[16]  Kathi Hogshead Davis,et al.  A Methodology for Translating a Conventional File System into an Entity-Relationship Model , 1985, ER.

[17]  E. B. Swanson,et al.  Software maintenance management , 1980 .

[18]  Jean-Luc Hainaut,et al.  Database Reverse Engineering: Models, Techniques, and Strategies , 1991, ER.

[19]  Jean-Luc Hainaut,et al.  Transformation-based Database Reverse Engineering , 1993 .

[20]  Shamkant B. Navathe,et al.  Abstracting Relational and Hierarchical Data with a Semantic Data Model , 1987, International Conference on Conceptual Modeling.

[21]  Jean-Luc Hainaut,et al.  Database CASE Tool Architecture: Principles for Flexible Design Strategies , 1992, CAiSE.

[22]  Oreste Signore,et al.  Reconstruction of ER Schema from Database Applications: a Cognitive Approach , 1994, ER.

[23]  Jean-Marc Petit,et al.  Using Queries to Improve Database Reverse Engineering , 1994, ER.

[24]  Kathi Hogshead Davis,et al.  Converting A Relational Database Model into an Entity-Relationship Model , 1987, ER.

[25]  R. P. Whittington.  Database Systems Engineering , 1988 .

[26]  Vincent Englebert,et al.  Requirements for information system reverse engineering support , 1995, Proceedings of 2nd Working Conference on Reverse Engineering.

[27]  James H. Cross,et al.  Reverse engineering and design recovery: a taxonomy , 1990, IEEE Software.

[28]  F. Springsteel,et al.  Reverse data engineering of E-R-designed relational schemas , 1990, Proceedings. PARBASE-90: International Conference on Databases, Parallel Architectures, and Their Applications.