Home
Patent Search
IMT Blog
REGISTER
|
SIGN IN
United States Patent Application
20030120665
Kind Code
A1
Fox, Joshua ; et al.
June 26, 2003
Run-time architecture for enterprise integration with transformation generation
Abstract
An enterprise application system including a run-time transformation server, and a message broker for routing and transforming data in the form of messages between a source application and a target application, including a plug-in for preparing requests to the run-time transformation server and for processing responses from the run-time transformation server. A method is also described and claimed.
Inventors:
Fox; Joshua
(Emek Haela, IL)
, Hellman; Ziv Z.
(Jerusalem, IL
)
, Schreiber; Marcel Zvi
(Jerusalem, IL
)
, Yuval; Tom Y.
(Jerusalem, IL
)
, Israel; Eliezer
(Jerusalem, IL
)
, Yitzhaki; Guy
(Jerusalem, IL
)
, Meir; Rannen
(Jerusalem, IL
)
Correspondence Name and Address:
BLAKELY, SOKOLOFF, TAYLOR & ZAFMAN LLP Seventh Floor 12400 Wilshire Boulevard
James C. Scheller, Jr.
Los Angeles
CA
90025-1026
US
Series Code:
104785
Filed:
March 22, 2002
U.S. Current Class:
707/100
U.S. Class at Publication:
707/100
Intern'l Class:
G06F 017/00;
G06F 007/00
Claims
What is claimed is:
1. An enterprise integration system comprising: a run-time transformation server; and a message broker for routing and transforming data in the form of messages between a source application and a target application, comprising a plug-in for preparing requests to said run-time transformation server and for processing responses from said run-time transformation server.
2. The enterprise integration system of claim 1 wherein messages received by said message broker from the source application conform to a source data schema, and wherein messages sent by said message broker to the target application conform to a target data schema.
3. The enterprise integration system of claim 2 wherein said message broker transforms messages from the source data schema to the target data schema using a transformation provided by said plug-in, and wherein said plug-in generates an appropriate transformation for transforming messages from the source data schema to the target data schema.
4. The enterprise integration system of claim 3 wherein said plug-in generates the appropriate transformation from a central semantic model.
5. The enterprise integration system of claim 4 wherein the central semantic model is a central ontology model.
6. The enterprise integration system of claim 3 wherein said plug-in caches generated transformations, thereby obviating the need to re-generate them.
7. The enterprise integration system of claim 3 wherein the messages are XML documents, wherein the source and target data schema are source and target XML schema, respectively, and wherein the plug-in generates an XSLT transformation.
8. The enterprise integration system of claim 2 wherein said message broker transforms messages from the source data schema to the target data schema using a transformation provided by said plug-in, wherein said plug-in requests and receives a transformation from said run-time transformation server, and wherein said run-time transformation server generates an appropriate transformation for transforming messages from the source data schema to the target data schema.
9. The enterprise integration system of claim 8 wherein said run-time transformation server generates the appropriate transformation from a central semantic model.
10. The enterprise integration system of claim 9 wherein the central semantic model is a central ontology model.
11. The enterprise integration system of claim 8 wherein said plug-in caches generated transformations, thereby obviating the need to re-request them.
12. The enterprise integration system of claim 8 wherein said run-time transformation server caches generated transformations, thereby obviating the need to re-generate them.
13. The enterprise integration system of claim 8 wherein the messages are XML documents, wherein the source and target data schema are source and target XML schema, respectively, and wherein the runtime transformation server generates an XSLT transformation.
14. The enterprise integration system of claim 2 wherein said plug-in requests and receives transformed messages from said run-time application server, and wherein said run-time transformation server generates an appropriate transformation for transforming messages from the source data schema to the target data schema, and transforms messages from the source data schema to the target data schema using the generated transformation.
15. The enterprise integration system of claim 14 wherein said run-time transformation server generates the appropriate transformation from a central semantic model.
16. The enterprise integration system of claim 15 wherein the central semantic model is a central ontology model.
17. The enterprise integration system of claim 14 wherein said run-time transformation server caches generated transformations, thereby obviating the need to re-generate them.
18. The enterprise integration system of claim 14 wherein the messages are XML documents, wherein the source and target data schema are source and target XML schema, respectively, and wherein the run-time transformation server generates an XSLT transformation.
19. The enterprise integration system of claim 1 wherein said message broker is an IBM WebsphereMQ integrator.
20. The enterprise integration system of claim 1 wherein said message broker is a component of Tibco Active Enterprise.
21. The enterprise integration system of claim 1 wherein said message broker further comprises an adapter for communicating between said message broker and said run-time transformation server.
22. The enterprise integration system of claim 21 including at least one protocol bridge between said adapter and said run-time transformation server for translating among network protocols.
23. The enterprise integration system of claim 22 wherein the at least one protocol bridge uses Remote Method Invocation (RMI)) for forward communication
24. The enterprise integration system of claim 23 wherein the at least one protocol bridge includes a Simple Object Access Protocol (SOAP) Web Services server.
25. The enterprise integration system of claim 24 wherein the SOAP Web Services server is an Apache Jakarta Tomcat server with Apache Axis.
26. A method for transforming data within an enterprise integration product, comprising: receiving a message from a source application, the message conforming to a source data schema; generating a transformation for transforming messages conforming to the source data schema to corresponding messages conforming to a target data schema; transforming the message from the source data schema to the target data schema using the generated transformation; and sending the transformed message to a target application.
27. The method of claim 26 wherein said generating generates the transformation from a central semantic model.
28. The method of claim 27 wherein the central semantic model is a central ontology model.
29. The method of claim 26 wherein the message is an XML document and wherein the source and target data schema are source and target XML schema, respectively.
30. The method of claim 26 further comprising: providing the source data schema and the target data schema; and requesting a transformation for transforming messages conforming to the source data schema to corresponding messages conforming to the target data schema.
31. The method of claim 26 further comprising: providing the message and the target data schema; and requesting a transformed message.
32. The method of claim 26 further comprising caching the generated transformation.
33. An ontology modeler comprising: a class builder for generating and editing classes within an ontology model; a property builder for generating and editing properties of classes within the ontology model; and a business rules builder for generating and editing business rules involving properties of classes within the ontology model.
34. The ontology modeler of claim 33 wherein said property builder generates one-to-one properties.
35. The ontology modeler of claim 33 wherein said property builder generates one-to-many properties.
36. The ontology modeler of claim 33 wherein said class builder includes a class builder user interface for designating that a given class is a subclass or a superclass of another class, and wherein a subclass of a class inherits properties of the class.
37. The ontology modeler of claim 36 further comprising a dependency analyzer for analyzing the impact of removal or modification of an inheritance relationship among classes of the ontology model.
38. The ontology modeler of claim 33 further comprising a dependency analyzer for analyzing the impact of removal of a given class or a given property on the ontology model.
39. The ontology modeler of claim 33 further comprising a dependency analyzer for analyzing the impact of modification of the target or the source of a given property on the ontology model.
40. The ontology modeler of claim 33 wherein the business rules include a constraint among class properties whose targets are fundamental data types.
41. The ontology modeler of claim 40 wherein the constraint involves a composition of properties.
42. The ontology modeler of claim 40 wherein the constraint uses a regular expression.
43. The ontology modeler of claim 42 wherein the regular expression is an arithmetic expression.
44. The ontology modeler of claim 42 wherein the regular expression is a logical expression.
45. The ontology modeler of claim 40 wherein the constraint uses a text string operation.
46. The ontology modeler of claim 40 wherein the constraint uses a date function.
47. The ontology modeler of claim 40 wherein the constraint uses a time function.
48. The ontology modeler of claim 40 wherein the constraint uses a look-up table.
49. The ontology modeler of claim 40 wherein the constraint uses a branching condition.
50. The ontology modeler of claim 33 wherein the business rules include an equivalence between properties.
51. The ontology modeler of claim 33 wherein the business rules include an equivalence between properties and compositions of properties.
52. The ontology modeler of claim 33 wherein the business rules include a declarations declaring that a designated property of a class is unique, so that distinct instances of the class have different values of the designated property.
53. The ontology modeler of claim 33 wherein the business rules include a declaration that a designated property of a class is required, so that an instance of the class is not valid unless a value is specified for the designated property.
54. The ontology modeler of claim 33 further comprising a display tool for displaying test instance of classes in the ontology model.
55. The ontology modeler of claim 54 wherein said display tool displays values of test instance properties that are fundamental data types.
56. The ontology modeler of claim 33 further comprising a test instance importer for importing test instances of classes in the ontology model from an external data source.
57. The ontology modeler of claim 33 further comprising a test instance builder for generating and editing test instances of classes in the ontology model.
58. The ontology modeler of claim 57 wherein said test instance builder includes a test instance builder user interface for indicating values for properties of a test instance.
59. The ontology modeler of claim 57 further comprising a test instance validator for validating test instances.
60. The ontology modeler of claim 59 wherein said test instance validator checks for inconsistencies with regard to property values of test instances.
61. The ontology modeler of claim 60 further comprising a reporting tool for reporting inconsistencies with regard to property values of text instances.
62. The ontology modeler of claim 59 wherein said test instance validator checks; for inconsistencies with respect to the business rules.
63. The ontology modeler of claim 62 further comprising a reporting tool for reporting inconsistencies with respect to the business rules.
64. The ontology modeler of claim 44 wherein said class builder generates a class of classes, the class of classes being a class the instances of which are themselves classes.
65. The ontology modeler of claim 64 wherein properties of a class of classes serve as metadata for the instance classes thereof.
66. The ontology modeler of claim 64 wherein properties of a class of classes are used for privilege management of the instance classes therof.
67. A method for building ontology models comprising: generating classes within an ontology model; generating properties of classes within the ontology model; and generating business rules involving properties of classes within the ontology model.
68. The method of claim 67 further comprising: editing classes within an ontology model; editing properties of classes within the ontology model. And editing business rules involving properties of classes within the ontology model.
69. The method of claim 67 wherein said generating properties generates one-to-one properties.
70. The method of claim 67 wherein said generating properties generates one-to-many properties.
71. The method of claim 67 further comprising designating that a given class is a sub class or a superclass of another class, and wherein a subclass of a class inherits properties of the class.
72. The method of claim 71 further comprising analyzing the impact of removal or modification of an inheritance relationship among classes of the ontology model.
73. The method of claim 67 further comprising analyzing the impact of removal of a given class or a given property on the ontology model.
74. The method of claim 67 further comprising analyzing the impact of modification of the source or the target of a given property on the ontology model.
75. The method of claim 67 wherein the business rules include a constraint among class properties whose targets are fundamental data types.
76. The method of claim 75 wherein the constraint involves a composition of properties.
77. The method of claim 75 wherein the constraint uses a regular expression.
78. The method of claim 77 wherein the regular expression is an arithmetic expression.
79. The method of claim 77 wherein the regular expression is a logical expression
80. The method of claim 75 wherein the constraint uses a text string operation.
81. The method of claim 75 wherein the constraint uses a date function.
82. The method of claim 75 wherein the constraint uses a time function.
83. The method of claim 75 wherein the constraint uses a look-up table.
84. The method of claim 75 wherein the constraint uses a branching condition.
85. The method of claim 67 wherein the business rules include an equivalence between properties.
86. The method of claim 67 wherein the business rules include an equivalence between properties and compositions of properties.
87. The method of claim 67 wherein the business rules include a declarations declaring that a designated property of a class is unique, so that distinct instances of the class have different values of the designated property.
88. The method of claim 67 wherein the business rules include a declaration that a designated property of a class is required, so that an instance of the class is not valid unless a value is specified for the designated property.
89. The method of claim 67 further comprising displaying test instance of classes in the ontology model.
90. The method of claim 89 wherein said displaying displays values of test instance properties that are fundamental data types.
91. The method of claim 67 further comprising importing test instances of classes in the ontology model from an external data source.
92. The method of claim 67 further comprising generating test instances of classes in the ontology model.
93. The method of claim 92 further comprising editing test instances of classes in the ontology model.
94. The method of claim 92 further comprising indicating values for properties of a test instance.
95. The method of claim 92 further comprising validating test instances.
96. The method of claim 95 further comprising checking for inconsistencies with regard to property values of test instances, vis a vis the business rules.
97. The method of claim 96 further comprising reporting inconsistencies with regard to property values of text instances.
98. The method of claim 95 further comprising checking for inconsistencies checks for inconsistencies with respect to the business rules.
99. The method of claim 98 further comprising reporting inconsistencies with respect to the business rules.
100. The method of claim 67 wherein said generating classes comprises generating a class of classes, the class of classes being a class the instances of which are themselves classes.
101. The method of claim 100 wherein properties of the class of classes serve as metadata for the instance classes thereof.
102. The method of claim 100 wherein properties of a class of classes are used for privilege management of the instance classes thereof.
103. A schema-to-ontology mapper, comprising: a storage for storing a schema, the schema including at least one primary data structure, the primary data structure having at least one ancillary data structure; and a map generating for generating a plurality of mappings, comprising: a class mapper for defining a primary mapping that is a correspondence between a primary data structure of the schema and a class of an ontology model; and a property mapper for defining an ancillary relationship between an ancillary data structure of the primary data structure and at least one property of the class.
104. The schema-to-ontology mapper of claim 103 wherein the schema is a relational database schema, wherein the primary data structure is a table and wherein the ancillary data structure is a column of a table.
105. The schema-to-ontology mapper of claim 103 wherein the schema is an XML schema, wherein the primary data structure is a complexType and wherein the ancillary data structure is an element or an attribute.
106. The schema-to-ontology mapper of claim 103 wherein the ancillary relationship uses an equation equating the ancillary data structure to a function of at least one property of the class.
107. The schema-to-ontology mapper of claim 106 wherein the function uses arithmetic expressions.
108. The schema-to-ontology mapper of claim 106 wherein the function uses text string expressions.
109. The schema-to-ontology mapper of claim 103 further comprising a dependency analyzer for analyzing the impact of removal of a given class or a given property on the plurality of mappings.
110. The schema-to-ontology mapper of claim 109 wherein said dependency analyzer issues an impact warning prior to removal of a given class or a given property.
111. The schema-to-ontology mapper of claim 103 further comprising a dependency analyzer for analyzing the impact of modification of the source or the target of a given property on the plurality of mappings.
112. The schema-to-ontology mapper of claim 111 wherein said dependency analyzer issues an impact warning prior to modification of the source or target of a given property.
113. The schema-to-ontology mapper of claim 103 further comprising a dependency analyzer for analyzing the impact of removal or modification of a class inheritance relationship on the plurality of mappings.
114. The schema-to-ontology mapper of claim 113 wherein said dependency analyzer issues an impact warning prior to removal or modification of a class inheritance relationship.
115. A method for mapping a schema to an ontology model, comprising: receiving a schema, the schema including at least one primary data structure, the primary data structure having at least one ancillary data structure; and generating a plurality of mappings, comprising: defining a primary mapping that is a correspondence between a primary data structure of the schema and a class of an ontology model, and defining an ancillary relationship between an ancillary data structure of the primary data structure and at least one property of the class.
116. The method of claim 115 wherein the schema is a relational database schema, wherein the primary data structure is a table and wherein the ancillary data structure is a column of a table.
117. The method of claim 115 wherein the schema is an XML schema, wherein the primary data structure is a complexType and wherein the ancillary data structure is an element or an attribute.
118. The method of claim 115 wherein the ancillary relationship uses an equation equating the ancillary data structure to a function of at least one property of the class.
119. The method of claim 118 wherein the function uses arithmetic expressions.
120. The method of claim 118 wherein the function uses text string expressions.
121. The method of claim 115 further comprising analyzing the impact of removal of a given class or a given property on the plurality of mappings.
122. The method of claim 121 further comprising issuing an impact warning prior to removal of a given class or a given property.
123. The method of claim 115 further comprising analyzing the impact of modification of the source or the target of a given property on the plurality of mappings.
124. The method of claim 123 further comprising issuing an impact warning prior to modification of the source or target of a given property.
125. The method of claim 115 further comprising analyzing the impact of removal or modification of a class inheritance relationship on the plurality of mappings.
126. The method of claim 125 further comprising issuing an impact warning prior to removal or modification of a class inheritance relationship.
127. A schema transformation generator comprising: a storage for storing a first mapping of a first schema into a central ontology model, and a second mapping of a second schema into the central ontology model wherein the first schema includes at least one first primary data structure, the first primary data structure having at least one first ancillary data structure, wherein the second schema includes at least one second primary data structure, the second primary data structure having at least one second ancillary data structure, wherein the first mapping includes at least one first primary mapping that is a correspondence between at least one first primary data structure of the first schema and a class of the central ontology model, and at least one first relationship between at least one first ancillary data structure of a first primary data structure and at least one property of a class, and wherein the second mapping includes at least one second primary mapping that is a correspondence between at least one second primary data structure of the second schema and a class of the central ontology model, and at least one second relationship between at least one second ancillary data structure of a second primary data structure and at least one property of a class; and a transformation generator for generating a transformation from the first schema into the second schema, using the first and second primary mappings and the first and second relationships.
128. The schema transformation generator of claim 127 further comprising a schema builder for building the second schema in accordance with at least one given query.
129. The schema transformation generator of claim 128 wherein the at least one given query is at least one given query on data conforming to the first schema.
130. The schema transformation generator of claim 128 wherein the at least one given query is at least one given query on instance data of the central ontology model.
131. The schema transformation generator of claim 127 further comprising a dependency analyzer for analyzing the impact of removal of a given class or a given property from the central ontology model on a generated transformation.
132. The schema transformation generator of claim 131 wherein said dependency analyzer indicates that a transformation should be re-generated after removal of a given class or a given property from the central ontology model.
133. The schema transformation generator of claim 127 further comprising a dependency analyzer for analyzing the impact of modification of the source or the target of a given property from the central ontology model on a generated transformation.
134. The schema transformation generator of claim 133 wherein said dependency analyzer indicates that a transformation should be re-generated after modification of the source or the target of a given property.
135. The schema transformation generator of claim 127 further comprising a dependency analyzer for analyzing the impact of removal or modification of a class inheritance relationship on a generated transformation.
136. The schema transformation generator of claim 135 wherein said dependency analyzer indicates that a transformation should be re-generated after removal or modification of a class inheritance relationship.
137. The schema transformation generator of claim 127 further comprising a dependency analyzer for analyzing the impact of removal or modification of a first or second primary mapping on a generated transformation.
138. The schema transformation generator of claim 137 wherein said dependency analyzer indicates that a transformation should be re-generated after removal or modification of a first or second primary mapping.
139. The schema transformation generator of claim 127 further comprising a dependency analyzer for analyzing the impact of removal or modification of a first or second relationship on a generated transformation.
140. The schema transformation generator of claim 139 wherein said dependency analyzer indicates that a transformation should be re-generated after removal or modification of a first or second relationship.
141. A method for generating a schema transformation comprising: storing a first mapping of a first schema into a central ontology model, and a second mapping of a second schema into the central ontology model, wherein the first schema includes at least one first primary data structure, the first primary data structure having at least one first ancillary data structure, wherein the second schema includes at least one second primary data structure, the second primary data structure having at least one second ancillary data structure, wherein the first mapping includes at least one first primary mapping that is a correspondence between at least one first primary data structure of the first schema and a class of the central ontology model, and at least one first relationship between at least one first ancillary data structure of a first primary data structure and at least one property of a class, and wherein the second mapping includes at least one second primary mapping that is a correspondence between at least one second primary data structure of the second schema and a class of the central ontology model, and at least one second relationship between at least one second ancillary data structure of a second primary data structure and at least one property of a class; and generating a transformation from the first schema into the second schema, using the first and second primary mappings and the first and second relationships.
142. The method of claim 141 further comprising building the second schema in accordance with at least one given query.
143. The method of claim 142 wherein the at least one given query is at least one given query on data conforming to the first schema.
144. The method of claim 142 wherein the at least one given query is at least one given query on instance data of the central ontology model.
145. The method of claim 141 further comprising analyzing the impact of removal of a given class or a given property from the central ontology model on a generated transformation.
146. The method of claim 145 further comprising indicating that a transformation should be re-generated after removal of a given class or a given property from the central ontology model.
147. The method of claim 141 further comprising analyzing the impact of modification of the source or the target of a given property from the central ontology model on a generated transformation.
148. The method of claim 147 further comprising indicating that a transformation should be re-generated after modification of the source or the target of a given property.
149. The method of claim 141 further comprising analyzing the impact of removal or modification of a class inheritance relationship on a generated transformation.
150. The method of claim 149 further comprising indicating that a transformation should be re-generated after removal or modification of a class inheritance relationship.
151. The method of claim 141 further comprising analyzing the impact of removal or modification of a first or second primary mapping on a generated transformation.
152. The method of claim 151 further comprising indicating that a transformation should be re-generated after removal or modification of a first or second primary mapping.
153. The method of claim 141 further comprising a dependency analyzer for analyzing the impact of removal or modification of a first or second relationship on a generated transformation.
154. The method of claim 153 further comprising indicating that a transformation should be re-generated after removal or modification of a first or second relationship.
Description
CROSS REFERENCES TO RELATED APPLICATIONS
[0001] This application is a continuation-in-part of assignee's pending application U.S. Ser. No. 10/053,045, filed on Jan. 15, 2002, entitled "Method and System for Deriving a Transformation by Referring Schema to a Central Model," which is a continuation-in-part of assignee's application U.S. Ser. No. 09/904,457 filed on Jul. 6, 2001, entitled "Instance Brower for Ontology," which is a continuation-in-part of assignee's application U.S. Ser. No. 09/866,101 filed on May 25, 2001, entitled "Method and System for Collaborative Ontology Modeling."
FIELD OF THE INVENTION
[0002] The present invention relates to data schema, and in particular to deriving transformations for transforming data from one schema to another.
BACKGROUND OF THE INVENTION
[0003] Ontology is a philosophy of what exists. In computer science ontology is used to model entities of the real world and the relations between them, so as to create common dictionaries for their discussion. Basic concepts of ontology include (i) classes of instances/things, and (ii) relations between the classes, as described hereinbelow. Ontology provides a vocabulary for talking about things that exist.
[0004] Instances/Things
[0005] There are many kinds of "things" in the world. There are physical things like a car, person, boat, screw and transistor. There are other kinds of things which are not physically connected items or not even physical at all, but may nevertheless be defined. A company, for example, is a largely imaginative thing the only physical manifestation of which is its appearance in a list at a registrar of companies. A company may own and employ. It has a defined beginning and end to its life.
[0006] Other things can be more abstract such as the Homo Sapiens species, which is a concept that does not have a beginning and end as such even if its members do.
[0007] Ontological models are used to talk about "things." An important vocabulary tool is "relations" between things. An ontology model itself does not include the "things," but introduces class and property symbols which can then be used as a vocabulary for talking about and classifying things.
[0008] Properties
[0009] Properties are specific associations of things with other things. Properties inchlde:
[0010] Relations between things that are part of each other, for example, between a PC and its flat panel screen;
[0011] Relations between things that are related through a process such as the process of creating the things, for example, a book and its author;
[0012] Relations between things and their measures, for example, a thing and its weight
[0013] Some properties also relate things to fundamental concepts such as natural numbers or strings of characters--for example, the value of a weight in kilograms, or the name of a person.
[0014] Properties play a dual role in ontology. On the one hand, individual things are referenced by way of properties, for example, a person by his name, or a book by its title and author. On the other hand, knowledge being shared is often a property of things, too. A thing can be specified by way of some of its properties, in order to query for the values of other of its properties.
[0015] Classes
[0016] Not all properties are relevant to all things. It is convenient to discuss the source of a property as a "class" of things, also referred to as a frame or, for end-user purposes, as a category. Often sources of several properties coincide, for example, the class Book is the source for both Author and ISBN Number properties.
[0017] There is flexibility in the granularity to which classes are defined. Cars is a class. Fiat Cars can also be a class, with a restricted value of a manufacturer property. It may be unnecessary to address this class, however, since Fiat cars may not have special properties of interest that are not common to other cars. In principle, one can define classes as granular as an individual car unit, although an objective of ontology is to define classes that have important properties.
[0018] Abstract concepts such as measures, as well as media such as a body of water which cannot maintain its identity after coming into contact with other bodies of water, may be modeled as classes with a quantity property mapping them to real numbers.
[0019] In a typical mathematical model, a basic ontology comprises:
[0020] A set C, the elements of which are called "class symbols;"
[0021] For each C.epsilon.C, a plain language definition of the class C;
[0022] A set P, the elements of which are called "property symbols;"
[0023] For each P.epsilon.F:
[0024] a plain language definition of P;
[0025] a class symbol called the source of P; and
[0026] a class symbol called the target of P; and
[0027] A binary transitive reflexive anti-symmetric relation, I, called the inheritance relation on C.times.C.
[0028] In the ensuing discussion, the terms "class" and "class symbol" are used interchangeably, for purposes of convenience and clarity. Similarly, the terms "property" and "property symbol" are also used interchangeably.
[0029] It is apparent to those skilled in the art that if an ontology model is extended to include sets in a class, then a classical mathematical relation on C'D can be considered as a property from C to sets in D.
[0030] If I(C.sub.1, C.sub.2) then C.sub.1 is referred to as a subclass of C.sub.2, and C.sub.2 is referred to as a superclass of C.sub.1. Also, C.sub.1 is said to inherit from C.sub.2.
[0031] A distinguished universal class "Being" is typically postulated to be a superclass of all classes in C.
[0032] Variations on an ontology model may include:
[0033] Restrictions of properties to unary properties, these being the most commonly used properties;
[0034] The ability to specify more about properties, such as multiplicity and invertibility.
[0035] The notion of a class symbol is conceptual, in that it describes a generic genus for an entire species such as Books, Cars, Companies and People. Specific instances of the species within the genus are referred to as "instances" of the class. Thus "Gone with the Wind" is an instance of a class for books, and "IBM" is an instance of a class for companies. Similarly, the notions of a property symbol is conceptual, in that it serves as a template for actual properties that operate on instances of classes.
[0036] Class symbols and property symbols are similar to object-oriented classes; in computer programming, such as C++ classes. Classes, along with their members and field variables, defined within a header file, serve as templates for specific class instances used by a programmer. A compiler uses header files to allocate memory for, and enables a programmer to use instances of classes. Thus a header file can declare a rectangle class with members left, right, top and bottom. The declarations in the header file do not instantiate actual "rectangle objects," but serve as templates for rectangles instantiated in a program. Similarly, classes of an ontology serve as templates for instances thereof.
[0037] There is, however, a distinction between C++ classes and ontology classes. In programming, classes are templates and they are instantiated to create programming objects. In ontology, classes document common structure but the instances exist in the real world and are not created through the class.
[0038] Ontology provides a vocabulary for speaking about instances, even before the instances themselves are identified. A class Book is used to say that an instance "is a Book." A property Author allows one to create clauses "author of" about an instance. A property Siblings allows one to create statements "are siblings" about instances. Inheritance is used to say, for example, that "every Book is a PublishedWork". Thus all vocabulary appropriate to PublishedWork can be used for Book.
[0039] Once an ontology model is available to provide a vocabulary for talking about instances, the instances themselves can be fit into the vocabulary. For each class symbol, C, all instances which satisfy "is a C" are taken to be the set of instances of C, and this set is denoted B(C). Sets of instances are consistent with inheritance, so that B(C.sub.1)B(C.sub.2) whenever C.sub.1 is a subclass of C.sub.2. Property symbols with source C.sub.1 and target C.sub.2 correspond to properties with source B(C.sub.1) and target B(C.sub.2). It is noted that if class C.sub.1 inherits from class C, then every instance of C.sub.1 is also an instance of C, and it is therefore known already at the ontology stage that the vocabulary of C is applicable to C.sub.1.
[0040] Ontology enables creation of a model of multiple classes and a graph of properties therebetween. When a class is defined, its properties are described using handles to related classes. These can in turn be used to look up properties of the related classes, and thus properties of properties can be accessed to any depth.
[0041] Provision is made for both classes, also referred to as "simple" classes, and "complex" classes. Generally, complex classes are built up from simpler classes using tags for symbols such as intersection, Cartesian product, set, list and bag. The "intersection" tag is followed by a list of classes or complex classes. The "Cartesian product" tag is also followed by a list of classes or complex classes. The set symbol is used for describing a class comprising subsets of a class, and is followed by a single class or complex class. The list symbol is used for describing a class comprising ordered subsets of a class; namely, finite sequences, and is followed by a single class or complex class. The bag symbol is used for describing unordered finite sequences of a class, namely, subsets that can contain repeated elements, and is followed by a single class or complex class. Thus set[C] describes the class of sets of instances of a class C, list[C] describes the class of lists of instances of class C, and bag[C] describes the class of bags of instances of class C.
[0042] In terms of formal mathematics, for a set S, set[S] is P(S), the power set of S; bag[S] is N.sup.S, where N is the set of non-negative integers; and list[S] is 1 n = 1 .infin. S n .
[0043] There are natural mappings 2 list [ S ] bag [ S ] set [ S ] . ( 1 )
[0044] Specifically, for a sequence (s.sub.1, s.sub.2, . . . , s.sub.n).epsilon.list[S], .phi.(s.sub.1, s.sub.2, . . . , s.sub.n) is the element f.epsilon.bag[S] that is the "frequency histogram" defined by f(s)=#{1.ltoreq.i.ltoreq.n: s.sub.i=s}; and for f.epsilon.bag[S], .psi.(f).epsilon.set[S] is the subset of S given by the support of f, namely, supp(f)={s.epsilon.S: f(s)>0}. It is noted that the composite mapping .phi..psi. maps the sequence (s.sub.1, s.sub.2, . . . , s.sub.n) into the set of its elements {s.sub.1, s.sub.2, . . . , s.sub.n}. For finite sets S, set[S] is also finite, and bag[S] and list[S] are countably infinite.
[0045] Provision is also made for one-to-one, or unary properties, and for one-to-many properties. The target of a one-to-one property is a simple class. Generally, the target of a one-to-many property is a complex class. For example, a one-to-many property named "children" may have a class Person as its source and a complex class set[Person] as its target, and a one-to-many property named "parents" may have a class Person as its source and a complex class Person.times.Person as its target.
[0046] A general reference on ontology systems is Sowa, John F., "Knowledge Representation," Brooks/Cole, Pacific Grove, Calif., 2000.
[0047] Relational database schema (RDBS) are used to define templates for organizing data into tables and fields. SQL queries are used to populate tables from existing tables, generally by using table join operations. Extensible markup language (XML) schema are used to described documents for organizing data into a hierarchy of elements and attributes. XSLT script is used to generate XML documents from existing documents, generally by importing data between tags in the existing documents. XSLT was originally developed in order to generate HTML pages from XML documents.
[0048] A general reference on relation databases and SQL is the document "Oracle 9i: SQL Reference," available on-line at http://www.oralcle.com. XML, XML schema, XPath and XSLT are standards of the World-Wide Web Consortium, and are available on-line at http://www.w3.org.
[0049] Often multiple schema exist for the same source of data, and as such the data cannot readily be imported or exported from one application to another. For example, two airline companies may each run applications that process relational databases, but if the relational databases used by the two companies conform to two different schema, then neither of the companies can readily use the databases of the other company. In order for the companies to share data, it is necessary to export the databases from one schema to another.
[0050] There is thus a need for a tool that can transform data conforming to a first schema into data that conforms to a second schema
SUMMARY OF THE INVENTION
[0051] The present invention provides a method and system for deriving transformations for transforming data from one schema to another. The present invention describes a general method and system for transforming data confirming with an input, or source data schema into an output, or target data schema. In a preferred embodiment, the present invention can be used to provide (i) an SQL query, which when applied to relational databases from a source RDBS, populates relational databases in a target RDBS; and (ii) XSLT script which, when applied to documents conforming with a source XML schema generates documents conforming with a target XML schema.
[0052] The present invention preferably uses an ontology model to determine a transformation that accomplishes a desired source to target transformation. Specifically, the present invention employs a common ontology model into which both the source data schema and target data schema can be mapped. By mapping the source and target data schema into a common ontology model, the present invention derives interrelationships among their components, and uses the interrelationships to determine a suitable transformation for transforming data conforming to the source data schema into data conforming to the target data schema.
[0053] Given a source RDBS and a target RDBS, in a preferred embodiment of the present invention an appropriate transformation of source to target databases is generated by:
[0054] (i) mapping the source and target RDBS into a common ontology model;
[0055] (ii) representing table columns of the source and target RDBS in terms of properties of the ontology model;
[0056] (iii) deriving expressions for target table columns in terms of source table columns; and
[0057] (iv) converting the expressions into one or more SQL queries.
[0058] Although the source and target RDBS are mapped into a common ontology model, the derived transformations of the present invention go directly from source RDBS to target RDBS without having to transform data via an ontological format. In distinction, prior art Universal Data Model approaches transform via a neutral model or common business objects.
[0059] The present invention applies to N relational database schema, where N.gtoreq.2. Using the present invention, by mapping the RDBS into a common ontology model, data can be moved from any one of the RDBS to any other one. In distinction to prior art approaches that require on the order of N.sup.2 mappings, the present invention requires at most N mappings.
[0060] For enterprise applications, SQL queries generated by the present invention are preferably deployed within an Enterprise Application Integration infrastructure. Those skilled in the art will appreciate that transformation languages other than SQL that are used by enterprise application infrastructures can be generated using the present invention. For example, IBM's ESQL language can similarly be derived for deployment on their Websphere MQ family of products.
[0061] Given a source XML schema and a target XML schema, in a preferred embodiment of the present invention an appropriate transformation of source to target XML documents is generated by:
[0062] (i) mapping the source and target XML schema into a common ontology model;
[0063] (ii) representing elements and attributes of the source and target XML schema in terms of properties of the ontology model;
[0064] (iii) deriving expressions for target XML elements and XML attributes in terms of source XML elements and XML attributes; and
[0065] (iv) converting the expressions into an XSLT script.
[0066] There is thus provided in accordance with a preferred embodiment of the present invention an enterprise application system including a run-time transformation server, and a message broker for routing and transforming data in the form of messages between a source application and a target application, including a plug-in for preparing requests to the run-time transformation server and for processing responses from the run-time transformation server.
[0067] There is further provided in accordance with a preferred embodiment of the present invention a method for transforming data within an enterprise application product, including receiving a message from a source application, the message conforming to a source data schema, generating a transformation for transforming messages conforming to the source data schema to corresponding messages conforming to a target data schema, transforming the message from the source data schema to the target data schema using the generated transformation, and sending the transformed message to a target application.
[0068] There is yet further provided in accordance with a preferred embodiment of the present invention an ontology modeler including a class builder for generating and editing classes within an ontology model, a property builder for generating and editing properties of classes within the ontology model, and a business rules builder for generating and editing business rules involving properties of classes within the ontology model.
[0069] There is additionally provided in accordance with a preferred embodiment of the present invention a method for building ontology models including generating classes within an ontology model, generating properties of classes within the ontology model, and generating business rules involving properties of classes within the ontology model.
[0070] There is moreover provided in accordance with a preferred embodiment of the present invention A schema-to-ontology mapper, including a storage for storing a schema, the schema including at least one primary data structure, the primary data structure having at least one ancillary data structure, and a map generating for generating a plurality of mappings, including a class mapper for defining a primary mapping that is a correspondence between a primary data structure of the schema and a class of an ontology model, and a property mapper for defining an ancillary relationship between an ancillary data structure of the primary data structure and at least one property of the class.
[0071] There is further provided in accordance with a preferred embodiment of the present invention a method for mapping a schema to an ontology model, including receiving a schema, the schema including at least one primary data structure, the primary data structure having at least one ancillary data structure, and generating a plurality of mappings, including defining a primary mapping that is a correspondence between a primary data structure of the schema and a class of an ontology model, and defining an ancillary relationship between a secondary data structure of the primary data structure and at least one property of the class.
[0072] There is yet further provided in accordance with a preferred embodiment of the present invention a schema transformation generator including a storage for storing a first mapping of a first schema into a central ontology model, and a second mapping of a second schema into the central ontology model, wherein the first schema includes at least one first primary data structure, the first primary data structure having at least one first ancillary data structure, wherein the second schema includes at least one second primary data structure, the second primary data structure having at least one second ancillary data structure, wherein the first mapping includes at least one first primary mapping that is a correspondence between at least one first primary data structure of the first schema and al class of the central ontology model, and at least one first relationship between at least one first ancillary data structure of a first primary data structure and at least one property of a class, and wherein the second mapping includes at least one second primary mapping that is a correspondence between at least one second primary data structure of the second schema and a class of the central ontology model, and at least one second relationship between at least one second ancillary data structure of a second primary data structure and at least one property of a class, and a transformation generator for generating a transformation from the first schema into the second schema, using the first and second primary mappings and the first and second relationships
[0073] There is yet further provided in accordance with a preferred embodiment of the present invention a method for generating a schema transformation including storing a first mapping of a first schema into a central ontology model, and a second mapping of a second schema into the central ontology model, wherein the first schema includes at least one first primary data structure, the first primary data structure having at least one first ancillary data structure, wherein the second schema includes at least one second primary data structure, the second primary data structure having at least one second ancillary data structure, wherein the first mapping includes at least one first primary mapping that is a correspondence between at least one first primary data structure of the first schema and a class of the central ontology model, and at least one first relationship between at least one first ancillary data structure of a first primary data structure and at least one property of a class, and wherein the second mapping includes at least one second primary mapping that is a correspondence between at least one second primary data structure of the second schema and a class of the central ontology model, and at least one second relationship between at least one second ancillary data structure of a second primary data structure and at least one property of a class, and generating a transformation from the first schema into the second schema, using the first and second primary mappings and the first and second relationships.
BRIEF DESCRIPTION OF THE DRAWINGS
[0074] The present invention will be more fully understood and appreciated from the following detailed description, taken in conjunction with the drawings in which:
[0075] FIG. 1 is a simplified flowchart of a method for deriving transformations for transforming data from one schema to another, in accordance with a preferred embodiment of the present invention;
[0076] FIG. 2 is a simplified block diagram of a system for deriving transformations for transforming data from one schema to another, in accordance with a preferred embodiment of the present invention;
[0077] FIG. 3 is a simplified flowchart of a method for building a common ontology model into which one or more data schema can be embedded, in accordance with a preferred embodiment of the present invention; FIG. 4
is a simplified block diagram of a system for building a common ontology model into which one or more data schema can be embedded, in accordance with a preferred embodiment of the present invention;
[0078] FIG. 5 is a simplified illustration of a mapping from an RDBS into an ontology model, in accordance with a preferred embodiment of the present invention;
[0079] FIG. 6 is a second simplified illustration of a mapping from an RDBS into an ontology model, in accordance with a preferred embodiment of the present invention;
[0080] FIG. 7 is a simplified illustration of relational database transformations involving constraints and joins, in accordance with a preferred embodiment of the present invention;
[0081] FIG. 8A is a simplified block diagram of a run-time architecture for implementing a preferred embodiment of the present invention within an enterprise application integration system;
[0082] FIGS. 8B-8D are simplified flowcharts for alternative implementations of the present invention within an enterprise application integration system;
[0083] FIG. 8E is a simplified illustration of an enterprise application product, such as Tibco Active Enterprise, with the present invention therewithin;
[0084] FIGS. 9A-9E are illustrations of a user interface for a software application that transforms data from one relational database schema to another, in accordance with a preferred embodiment of the present invention;
[0085] FIG. 10 is an illustration of a user interface for an application that imports an RDBS into the software application illustrated in FIGS. 8A-8E, in accordance with a preferred embodiment of the present invention;
[0086] FIGS. 11A-11R are illustrations of a user interface for a software application that transforms data from one XML schema to another, in accordance with a preferred embodiment of the present invention;
[0087] FIG. 12 is an illustration of ontology model corresponding to a first example;
[0088] FIG. 13 is an illustration of ontology model corresponding to a second example;
[0089] FIG. 14 is an illustration of ontology model corresponding to a third example;
[0090] FIG. 15 is an illustration of ontology model corresponding to a fourth example;
[0091] FIG. 16 is an illustration of ontology model corresponding to a fifth and sixth example;
[0092] FIG. 17 is an illustration of ontology model corresponding to a seventh example.
[0093] FIG. 18 is an illustration of ontology model corresponding to an eighth example
[0094] FIG. 19 is an illustration of ontology model corresponding to a ninth example
[0095] FIG. 20 is an illustration of ontology model corresponding to a tenth example;
[0096] FIG. 21 is an illustration of ontology model corresponding to an eleventh example;
[0097] FIG. 22 is an illustration of ontology model corresponding to a twelfth and seventeenth example.
[0098] FIG. 23 is an illustration of ontology model corresponding to a thirteenth example
[0099] FIG. 24 is an illustration of ontology model corresponding to a fourteenth example
[0100] FIG. 25 is an illustration of ontology model corresponding to a twenty-second example;
[0101] FIG. 26 is an illustration of ontology model corresponding to a twenty-third example; and
[0102] FIG. 27 is a simplified dependency graph used for impact analysis, in accordance with a preferred embodiment of the present invention.
DETAILED DESCRIPTION OF A PREFERRED EMBODIMENT
[0103] The present invention concerns deriving transformations for transforming data conforming with one data schema to data conforming to another data schema. Preferred embodiments of the invention are described herein with respect to table-based data schema such as RDBS, and document-based schema such as XML schema.
[0104] Reference is now made to FIG. 1, which is a simplified flowchart of a method for deriving transformations for transforming data from one schema to another, in accordance with a preferred embodiment of the present invention. The flowchart begins at step 110. At step, 120 a source data schema and a target data schema are imported. These data schema describe templates for storing data, such as templates for tables and table columns, or templates for structured documents. If necessary, the source data schema and/or the target data schema may be converted from a standard format to an internal format. For example, they may be converted from Oracle format to an internal format.
[0105] At steps 130-160 a common ontology model is obtained, into which the source data schema and the target data schema can both be embedded, At step 130 a determination is made as to whether or not an initial ontology model is to be imported. If not, logic passes directly to step 160. Otherwise, at step 140 an initial ontology model is imported. If necessary, the initial ontology model may be converted from a standard format, such as one of the formats mentioned hereinabove in the Background, to an internal format.
[0106] At step 150 a determination is made as to whether or not the initial ontology model is suitable for embedding both the source and target data schema. If so, logic passes directly to step 170. Otherwise, at step 160 a common ontology model is built If an initial ontology model was exported, then preferably the common ontology is built by editing the initial ontology model; specifically, by, adding classes and properties thereto. Otherwise, the common ontology model is built from scratch. It may be appreciated that the common ontology model may be built automatically with or without user assistance.
[0107] At step 170 the source and target data schema are mapped into the common ontology model, and mappings therefor are generated. At step 180 a transformation is derived for transforming data conforming with the source data schema into data conforming with the target data schema, based on the mappings derived at step 170. Finally, the flowchart terminates at step 190.
[0108] Reference is now made to FIG. 2, which is a simplified block diagram of a system 200 for deriving transformations for transforming data from one schema to another, in accordance with a preferred embodiment of the present invention. Shown in FIG. 2 is a schema receiver 210 for importing a source data schema and a target data schema. These data schema describe templates for storing data, such as templates for tables and table columns, and templates for structured documents. If necessary, schema receiver 210 converts the source and target data schema from an external format to an internal format.
[0109] Also shown in FIG. 2 is an ontology receiver/builder 220 for obtaining a common ontology model, into which the source data schema and the target data schema can both be embedded. The operation of ontology receiver/builder 220 is described hereinabove in steps 130-160 of FIG. 1.
[0110] The source and target data schema, and the common ontology model are used by a mapping processor 230 to generate respective source and target mappings, for mapping the source data schema into the common model and for mapping the target data schema into the common ontology model. In a preferred embodiment of the present invention, mapping processor 230
includes a class identifier 240 for identifying ontology classes with corresponding to components of the source and target data schema, and a property identifier 250 for identifying ontology properties corresponding to other components of the source and target data schema, as described in detail hereinbelow.
[0111] Preferably, the source and target mappings generated by mapping processor, and the imported source and target data schema are used by a transformation generator 260 to derive a source-to-target transformation, for transforming data conforming to the source data schema into data conforming to the target data schema.
[0112] Reference is now made to FIG. 3, which is a simplified flowchart of a method for building a common ontology model into which one or more data schema can be embedded, in accordance with a preferred embodiment of the present invention. The flowchart begins are step 310. Steps 120, 140 and 160 are similar to these same steps in FIG. 1, as described hereinabove. Finally, the flowchart terminates at step 320.
[0113] Reference is now made to FIG. 4, which is a simplified block diagram of a system 400 for building a common ontology model into which one or more data schema can be embedded, in accordance with a preferred embodiment of the present invention. Shown in FIG. 4 is schema receiver 210 from FIG. 2 for importing data schema. Also shown in FIG. 4 is an ontology receiver 420, for importing an initial ontology model. If necessary, ontology receiver 420 converts the initial ontology model from an external format to an internal format The initial ontology model and the imported data schemas are used by an ontology builder 430 for generating a common ontology model, into which the imported data schemas can all be embedded. In a preferred embodiment of the present invention, ontology builder 430 generates the common ontology model by editing the initial ontology model; specifically, by using a class builder 440 to add classes thereto based on components of the imported data schema, and by using a property builder 450 to add properties thereto based on other components of the imported data schema.
[0114] A feature of the present invention is the capability to generate test instances of classes. In a preferred embodiment, a test instance is represented as an XML document that describes the instance and some or all of the values of its properties. Generation of test instances is enabled both manually, by a user filling in property values, and automatically without user intervention.
[0115] Applications of the present invention include inter alia:
[0116] integrating between two or more applications that need to share data;
[0117] transmitting data from a database schema across a supply chain to a supplier or customer using a different database schema;
[0118] moving data from two or more databases with different schemas into a common database, in order that queries may be performed across the two or more databases;
[0119] loading a data warehouse database for off-line analysis of data from multiple databases;
[0120] synchronizing two databases;
[0121] migrating data when a database schema is updated;
[0122] moving data from an old database or database application to a replacement database or database application, respectively; and
[0123] data cleansing where multiple databases store data with redundancy and perhaps with inconsistencies.
[0124] Relational Database Schema
[0125] Relational database schema (RDBS), also referred to as table definitions or, in some instances, metadata, are used to define templates for organizing data into tables and table columns, also referred to as fields. Often multiple schema exist for the same source of data, and as such the data cannot readily be imported or exported from one application to another. The present invention describes a general method and system for transforming an input, or source relational database schema into an output, or target schema. In a preferred embodiment, the present invention can be used to provide an SQL query, which when applied to a relational database from the source schema, produces a relational database in the target schema.
[0126] As described in detail hereinbelow, the present invention preferably uses an ontology model to determine an SQL query that accomplishes a desired source to target transformation. Specifically, the present invention employs a common ontology model into which both the source RDBS and target RDBS can be mapped. By mapping the source and target RDBS into a common ontology model, the present invention derives interrelationships among their tables and fields, and uses the interrelationships to determine a suitable SQL query for transforming databases conforming with the source RDBS into databases conforming with the target RDBS.
[0127] The present invention can also be used to derive executable code that transforms source relational databases into the target relational databases. In a preferred embodiment, the present invention creates a Java program that executes the SQL query using the JDBC (Java Database Connectivity) library. In an alternative embodiment the Java program manipulates the databases directly, without use of an SQL query.
[0128] For enterprise applications, SQL queries generated by the present invention are preferably deployed within an Enterprise Application Integration infrastructure.
[0129] Although the source and target RDBS are mapped into a common ontology model, the derived transformations of the present invention go directly from source RDBS to target RDBS without having to transform data via an ontological format. In distinction, prior art Universal Data Model approaches transform via a neutral model.
[0130] The present invention applies to N relational database schema, where N.gtoreq.2. Using the present invention, by mapping the RDBS into a common ontology model, data can be moved from any one of the RDBS to any other one. In distinction to prior art approaches that require on the order of N.sup.2 mappings, the present invention requires at most N mappings.
[0131] A "mapping" from an RDBS into an ontology model is defined as:
[0132] (i) an association of each table from the RDBS with a class in the ontology model, in such a way that rows of the table correspond to instances of the class; and
[0133] (ii) for each given table from the RDBS, an association of each column of the table with a property or a composition of properties in the ontology model, the source of which is the class corresponding to the given table and the target of which has a data type that is compatible with the data type of the column.
[0134] A mapping from an RDBS into an ontology model need not be surjective. That is, there may be classes and properties in the ontology that do not correspond to tables and columns, respectively, in the RDBS. A mapping is useful in providing a graph representation of an RDBS.
[0135] In general, although a mapping from an RDBS into an ontology model may exist, the nomenclature used in the RDBS may differ entirely from that used in the ontology model. Part of the utility of the mapping is being able to translate between RDBS language and ontology language. It may be appreciated by those skilled in the art, that in addition to translating between RDBS table/column language and ontology class/property language, a mapping is also useful in translating between queries from an ontology query language and queries from an RDBS language such as SQL (standard query language).
[0136] Reference is now made to FIG. 5, which is a first simplified illustration of a mapping from an RDBS into an ontology model, in accordance with a preferred embodiment of the present invention. Shown in FIG. 5 is a table 500, denoted T1, having four columns denoted C1, C2, C3
and C4. Also shown in FIG. 1 is an ontology model 550 having a class denoted K1 and properties P1, P2, P3 and P4 defined on class T1. The labeling indicates a mapping from table T1 into class K1, and from columns C1, C2, C3 and C4 into respective properties P1, P2, P3 and P4.
[0137] Reference is now made to FIG. 6, which is a second simplified illustration of a mapping from an RDBS into an ontology model, in accordance with a preferred embodiment of the present invention. Shown in FIG. 6 are table T1 from FIG. 5, and a second table 600, denoted T2, having four columns denoted D1, D2, D3 and D4. Column C1 of table T1 is a key; i.e., each entry for column C1 is unique, and can be used as an identifier for the row in which it is situated. Column D3 of table T2
refers to table T1, by use of the key from column C1. That is, each entry of column D3 refers to a row within table T1, and specifies such row by use of the key from C1 for the row.
[0138] Also shown in FIG. 6 is an ontology model 650 having two classes, denoted K1 and K2. Class K1 has properties P1, P2, P3 and P4 defined thereon, and class K2 has properties Q1, Q2, Q4 and S defined thereon. Property S has as its source class K1 and as its target class K2. The labeling indicates a mapping from table T1 into class K1, and from columns C1, C2, C3 and C4 into respective properties P1, P2, P3 and P4. The fact that C1 serves as a key corresponds to property P1 being one-to-one, so that no two distinct instances of class K1 have the same values for property P1.
[0139] The labeling also indicates a mapping from table T2 into class K2, and from columns D1, D2 and D4 into respective properties Q1, Q2 and Q4. Column D3 corresponds to a composite property P1oS, where o denotes function composition. In other words, column D3 corresponds to property P1 of S(K2).
[0140] The targets of properties P1, P2, P3, P4, Q1, Q2 and Q4 are not shown in FIG. 6, since these properties preferably map into fundamental types corresponding to the data types of the corresponding columns entries. For example, the target of P1 may be an integer, the target of P2 may be a floating-point number, and the target of P3 may be a character string. Classes for such fundamental types are not shown in order to focus on more essential parts of ontology model 650.
[0141] Classes K1 and K2, and property S are indicated with dotted lines in ontology model 650. These parts of the ontology are transparent to the RDBS underlying tables T1 and T2. They represent additional structure present in the ontology model, which is not directly present in the RDBS.
[0142] Given a source RDBS and a target RDBS, in a preferred embodiment of the present invention an appropriate transformation of source to target RDBS is generated by:
[0143] (i) mapping the source and target RDBS into a common ontology model;
[0144] (ii) representing fields of the source and target RDBS in terms of properties of the ontology model, using symbols for properties;
[0145] (iii) deriving expressions for target symbols in terms of source symbols; and
[0146] (iv) converting the expressions into one or more SQL queries.
[0147] Reference is now made to FIG. 7, which is a simplified illustration of relational database transformations involving constraints and joins, in accordance with a preferred embodiment of the present invention.
[0148] XML Schema
[0149] As described in detail hereinbelow, the present invention preferably uses an ontology model to determine an XSLT transformation that accomplishes a desired source to target transformation. Specifically, the present invention employs a common ontology model into which both the source XML schema and target XML schema can be mapped. By mapping the source and target XML schema into a common ontology model, the present invention derives interrelationships among their elements and attributes, and uses the interrelationships to determine suitable XSLT script for transforming documents generating documents conforming to the target XML schema from documents conforming to the source XML schema.
[0150] It may be appreciated by those skilled in the art that the present invention applies to structured document formats other than XML. For example, it applies inter alia to the message formats of Tibco Active Enterprise and IBM WebsphereMQ. Similarly, the present invention applies to transformation languages other than XSLT. For example, it applies inter alia to ESQL, which is the transformation language of IBM WebsphereMQ and to the transformations used by Tibco MessageBroker.
[0151] It may be appreciated by those skilled in the art that the present invention can be employed to run in batch mode, in response to GUI commands at design-time, and also in run-time mode, to generate transformations dynamically on the fly.
[0152] The present invention can also be used to derive executable code that transforms source XML documents into the target XML documents. In a preferred embodiment, the present invention packages the derived XSLT script with a Java XSLT engine to provide an executable piece of Java code that can execute the transformation.
[0153] Preferably, this is used to deploy XSLT scripts within an EAI product such as Tibco. Specifically, in a preferred embodiment of the present invention, a function (similar to a plug-in) is installed in a Tibco MessageBroker, which uses the Xalan XSLT engine to run XSLT scripts that are presented in text form. As an optimization, the XSLT script files are preferably compiled to Java classfiles.
[0154] Reference is now made to FIG. 8A, which is a simplified block diagram of a run-time architecture for implementing a preferred embodiment of the present invention within an enterprise application integration system. Shown in FIG. 8A is an enterprise system 800
including a source application 805, such as an enterprise accounting system, and a target application 810, such as an enterprise inventory system. Source application 805 and target application 810 communicate via messaging bus 815. Typically, source application 805 sends information to target application 810 in the form of documents containing data. However, source application 805 and target application 810 may use different schema for data representation. A document processed by source application 805 typically must conform to a source data schema used by source application 805, and similarly a document processed by target application 810 typically must conform to a target data schema used by target application 810. Thus in general documents prepared by source application 805 are not in a proper format to be understood by target application 810.
[0155] To facilitate document routing and transformation, a Message Broker 820 is used to perform simple address-based and rule-based routing and transformation Message Broker 820 is typically part of an enterprise application integration (EAI product, such as IBM's WebsphereMQ or Tibco's Active Enterprise. "Message Broker" is a term used by Tibco. Within IBM WebsphereMQ, it is referred to as an "Integrator." For purposes of clarity the name Message Broker is used henceforth within the present specification.
[0156] Message Broker 820 includes an adapter 825 and a transform plug-in 830. Adapter 825 is a component that enables Message Broker 820 to communicate with outside software. For example, adapter 825 may be used to query a database or to get information from an enterprise information system such as SAP. Plug-in 830 is a component that is dynamically loaded into Message Broker 820. Plug-in 830 is used to run XSLT within enterprise applications such as Tibco Active Enterprise and IBM WebsphereMQ, which use their own transformation languages.
[0157] For some EAI systems, use of adapter 825 may be optional. Generally, though, vendors of EAI systems recommend use of an adapter, rather than direct network access by plug-in 830. Adapter 825 may implement either (i) proprietary application programming interfaces (APIs) exposed by Message Broker 820, such as Tibco Message Broker or WebsphereMQ Integrator, or (ii) cross-platform APIs, such as Java Connector Architecture.
[0158] Also shown in FIG. 8A is a run-time transformation server 835, which communicates with plug-in 830 via adapter 825. In a preferred embodiment of the present invention, run-time transformation server 835
is used to assist Message Broker 820 in converting documents from the source schema to the target schema. Plug-in 830 is used to prepare requests and process responses for run-time transformation server 835. There are several embodiments regarding the role of transformation server 835, three of which are described in FIGS. 8B-8D. For enterprise applications that run XSLT natively, plug-in 830 may not be necessary. Specifically, for architectures based on the implementation illustrated in FIG. 8B, where run-time transformation server 835 is not used and the purpose of plug-in 830 is to run XSLT scripts, plug-in 830 is not necessary for enterprise applications that run XSLT natively. However, for architectures based on implementations which use run-time transformation server 835, such as the implementations illustrated in FIGS. 8C and 8D, plug-in 830 is also used to prepare responses and process requests from run-time transformation server 835 and, as such, is a necessary component even within enterprise applications that run XSLT natively.
[0159] Communication between adapter 825 and run-time transformation server 835 is preferably achieved using an appropriate network request-response protocol.
[0160] It may be appreciated by those skilled in the art that one or more additional nodes may serve as protocol bridges between adapter 825 and run-time transformation server 835, translating among different network protocols. For example, in one implementation of the present invention, adapter 825 communicates using Simple Object Access Protocol (SOAP) Web Services with a SOAP Web Services server, such as Apache Jakarta Tomcat with Apache Axis. Such a SOAP Web Services server acts as a bridge, passing communication over Remote Method Invocation (RMI) to run-time transformation server 835.
[0161] Information about SOAP and Web Services is available though the World-Wide-Web Consortium at http://www.w3.org/2002/ws.
[0162] Reference is now made to FIG. 8B, which is a simplified flowchart for a first implementation of the present invention within an enterprise application integration system. In this first implementation, run-time transformation server 835 is not used. At step 850 Message Broker 820
receives a document from source application 805, intended for target application 810. In this first implementation, Message Broker 820 is configured to know the source schema and target schema, and to know which transformation to use for a given source schema and target schema. At step 890 plug-in 830 runs the specified transformation and transforms the document from the source schema to the target schema. At step 895, Message Broker 820 sends the transformed document to target application 810.
[0163] Reference is now made to FIG. 8C, which is a simplified flowchart for a second implementation of the present invention within an enterprise application integration system. In this second implementation run-time transformation server 835 is used to provide plug-in 830 with an appropriate transformation, such as an appropriate XSLT file. At step 850, Message Broker 820 receives a document from source application 805, intended for target application 810. In this second implementation, Message Broker 820 is configured to know the source and target schema, but not the transformation. Preferably, Message Broker 820 extracts an identifier for the source schema from an XML document received from source application 805. At step 855, plug-in 830 requests a transformation, such as an XSLT file or XSLT translet, from run-time transformation server 835. Preferably, plug-in 830 issues a request to run-time transformation server 835 specifying a source and target schema and requesting a transformation therebetween.
[0164] At step 860 run-time transformation server 835 generates the requested transformation, in accordance with a preferred embodiment of the present invention. It may be appreciated by those skilled in the art that run-time transformation server 835 may already have the requested transformation available, for example, having cached it from a previous request, or having it otherwise available in a memory store. In such a case, step 860 is omitted. At step 865 run-time transformation server 835
sends the requested transformation to plug-in 830. At step 890 plug-in 830 runs the specified transformation and transforms the document to the target schema. At step 895, Message Broker 820 sends the transformed document to target application 810.
[0165] Reference is now made to FIG. 8D, which is a simplified flowchart for a third implementation of the present invention within an enterprise application integration system. In this third implementation run-time transformation server 835 is used to perform the transformations from source schema to target schema. At step 850, Message Broker 820 receives a document from source application 805, intended to target application 810. In this third implementation, Message Broker 820 is configured to know the target schema. At step 870 plug-in 830 sends the document to run-time transformation server 835, and requests that run-time transformation server 835 transform the document to a specified target schema.
[0166] Run-time transformation server 835 receives the source document and the target schema. Typically, the source document includes information about the source schema. For example, XML documents typically include references to the XML schema to which they conform. Run-time transformation server 835 generates an appropriate transformation, in accordance with a preferred embodiment of the present invention, and at step 875 run-time transformation server 835 transforms the document from the source schema to the target schema. It may be appreciated by those skilled in the art that run-time transformation server 835 may already have the requested transformation available, for example, having cached it from a previous request, or having it otherwise available in a memory store. In such a case, it is not necessary to generate the transformation.
[0167] At step 880 run-time transformation server 835 sends the transformed document to plug-in 830. At step 885, plug-in 830 passes the documents to Message Broker 820, and at step 895 Message Broker 820 sends the transformed document to target application 810.
[0168] Reference is now made to FIG. 8E, which is a simplified illustration of an enterprise application product, such as Tibco Active Enterprise, with the present invention therewithin. Shown in FIG. 8E is a Message Broker data flow in which an input document is transformed for delivery to two targets, each target having its own target schema Unicorn Coherence is used to generate each of the source-to-target transformation, in accordance with a preferred embodiment of the present invention. FIG. 8E corresponds to the flowchart shown in FIG. 8B.
[0169] It may be appreciated by those skilled in the art that source application 805 and target application 810 may use multiple schemata and, as such, the references to source schema and target schema are intended to include single schema and multiple schemata.
[0170] User Interface
[0171] Applicant has developed a software application, named COHERENCE.TM., which implements a preferred embodiment of the present invention to transform data from one schema to another. Coherence enables a user
[0172] to import source and target RDBS;
[0173] to build an ontology model into which both the source and target RDBS can be mapped;
[0174] to map the source and target RDBS into the ontology model; and
[0175] to impose constraints on properties of the ontology model.
[0176] Once the mappings are defined, Coherence generates an SQL query to transform the source RDBS into the target RDBS.
[0177] Reference is now made to FIGS. 9A-9E, which are illustrations of a user interface for transforming data from one relational database schema to another using the Coherence software application, in accordance with a preferred embodiment of the present invention. Shown in FIG. 9A is a main Coherence window 905 with a left pane 910 and a right pane 915. Window 905 includes three primary tabs 920, 925 and 930, labeled Authoring, Mapping and Transformations, respectively. Authoring tab 920 is invoked in order to display information about the ontology model, and to modify the model by adding, deleting and editing classes and properties. Mapping tab 925 is invoked in order to display information about the RDBS and the mappings of the RDBS into the ontology, and to edit the mappings. Transformations tab 930 is invoked to display transformations in the form of SQL queries, from a source RDBS into a target RDBS. In FIG. 9A, tab 920 for Authoring is shown selected.
[0178] Left pane 910 includes icons for two modes of viewing an ontology: icon 935 for viewing in inheritance tree display mode, and icon 940 for viewing in package display mode.
[0179] Inheritance tree display mode shows the classes of the ontology in a hierarchical fashion corresponding to superclass and subclass relationships. As illustrated in FIG. 9A, in addition to the fundamental classes for Date, Number, Ratio, String and NamedElement, there is a class for City. Corresponding to the class selected in left pane 910, right pane 915 displays information about the selected class. Right pane 915 includes six tabs for class information display: tab 945 for General, tab 950 for Properties, tab 955 for Subclasses, tab 960 for Enumerated Values, tab 965 for Relations and tab 970 for XML schema. Shown in FIG. 9A is a display under tab 945 for General. The display includes the name of the class, Being, and the package to which it belongs; namely, fundamental. Also shown in the display is a list of immediate superclasses, which is an empty list for class Being. Also shown in the display is a textual description of the class; namely, that Being is a root class for all classes.
[0180] Tab 960 for Enumerated Values applies to classes with named elements; i.e., classes that include a list of all possible instances. For example, a class Boolean has enumerated values "True" and "False," and a class Gender may have enumerated values "Male" and "Female."
[0181] FIG. 9B illustrates package display mode for the ontology. Packages are groups including one or more ontology concepts, such as classes, and properties. Packages are used to organize information about an ontology into various groupings. As illustrated in FIG. 9B, there is a fundamental package that includes fundamental classes, such as Being, Boolean, Date and Integer. Also shown in FIG. 19B is a package named WeatherFahrenheit, which includes a class named City.
[0182] As shown in FIG. 9B, City is selected in left pane 910 and, correspondingly, right pane 915 displays information about the class City. Right pane 915 display information under Tab 950 for Properties. As can be seen, class City belongs to the package WeatherFahrenheit, and has four properties; namely, Celsius of type RealNumber, city of type String, Fahrenheit of type RealNumber and year of type RealNumber. FIG. 9B indicates that the property Celsius satisfies a constraint. Specifically, Celsius=5*(Fahrenheit-32)/9.
[0183] In FIG. 9C, the tab 925 for Mapping is shown selected. As shown in the left pane of FIG. 9C, two RDBS have been imported into Coherence. A first RDBS named WeatherCelsius, which includes a table named Towns, and a second RDBS named WeatherFahrenheit, which includes a table named Cities.
[0184] The table named Cities is shown selected in FIG. 9C, and correspondingly the right pane display information regarding the mapping of Cities into the ontology. As can be seen, the table Cities contains three fields; namely, Fahrenheit, city and year. The table Cities has been mapped into the ontology class City, the field Fahrenheit has been mapped into the ontology property Fahrenheit, the field city has been mapped into the ontology property name, and the field year has been mapped into the ontology property year. The RDBS WeatherFahrenheit will be designated as the source RDBS.
[0185] When tab 925 for Mapping is selected, the right pane includes three tabs for displaying information about the RDBS: tab 975 for Map Info, tab 980 for Table Info and tab 985 for Foreign Keys.
[0186] The RDBS named WeatherCelsius is displayed in FIG. 9D. As can be seen, the table Towns contains three fields; namely, town, Celcius and year. The table Towns has been mapped into the ontology class City, the field town has been mapped into the ontology property name, the field Celcius has been mapped into the ontology property Celcius, and the field year had been mapped into the ontology property year. The RDBS WeatherCelcius will be designated as the target RDBS.
[0187] As such, the target RDBS is
1TABLE I Towns 1
[0188] and the source RDBS is
2TABLE II Cities 2
[0189] In FIG. 9E, the tab 930 for Transformations is shown selected. As can be seen in the right pane, the source table is Cities and the target table is Towns. The SQL query
3
INSERT INTO.vertline. WeatherCelcius.Towns(CELCIUS, TOWN, YEAR) (SELECT (5 * (A.FAHRENHEIT - 32)/9) AS CELCIUS, A.CITY AS TOWN, A.YEAR AS YEAR FROM WeatherFahrenheit.Cities A);
[0190] accomplishes the desired transformation.
[0191] Reference is now made to FIG. 10, which is an illustration of a user interface for an application that imports an RDBS into Coherence, in accordance with a preferred embodiment of the present invention. Shown in FIG. 10 is a window 1010 for a schema convertor application. Preferably, a user specifies the following fields:
[0192] Database Name 1020: What Oracle refers to as an SID (System Identifier).
[0193] Host Name 1030: The name of an Oracle 8i server (or Global Database Name).
[0194] Port 1040: Port number
[0195] Username 1050: The username of a user with privileges to the relevant schemas.
[0196] Password 1060: The password of the user with privileges to the relevant schemas.
[0197] Oracle schema 1070: The schema or database in Oracle to be converted to .SML format. The .SML format is an internal RDBS format used by Coherence. When importing more than one schema, a semicolon (;) is placed between schema names.
[0198] Coherence schema 2080: The label identifying the RDBS that is displayed on the Mapping Tab in Coherence. This field is optional; if left blank, the Oracle schema name will be used.
[0199] Output File 1090: A name for the .SML file generated.
[0200] Reference is now made to FIGS. 11A-11R, which are illustrations of a for transforming data from one XML schema to another using the Coherence software application, in accordance with a preferred embodiment of the present invention. Shown in FIG. 11A is a window with package view of an Airline Integration ontology model in its left lane. The left pane displays classes from a fundamental package. A class Date is shown highlighted; and its properties are shown in the right pane. Fundamental packages are used for standard data types. Shown in FIG. 11B is a window with a hierarchical view of the Airline Integration ontology model in its left pane. The left pane indicates that FrequentFlyer is a subclass of Passenger, Passenger is a subclass of Person, and Person is a subclass of Being. The right pane displays general information about the class FrequentFlyer.
[0201] FIG. 11C shows a window used for opening an existing ontology model. In the Coherence software application, ontology models are described using XML and stored in .oml files. Such files are described in applicant's co-pending patent application U.S. Ser. No. 09/866,101 filed on May 25, 2001 and entitled METHOD AND SYSTEM FOR COLLABORATIVE ONTOLOGY MODELING, the contents of which are hereby incorporated by reference.
[0202] FIG. 11D shows the hierarchical view from FIG. 11B, indicating properties of the FrequentFlyer class. The property fullName is highlighted, and a window for constraint information indicates that there is a relationship among the ontology properties firstName, lastName and fullName; namely, that fullName is the concatenation of firstName and lastName with a white space therebetween. This relationship is denoted as Constraint.sub.--5.
[0203] FIG. 11E shows the hierarchical view from FIG. 11B, indicating test instance of the Passenger class. A list of instances is displayed in the right pane, along with property values for a specific selected instance from the list.
[0204] FIG. 11F shows two imported XML schema for airline information. FIG. 11G shows a window for importing XML schema into Coherence. FIG. 11H shows a window with a display of an imported XML schema for British Airways, with a list of complexTypes from the imported schema. The complexType Journey is selected, and the right pane indicates that Journey and its elements are currently not mapped to a class and properties of the ontology model.
[0205] FIG. 11I shows a window for generating a mapping from the British Airways XML schema into the Airline Integration ontology model. The ontology class Flight is shown selected to correspond to the XML ComplexType Journey. FIG. 11J shows the left pane from FIG. 11H, with the right pane now indicating that the XML complexType Journey from the British Airways XML schema has been mapped to the class Flight from the Airline Integration ontology model. FIG. 11K shows the left pane from FIG. 11H, with a window for selecting properties and indirect properties (i.e., compositions of properties) to correspond to elements from the XML schema. Shown selected in FIG. 11K is a property distanceInMile( ) of the class Flight. FIG. 11L shows the left pane from FIG. 11H, with the right pane now indicated that Journey has been mapped to Flight, and the XML element distance_in_miles within the complexType Journey has been mapped to the property distanceInMiles( ) of the class Flight. FIG. 11M shows the left pane from FIG. 11H, with the right pane now indicating that the mapping has been extended to all XML elements of the complexType Journey, showing the respective properties to which each element is mapped. FIG. 11N shows schema info for the complexType Journey, listing its elements and their data types.
[0206] FIG. 11O shows a window for specifying a transformation to be derived. Shown in FIG. 11O is a request to derive a transformation from a source data schema, namely, the imported SwissAir XML schema to a target data schema, namely, the imported British Airways XML schema. Shown in FIG. 11P is an XSLT script generated to transform XML documents conforming to the SwissAir schema to XML documents conforming to the British Airways schema FIG. 11Q shows a specific transformation of a SwissAir XML document to a British Airways XML document, obtained by applying the derived XSLT script from FIG. 11P. Finally, FIG. 11R shows a display of the newly generated British Airways XML document with specific flights and passengers.
EXAMPLES
[0207] For purposes of clarity and exposition, the workings of the present invention are described first through a series of twenty-three examples, followed by a general description of implementation. Two series of examples are presented. The first series, comprising the first eleven examples, relates to RDBS transformations:. For each of these examples, a source RDBS and target RDBS are presented as input, along with mappings of these schema into a common ontology model. The output is an appropriate SQL query that transforms database tables that conform to the source RDBS, into database tables that conform to the target RDBS. Each example steps through derivation of source and target symbols, expression of target symbols in terms of source symbols and derivation of an appropriate SQL query based on the expressions.
[0208] The second series of examples, comprising the last twelve examples, relates to XSLT transformation. For each of these examples, a source XML schema and target XML schema are presented as input, along with mappings of these schema into a common ontology model. The output is an appropriate XSLT script that transforms XML documents that conform to the source schema into XML documents that conform to the target schema.
A First Example
Schoolchildren
[0209] In a first example, a target table is of the following form.
4TABLE III Target Table T for First Example 3
[0210] Four source tables are given as follows:
5TABLE IV Source Table S.sub.1 for First Example 4
[0211]
6TABLE V Source Table S.sub.2 for First Example 5
[0212]
7TABLE VI Source Table S.sub.3 for First Example 6
[0213]
8TABLE VII Source Table S.sub.4 for First Example 7
[0214] The underlying ontology is illustrated in FIG. 12. The dotted portions of the ontology in FIG. 12 show additional ontology structure that is transparent to the relational database schema. Using the numbering of properties indicated in FIG. 12, the unique properties of the ontology are identified as:
9TABLE VIII Unique Properties within Ontology for First Example Property Property Index name(Child) 6
national_insurance_number(Person) 4
name(School) 10
[0215] The mapping of the target schema into the ontology is as follows:
10TABLE IX Mapping from Target schema to Ontology for First Example Prop- erty schema Ontology Index T Class: Child T.Child_Name Property: name(Child) 6
T.Mother_Name Property: name(mother(Child)) 3o5
T.School.sub.-- Property: location(school.sub.-- 12o9
Location attending(Child)) T.Form Property: current_school_form(Child) 8
[0216] The symbol o is used to indicate composition of properties. The mapping of the source schema into the ontology is as follows:
11TABLE X Mapping from Source schema to Ontology for First Example Prop- erty schema Ontology Index S.sub.1 Class: Child S.sub.1.Name Property: name(Child) 6
S.sub.1.School_Attending Property: name(school_attending 10o9
(Child)) S.sub.1.Mother_NI_Number Property: national.sub.-- 4o5
insurance_number(mother(Child)) S.sub.2 Class: Person S.sub.2.NI_Number Property: national_insurance.sub.-- 4
number(Person) S.sub.2.Name Property: name(Person) 3
S.sub.2.Region Property: region_of_residence(Person) 1
S.sub.2.Car_Number Property: car_registration_number 2
(Person) S.sub.3 Class: School S.sub.3.Name Property: name(School) 10
S.sub.3.Location Property: location(School) 12
S.sub.3.HeadTeacher Property: name(headteacher(School)) 3o11
S.sub.4 Class: Child S.sub.4.Name Property: name(Child) 6
S.sub.4.Year Property: year_of_schooling(Child) 7
S.sub.4.Form Property: current_school_form(Child) 8
[0217] The indices of the source properties are:
12TABLE XI Source Symbols for First Example Source Table Source Symbols S.sub.1
10o9o6.sup.-1
4o5o6.sup.-1
S.sub.2
3o4.sup.-1
1o4.sup.-1
2o4.sup.-1
S.sub.3
12o10.sup.-1
3o11o10.sup.-1
S.sub.4
7o6.sup.-1
8o6.sup.-1
[0218] The symbols in Table XI relate fields of a source table to a key field. Thus in table S.sub.1 the first field, S.sub.1.Name is a key field. The second field, S.sub.1.School_Attending is related to the first field by the composition 10o9o6.sup.-1, and the third field, S.sub.1.Mother_NI_Number is related to the first field by the composition 4o5o6.sup.-1. In general, if a table contains more than one key field, then expressions relative to each of the key fields are listed.
[0219] The inverse notation, such as 6.sup.-1 is used to indicate the inverse of property 6. This is well defined since property 6 is a unique, or one-to-one, property in the ontology model. The indices of the target properties, keyed on Child_Name are:
13TABLE XII Target Symbols for First Example Target Table Target Symbols Paths T 3o5o6.sup.-6 (3o4.sup.-1) o (4o5o6.sup.-1) 12o9o6.sup.-1
(12o10.sup.-1) o (10o9o6.sup.-1) 8o6.sup.-1 (8o6.sup.-1)
[0220] Based on the paths given in Table XII, the desired SQL query is:
14
INSERT INTO T(Child_Name, Mother_Name, School_Location, Form) (SELECT S.sub.1.Name AS Child_Name, S.sub.2.Name AS Mother_Name, S.sub.3.Location AS School_Location, S.sub.4.Form AS Form FROM S.sub.1, S.sub.2, S.sub.3, S.sub.4
WHERE S.sub.2.NI_Number = S.sub.1.Mother_NI_Number AND S.sub.3.Name = S.sub.1.School_Attending AND S.sub.4.Name = S.sub.1.Name);
[0221] The rules provided with the examples relate to the stage of converting expressions of target symbols in terms of source symbols, into SQL queries. In general,
[0222] Rule 1: When a target symbol is represented using a source symbols, say (aob.sup.-1), from a source table, S, then the column of S mapping to a is used in the SELECT clause of the SQL query and the column of S mapping to b is used in the WHERE clause.
[0223] Rule 2: When a target symbol is represented as a composition of source symbols, say (aob.sup.-1) o (boc.sup.-1), where aob.sup.-1 is taken from a first source table, say S.sub.1, and boc.sup.-1 is taken from a second source table, say S.sub.2, then S.sub.1 and S.sub.2 must be joined in the SQL query by the respective columns mapping to b.
[0224] Rule 3: When a target symbol is represented using a source symbols, say (aob.sup.-1), from a source table, S, and is not composed with another source symbol of the form boc.sup.-1, then table S must be joined to the target table through the column mapping to b.
[0225] When applied to the following sample source data, Tables XIII, XIV, XV and XVI, the above SQL query produces the target data in Table XVII.
15TABLE XIII Sample Source Table S.sub.1 for First Example Name School Attending Mother NI Number Daniel Ashton Chelsea Secondary School 123456
Peter Brown Warwick School for Boys 673986
Ian Butler Warwick School for Boys 234978
Matthew Davies Manchester Grammar School 853076
Alex Douglas Weatfields Secondary School 862085
Emma Harrison Camden School for Girls 275398
Martina Howard Camden School for Girls 456398
[0226]
16TABLE XIV Sample Source Table S.sub.2 for First Example NI_Number Name Region Car_Number 123456
Linda London NULL 673986 Amanda Warwick NULL 456398 Claire Cambridgeshire NULL 862085 Margaret NULL NULL 234978 Amanda NULL NULL 853076 Victoria Manchester NULL 275398 Elizabeth London NULL
[0227]
17TABLE XV Sample Source Table S.sub.3 for First Example Name Location HeadTeacher Manchester Grammar School Manchester M. Payne Camden School for Girls London J. Smith Weatfields Secondary School Cambridgeshire NULL Chelsea Secondary School London I. Heath Warwick School for Boys Warwickshire NULL
[0228]
18TABLE XVII Sample Source Table S.sub.4 for First Example Name Year Form Peter Brown 7 Lower Fourth Daniel Ashton 10 Mid Fifth Matthew Davies 4 Lower Two Emma Harrison 6 Three James Kelly 3 One Greg McCarthy 5 Upper Two Tina Reynolds 8 Upper Fourth
[0229]
19TABLE XVI Sample Target Table T for First Example Child_Name Mother_Name School_Location Form Daniel Ashton Linda London Mid Fifth Peter Brown Amanda Warwickshire Lower Fourth Matthew Davies Victoria Manchester Lower Two Emma Harrison Elizabeth London Three
A Second Example
Employees
[0230] In a second example, a target table is of the following form:
20TABLE XVIII Target Table T for Second Example 8
[0231] Four source tables are given as follows:
21TABLE XIX Source Table S.sub.1 for Second Example 9
[0232]
22TABLE XX Source Table S.sub.2 for Second Example 10
[0233]
23TABLE XXI Source Table S.sub.3 for Second Example 11
[0234]
24TABLE XXII Source Table S.sub.4 for Second Example 12
[0235] The underlying ontology is illustrated in FIG. 13. The dotted portions of the ontology in FIG. 13 are additional ontology structure that is transparent to the relational database schema. The unique properties of the ontology are:
25TABLE XXIII Unique Properties within Ontology for Second Example Property Property Index name(Employee) 3
ID#(Employee) 4
[0236] The mapping of the target schema into the ontology is as follows:
26TABLE XXIV Mapping from Target schema to Ontology for Second Example schema Ontology Property Index T Class: Employee T.Name Property: name(Employee) 3
T.Department Property: code(departmental_affiliation 8o7
(Employee)) T.Supervisor Property: name(supervisor(Employee)) 3o6
T.Room# Property: room_number(Employee) 1
[0237] The mapping of the source schema into the ontology is as follows:
27TABLE XXV Mapping from Source schema to Ontology for Second Example schema Ontology Property Index S.sub.1 Class: Employee S.sub.1.Emp_ID# Property: ID#(Employee) 4
S.sub.1.Name Property: name(Employee) 3
S.sub.1.Department Property: code(departmental.sub.-- affiliation(Employee)) 8o7
S.sub.2 Class: Employee S.sub.2.Employee.sub.-- Property: name(Employee) 3
Name S.sub.2.Supervisor Property: name(supervisor(Employee)) 3o6
S.sub.2.Project Property: project_assignment 5
(Employee) S.sub.3 Class: Employee S.sub.3.ID# Property: ID#(Employee) 4
S.sub.3.Room.sub.-- Property: room_number(Employee) 1
Assignment S.sub.3.Telephone# Property: tel#(Employee) 2
S.sub.4 Class: Department S.sub.4.Department Property: code(Department) 8
S.sub.4.Budget Property: budget_amount(Department) 9
[0238] The indices of the source properties are:
28TABLE XXVI Source Symbols for Second Example Source Table Source Symbols S.sub.1
3o4.sup.-1
8o7o4.sup.-1
4o3.sup.-1
8o7o3.sup.-1
S.sub.2
3o6o3.sup.-1
5o3.sup.-1
S.sub.3
1o4.sup.-1
2o4.sup.-1
S.sub.4
9o8.sup.-1
[0239] The indices of the target properties, keyed on Name are:
29TABLE XXVII Target Symbols for Second Example Target Table Target Symbols Paths T 8o7o3.sup.-1 (8o7o3.sup.-1) 3o6o3.sup.-1 (3o6o3.sup.-1) 1o3.sup.-1 (1o4.sup.-1) o (4o3.sup.-1)
[0240] Based on the paths given in Table XXVII, the desired SQL query is:
30
INSERT INTO T(Name, Department, Supervisor, Room#) (SELECT S.sub.1.Name AS Name, S.sub.1.Department AS Department, S.sub.2.Supervisor AS Supervisor, S.sub.3.Room_Assignment AS Room# FROM S.sub.1, S.sub.2, S.sub.3
WHERE S.sub.2.Employee_Name = S.sub.1.Name AND S.sub.3.ID# = S.sub.1.Emp_ID#);
[0241] It is noted that Table S.sub.4 not required in the SQL. When applied to the following sample source data, Tables XXVIII, XXIX and XXX, the above SQL query produces the target data in Table XXXI.
31TABLE XXVIII Sample Source Table S.sub.1 for Second Example EMP_ID# Name Department 198
Patricia SW 247 Eric QA 386 Paul IT
[0242]
32TABLE XXIX Sample Source Table S.sub.2 for Second Example Employee_Name Supervisor Project Eric John Release 1.1
Patricia George Release 1.1
Paul Richard Release 1.1
[0243]
33TABLE XXX Sample Source Table S.sub.3 for Second Example ID# Room_Assignment Telephone# 386 10 106
198 8 117
247 7 123
[0244]
34TABLE XXXI Sample Target Table T for Second Example Name Department Supervisor Room# Patricia SW George 8
Eric QA John 7
Paul IT Richard 10
A Third Example
Airline Flights
[0245] In a third example, a target table is of the following form:
35TABLE XXXII Target Table T for Third Example 13
[0246] Two source tables are given as follows:
36TABLE XXXIII Source Table S.sub.1 for Third Example 14
[0247]
37TABLE XXXIV Source Table S.sub.2 for Third Example 15
[0248] The underlying ontology is illustrated in FIG. 14. The dotted portions of the ontology in FIG. 14 are additional ontology structure that is transparent to the relational database schema. The unique properties of the ontology are:
38TABLE XXXV Unique Properties within Ontology for Third Example Property Property Index name(Airport) 1
ID(Flight) 6
[0249] The mapping of the target schema into the ontology is as follows:
39TABLE XXXVI Mapping from Target schema to Ontology for