Automated mapping of conceptual schemas to relational schemas

  • J. I. McCormack
  • T. A. Halpin
  • P. R. Ritson
Schema Transformation
Part of the Lecture Notes in Computer Science book series (LNCS, volume 685)


Many CASE tools for information systems engineering can input a conceptual data model of an application and map this to a logical data model for implementation. Typically this involves mapping an ER (Entity-Relationship) conceptual schema to a relational database schema. Since the graphic notation of ER, or the mapping algorithm itself, fails to capture many constraints and derivation rules, these additional features must be coded up manually. Object-Role Modelling (ORM) provides a simpler and richer notation, enabling most of these additional features to be catered for in the mapping. The most well known version of ORM is NIAM, and a number of CASE tools now support this method. Recently, an extended ORM language called FORML has been developed which is even more expressive, and a complete mapping algorithm has been developed and automated. This paper provides an overview of the mapping algorithm and the use of role-graphs for automation.


Conceptual Schema Object Type Relational Schema Case Tool Derivation Rule 
These keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.


  1. Batini, C., Ceri, S. & Navathe, S.B. 1992, Conceptual Database Design: an entityrelationship approach, Benjamin/Cummings, Redwood City CA.Google Scholar
  2. Codd, E.F. 1990, The Relational Model for Database Management: Version 2, Addison-Wesley, Reading MA.Google Scholar
  3. Control Data 1982, IAST: Information Analysis Support Tools, Reference Manual (Control Data Publication no. 60484610).Google Scholar
  4. Czejdo, B., Elmasri, R., Rusinkiewicz, M. & Erabley, D.W. 1990, ‘A Graphical Data Manipulation Language for an Extended Entity-Relationship Model', IEEE Computer, March 1990, pp. 26–37.Google Scholar
  5. De Troyer, O., Meersman, R. & Verlinden, P. 1988, ‘RIDL* on the CRIS Case: a Workbench for NIAM', Computerized Assistance during the Information Systems Life Cycle: Proc. CRIS88, eds T.W.Olle, A.A. Verrijn-Stuart & L. Bhabuta, North-Holland, Amsterdam.Google Scholar
  6. De Troyer, O. 1989, ‘RIDL: A Tool for the Computer-Assisted Engineering of Large Databases in the Presence of Integrity Constraints', Proc. ACM-SIGMOD Int. Conf. on Management of Data, Oregon.Google Scholar
  7. De Troyer, O. 1991, ‘The OO-Binary Relationship Model: a truly object-oriented conceptual model', Advanced Information Systems Engineering: Proc. CAiSE-91, Springer-Verlag Lecture Notes in Computer Science, no. 498, Trondheim.Google Scholar
  8. Elmasri, R. & Navathe, S.B. 1989, Fundamentals of Database Systems, Benjamin/Cummings, Redwood City CA.Google Scholar
  9. Halpin, T.A. 1989a, ‘A Logical Analysis of Information Systems: static aspects of the data-oriented perspective', PhD thesis, University of Queensland.Google Scholar
  10. Halpin, T.A. 1989b, ‘Contextual Equivalence of Conceptual Schemas', Proc. Advanced Database Systems Symposium, Info. Processing Soc. of Japan, Kyoto, pp. 47–54.Google Scholar
  11. Halpin, T.A. 1991a, ‘A fact-oriented approach to schema transformation', Proc. MFDBS-91, Springer Verlag Lec. Notes in Computer Science, no. 495, Rostock.Google Scholar
  12. Halpin, T.A. 1991b, ‘WISE: a Workbench for Information System Engineering', Proc. 2nd Workshop on Next Generation of CASE Tools, Trondheim (May 1991), reprinted in Next Generation CASE Tools, eds K. Lyytinen & V.-P. Tahvanainen, IOS Press, Amsterdam 1992.Google Scholar
  13. Halpin, T.A. 1992, ‘Fact-oriented schema optimization', Proc. CISMOD-92, Bangalore, India, July 1992.Google Scholar
  14. Halpin, T.A., Harding, J. & Oh, C-H. 1992, ‘Automated support for Subtyping', Proc. 3rd Workshop on Next Generation of CASE Tools, eds B. Theodoulidis & A. Sutcliffe, UMIST, UK.Google Scholar
  15. Halpin, T.A. & McCormack, J. 1992, ‘Automated Validation of Conceptual Schema Constraints', Advanced Inf. Systems Engineering: Proc. CAiSE'92, ed. P. Loucopoulos, Springer Verlag Lec. Notes in Computer Science, no. 593, pp. 445–62.Google Scholar
  16. Halpin, T.A. & Orlowska, M.E. 1991, ‘Fact-Oriented Modelling for Data Analysis', Journal of Information Systems, vol. 2, no. 2, Blackwell Scientific, Oxford.Google Scholar
  17. Halpin, T.A. & Ritson, P.R. 1992, ‘Fact-Oriented Modelling and Null Values', Research and Practical Issues in Databases: Proc. 3rd Australian Database Conf., eds B. Srinivasan & J. Zeleznikov, World Scientific, Singapore.Google Scholar
  18. Hohenstein, U. 1990, ‘Automatic transformation of an Entity-relationship query language into SQL', Entity-Relationship Approach to database design and querying (Proc. 8th ER conf.), ed. F.H. Lochovsky, Elsevier Science Pub., Amsterdam.Google Scholar
  19. Hohenstein, U. & Engels, G. 1991, ‘Formal semantics of an entity-relationship-based query language', Entity-Relationship Approach: the core of conceptual modelling (Proc. 9th ER conf.), ed. H. Kangassalo, Elsevier Science Pub., Amsterdam.Google Scholar
  20. Intellibase, 1990, RIDL-M User's Guide, Intellibase N.V., Belgium.Google Scholar
  21. ISO 1982, Concepts and Terminology for the Conceptual Schema and the Information Base, ed. J.J. van Griethuysen, ISO TC97/SC5/WG3, Eindhoven.Google Scholar
  22. Mark, L., 1987, ‘The Binary Relationship Model — 10th Anniversary', Tech. Report CS-TR-1933, Uni. of Maryland.Google Scholar
  23. Markowitz, V.M. 1990, ‘Referential integrity revisited: an object-oriented perspective', Proc. 16th VLDB Conf., Brisbane.Google Scholar
  24. Markowitz, V.M. & Shoshani, A. 1990, ‘Abbreviated query interpretation in extended Entity-Relationship oriented databases', Entity-Relationship Approach to database design and querying, ed. F.H. Lochovsky, Elsevier Science Pub., Amsterdam.Google Scholar
  25. Nienhuys-Cheng, S. 1990, ‘Classification and Syntax of Constraints in Binary Semantical Networks', Inform. Systems, vol. 15, no. 5, pp. 497–513.Google Scholar
  26. Nijssen, G.M. & Halpin, T.A. 1989, Conceptual Schema and Relational Database Design, Prentice Hall, Sydney.Google Scholar
  27. Olle, T.W., Hagelstein, J., Macdonald, I.G., Rolland, C., Sol, H.G., Van Assche, F.J.M. & Verrijn-Stuart, A.A. 1991, Information Systems Methodologies — A Framework for Understanding, 2nd edn., Addison-Wesley, Wokingham, England.Google Scholar
  28. Ovum 1992, Ovum Evaluates: CASE Products, Ovum Ltd, London.Google Scholar
  29. Papastamatiou, G. 1992, ‘Transaction semantics in a conceptual rule language', Proc. 3rd workshop on Next Generation CASE Tools, eds B. Theodoulidis & A. Sutcliffe, UMIST, UK.Google Scholar
  30. Reiner, D. 1992, ‘Database Design Tools', in Batini, Ceri & Navathe (op. cit.), Ch. 15.Google Scholar
  31. Ritson, P.R. & Halpin, T.A. 1992, ‘Mapping conceptual constraints to a relational schema', Tech. Report 223, Dept of Computer Science, University of Queensland.Google Scholar
  32. Ritson, P.R. & Halpin, T.A. 1993, ‘Mapping One-to-One Predicates to a Relational Schema', Proc. 4th Australian Database Conf., (Brisbane, February 1993), World-Scientific, Singapore.Google Scholar
  33. Rochfeld, A., Morejon, J. & Negros, P. 1991, ‘Inter-Relationship Links in E-R Model'.Google Scholar
  34. Shoval, P. & Even-Chaime, M. 1987, ‘ADDS: A system for automatic database schema design based on the binary-relationship model', Data and Knowledge Engineering, vol. 2, pp. 123–44.Google Scholar
  35. Shoval, P., Gudes, E. & Goldstein, M. 1988, ‘GISD: A Graphical Interactive System for Conceptual Database Design', Inform. Systems, vol. 13, no. 1, pp. 81–95.Google Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 1993

Authors and Affiliations

  • J. I. McCormack
    • 1
  • T. A. Halpin
    • 1
  • P. R. Ritson
    • 1
  1. 1.Key Centre for Software Technology Department of Computer ScienceUniversity of QueenslandAustralia

Personalised recommendations