Creating a logical table from multiple differently formatted physical tables having different access methods

• US 20060136469A1
• Filed: 12/17/2004
• Published: 06/22/2006
• Est. Priority Date: 12/17/2004
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Kolz, Daniel P., Dettinger, Richard D., Tenner, Jeffrey W., Stevens, Richard J.
• Granted Patent: US 8,112,459 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2452 Query translation
• CPC Class Codes: G06F 16/2471 Distributed queries
• CPC Class Codes: G06F 16/256 in federated or virtual databases

Abstract

Embodiments of the present invention provide methods for constructing a database abstraction model that includes logical fields that map to a logical table. The logical table is constructed using data extracted from multiple, differently formatted physical tables. The logical table allows logically related data stored in multiple databases to appear as data from a single table. Relationships between data stored in multiple, differently formatted databases are maintained and logical fields that include multiple access methods that map to the multiple databases are replaced with a single access method that maps to the logical table. Data relationships that are defined for the database abstraction model are modified to reflect the logical table.

Claims

1. A method of creating a data model of logically related data stored in multiple databases comprising:

providing a database abstraction model that defines a plurality of logical fields, wherein each logical field specifies a name for the logical field and at least one access method that maps the logical field to data in one of the multiple databases, and wherein the database abstraction model further defines a set of relationships between data stored in the multiple databases;

identifying at least one logical field that specifies multiple access methods, wherein each access method maps to data stored in a different one of the multiple databases;

generating a logical table, wherein the logical table includes the data from each of the multiple databases referenced by the multiple access methods of the at least one logical field;

replacing the multiple access methods for the at least one logical field with a single access method that maps the at least one logical field to the logical table; and

updating the set of relationships to reflect relationships between the logical table and other data in the multiple databases.

2. The method of claim 1, wherein the multiple databases comprise relational database systems.

3. The method of claim 2, wherein each of the multiple databases is constructed using a differently formatted set of tables used to store logically related data.

4. The method of claim 1, wherein providing a database abstraction model comprises, merging multiple database abstraction models created for different databases into to a single database abstraction model, wherein at least one logical field of the merged database abstraction model provides multiple access methods that each map to logically related data stored in a different one of the databases.

5. The method of claim 2, wherein the multiple databases include a local database system and a remote database system located at a remote location relative to the local database system.

6. The method of claim 2, wherein the relationships between data stored in the multiple databases comprise primary or foreign key relationships between columns of relational tables.

7. The method of claim 2, wherein the logical table comprises a relational table wherein each column of the logical table is an aggregation of the logically related data stored in the multiple databases.

8. The method of claim 2, wherein each relationship defined by the set of relationships comprises an indication of a first column from a first table and a second column from a second table, wherein the first column and second column store logically related data.

9. The method of claim 8, wherein updating the set of relationships comprises:

removing, from the set of relationships, any relationship wherein both the first and second columns each specify a column included in the logical table; and

replacing a first relationship with a second relationship, wherein the first relationship indicates a relationship between a column that is included in the logical table and a column from a table that is not included in the logical table, and wherein the second relationship indicates a relationship between the column that is not included in the logical table and a corresponding column of the logical table.

10. The method of claim 2, wherein generating the logical table comprises:

identifying the tables referenced by the multiple access methods of the least one logical field;

adding, to the logical table, data from columns from tables referenced by the multiple access methods of the least one logical field; and

determining whether another logical field with multiple access methods references a column of data from a table not already included in the logical table, and if so, adding that column of data to the logical table.

11. The method of claim 2, wherein a stored SQL statement used to represent the logical table.

15. The method of claim 2, wherein each of the multiple databases is constructed using a differently formatted set of tables used to store logically related data.

12. A method for generating a logical table from multiple databases that store logically related data in differently formatted physical tables, comprising:

providing a database abstraction model that defines a plurality of logical fields, wherein each logical field specifies a name for the logical field and at least an access method that maps the logical field to data in one of the physical tables;

identifying logical fields that specify multiple access methods that each map to a column of data stored in one of the multiple databases, and for each such access method;

identifying the differently formatted physical tables referenced by the multiple access methods;

adding, to a column of the logical table, data from columns of the differently formatted physical tables referenced by the multiple access methods; and

determining whether another logical field with multiple access methods references a column of data, from any of the physical tables, not already included in the logical table, and if so, adding that column of data to the logical table; and

