Nothing Special   »   [go: up one dir, main page]
Skip to main content
Springer Nature Link
Log in

Database Semantics Recovery through Analysis of Dynamic SQL Statements

  • Chapter
Journal on Data Semantics XV

Part of the book series: Lecture Notes in Computer Science ((JODS,volume 6720))

Abstract

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.

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

Access this chapter

Log in via an institution

Subscribe and save

Springer+ Basic
$34.99 /Month
  • Get 10 units per month
  • Download Article/Chapter or eBook
  • 1 Unit = 1 Article or 1 Chapter
  • Cancel anytime
Subscribe now

Buy Now

eBook
USD 15.99
Price excludes VAT (USA)
  • Available as PDF
  • Read on any device
  • Instant download
  • Own it forever
Softcover Book
USD 15.99
Price excludes VAT (USA)
  • Compact, lightweight edition
  • Dispatched in 3 to 5 business days
  • Free shipping worldwide - see info

Tax calculation will be finalised at checkout

Purchases are for personal use only

Institutional subscriptions

Preview

Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.

Similar content being viewed by others

References

  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)

    Article  Google Scholar 

  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. 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. 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. 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)

    Chapter  Google Scholar 

  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)

    Article  MathSciNet  Google Scholar 

  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)

    Chapter  Google Scholar 

  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)

    Chapter  Google Scholar 

  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. 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)

    Chapter  Google Scholar 

  11. Hainaut, J.L.: Introduction to database reverse engineering. LIBD Publish (2002), http://www.info.fundp.ac.be/~dbm/publication/2002/DBRE-2002.pdf

  12. Markowitz, V.M., Makowsky, J.A.: Identifying extended entity-relationship object structures in relational schemas. IEEE Trans. Softw. Eng. 16, 777–790 (1990)

    Article  Google Scholar 

  13. Premerlani, W.J., Blaha, M.R.: An approach for reverse engineering of relational databases. Commun. ACM 37(5), 42 (1994)

    Article  Google Scholar 

  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)

    Article  Google Scholar 

  15. Lopes, S., Petit, J.M., Toumani, F.: Discovering interesting inclusion dependencies: application to logical database tuning. Inf. Syst. 27, 1–19 (2002)

    Article  MATH  Google Scholar 

  16. Yao, H., Hamilton, H.J.: Mining functional dependencies from data. Data Min. Knowl. Discov. 16(2), 197–219 (2008)

    Article  MathSciNet  Google Scholar 

  17. Pannurat, N., Kerdprasop, N., Kerdprasop, K.: Database reverse engineering based on association rule mining. CoRR abs/1004.3272 (2010)

    Google Scholar 

  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)

    Chapter  Google Scholar 

  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. Henrard, J.: Program Understanding in Database Reverse Engineering. PhD thesis, University of Namur (2003)

    Google Scholar 

  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)

    Chapter  Google Scholar 

  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)

    Article  Google Scholar 

  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)

    Chapter  Google Scholar 

  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)

    Chapter  Google Scholar 

  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)

    Chapter  Google Scholar 

  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. 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)

    Chapter  Google Scholar 

  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. 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)

    Chapter  Google Scholar 

  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)

    Article  Google Scholar 

  31. Cleve, A.: Program Analysis and Transformation for Data-Intensive System Evolution. PhD thesis, University of Namur (October 2009)

    Google Scholar 

  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)

    Chapter  Google Scholar 

  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)

    Chapter  Google Scholar 

  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. 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)

    Article  Google Scholar 

  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)

    Chapter  Google Scholar 

  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)

    Article  Google Scholar 

  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)

    Article  Google Scholar 

  39. Codd, E.F.: A relational model of data for large shared data banks. Commun. ACM 13(6), 377–387 (1970)

    Article  MATH  Google Scholar 

  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. DB-MAIN: The DB-MAIN official website (2011), http://www.db-main.be

  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. Zhu, H., Hall, P.A.V., May, J.H.R.: Software unit test coverage and adequacy. ACM Comput. Surv. 29, 366–427 (1997)

    Article  Google Scholar 

  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. 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)

    Chapter  Google Scholar 

  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)

    Chapter  Google Scholar 

  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 

Download references

Author information

Authors and Affiliations

  1. Faculty of Computer Science, PReCISE Research Center, University of Namur, Belgium

    Anthony Cleve & Jean-Luc Hainaut

  2. Department of Education and Technology, University of Namur, Belgium

    Jean-Roch Meurisse

  3. Department of Information and Communication Sciences, Université Libre de Bruxelles, Belgium

    Anthony Cleve

Authors
  1. Anthony Cleve
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Jean-Roch Meurisse
    View author publications

    You can also search for this author in PubMed Google Scholar

  3. Jean-Luc Hainaut
    View author publications

    You can also search for this author in PubMed Google Scholar

Editor information

Editors and Affiliations

  1. EPFL-IC – Database Laboratory, Lausanne, Switzerland

    Stefano Spaccapietra

Rights and permissions

Reprints and permissions

Copyright information

© 2011 Springer-Verlag Berlin Heidelberg

About this chapter

Cite this chapter

Cleve, A., Meurisse, JR., Hainaut, JL. (2011). Database Semantics Recovery through Analysis of Dynamic SQL Statements. In: Spaccapietra, S. (eds) Journal on Data Semantics XV. Lecture Notes in Computer Science, vol 6720. Springer, Berlin, Heidelberg. https://doi.org/10.1007/978-3-642-22630-4_5

Download citation

  • DOI: https://doi.org/10.1007/978-3-642-22630-4_5

  • Publisher Name: Springer, Berlin, Heidelberg

  • Print ISBN: 978-3-642-22629-8

  • Online ISBN: 978-3-642-22630-4

  • eBook Packages: Computer ScienceComputer Science (R0)

Publish with us

Policies and ethics

Search

Navigation