Method and system for database join disambiguation

• US 7,529,730 B2
• Filed: 02/12/2003
• Issued: 05/05/2009
• Est. Priority Date: 02/12/2002
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Seeds, Glen Michael, Cazemier, Henk, Potter, Charles Mike
• Primary Examiner(s): Alam; Hosain T
• Assistant Examiner(s): Saeed; Usmaan
• Application Number:
  US10/366,261
• Publication Number:
  US 20030217048A1
• Time in Patent Office: 2,274 Days
• Field of Search: 707/3, 707/2, 707/4, 707/5, 707/6, 707/10, 707/100, 707/101
• US Class Current: 1/1
• 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/2452 Query translation
• CPC Class Codes: G06F 16/24564 Applying rules; Deductive queries
• CPC Class Codes: G06F 16/2468 Fuzzy queries
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

In cases where two or more database tables can be joined by more than one relationship or criterion on the row values of selected columns, to produce what is effectively a wider table, there may result an ambiguity of which join should be used. The invention provides a means of join disambiguation that is rule-driven. The user is relieved of understanding the details of how the disambiguation is being done. The system described in this invention makes the choice of which join to use based on clear transformation rules which relate to folders of the tables and their relationships.

Claims

1. A computer-implemented method of accessing information in a database, the database having a plurality of query subjects and a plurality of relationship paths between the plurality of query subjects, and creating a report comprising:

organizing the plurality of query subjects and the plurality of relationship paths between the plurality of query subjects into one or more subject area groupings, the one or more subject area groupings being arranged in a hierarchical tree structure;

accepting from a user an indication of elements required in the report to be retrieved from one or more of the plurality of query subjects in the database;

creating a high level query specifying query elements needed to retrieve the elements required in the report;

determining that the high level query includes a plurality of join relationship paths between two query subjects that makes the high level query ambiguous;

identifying a sub-tree of the hierarchical tree structure, the identified sub-tree having a root node comprising a subject area grouping, the subject area grouping including a relationship path joining the two query subjects that are making the high level query ambiguous;

selecting the relationship path of the subject area grouping in the root node of the identified sub-tree;

disambiguating the high level query to create an unambiguous database query joining the two query subjects using the selected relationship path of the subject area grouping in the root node of the identified sub-tree;

querying, using a computer, the database with the unambiguous database query; and

creating the report based on a result of the querying the database with the unambiguous database query.

2. The method as claimed in claim 1, wherein organizing the plurality of query subjects and the plurality of relationship paths includes:

identifying query subjects with a plurality of relationship paths;

including a first identified query subject of the identified query subjects in a first subject area grouping;

including a joinable query subject that is joinable to the first identified query subject; and

including a relationship path of the plurality of relationship paths joining the first identified joinable query subject to the joinable query subject in the grouping.

3. The method as claimed in claim 2, further comprising:

including the grouping in a further hierarchical grouping;

including a second query subject in the further grouping that is joinable to a query subject included in the grouping; and

including a further relationship joining the second query subject to the query subject included in the grouping.

4. The method as claimed in claim 1, further comprising:

creating query subject shortcuts for the query subjects;

creating relationship shortcuts for the relationship paths between the query subjects; and

using the query subject shortcuts and relationship shortcuts as the query subjects and the relationship paths.

5. The method as claimed in claim 1, further comprising:

creating query subject copies for the query subjects;

creating relationship copies for the relationship paths between the query subjects; and

using the query subject copies and relationship copies as the query subjects and the relationships.

6. The method as claimed in claim 1, wherein the database comprises a plurality of databases.

7. The method as claimed in claim 1, wherein disambiguating the high level query further comprises:

selecting a further relationship to form a collection of relationships, each relationship of the collection of relationships connecting two query subjects, where the collection of relationships is based on the relationships located in the smallest common sub-tree that contain the elements of the query subjects required in the report.