SQL query generator utilizing matrix structures

• US 6,578,028 B2
• Filed: 01/14/2002
• Issued: 06/10/2003
• Est. Priority Date: 12/30/1999
• Status: Expired due to Fees

• Current Assignee: Decode Genetics Ehf.
• Original Assignee: Decode Genetics Ehf.
• Inventors: Egilsson, Ágúst Sverrir, Sigurjónsson, Styrmir, Gudbjartsson, Hákon
• Primary Examiner(s): Alam, Hosain
• Assistant Examiner(s): VEILLARD, JACQUES
• Application Number:
  US10/047,989
• Publication Number:
  US 20020095430A1
• Time in Patent Office: 512 Days
• Field of Search: 707/1, 707/2, 707/3, 707/4, 707/5, 707/100, 707/101, 707/102, 707/103.R, 707/204
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2423 Interactive query statement specification based on a database schema
• CPC Class Codes: G06F 16/2448 for particular applications; for extensibility, e.g. user defined types
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure

Abstract

A graphical SQL query generator and query operators are disclosed. The query tool embeds matrix objects that are used for building and transforming SQL based queries, views, data cubes and other relations including “virtual” or calculated relations. The matrix objects trace the structure of a relation to its source data and thereby make the structure of complex multilayered database queries transparent to the end-user. The methods are especially useful in data warehousing where the buildup of fact tables in star or other schemas needs to be transparent to the user. Thus, a system is disclosed that is able to quickly reveal how data warehousing schemas and relations in SQL and similar relational type databases evolve from source data and each other into more complicated structures. It supports the creation and maintenance of complex database structures such as scientific studies and other decision support structures as well as simplifying ad-hoc querying of SQL databases. The methods disclosed are used to implement a more transparent and yet powerful relational algebra on top of the standard SQL relational algebra used in most SQL databases.

Claims

1. A query definition system for implementing relational algebra in a relational database, the system comprising:

(a) means for associating with a relation definition a dependency matrix containing entries representing dependencies between said relation and its subparts, each subpart representing another relation; and

(b) means for displaying, on a display device, structural information about said relation using the columns and rows and values from said dependency matrix; and

(c) means for modifying said relation by transforming its dependency matrix by applying an operator to the structure of said relation, said operator comprising at least one of;

a depth-of-field operator, and a blowup operator.

2. The system of claim 1, further including means for implementing a drill down algorithm by displaying, upon request, one or more dependency matrices associated with at least some of said subparts, thereby, quickly revealing the detailed structure of said relation.

3. The system of claim 1, further including means for creating, editing or modifying query or named table variable definitions stored by a database system by editing the entries and structure of their corresponding dependency matrices.

4. The system of claim 3, wherein the structure and entries of said dependency matrices and other associated query attributes are used to generate statements adhering to an SQL relational database standard.

5. The system of claim 1, wherein the structure and entries of said dependency matrices and other associated query attributes are generated from query definitions adhering to an SQL relational database standard.

6. The system of claim 1, further including means for creating, editing or modifying relation definitions, including calculated relations, by editing the entries and structure of their corresponding dependency matrices.

7. A query definition method for implementing relational algebra in a relational database, comprising:

(a) associating with a relation definition a dependency matrix containing entries representing dependencies between said relation and its subparts, each subpart representing another relation; and

(b) displaying, on a display device, structural information about said relation using the columns and rows and values from said dependency matrix; and

(c) modifying said relation by transforming its dependency matrix by applying an operator to the structure of said relation, said operator comprising at least one of;

a depth-of-field operator, and a blowup operator.

8. The method of claim 7, further including implementing a drill down algorithm by displaying, upon request, one or more dependency matrices associated with at least some of said subparts, thereby, quickly revealing the detailed structure of said relation.

9. The method of claim 7, further including creating, editing or modifying query or named table variable definitions stored by a database system by editing the entries and structure of their corresponding dependency matrices.

10. The method of claim 9, wherein the structure and entries of said dependency matrices and other associated query attributes are used to generate statements adhering to an SQL relational database standard.

11. The method of claim 7, wherein the structure and entries of said dependency matrices and other associated query attributes are generated from query definitions adhering to an SQL relational database standard.

12. The method of claim 7, further including creating, editing or modifying relation definitions, including calculated relations, by editing the entries and structure of their corresponding dependency matrices.