Advertisement

Using SQL Queries to Evaluate the Design of SQL Databases

  • Erki EessaarEmail author
  • Janina Voronova
Conference paper
Part of the Lecture Notes in Electrical Engineering book series (LNEE, volume 312)

Abstract

The system catalog of a database with explicit schemas contains among other things information about the structure of the database. Queries based on the system catalog allow us to search occurrences of database design antipatterns (database design flaws). In this paper, we present the results of an evaluation of a set of SQL databases. We used the queries that were presented in the previous paper on this topic. A goal of the research is to further experimentally evaluate the queries. We present findings about the queries as well as evaluated databases. In addition, we propose more questions about the design of conceptual schemas of SQL databases that can be answered by querying their system catalogs. The use of the queries would allow us to partially automate the process of evaluating structure and constraints of existing databases and detecting design flaws.

Keywords

Antipattern Database design Metadata SQL System catalog 

References

  1. 1.
    E. Eessaar, “On Query-based Search of Possible Design Flaws of SQL Databases,” in Proc. Int. Conf. on Systems, Computing Sciences & Software Engineering (SCSS 12), in press.Google Scholar
  2. 2.
    B. Karwin, SQL Antipatterns. Avoiding the Pitfalls of Database Programming, The Pragmatic Bookshelf, 2010, pp. 15-155.Google Scholar
  3. 3.
    IWD 9075-11:201?(E) Information technology — Database languages — SQL — Part 11: Information and Definition Schemas (SQL/Schemata). 2011-12-21.Google Scholar
  4. 4.
    “PostgreSQL 9.2 Documentation,” [Online]. Available: http://www.postgresql.org/docs/
  5. 5.
    C.J. Date, An Introduction to Database Systems, 8th ed.. Boston: Pearson/Addison Wesley, 2003.Google Scholar
  6. 6.
    IWD 9075-2:201?(E) Information technology — Database languages — SQL — Part 2:Foundation (SQL/Foundation). 2011-12-21.Google Scholar
  7. 7.
    E. Eessaar, “On Using a Semiotic Quality Framework to Evaluate the Quality of Conceptual Database Schemas,” in Proc. Int. Conf. on Systems, Computing Sciences & Software Engineering (SCSS 10), pp. 103–115.Google Scholar
  8. 8.
    “Rational Rose,” [Online]. Available: http://www.ibm.com/developerworks/rational/products/rose/
  9. 9.
    S. Lightstone, T. Teorey, and T. Nadeau, Physical Database Design. The Database Professional’s Guide to Exploiting Indexes, Views, Storage, and More. Elsevier, 2007, ch. 12.Google Scholar
  10. 10.
    “Parasoft,” [Online]. Available: http://www.parasoft.com/
  11. 11.
    C.J. Date, SQL and Relational Theory. How to Write Accurate SQL Code. O’Reilly, 2009, ch. 4.Google Scholar
  12. 12.
    M. Piattini, C. Calero, H. Sahraoui, and H. Lounis, “Object-Relational Database Metrics,” L’Object, 2001.Google Scholar
  13. 13.
    M. Fowler. (2013, January 7). Schemaless Data Structures. [Online]. Available: http://martinfowler.com/articles/schemaless/
  14. 14.
    D.L. Moody, “Theoretical and practical issues in evaluating the quality of conceptual models: current state and future directions,” Data & Knowledge Engineering, vol. 55, pp. 243-276, 2005.Google Scholar
  15. 15.
    J. Carter and J. English, “How Shall we Assess This? ITiCSE’03 Working Group. Initial survey results,” [Online]. Available: http://www.cs.kent.ac.uk/people/staff/jec/assess/stats.html
  16. 16.
    P. Ihantola, T. Ahoniemi, V. Karavirta, and O. Seppälä, “Review of Recent Systems for Automatic Assessment of Programming Assignments,” in 2010 Proc. Koli Calling Conf., pp. 86-93.Google Scholar
  17. 17.
    S. Dekeyser, M. de Raadt, T.Y. Lee, “Computer Assisted Assessment of SQL Query Skills,” in 2007 Proc. ADC Conf.-Volume 63, pp. 53-62.Google Scholar
  18. 18.
    M. Piattini, C. Calero, and M. Genero, “Table Oriented Metrics for Relational Databases,” Software Quality Journal, vol. 9, pp. 79–97, 2001.Google Scholar
  19. 19.
    L. Rönnbäck, O. Regardt, M. Bergholtz, P. Johannesson, and P. Wohed “Anchor Modeling - Agile Information Modeling in Evolving Data Environments,” Data & Knowl. Eng., vol. 69, pp. 1229-1253, Dec. 2010.Google Scholar

Copyright information

© Springer International Publishing Switzerland 2015

Authors and Affiliations

  1. 1.Department of InformaticsTallinn University of TechnologyTallinnEstonia

Personalised recommendations