Method and apparatus for automatic table selection and generation of structured query language instructions

• US 5,519,859 A
• Filed: 11/15/1993
• Issued: 05/21/1996
• Est. Priority Date: 11/15/1993
• Status: Expired due to Term

• Current Assignee: John A. Grace
• Original Assignee: John A. Grace
• Inventors: Grace, John A.
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): Loomis, John C.
• Application Number:
  US08/153,136
• Time in Patent Office: 918 Days
• Field of Search: 395/600
• US Class Current: 707/690
• CPC Class Codes: G06F 16/2428 Query predicate definition using graphical user interfaces, including menus and forms G06F16/2423 takes precedence
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/954 Relational
• CPC Class Codes: Y10S 707/957 Multidimensional
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

A method and apparatus is provided for generating structured query language instructions for communication to a database. In response to receipt of user-supplied input dimensions, an output table is automatically selected that represents the user'"'"'s query of the relational database. The system automatically identifies and generates the links between data in predefined data tables within the database and the automatically selected output table. From the output table and data links, structured query language instructions are automatically generated. A technique is included to select the minimum number of tables reflective of the input dimensions. Each link between the input data tables and the output table is checked bi-directionally to ensure the integrity of the table selected.

Claims

1. A method for providing a plurality of query instructions to a database, comprising the steps of:

receiving a plurality of input dimensions for selectively obtaining relational information between data in the database;

checking at least one driver table for relational information about the database;

automatically locating an intersection of common data between the plurality of input dimensions; and

generating a plurality of query instructions for communication to the database, the plurality of query instructions representing the relational query between the plurality of input dimensions.

2. The method defined in claim 1, wherein the plurality of query instructions comprise structured query language instructions.

3. The method defined in claim 2, wherein the plurality of structured query language instructions generated are compatible with the database.

4. The method defined in claim 1, wherein the at least one driver table comprises a plurality of driver tables.

5. The method defined in claim 4, further comprising the step of detecting the existence of relevant facts from identified data table, the relevant facts based upon a predefined criteria.

6. The method defined in claim 5, further comprising the step of generating at least one link between a first relevant fact within the input dimension and a second relevant fact within the identified data table.

7. The method defined in claim 6, further comprising the step of checking the integrity of the at least one link for consistency between the input dimension and data in the at least one data table.

8. The method defined in claim 6, further comprising the step of providing to a user a visual representation of the at least one link.

9. The method defined in claim 1, further comprising the step of defining at least one function for measurement based upon the predefined criteria.

10. The method defined in claim 1, wherein the plurality of input dimensions is supplied by a user.

11. The method defined in claim 10, wherein the user-supplied input dimensions are received through a multi-dimensional user interface.

12. The method defined in claim 11, wherein the multi-dimensional user interface receives input dimensions through a Cartesian coordinate input graph.

13. The method defined in claim 1, wherein the step of automatically locating the intersection comprises identifying a minimum subset of data tables.

14. The method defined in claim 1, further comprising the step of dynamically selecting at least one output table representative of the intersection.

15. A method for providing a plurality of query instructions to a database, comprising the steps of:

receiving a plurality of user-supplied input dimensions to query the database, the user-supplied input dimensions for selectively obtaining relational information between data in the database;

checking at least one driver table for relational information about the database;

identifying a subset of data tables from the plurality of data tables, the subset of data tables corresponding to the user-supplied input dimensions;

automatically locating an intersection of common data between the subset of data tables;

dynamically selecting at least one output table representative of the intersection of the common data between the subset of data tables;

generating at least one link between a first data within the subset of data tables and a corresponding data within the at least one output table;

automatically checking the integrity of the at least one link to confirm the existence of the first data in the output table; and

generating a plurality of query instructions for communication to the database, the plurality of query instructions representing the relationship identified through the at least one link between the user-supplied input dimensions.

16. A system for providing a plurality of query instructions for communication to a database having a plurality of data stored in the database, comprising:

input logic operable to receive a plurality of input dimensions, each input dimension representing data stored in the database;

means coupled to the input logic for automatically identifying an intersection of data common to the plurality of input dimensions; and

an output coupled to the database, the output for communicating a plurality of query instructions to the database, the plurality of query instructions developed from the intersection of common data.

17. The system defined in claim 16, further comprising means for generating at least one link between the common data from the input dimensions.

18. The system defined in claim 17, further comprising means for checking the integrity of the at least one link for consistency between data associated with the input dimensions.

19. The system defined in claim 18, further comprising a display, the display for providing a visual representation of the at least one link.

20. The system defined in claim 19, wherein the display comprises a multi-dimensional user interface.

21. The system defined in claim 20, wherein the multi-dimensional user interface comprises a Cartesian coordinate graphical input.

22. The system defined in claim 16, wherein the input logic is operable to receive constraints on the input dimensions.

23. The system defined in claim 16, wherein the input logic is operable to receive at least one predetermined function for measurement based upon the predefined criteria.

24. A system for providing a plurality of structured query language instructions for communication to a database having a plurality of data tables for organizing data stored in the database based upon a predefined criteria, comprising:

a memory for storing at least one driver table providing a translation of the relationship of the data tables in the database;

means coupled to the memory for providing an indication of the data tables stored in the database and communicating such indication to a user;

input logic coupled to the memory, the input logic operable to receive a plurality of user-supplied input dimensions, each input dimension identifying at least one data table stored in the database;

a display coupled to the input logic, the display for providing a visual representation of the plurality of input dimensions;

means coupled to the input logic for automatically identifying an intersection of data common to the plurality of input dimensions and generating at least one intersection table of common data in response to the identification;

means coupled to the display for generating at least one link identifying the common data between the identified input dimensions and displaying the at least one link on the display; and

an output coupled to the database, the output for communicating a plurality of structured query language instructions to query the database for requested data, the plurality of structured query language instructions developed from the generation of the at least one link.