Query Graph Generation for Database Schema Complexity
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Complex database schemas, such as those with chasm traps and fan traps, can lead to unexpected query results due to double-counting and logical errors, making it challenging to conduct meaningful analysis and obtain accurate data from databases.
Innovation Solution
A system and method for query generation based on a logical data model, which involves accessing a join graph representing tables in a database, selecting a connected subgraph, generating multiple leaf queries, and creating a query graph to transform the query, ensuring proper joining and avoiding over-counting by inserting primary key columns for shared attribution dimension tables.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If complex database schemas with chasm traps and fan traps are queried using traditional methods, then the database can store vast quantities of data, but the query results become unexpected or counterintuitive due to double-counting and logical errors
Solution Approach 1:
The patent segments the complex query into multiple simpler leaf queries, each operating on a specific connected subgraph of the join graph. By dividing the query execution into discrete segments (leaf queries) that can be independently evaluated and then combined, the system avoids the double-counting issues that arise in complex schemas with chasm traps and fan traps.
Solution Approach 2:
The patent introduces a query graph as an intermediary representation between the original query and the database execution. This query graph serves as a mediator that transforms the complex query into a structured form with identified leaf queries and join paths, enabling accurate query execution by preventing logical errors before they occur during actual data retrieval.
2Ease of operation
If traditional query methods are used on complex schemas, then simple database operations can be performed, but the results suffer from double-counting and logical errors
Solution Approach 1:
The patent performs preliminary analysis of the join graph to identify connected subgraphs and determine the appropriate leaf queries before actual query execution. By pre-processing the query to establish the query graph structure and identify join paths in advance, the system ensures correct results without requiring complex runtime logic during query execution.
Solution Approach 2:
The system automatically generates the query graph and identifies leaf queries based on the database schema and input query, without requiring manual intervention or complex user configuration. The query transformation process is self-service, where the system handles the complexity of schema analysis and query decomposition autonomously, maintaining ease of operation while ensuring reliability.
3Measurement precision
If complex join graphs are analyzed to ensure accurate query results, then query accuracy improves, but the computational overhead and complexity of query processing increases
Solution Approach 1:
The patent segments the complex join graph analysis into manageable components by identifying connected subgraphs and their corresponding leaf queries. This segmentation reduces the overall complexity by breaking down the analysis task into smaller, independent sub-tasks that can be processed separately and then combined, making the query processing more tractable while maintaining accuracy.
Solution Approach 2:
The patent applies partial analysis by focusing only on the connected subgraphs relevant to the specific query rather than analyzing the entire join graph. By performing analysis only on the necessary portions of the schema that are actually referenced in the query, the system achieves accurate results with reduced computational overhead, avoiding unnecessary analysis of unrelated parts of the database schema.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
Systems and methods for query generation based on a logical data model are described. For example, methods may include accessing a first join graph representing tables in a database; receiving a first query that references data in two or more of the tables of the database; selecting a connected subgraph of the first join graph that includes the two or more tables referenced in the first query; generating multiple leaf queries that reference respective subject tables that are each a root table of the connected subgraph or a table including a measure referenced in the first query; generating a query graph that specifies joining of results from queries based on the multiple leaf queries to obtain a transformed query result; and invoking a transformed query on the database that is based on the query graph and the queries based on the multiple leaf queries to obtain the transformed query result.