US 5,584,024 AGranted

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

AssigneeSoftware AG

InventorSteven P. Shwartz

Life of this patent

Estimating the term…

Loading patent…

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.

Claims27

Dependent claims

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.

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.

4 more elements

Dependencies are inferred from the claim text.