Database Semantics Recovery through Analysis of Dynamic SQL Statements

  • Anthony Cleve
  • Jean-Roch Meurisse
  • Jean-Luc Hainaut
Part of the Lecture Notes in Computer Science book series (LNCS, volume 6720)


The documentation of a database includes its conceptual schema, that formalizes the semantics of the data, and its logical schema that translates the former according to an operational database model. Important engineering processes such as database and program evolution rely on a complete and accurate database documentation. In many cases, however, these schemas are missing, or, at best, incomplete and outdated. Their reconstruction, a process called database reverse engineering, requires DDL code analysis but, more important, the elicitation of implicit constructs (data structures and constraints), that is, constructs that have been incompletely translated into the operational database schema. The most powerful discovery technique of these implicit constructs is the static analysis of application program source code, and, in particular of embedded SQL statements. Unfortunately, the increasing use of dynamic SQL in business applications development makes such techniques helpless, so that modern web software systems can no longer be correctly redocumented through current reverse engineering techniques. This paper introduces and evaluates dynamic SQL capturing and analysis techniques and shows that they can replace and even improve upon pure static analysis. A real world case study is presented and discussed.


IEEE Computer Society Reverse Engineering Database Schema Query Execution Logical Schema 
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.


Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.


  1. 1.
    Casanova, M.A., De Sa, J.E.A.: Mapping uninterpreted schemes into entity-relationship diagrams: two applications to conceptual schema design. IBM J. Res. Dev. 28(1), 82–94 (1984)CrossRefGoogle Scholar
  2. 2.
    Davis, K.H., Arora, A.K.: A methodology for translating a conventional file system into an entity-relationship model. In: Proceedings of the Fourth International Conference on Entity-Relationship Approach, pp. 148–159. IEEE Computer Society, Washington, DC, USA (1985)Google Scholar
  3. 3.
    Navathe, S.B., Awong, A.M.: Abstracting relational and hierarchical data with a semantic data model. In: Proceedings of the Sixth International Conference on Entity-Relationship Approach (ER 1987), pp. 305–333. North-Holland Publishing Co., Amsterdam (1988)Google Scholar
  4. 4.
    Johannesson, P.: A method for transforming relational schemas into conceptual schemas. In: Proceedings of the Tenth International Conference on Data Engineering (ICDE 2004), pp. 190–201. IEEE Computer Society, Washington, DC, USA (1994)Google Scholar
  5. 5.
    Blaha, M.R., Premerlani, W.J.: Observed idiosyncracies of relational database designs. In: Proceedings of the Second Working Conference on Reverse Engineering (WCRE 1995), p. 116. IEEE Computer Society, Washington, DC, USA (1995)CrossRefGoogle Scholar
  6. 6.
    Hainaut, J.L., Englebert, V., Henrard, J., Hick, J.M., Roland, D.: Database reverse engineering: From requirements to care tools. Automated Software Engineering 3, 9–45 (1996)CrossRefMathSciNetGoogle Scholar
  7. 7.
    Davis, K.H., Aiken, P.H.: Data reverse engineering: A historical survey. In: Proceedings of the Seventh Working Conference on Reverse Engineering (WCRE 2000), p. 70. IEEE Computer Society, Washington, DC, USA (2000)CrossRefGoogle Scholar
  8. 8.
    Hainaut, J.L.: Legacy and future of data reverse engineering. In: Proceedings of the 16th Working Conference on Reverse Engineering (WCRE 2009), p. 4. IEEE Computer Society, Los Alamitos (2009)CrossRefGoogle Scholar
  9. 9.
    Hainaut, J.L., Henrard, J., Englebert, V., Roland, D., Hick, J.M.: Database reverse engineering. In: Liu, L., Özsu, M.T. (eds.) Encyclopedia of Database Systems, pp. 723–728. Springer, US (2009)Google Scholar
  10. 10.
    Hainaut, J.-L.: The transformational approach to database engineering. In: Lämmel, R., Saraiva, J., Visser, J. (eds.) GTTSE 2005. LNCS, vol. 4143, pp. 95–143. Springer, Heidelberg (2006)CrossRefGoogle Scholar
  11. 11.
    Hainaut, J.L.: Introduction to database reverse engineering. LIBD Publish (2002),
  12. 12.
    Markowitz, V.M., Makowsky, J.A.: Identifying extended entity-relationship object structures in relational schemas. IEEE Trans. Softw. Eng. 16, 777–790 (1990)CrossRefGoogle Scholar
  13. 13.
    Premerlani, W.J., Blaha, M.R.: An approach for reverse engineering of relational databases. Commun. ACM 37(5), 42 (1994)CrossRefGoogle Scholar
  14. 14.
    Chiang, R.H.L., Barron, T.M., Storey, V.C.: Reverse engineering of relational databases: extraction of an eer model from a relational database. Data Knowl. Eng. 12(2), 107–142 (1994)CrossRefGoogle Scholar
  15. 15.
    Lopes, S., Petit, J.M., Toumani, F.: Discovering interesting inclusion dependencies: application to logical database tuning. Inf. Syst. 27, 1–19 (2002)CrossRefzbMATHGoogle Scholar
  16. 16.
    Yao, H., Hamilton, H.J.: Mining functional dependencies from data. Data Min. Knowl. Discov. 16(2), 197–219 (2008)CrossRefMathSciNetGoogle Scholar
  17. 17.
    Pannurat, N., Kerdprasop, N., Kerdprasop, K.: Database reverse engineering based on association rule mining. CoRR abs/1004.3272 (2010)Google Scholar
  18. 18.
    Petit, J.M., Kouloumdjian, J., Boulicaut, J.F., Toumani, F.: Using queries to improve database reverse engineering. In: Loucopoulos, P. (ed.) ER 1994. LNCS, vol. 881, pp. 369–386. Springer, Heidelberg (1994)CrossRefGoogle Scholar
  19. 19.
    Di Lucca, G.A., Fasolino, A.R., de Carlini, U.: Recovering class diagrams from data-intensive legacy systems. In: Proceedings of the 16th IEEE International Conference on Software Maintenance (ICSM 2000), p. 52. IEEE Computer Society, Los Alamitos (2000)Google Scholar
  20. 20.
    Henrard, J.: Program Understanding in Database Reverse Engineering. PhD thesis, University of Namur (2003)Google Scholar
  21. 21.
    Cleve, A., Henrard, J., Hainaut, J.L.: Data reverse engineering using system dependency graphs. In: Proceedings of the 13th Working Conference on Reverse Engineering (WCRE 2006), pp. 157–166. IEEE Computer Society, Washington, DC, USA (2006)CrossRefGoogle Scholar
  22. 22.
    Choobineh, J., Mannino, M.V., Tseng, V.P.: A form-based approach for database analysis and design. Communications of the ACM 35(2), 108–120 (1992)CrossRefGoogle Scholar
  23. 23.
    Terwilliger, J.F., Delcambre, L.M.L., Logan, J.: The user interface is the conceptual model. In: Embley, D.W., Olivé, A., Ram, S. (eds.) ER 2006. LNCS, vol. 4215, pp. 424–436. Springer, Heidelberg (2006)CrossRefGoogle Scholar
  24. 24.
    Ramdoyal, R., Cleve, A., Hainaut, J.-L.: Reverse engineering user interfaces for interactive database conceptual analysis. In: Pernici, B. (ed.) CAiSE 2010. LNCS, vol. 6051, pp. 332–347. Springer, Heidelberg (2010)CrossRefGoogle Scholar
  25. 25.
    Andersson, M.: Searching for semantics in cobol legacy applications. In: Data Mining and Reverse Engineering: Searching for Semantics, IFIP TC2/WG2.6 Seventh Conference on Database Semantics (DS-7). IFIP Conference Proceedings, vol. 124, pp. 162–183. Chapman & Hall, Boca Raton (1998)CrossRefGoogle Scholar
  26. 26.
    Embury, S.M., Shao, J.: Assisting the comprehension of legacy transactions. In: Proceedings of the 8th Working Conference on Reverse Engineering (WCRE 2001), p. 345. IEEE Computer Society, Washington, DC, USA (2001)Google Scholar
  27. 27.
    Willmor, D., Embury, S.M., Shao, J.: Program slicing in the presence of a database state. In: ICSM 2004: Proceedings of the 20th IEEE International Conference on Software Maintenance, pp. 448–452. IEEE Computer Society, Washington, DC, USA (2004)CrossRefGoogle Scholar
  28. 28.
    Maule, A., Emmerich, W., Rosenblum, D.S.: Impact analysis of database schema changes. In: Proceedings of the 30th international conference on Software engineering (ICSE 2008), pp. 451–460. ACM Press, New York (2008)Google Scholar
  29. 29.
    van den Brink, H., van der Leek, R., Visser, J.: Quality assessment for embedded sql. In: Proceedings of the 7th IEEE International Working Conference on Source Code Analysis and Manipulation (SCAM 2007), pp. 163–170. IEEE Computer Society, Los Alamitos (2007)CrossRefGoogle Scholar
  30. 30.
    Ngo, M.N., Tan, H.B.K.: Applying static analysis for automated extraction of database interactions in web applications. Inf. Softw. Technol. 50(3), 160–175 (2008)CrossRefGoogle Scholar
  31. 31.
    Cleve, A.: Program Analysis and Transformation for Data-Intensive System Evolution. PhD thesis, University of Namur (October 2009)Google Scholar
  32. 32.
    Kiczales, G., Hilsdale, E.: Aspect-oriented programming. In: ESEC/FSE-9: Proceedings of the 8th European software engineering conference held jointly with 9th ACM SIGSOFT international symposium on Foundations of software engineering, p. 313. ACM, New York (2001)CrossRefGoogle Scholar
  33. 33.
    Kiczales, G., Hilsdale, E., Hugunin, J., Kersten, M., Palm, J., Griswold, W.G.: An overview of aspectj. In: Lee, S.H. (ed.) ECOOP 2001. LNCS, vol. 2072, pp. 327–353. Springer, Heidelberg (2001)CrossRefGoogle Scholar
  34. 34.
    Cleve, A., Hainaut, J.L.: Dynamic analysis of sql statements for data-intensive applications reverse engineering. In: Proceedings of the 15th Working Conference on Reverse Engineering, pp. 192–196. IEEE Computer Society, Los Alamitos (2008)Google Scholar
  35. 35.
    Petit, J.M., Toumani, F., Kouloumdjian, J.: Relational database reverse engineering: A method based on query analysis. Int. J. Cooperative Inf. Syst. 4, 287–316 (1995)CrossRefGoogle Scholar
  36. 36.
    Lopes, S., Petit, J.M., Toumani, F.: Discovery of “Interesting” data dependencies from a workload of SQL statements. In: Żytkow, J.M., Rauch, J. (eds.) PKDD 1999. LNCS (LNAI), vol. 1704, pp. 430–435. Springer, Heidelberg (1999)CrossRefGoogle Scholar
  37. 37.
    Tan, H.B.K., Ling, T.W., Goh, C.H.: Exploring into programs for the recovery of data dependencies designed. IEEE Trans. Knowl. Data Eng. 14(4), 825–835 (2002)CrossRefGoogle Scholar
  38. 38.
    Tan, H.B.K., Zhao, Y.: Automated elicitation of inclusion dependencies from the source code for database transactions. Journal of Software Maintenance 15(6), 379–392 (2003)CrossRefGoogle Scholar
  39. 39.
    Codd, E.F.: A relational model of data for large shared data banks. Commun. ACM 13(6), 377–387 (1970)CrossRefzbMATHGoogle Scholar
  40. 40.
    Cleve, A., Lemaitre, J., Hainaut, J.L., Mouchet, C., Henrard, J.: The role of implicit schema constructs in data quality. In: Proceedings of the 6th International Workshop on Quality in Databases (QDB 2008), pp. 33–40 (2008)Google Scholar
  41. 41.
    DB-MAIN: The DB-MAIN official website (2011),
  42. 42.
    Cohen, W.W., Ravikumar, P., Fienberg, S.E.: A comparison of string distance metrics for name-matching tasks. In: Proceedings of IJCAI-2003 Workshop on Information Integration on the Web (IIWeb-2003), pp. 73–78 (2003)Google Scholar
  43. 43.
    Zhu, H., Hall, P.A.V., May, J.H.R.: Software unit test coverage and adequacy. ACM Comput. Surv. 29, 366–427 (1997)CrossRefGoogle Scholar
  44. 44.
    Kapfhammer, G.M., Soffa, M.L.: A family of test adequacy criteria for database-driven applications. In: Proceedings of the 9th European software engineering conference held jointly with 11th ACM SIGSOFT international symposium on Foundations of software engineering. ESEC/FSE-11, pp. 98–107. ACM, New York (2003)Google Scholar
  45. 45.
    Debusmann, M., Geihs, K.: Efficient and transparent instrumentation of application components using an aspect-oriented approach. In: Brunner, M., Keller, A. (eds.) DSOM 2003. LNCS, vol. 2867, pp. 209–220. Springer, Heidelberg (2003)CrossRefGoogle Scholar
  46. 46.
    Yang, Y., Peng, X., Zhao, W.: Domain feature model recovery from multiple applications using data access semantics and formal concept analysis. In: Proceedings of the 16th International Working Conference on Reverse Engineering (WCRE 2009), pp. 215–224. IEEE Computer Society, Los Alamitos (2009)CrossRefGoogle Scholar
  47. 47.
    Alalfi, M., Cordy, J., Dean, T.: WAFA: Fine-grained dynamic analysis of web applications. In: Proceedings of the 11th International Symposium on Web Systems Evolution (WSE 2009), pp. 41–50. IEEE Computer Society, Los Alamitos (2009)Google Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 2011

Authors and Affiliations

  • Anthony Cleve
    • 1
    • 3
  • Jean-Roch Meurisse
    • 2
  • Jean-Luc Hainaut
    • 1
  1. 1.Faculty of Computer Science, PReCISE Research CenterUniversity of NamurBelgium
  2. 2.Department of Education and TechnologyUniversity of NamurBelgium
  3. 3.Department of Information and Communication SciencesUniversité Libre de BruxellesBelgium

Personalised recommendations