Schema mapping system and method

• US 6,718,320 B1
• Filed: 10/04/1999
• Issued: 04/06/2004
• Est. Priority Date: 11/02/1998
• Status: Expired due to Term

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Subramanian, Narayana, Venkataraman, Shivakumar
• Primary Examiner(s): Corrielus, Jean M.
• Application Number:
  US09/412,033
• Time in Patent Office: 1,646 Days
• Field of Search: 707/3, 707/102, 707/104, 707/1, 707/70
• US Class Current: 707/719
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

A schema mapping system and method are particularly suitable for expressing a schema mapping between two database schemas that may be heterogeneous (restructuring views of each other). The schema mapping system is useful for generating a least cost query plan expressible on a plurality of heterogeneous tables that are restructuring views of each other. The system may be configured to receive a query against one of the tables and convert it to a plurality of alternate queries, each formatted for the particular schema of a different one of the plurality of heterogeneous tables. In generating the alternate queries, the schema mapping system may utilize SchemaSQL view definitions and may employ restructuring operators expressing and capable of conducting a restructuring of a table into a restructuring view of the table. The system may employ a canonical schema and a virtual canonical table to express a mapping between restructuring views, and may utilize the schema mapping for purposes of query conversion. The alternate queries may be costed and optimized and a query plan returned that has a least cost or service time involved.

Claims

1. An apparatus for implementing a database query system, the apparatus comprising:

a processor for executing instructions; and

a memory device having thereon modules of executable and operational data for execution by the processor, the modules comprising;

a schema mapping module executable on a processor to express a schema mapping between a plurality of database schemas, the database schemas containing at least partially replicated information; and

a middleware module adapted to employ a middleware schema for use as a reference by the schema mapping module in expressing the schema mapping.

2. The apparatus of claim 1, wherein the middleware module comprises a canonical schema module and the middleware schema comprises a canonical schema.

3. The apparatus of claim 1, wherein the middleware module is executable on a processor to devise a virtual middleware table.

4. The apparatus of claim 1, wherein the plurality of database schemas are heterogeneous database schemas.

5. The apparatus of claim 1, further comprising a plurality of operators employable by the schema mapping module in expressing the schema mapping, each operator representing a particular type of manipulation between heterogeneous schemas.

6. The apparatus of claim 5, wherein the plurality of operators are adapted for noninvasive use with existing databases.

7. The apparatus of claim 6, wherein the plurality of operators comprise a fold operator, an unfold operator, a split operator, and a unite operator.

8. The apparatus of claim 1, wherein the schema mapping module comprises an SQL view definition expressing the schema mapping.

9. The apparatus of claim 1, further comprising a schema restructuring module executable on a processor to restructure a database relation into a restructuring view of the database relation in conjunction with the schema mapping module and the middleware module.

10. The apparatus of claim 1, further comprising:

a query translation module executable on a processor to communicate with the schema mapping module and translate a query executable on one of the plurality of database schemas to equivalent queries on others of the plurality of database schemas; and

a costing module executable on the processor to select from among the equivalent queries a query corresponding to a selected criterion.

11. A method for implementing a database query system, the method comprising:

expressing a schema mapping between a plurality of database schemas, the database schemas containing at least partially replicated information; and

referencing a middleware schema in expressing the schema mapping.

12. The method of claim 11, wherein the middleware schema comprises a canonical schema.

13. The method of claim 11, wherein referencing a middleware schema comprises referencing a virtual middleware table.

14. The method of claim 11, wherein the plurality of database schemas comprise heterogeneous database tables.

15. The method of claim 11, wherein expressing the schema mapping further comprises employing a plurality of operators, each operator representing a particular type of manipulation between heterogeneous schemas.

16. The method of claim 15, wherein the plurality of operators are adapted for noninvasive use with existing databases.

17. The method of claim 16, wherein the plurality of operators include a fold operator, an unfold operator, a split operator, and a unite operator.

18. The method of claim 11, wherein expressing the schema mapping further comprises employing an SQL view definition expressing the schema mapping.

19. The method of claim 11, further comprising restructuring a database relation into a restructuring view of the database relation with the schema mapping.

20. The method of claim 11, further comprising:

automatically translating a query executable on one of the plurality of database schemas to equivalent queries on others of the plurality of database schemas; and

automatically selecting from among the equivalent queries a query corresponding to a selected criterion.

21. An article of manufacture comprising a program storage medium readable by a processor and embodying one or more instructions executable by the processor to perform a method for implementing a database query system, the method comprising:

expressing a schema mapping between a plurality of database schemas, the database schemas containing at least partially replicated information; and

referencing a middleware schema in expressing the schema mapping.

22. The article of manufacture of claim 21, wherein the middleware schema comprises a canonical schema.

23. The article of manufacture of claim 21, wherein referencing a middleware schema comprises referencing a virtual middleware table.

24. The article of manufacture of claim 21, wherein the plurality of database schemas comprise heterogeneous database tables.

25. The article of manufacture of claim 21, wherein expressing the schema mapping further comprises employing a plurality of operators, each operator representing a particular type of manipulation between different schemas.

26. The article of manufacture of claim 25, wherein the plurality of operators are adapted for noninvasive use with existing databases.

27. The article of manufacture of claim 26, wherein the plurality of operators include an operator selected from the group consisting of a fold operator, an unfold operator, a split operator, and a unite operator.

28. The article of manufacture of claim 21, wherein expressing the schema mapping further comprises employing an SQL view definition expressing the schema mapping.

29. The method of claim 21, wherein the method further comprises restructuring a database relation into a restructuring view of the database relation with the schema mapping.

30. The article of manufacture of claim 21, wherein the method further comprises:

automatically translating a query executable on one of the plurality of database schemas to equivalent queries on others of the plurality of database schemas; and

automatically selecting from among the equivalent queries a query corresponding to a selected criterion.