Reverse engineering of relational database physical schemas

  • Isabelle Comyn-Wattiau
  • Jacky Akoka
Session 9: Capturing Design Information
Part of the Lecture Notes in Computer Science book series (LNCS, volume 1157)


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.


Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.


  1. 1.
    Akoka, J., Comyn-Wattiau, I.,“MeRCI: Une Méthode de Rétroconception Intelligente des Bases de Données Relationnelles”, CERESSEC W.P., 1996.Google Scholar
  2. 2.
    Andersson M., Extracting an Entity Relationship Schema from a Relational Database through Reverse Engineering, in Proceedings of the 13th Conf. on ER Approach, Manchester, UK, Dec 1994.Google Scholar
  3. 3.
    Batini C., Ceri S., Navathe S.B., Conceptual Database Design: An Entity-Relationship Approach, The Benjamin/Cummings Publishing Company, Inc., 1992.Google Scholar
  4. 4.
    Casanova M., Amarel de Sa J., Designing Entity Relationship Schemas for Conventional Information Systems, in Proc. of Entity-Relationship Approach, pp. 265–278, 1983.Google Scholar
  5. 5.
    Casanova M.A., Amarel de Sa J., Mapping uninterpreted Schemes into Entity-Relationship diagrams: two applications to conceptual schema design, IBM J. Res. & Develop., 28(1), January, 1984.Google Scholar
  6. 6.
    Chiang R.H.L., Barron T.M., Storey V.C., Performance Evaluation of Reverse Engineering Relational Databases into Extended Entity-Relationship Models, in Proc. of the 12th Int. Conf. on ER Approach, Arlington, USA, Décembre 1993.Google Scholar
  7. 7.
    Chiang R.H.L., Barron T.M., Storey V.C., Reverse Engineering of Relational Database: Extraction of an EER model from a relationnal database, Data & Knowledge Engineering 12, pp 17–142, 1994.Google Scholar
  8. 8.
    Chiang R.H.L., A knowledge-based system for performing reverse engineering of relational databases, Decision Support Systems 13, pp 295–312, 1995.Google Scholar
  9. 9.
    Chikofsky E.J., Cross J., Reverse Engineering and Design Recovery,: a Taxonomy, IEEE Software, January 1990.Google Scholar
  10. 10.
    Davis K.H., Arora A.K., A Methodology for Translating a Conventional File System into an Entity-Relationship Model, Proc. of the 4th Int. Conf. on ER Approach, Chicago, USA, Octobre 1985.Google Scholar
  11. 11.
    Davis K.H., Arora A.K., Converting a Relational Database Model into an Entity-Relationship Model, Proc. of the 6th Int. Conf. on ER Approach, New York, USA, Novembre 1987.Google Scholar
  12. 12.
    Fonkam M.M, Gray W.A., An Approach to Eliciting the Semantics of Relational Databases, Proc. of 4th Int. Conf. on Advance Information Systems Engineering-CAiSE'92, pp. 463–480, Springer-Verlag, 1992.Google Scholar
  13. 13.
    Hainaut J.L., Database Reverse Engineering: Models, techniques and strategies, Proc. of the Tenth International Conference on Entity-Relationship Approach, 1991.Google Scholar
  14. 14.
    Hainaut J.L., Cadelli M., Decuyper B., Marchand O., Database CASE Tool Architecture: Principles for Flexible Design Strategies, Proc. of 4th Int. Conf. on Advance Information Systems Engineering — CAiSE'92, Springer-Verlag, 1992.Google Scholar
  15. 15.
    Hainaut J.L., Tonneau C., Joris M., Chandelon M., Schema Transformation Techniques for Database Reverse Engineering, Proc. of the 12th Inter. Conf. on Entity-Relationship Approach, Arlington, Texas, Dec 1993.Google Scholar
  16. 16.
    Hainaut J.L., Tonneau C., Joris M., Chandelon M., Transformation-based Database Reverse Engineering, in Proc. of the 13th Int. Conf. on ER Approach, Manchester, UK, Dec 1994.Google Scholar
  17. 17.
    Hainaut J.L., Englebert V., Henrard J., Hick J.M., Roland D., Requirements for Information System Reverse Engineering Support, Proc. of the IEEE Working Conf. on Reverse Engineering, Toronto, Canada, IEEE Computer Society Press, July 1995.Google Scholar
  18. 18.
    Johannesson P., Kalman K., A Method for translating Relational Schema into Conceptual Schemas, Proc. of the Eighth International Conference on Entity-Relationship Approach, 1991, pp 231–246.Google Scholar
  19. 19.
    Kalman K., Implementation and Critique of an algorithm which maps a relational database to a conceptual model, Proc. of 3th Int. Conf. on Advance Information Systems Engineering — CAiSE'91, 1991.Google Scholar
  20. 20.
    Lientz B.P., Swanson, E.B., Software Maintenance Management, Reading Mass:Addison Wesley, 1980.Google Scholar
  21. 21.
    Navathe S.B., Awong A., Abstracting Relational and Hierarchical Data with a Semantic Data Model, Proc. of Entity-Relationship Approach: a Bridge to the User, Elsevier Science Publishers, pp. 305–333, 1988.Google Scholar
  22. 22.
    Nilsson E.G., The Translation of COBOL Data Structure to an Entity-Rel-Type Conceptual Schema, Proc. of Entity-Relationship Approach, October, 1985.Google Scholar
  23. 23.
    Mishra, P., Eich, M.H.,“Join Processing in Relational Databases”, ACM Computing Surveys, 24(1), March 1992.Google Scholar
  24. 24.
    Petit J.M., Kouloumdjian J., Boulicaut J.F., Toumouni F., Using Queries to Improve Database Reverse Engineering, Proceedings of 13th International Conference on ER Approach, Manchester, 1994.Google Scholar
  25. 25.
    Premerlani W.J., Blaha M.R., An approach for Reverse Engineering of Relational Databases, Communications of the ACM, Vol 37(5), pp 42–49, May 1994.Google Scholar
  26. 26.
    Signore O., Loffredo M., Gregori M., Cima M., Reconstruction of ER Schema from Database Applications: a Cognitive Approach, Proc. of the 13th Int. Conf. on ER Approach, Manchester, UK, Dec. 1994.Google Scholar
  27. 27.
    Springstell F.N, Kou C., Reverse Data Engineering of E-R designed Relational schemas, in Proc. of Databases, Parallel Architectures and their Applications, March 1990.Google Scholar
  28. 28.
    Tilley S.R., Müller H.A., Withney M.J., Wong K., Domain-Retargetable Reverse Engineering, Proc. of IEEE Working Conf. on Software Maintenance, 1993.Google Scholar
  29. 29.
    Vermeer, M.W.W., Apers, P.M.G.,“Reverse Engineering of Relational Database Applications”, Proc. of the 14th Conf. on Object-Oriented and Entity-Relationship (OOER'95), Brisbane, Australia, 1995.Google Scholar
  30. 30.
    Winans J., Davis K.H., Software Reverse Engineering from a Currently Existing IMS Database to an Entity-Relationship Model, Proc. of Entity-Relationship Approach, pp. 345–360, Oct. 1990.Google Scholar
  31. 31.
    Wittington, R.P., Database Systems Engineering, Clarendon Press, Oxford, 1988.Google Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 1996

Authors and Affiliations

  • Isabelle Comyn-Wattiau
    • 2
  • Jacky Akoka
    • 1
  1. 1.Ecole Supérieure des Sciences Economiques et Commerciales (ESSEC)CergyFrance
  2. 2.ESSEC and Laboratoire PRiSMUniversité de VersaillesVersailles

Personalised recommendations