Interactive database query system and method for prohibiting the selection of semantically incorrect query parameters

• US 5,584,024 A
• Filed: 03/24/1994
• Issued: 12/10/1996
• Est. Priority Date: 03/24/1994
• Status: Expired due to Fees

• Current Assignee: Speedware Limitee/Limited
• Original Assignee: Software AG
• Inventors: Shwartz, Steven P.
• Primary Examiner(s): Amsbury, Wayne
• Assistant Examiner(s): Choules, Jack M.
• Application Number:
  US08/217,099
• Time in Patent Office: 992 Days
• Field of Search: 395/600, 395/922, 364/419.01, 364/419.07, 364/974.6, 364/972.2, 364/274.2, 364/274.7, 364/275.1, 364/275.4, 364/283.3, 364/282.1
• 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/243 Natural language query formulation
• CPC Class Codes: Y10S 706/922 Computer program preparation
• CPC Class Codes: Y10S 706/934 Information retrieval or Information management
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

A database query system includes a query assistant that permits the user to enter only queries that are both syntactically and semantically valid (and that can be processed by an SQL generator to produce semantically valid SQL). Through the use of dialog boxes, a user enters a query in an intermediate English-like language which is easily understood by the user. A query expert system monitors the query as it is being built, and using information about the structure of the database, it prevents the user from building semantically incorrect queries by disallowing choices in the dialog boxes which would create incorrect queries. An SQL generator is also provided which uses a set of transformations and pattern substitutions to convert the intermediate language into a syntactically and semantically correct SQL query.

The intermediate language can represent complex SQL queries while at the same time being easy to understand. The intermediate language is also designed to be easily converted into SQL queries. In addition to the query assistant and the SQL generator, an administrative facility is provided which allows an administrator to add a conceptual layer to the underlying database making it easier for the user to query the database. This conceptual layer may contain alternate names for columns and tables, paths specifying standard and complex joins, definitions for virtual tables and columns, and limitations on user access.

Claims

1. A database query system for interactively creating, with a user, a syntactically and semantically correct query for a relational database having a plurality of tables, each of said tables having a plurality of columns and having a predetermined relationship to another of said tables, said system comprising:

a conceptual layer manager for storing conceptual information about the relational database, said conceptual information including structural information concerning the identity of each of the tables and columns and the directionality and cardinality of the relationships between the tables;

a query assistant user interface ("QAUI") presenting to the user a selectable table set of selectable tables from among the tables in the database, a selectable column set of selectable columns from among the columns of each of said tables in the database, and a selectable column operations set of selectable column operations on the columns, from which the user may select tables, columns, and column operations to construct a database query for said database in an intermediate query language, said QAUI further accepting from the user selections of tables, columns, and column operations;

a query assistant expert ("QAES") coupled to said QAUI to receive from said QAUI the identity of each table, column, or column operation selected by the user, said QAES returning to the QAUI after each selection by the user an updated version of said selectable table set, said selectable column set, and said selectable column operations set, said QAES excluding from said selectable sets any table, column, or column operation which, if selected by the user, would, based on the then-current state of the database query and said conceptual information, produce a semantically incorrect query.

2. The database query system of claim 1 wherein said QAES includes:

a storage system for maintaining state information about the current state of a database query; and

a query expert logic system specifying to said QAUI said selectable sets by analyzing said state information maintained in said storage system and said conceptual information stored by said conceptual layer manager.

3. A database query system according to claim 2 wherein said storage system includes:

a set of state variables; and

a set of access routines for adding deleting and modifying said state variables.

4. A database query system according to claim 2 wherein said storage system includes:

a state database, said state database containing said state information; and

a set of database access routines for adding to, deleting from and modifying said state database.

5. A database query system according to claim 2 wherein said query expert logic system is composed of procedural logic.

6. A database query system according to claim 2 wherein said query expert logic system is a rule-based expert system.

7. A database query system according to claim 2 wherein said conceptual information further comprise one or more of the following:

foreign keys, table join paths, table join expression for non-equijoins, virtual table definitions, virtual column definitions, table descriptions, column descriptions, hidden tables, and hidden columns.

