Skip to main content

Reverse engineering of relational database physical schemas

  • Session 9: Capturing Design Information
  • Conference paper
  • First Online:
Conceptual Modeling — ER '96 (ER 1996)

Part of the book series: Lecture Notes in Computer Science ((LNCS,volume 1157))

Included in the following conference series:

  • 205 Accesses

Abstract

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.

This is a preview of subscription content, log in via an institution to check access.

Access this chapter

Institutional subscriptions

Preview

Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.

References

  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. 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. Batini C., Ceri S., Navathe S.B., Conceptual Database Design: An Entity-Relationship Approach, The Benjamin/Cummings Publishing Company, Inc., 1992.

    Google Scholar 

  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. 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. 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. 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. 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. Chikofsky E.J., Cross J., Reverse Engineering and Design Recovery,: a Taxonomy, IEEE Software, January 1990.

    Google Scholar 

  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. 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. 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. Hainaut J.L., Database Reverse Engineering: Models, techniques and strategies, Proc. of the Tenth International Conference on Entity-Relationship Approach, 1991.

    Google Scholar 

  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. 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. 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. 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. 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. 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. Lientz B.P., Swanson, E.B., Software Maintenance Management, Reading Mass:Addison Wesley, 1980.

    Google Scholar 

  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. 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. Mishra, P., Eich, M.H.,“Join Processing in Relational Databases”, ACM Computing Surveys, 24(1), March 1992.

    Google Scholar 

  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. 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. 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. 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. 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. 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. 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. Wittington, R.P., Database Systems Engineering, Clarendon Press, Oxford, 1988.

    Google Scholar 

Download references

Author information

Authors and Affiliations

Authors

Editor information

Bernhard Thalheim

Rights and permissions

Reprints and permissions

Copyright information

© 1996 Springer-Verlag Berlin Heidelberg

About this paper

Cite this paper

Comyn-Wattiau, I., Akoka, J. (1996). Reverse engineering of relational database physical schemas. In: Thalheim, B. (eds) Conceptual Modeling — ER '96. ER 1996. Lecture Notes in Computer Science, vol 1157. Springer, Berlin, Heidelberg. https://doi.org/10.1007/BFb0019935

Download citation

  • DOI: https://doi.org/10.1007/BFb0019935

  • Published:

  • Publisher Name: Springer, Berlin, Heidelberg

  • Print ISBN: 978-3-540-61784-6

  • Online ISBN: 978-3-540-70685-4

  • eBook Packages: Springer Book Archive

Publish with us

Policies and ethics