Method for dynamically finding relations between database tables

• US 8,019,771 B2
• Filed: 11/30/2006
• Issued: 09/13/2011
• Est. Priority Date: 11/30/2006
• Status: Active Grant

• Current Assignee: Domo, Inc.
• Original Assignee: International Business Machines Corporation
• Inventors: Iwao, Inagaki
• Primary Examiner(s): Truong; Cam Y T
• Application Number:
  US11/565,017
• Publication Number:
  US 20080133491A1
• Time in Patent Office: 1,748 Days
• Field of Search: 707/4
• US Class Current: 707/759
• CPC Class Codes: G06F 16/256 in federated or virtual databases

Abstract

A method and system are provided for dynamically relating tables in a federated database based upon a data manipulation language in a query transaction. After the start of a transaction has been detected, a data manipulation language from the transaction is extracted and evaluated to determine the table(s) and column(s) referenced by the transaction language. The data relation output unit determines the relationship(s) between the extracted table(s) and column(s) in real-time and conveys this information through a visual display.

Claims

1. A method for relating tables in a database comprising:

a federated server receiving queries within a transaction, said queries provided by gathering data manipulation language for each input statement in a query transaction, wherein said statement refers to elements associated with different databases forming a federated database;

analyzing said data manipulation language in gathered statement, including;

establishing a relationship between data elements referred to in said statement that are associated with the different databases in real-time, and if said query transaction includes a sub query, executing said sub query, and

establishing a relationship between data elements in multiple input statements in one client session by extracting table name and column name data used in said transaction associated with said input statement;

clarifying characteristics of relationships of said data elements based on structure of said data manipulation language and content of said query transaction; and

classifying said characteristics; and

conveying said classified characteristics in a display format.

2. The method of claim 1, wherein the step of establishing a relationship between data elements occurs in real-time during execution of said input statement.

3. The method of claim 1, wherein the step of establishing a relationship between data elements in multiple input statements occurs in an asynchronous manner by using a transaction log.

4. The method of claim 3, wherein the step of establishing a relationship among data elements in one client session includes executing a sub query on a basis of a table name and column name in each input statement.

5. The method of claim 1, wherein the step of conveying said classified characteristics in a display format includes graphically displaying said established relationship through a display apparatus, wherein said display includes said extracted table name and column name and indicia demonstrating said relationship.

6. The method of claim 1, wherein said federated database integrates multiple autonomous database systems into a single database.

7. A computer system comprising:

a server, having a processor, in communication with a federated database in storage media, said server comprising;

a status management unit to detect start and end of a query transaction;

a data processing unit to extract data manipulation language in each query statement within said transaction and to analyze said language, said data manipulation language referring to tables and columns, wherein said tables and columns are associated with different databases forming the federated database and said language analysis includes establishing a relationship between data elements in multiple input statements in one client session;

a data relation output unit to establish a relationship among extracted tables and columns in real-time, and if said query transaction includes a sub query, executing said sub query; and

said data relation output unit to clarify characteristics of said established relationship based on structure of said data manipulation language and content of said query transaction, and classify said characteristics, and to convey said classified characteristics in a visual display.

8. The computer system of claim 7, wherein said data relation output unit establishes a relationship between data elements in real-time.

9. The computer system of claim 7, wherein said data relation output unit establishes a relationship between data elements in multiple input statements asynchronously through use of a transaction log.

10. The computer system of claim 7, wherein said data relation out unit establishes a relationship among data in one client session.

11. The computer system of claim 7, further comprising execution of a sub query on a basis of a table name and column name in each input statement.

12. The computer system of claim 7, wherein said visual display includes extracted table name, extracted column name, and indicia to demonstrate said relationship.

13. An article comprising:

a computer readable data storage medium;

instructions in said medium to receive queries within a transaction, said queries provided by gathering data manipulation language for each input statement in a query transaction, wherein said statement refers to data elements associated with different databases forming a federated database;

instructions in said medium to analyze said data manipulation language in said gathered statement, including;

establishing a relationship between the data elements referred to in said statement that are associated with different databases in real-time, and if said query transaction includes a sub query, executing said sub query, and

establishing a relationship between the data elements in multiple input

statements in one client session by extracting table name and column name data used in said transaction associated with said input statement;

instructions in said medium to clarify characteristics of relationships of said data elements based on structure of said data manipulation language and content of said query transaction;

instructions in said medium to classify said characteristics; and

instructions in said medium to convey said classified characteristics in a display format.

14. The article of claim 13, wherein said instructions to establish a relationship between data elements includes instructions to extract table and column name data.

15. The article of claim 13, wherein said instructions to establish a relationship between data elements occurs in an asynchronous manner through a log of said query transaction.

16. The article of claim 14, further comprising instructions to execute a sub query based on said table and column name in each input statement.

17. The article of claim 13, wherein said instructions to establish a relationship between data elements occurs in real-time during execution of said input statement.

18. The article of claim 13, further comprising instruction to graphically display said established relationship between data elements through a visual display, wherein said visual display includes extracted table and column name and indicia demonstrating said relationship.