8. A database query system according to claim 2 wherein said conceptual information further comprises table join expression for non-equijoins.

9. The database query system of claim 2 wherein if said current state of said database query includes an aggregate column operation on a column in a first table, said query expert logic system excludes from said selectable table set any other of said tables that is more detailed than said first table or is joinable with said first table only through another more detailed table.

10. The system of claim 2 wherein the database includes at least four tables and wherein if in the then-current state of said database query two of the tables are selected, said query expert logic system excludes from said selectable table set any other of said tables that does not form in combination with said two selected tables a navigable set.

11. The system of claim 2 wherein said query expert logic system excludes from said selectable column set for any selected one of said tables any numeric column for which, if in said current state of said database query an aggregate column operation is applied to another column based on said selected table or based on another table having a one-to-one relationship with said selected table.

12. A database query system according to claim 2 wherein said conceptual information further comprises virtual column definitions.

13. A database query system according to claim 12 wherein said virtual column definition contains primary key and foreign key references to define a join operation.

14. A database query system according to claim 1 wherein said each of said selectable table set, said selectable column set, and said selectable column operation set is mutually exclusive to a corresponding nonselectable table set, nonselectable column set, and nonselectable column operation set and is a subset of all tables, columns, and column operations, respectively, maintained by said conceptual layer manager which the user may next select in building a semantically correct database query.

15. A database query system according to claim 14 wherein said QAUI indicates to the user said selectable sets set of permissible selections and not displaying said nonselectable sets.

16. A database query system according to claim 14 wherein said QAUI displays said nonselectable sets and visually differentiates said selectable sets from said nonselectable sets.

17. A database query system according to claim 16 wherein said QAUI visually differentiates said sets by color.

18. A database query system according to claim 16 wherein said QAUI visually differentiates said selectable and nonselectable sets by type characteristic.

19. The database query system of claim 1 further comprising a query generator coupled to said QAUI to receive from said QAUI a completed database query in said intermediate query language, said query generator converting said query from said intermediate query language into a target query language different from said intermediate query language.

20. A database query system according to claim 19 wherein said target query language is Structured Query Language (SQL).

21. A database query system according to claim 19 wherein said query generator converts said intermediate language query into said target language by a set of successive transformations.

22. A database query system according to claim 21 wherein at least one of said set of successive transformations is transformation by pattern substitution.

23. A database query system according to claim 21 wherein said set of transformations comprises:

a set of structural transformations;

a set of transformation to include inferred information; and

a set of transformations by pattern substitution.

24. A method for interactively building a syntactically and semantically correct query of a relational database from selections by a user, said database having a plurality of tables, each of said tables having a plurality of columns and having a predetermined relationship to another of said tables, said method comprising the steps of:

presenting to the user a selectable table set of selectable tables from among the tables in the database;

receiving from the user a selection of a first one of said selectable tables;

presenting to the user a selectable column set of selectable columns from among the columns based on said first selected table;

receiving from the user a selection of a first selected column from among said selectable columns;

presenting to the user a selectable column operation set of selectable column operations applicable to said first selected column;

receiving from the user a selection of one of said column operations;

presenting to the user a first updated version of said selectable table set from which the user may select a second selected table, said selectable table set excluding any table that is more detailed than said first selected table on which said selected column operation is applied or that is joinable with said first selected table only through a more detailed table if said selected column operation is an aggregate operation.

25. The method of claim 24 wherein said first updated version of said selectable table set further excludes any table that is not joinable with said first selected table.

26. The method of claim 25 wherein the database includes at least four tables and further comprising the steps of:

receiving from the user a selection of a second selected table from said first updated version of said selectable table set; and

presenting to the user a second updated version of said selectable table set from which the user may select a third selected table, said selectable table set excluding any table the selection of which does not form in combination with said first and second selected tables a navigable set.

27. The method of claim 26 further comprising the steps of:

receiving from the user a selection of a second selected table from said first updated version of said selectable table set; and

presenting to the user an updated version of said selectable column set based on said second selected table, said selectable columns set excluding any column that contains non-numeric information if said second selected table is the same as said first selected table or has a one-to-one relationship with said first selected table.