1 / 28

A Graph-Based Framework for Representing SQL Constructs

This paper presents HECATAEUS, a graph-based modeling framework that uniformly represents SQL constructs such as relational tables, views, database constraints, and SQL queries. The framework enhances the visualization and analysis of database structures, aiding database administrators and designers. By employing directed labeled graphs, it captures the complexity of database schemas and facilitates performance evaluation and quality assessment. A case study involving the TPC-C benchmark demonstrates the framework’s practical implementation and potential future applications in database design.

cleantha-thanos
Télécharger la présentation

A Graph-Based Framework for Representing SQL Constructs

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. HECATAEUS A Framework for Representing SQL Constructs as Graphs George Papastefanatos1, Kostis Kyzirakos1, Panos Vassiliadis2, Yannis Vassiliou1 1 National Technical University of Athens-KDBMS Laboratory {gpapas,kkyzir,yv}@dbnet.ece.ntua.gr 2Universityof Ioannina {pvassil@cs.uoi.gr}

  2. Outline • Background & Motivation • A Graph-based Model for SQL Constructs • Implementation of the Framework • Case study: Modeling TPC-C benchmark • Conclusions and Future Work G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  3. Outline • Background & Motivation • A Graph-based Model for SQL Constructs • Implementation of the Framework • Case study: Modeling TPC-C benchmark • Conclusions and Future Work G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  4. Contribution • A graph modeling framework that uniformly covers relational tables, views, database constraints and SQL queries • An implementation of the framework, named HECATAEUS. G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  5. Why bother modeling SQL queries as graphs? • Visualization and analysis of both the structure and the workload of the system • Forecasting of the impact of changes • Evaluation of the quality of the database design • Framework stakeholders • Database Administrators • Database Designers / Developers G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  6. Motivation Queries • Evolution • Which part(s) of the system is (are) affected if we delete an attribute and how? • Performance • Which part(s) of the system has (have) the most workload? • Database Analysis and Quality Evaluation • Which tables are correlated the most ? • Is design A better than design B and why? G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  7. Background • Traditional database modeling techniques, like ER diagrams,focus on conceptual level modeling. • Visual Query Languages – Visualization techniques • Graph-based • Icon-Based • Diagram-Based G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  8. Outline • Background & Motivation • A Graph-based Model for SQL Constructs • Implementation of the Framework • Case study: Modeling TPC-C benchmark • Conclusions and Future Work G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  9. Graph Model • A graph modeling technique that uniformly covers relational tables, views, database constraints and SQL queries • Directed Labeled Graph • Relations • Conditions • Queries/Views G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  10. Graph Model (continued) • The proposed modeling technique represents all the aforementioned database parts as a directed labeled graph with specific types of nodes and edges for each database part and relationship. • Graphs are employed as a modeling technique because they can address the large size and complexity that characterize a database schema. G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  11. Representing Relations • Relation: EMP(Empid, Name, Salary) G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  12. Primary Key on EMPID Query condition SELECT EMPID, NAME FROM EMP WHERE AGE>30 AND SALARY>5000; Representing Conditions G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  13. Representing Queries SELECT EMP.EMPID,Sum(WORKS.Hours) as T_Hours FROM EMP, WORKS WHERE EMP.EMPID = WORKS.EMPID GROUPBY EMP.EMPID; G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  14. Representing Queries - SELECT Subgraph SELECTEMP.EMPID,Sum(WORKS.Hours) as T_Hours G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  15. Representing Queries – FROM Subgraph FROMEMP,WORKS G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  16. Representing Queries - WHERE Subgraph WHEREEMP.EMPID = WORKS.EMPID G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  17. Representing Queries - GROUPBY Subgraph GROUPBY EMP.EMPID G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  18. Outline • Background & Motivation • A Graph-based Model for SQL Constructs • Implementation of the Framework • Case study: Modeling TPC-C benchmark • Conclusions and Future Work G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  19. HECATAEUS • Input • Database schemas(DDL Files) • Queries (SQL scripts) • Representation in the proposed graph model • Dynamic Graph – Holds all the semantics for the types of nodes and edges G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  20. HECATAEUS Functionality • Different views of the graph(zoom in zoom out) • Subgraph Isolation/Highlighting • Manipulation of the graph components(e.g. add edge/ remove node) • Output graph to various formats (PostScript, SVG) • Definition and application of graph algorithms and metrics G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  21. Architecture • Parser • Graph Manager • Metrics Manager • Catalog G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  22. Development Platform • MS Visual Studio .NETPlatform • Parser and Catalog: SharpHSQL, an open-source database inC#. • Graph Manager and Metrics Manager:LEDAC++ libraries for .NET technology G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  23. Outline • Background & Motivation • A Graph-based Model for SQL Constructs • Implementation of the Framework • Case study: Modeling TPC-C benchmark • Conclusions and Future Work G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  24. Demo – Modeling TPC-C Benchmark G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  25. Outline • Background & Motivation • A Graph-based Model for SQL Constructs • Implementation of the Framework • Case study: Modeling TPC-C benchmark • Conclusions and Future Work G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  26. Conclusions • Contributions • A graph modeling framework that uniformly covers relational tables, views, database constraints and SQL queries • An implementation of the framework, named HECATAEUS. • Potential Benefits • Visualization of both the structure and the workload of the system • Evaluation of quality of Database Design • Forecasting of the impact of changes G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  27. Future Work - Research in Progress • Definition of Metrics and Algorithms for Evolution • Definition of Metrics for Design Quality • Application of graph-theoretic metrics and algorithms • Reverse functionality. Automatic DDL and SQL code creation from graphs • Integration of Hecataeus with a Repository G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

  28. Thank you! • Many thanks to our hosts! (Muitos agradecimentos a nossos anfitriões!) • Any questions? G.Papastefanatos, K.Kyzirakos, P.Vassiliadis, Y. Vassiliou. A Framework for Representing SQL Constructs as Graphs EMMSAD’05, Porto, 2005

More Related