On Query-Based Search of Possible Design Flaws of SQL Databases

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


System catalog, which is a part of each SQL database, is a repository where the data in its base tables describes the SQL-schemas (schemas) in the database. The SQL standard specifies the Information Schema, which must contain virtual tables (views) that are created based on the base tables of the system catalog. In this paper, we investigate to what extent one can find information about possible design flaws of a SQL database by querying the tables in its Information Schema and possibly tables in its other schemas. We do this based on a set of SQL database design antipatterns, each of which presents a particular type of database design flaw.


SQL database SQL-schemas Design flaw Query-based search 


  1. 1.
    N. Moha, Y.-G. Gueheneuc, L. Duchien, and A.-F. Le Meur, “DECOR: A Method for the Specification and Detection of Code and Design Smells,” IEEE Transactions on Software Engineering, vol. 36, issue 1, pp. 20-36, Jan.-Feb. 2010.Google Scholar
  2. 2.
    B. Karwin, SQL Antipatterns. Avoiding the Pitfalls of Database Programming, The Pragmatic Bookshelf, 2010.Google Scholar
  3. 3.
    M. Blaha, “A retrospective on industrial database reverse engineering projects – part 2,” Proc. Eighth Working Conference on Reverse Engineering, 2001, pp. 147–153.Google Scholar
  4. 4.
    C. J. Date, SQL and Relational Theory. How to Write Accurate SQL Code, USA: O’Reilly, 2009.Google Scholar
  5. 5.
    R. Stephens, Beginning Database Design Solutions, New York: Wiley Publishing, Inc., 2008.Google Scholar
  6. 6.
    L. Rising. (2012, July 27). Patterns Almanac 2000 [Online]. Available:
  7. 7.
    A. Cleve. (2012, July 27). “Co-Evolution of Databases and Programs,” IPA Fall Days 2008. [Online]. Available:
  8. 8.
    S. W. Ambler and P. J. Sadalage, Refactoring Databases: Evolutionary Database Design, Addison-Wesley, 2006.Google Scholar
  9. 9.
    IWD 9075-11:201?(E) Information technology — Database languages — SQL — Part 11: Information and Definition Schemas (SQL/Schemata). 2011–12–21.Google Scholar
  10. 10.
    O. Ciupke, “Automatic detection of design problems in object-oriented reengineering,” Proc. TOOLS 30, 1999, pp. 18-32.Google Scholar
  11. 11.
    SQL> shutdown abort .com (2012, September 23). [Online]. Available:
  12. 12.
    M. Salehie, S. Li, and L. Tahvildari, “A Metric-Based Heuristic Framework to Detect Object-Oriented Design Flaws,” Proc. 14th IEEE International Conference on Program Comprehension, 2006, pp. 159–168.Google Scholar
  13. 13.
    D. Beyer, A. Noack, and C. Lewerentz, “Efficient Relational Calculation for Software Analysis,” IEEE Transactions on Software Engineering, vol. 31, no. 2, pp 137-149, Feb. 2005.CrossRefGoogle Scholar
  14. 14.
    Oracle SQL Developer. (2012, July 26). [Online]. Available:
  15. 15.
    D. H. Akehurst, B. Bordbar, P. J. Rodgers, and N. T. G, Dalgliesh, “Automatic Normalisation via Metamodelling,” Proc. ASE 2002 Workshop on Declarative Meta Programming to Support Software Development, September 2002.Google Scholar
  16. 16.
    Y. Huhtala, J. Kärkkäinen, P. Porkka, and H. Toivonen, “TANE: An Efficient Algorithm for Discovering Functional and Approximate Dependencies,” The Computer Journal, vol. 42, issue 2, pp. 100–111, 1999.Google Scholar
  17. 17.
    P. Andritsos, R. J. Miller, and P. Tsaparas, “InformationTheoretic Tools for Mining Database Structure from Large Data Sets,” Proc. SIGMOD ‘04, 2004, pp.731-742.Google Scholar
  18. 18.
    G. Bruno, P. Garza, E. Quintarelli, and R. Rossato, “Anomaly Detection in XML Databases by Means of Association Rules,” Proc. 18th International Workshop on Database and Expert Systems Applications, 2007, pp. 387–391.Google Scholar
  19. 19.
    S. Chaudhuri, V. Narasayya, “Self-Tuning Database Systems: A Decade of Progress,” Proc.33rd International Conference on Very Large Data Bases, 2007, pp. 3–14.Google Scholar
  20. 20.
    U. Hustadt, “Do we need the closed-word assumption in knowledge representation?” in Working Notes of the KI’94 Workshop: Reasoning about Structured Objects: Knowledge Representation Meets Databases, volume D-94-11of Document, 1994, pp. 24–26Google Scholar
  21. 21.
    PostgreSQL 9.2 Documentation. (2012, September 24). [Online]. Available:
  22. 22.
    IWD 9075-2:201?(E) Information technology — Database languages — SQL — Part 2:Foundation (SQL/Foundation). 2011–12-21.Google Scholar

Copyright information

© Springer International Publishing Switzerland 2015

Authors and Affiliations

  1. 1.Department of InformaticsTallinn University of TechnologyTallinnEstonia

Personalised recommendations