updating a database schema to include the logical table, wherein the database schema defines the relationships between data stored in the multiple databases and between the columns included in the logical table.

13. A computer-readable medium containing a plurality of instructions which, when executed on a computer system is configured to perform operations comprising:

providing a database abstraction model that defines a plurality of logical fields, wherein each logical field provides a name for the logical field and at least an access method that maps the logical field to data in one of the multiple databases, and wherein the database abstraction model further defines a set of relationships between data stored in the multiple databases;

identifying at least one logical field that provides multiple access methods, wherein each access method maps to data stored in a different one of the multiple databases;

generating a logical table, wherein the logical table includes the data from each of the multiple databases referenced by the multiple access methods of the at least one logical field;

replacing the multiple access methods for the at least one logical field with a single access method that maps the at least one logical field to the logical table; and

updating the set of relationships to reflect relationships between the logical table and other data in the multiple databases.

14. The computer-readable medium of claim 13, wherein the multiple databases comprise relational database systems.

16. The computer-readable medium of claim 13, wherein providing a database abstraction model comprises merging multiple database abstraction models created for different databases into to a single database abstraction model, wherein at least one logical field of the merged database abstraction model provides multiple access methods that each map to logically related data stored in a different one of the multiple databases.

17. The computer-readable medium of claim 14, wherein the multiple databases include a local database system and remote database systems accessed at remote locations relative to the local database system.

18. The computer-readable medium of claim 14, wherein the relationships between data stored in the multiple databases comprise primary or foreign key relationships between tables in the multiple databases.

19. The computer-readable medium of claim 14, wherein the logical table comprises a relational table wherein each column of the logical table is an aggregation of the logically related data stored in the multiple databases.

20. The computer-readable medium of claim 14, wherein the each of the set of relationships comprise an indication of a first column from a first table and a relationship to a second column in a second table.

21. The computer-readable medium of claim 20, wherein updating the set of relationships comprises:

removing, from the set of relationships, any relationship wherein both the first and second columns each specify a column included in the logical table; and

replacing a relationship between a first column from a table that is not included in the logical table and the second column from a table that is included in the logical table with a relationship between the first column and a corresponding column of the logical table.

22. The computer-readable medium of claim 14, wherein generating a logical table comprises:

identifying the tables referenced by the multiple access methods of the least one logical field;

adding, to the logical table, data from columns from tables referenced by the multiple access methods of the least one logical field; and

determining whether another logical field with multiple access methods references a column of data from a table not already included in the logical table, and if so, adding that column of data to the logical table.

23. A computer-readable medium containing a plurality of instructions which, when executed on a computer system is configured to perform operations for generating a logical table from multiple databases that store data in differently formatted physical tables, comprising:

accessing a database abstraction model that defines a plurality of logical fields, wherein each logical field specifies a name for the logical field and at least one access method that maps the logical field to data in one of the physical tables;

identifying logical fields that specify multiple access methods that map to logically related data stored in the multiple databases, and for each such access method;

identifying the differently formatted physical tables referenced by the multiple access methods;

adding, to the logical table, logically related data from columns of the physical tables referenced by the multiple access methods; and

determining whether any other logical field with multiple access methods references a column of data, from any of the physical tables, not already included in the logical table, and if so, adding that column of data to the logical table; and

updating a database schema to include the logical table, wherein the database schema defines the relationships between data stored in the multiple databases.

24. A system for processing abstract queries, comprising:

a plurality of database systems;

a database abstraction model that defines a plurality of logical fields, wherein each logical field specifies a name for the logical field and at least an access method that maps the logical field to data in one of the databases, and wherein at least one access method provides multiple access methods that each map to logically related data stored in a different one of the multiple databases;

a user interface configured to allow a user to compose an abstract query from the plurality of logical fields; and

a runtime component configured to receive and abstract query, and in response, to generate a resolved query of the plurality of database systems and further configured to (i) generate a logical table, wherein the logical table includes the data from each of the plurality of database systems referenced by the multiple access methods of the at least one logical field;

(ii) replace the multiple access methods for the at least one logical field with a single access method that maps the at least one logical field to the logical table; and

(iii) update a set of relationships defined for the database abstraction model.

25. The system of claim 24, wherein the plurality of database system includes at least on relational database system, and wherein the logical table comprises a relational table.