Join Graph Generation from Query Logs for Database Modeling
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Automated database modeling tools are limited in uncovering implicit information in databases, as they only explicitly map previously defined queries, failing to reveal the full understanding of data relationships and potential query formations.
Innovation Solution
A method and system for generating a join graph for relational database queries by parsing a log of previously executed queries, identifying semantically characterizable components, and selectively adding or merging generated join graphs into a data model, with frequency-based filtering and component analysis to create unique columns and mathematical operations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of information
If automated database modeling tools explicitly map previously defined queries, then the data model can be generated from existing query definitions, but implicit information and deeper data relationships remain undiscovered
Solution Approach 1:
The system performs preliminary action by analyzing query logs before generating the data model. It parses and processes historical query execution patterns in advance to extract implicit join relationships, table relationships, and data dependencies that are not explicitly defined in the database schema. This preliminary analysis enables the system to uncover hidden information and enrich the data model with discovered relationships.
Solution Approach 2:
The system implements feedback by using executed query logs as input to continuously improve the data model. By analyzing the actual query patterns that users execute against the database, the system receives feedback about real data relationships and usage patterns. This feedback loop allows the system to iteratively refine the data model, adding discovered join graphs and relationships that reflect actual data usage rather than just explicit definitions.
2Measurement precision
If the system parses and analyzes all query logs to discover implicit relationships, then complete understanding of data relationships is achieved, but processing time and computational resources increase
Solution Approach 1:
The system applies the extraction principle by isolating and focusing analysis on specific query components that reveal data relationships. Instead of processing entire query logs in detail, it extracts key elements such as join conditions, table references, and relationship patterns from the query statements. This selective extraction maintains comprehensive relationship discovery while reducing the computational burden of analyzing every aspect of every query.
Solution Approach 2:
The system uses partial action by analyzing a representative subset of query patterns rather than exhaustively processing every single query in the log. It identifies and focuses on the most significant query types and relationships that provide the greatest insight into data structure, accepting that not every possible relationship will be discovered but achieving sufficient understanding for practical data modeling purposes.
3Adaptability or versatility
If the data model includes only explicitly defined relationships, then the model remains simple and easy to maintain, but it lacks the depth needed for complex query formulation
Solution Approach 1:
The system introduces an intermediary layer between the explicit database schema and the query formulation process. This intermediary consists of discovered join graphs and inferred relationships that are added to the data model based on query log analysis. These intermediary elements bridge the gap between simple explicit definitions and complex query needs, providing additional relationship information that enables more sophisticated query formulation without directly modifying the underlying database schema.
Solution Approach 2:
The system applies nesting by embedding discovered relationships within the existing data model structure. The inferred join graphs and implicit relationships are nested within the framework of explicitly defined tables and columns, creating a multi-layered model where simple explicit definitions form the base layer and discovered implicit relationships form additional nested layers that provide enhanced query capabilities while maintaining the original schema structure.
Data Source
AI summary
A method, system and computer program product for join graph generation based upon a log of previously executed database queries includes method for generating a join graph for relational database queries. The method includes loading into memory of a computer, a log of a set of database queries previously executed against data in a database and sequentially parsing each of the queries in the log to identify different semantically characterizable components of each of the queries. The method further includes generating a join graph for each of the queries from corresponding ones of the components. Finally, the method includes selectively adding each of the generated join graphs to a set of join graphs in a data model for the data in the database.