SQL Path Expressions for Database Query Simplification
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database languages, such as SQL, are complex and inefficient for querying relational databases, especially for developers who are not experts in SQL programming, leading to a fragmented environment where information cannot be shared across different higher-level models, resulting in redundant information and increased overhead.
Innovation Solution
The enhancement of database languages by incorporating entity-relationship models (ERMs) with structured types and named associations, allowing path expressions, filter expressions, and shorthand notation to simplify query statements, thereby improving conciseness and comprehensiveness.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If standard SQL is used for database querying, then data retrieval can be performed on relational databases, but the language becomes complex and difficult for non-expert developers to use
Solution Approach 1:
The patent introduces an intermediary layer (extended SQL syntax with path expressions and associations) between the simple user intent and the complex underlying relational database operations. This intermediary syntax allows developers to express queries in a more intuitive way while the system handles the translation to standard SQL operations, effectively mediating between ease of use and implementation complexity.
Solution Approach 2:
The patent changes the parameterization of SQL queries by allowing direct specification of navigation paths and associations in the query syntax itself, rather than requiring complex JOIN operations. This parameter change enables developers to think in terms of data relationships (associations) rather than table connections, simplifying the query construction process.
2Ease of operation
If higher-level models (OData EDM, ERM, JPA, etc.) are introduced to simplify data access, then data models become more comprehensible, but information cannot be shared across different models leading to fragmentation
Solution Approach 1:
The patent creates a universal query syntax that can represent multiple higher-level modeling concepts (associations, path expressions, filters) within a single framework. This universal syntax allows the same query language to work across different data models and applications, enabling information sharing while maintaining the comprehensibility benefits of higher-level models.
Solution Approach 2:
The patent segments the complex task of cross-model information sharing into manageable components: path expressions for navigation, associations for relationships, and filters for constraints. This segmentation allows each component to be independently defined and reused across different models, facilitating interoperability while maintaining model-specific comprehensibility.
3Adaptability or versatility
If multiple higher-level models are used to contribute additional information, then data models become more expressive, but redundant information is provided across applications increasing overhead
Solution Approach 1:
The patent merges the functionality of multiple higher-level models into a single unified syntax framework. By combining path expressions, associations, and filters into one language, the system eliminates redundant information representation across different models while preserving the expressiveness needed for complex data relationships and constraints.
Data Source
AI summary
Disclosed are techniques employed alone or in combination, to further enhance a database language and improve conciseness and comprehensiveness of query statements in application code. Embodiments may build upon an initial extension of database language to accommodate concepts of entity-relationship models (ERMs). That initial extension may include entities with structured types, and named associations. One technique modifies SQL to allow the SELECT command to specify element path expressions based upon a relationship (e.g. named associations between ERM elements). Another technique modifies SQL to introduce filter expressions suffixed to associations within path expressions, thus improving adherence to the principle of locality within query statements. Yet another technique allows a shorthand to “factor out” association path prefixes, thereby avoiding lengthy lists of path expressions in order to read several elements from the same nested structure or a same target entity behind a single association. Other techniques simplifying database querying are also disclosed.