Method and system for mapping enterprise data assets to a semantic information model

[0001] This application is a continuation-in-part of assignee's application U.S. Ser. No. 10/340,068, filed on Jan. 9, 2003 now abandoned, entitled “Brokering Semantics between Web Services”, which is a continuation-in-part of assignee's application U.S. Ser. No. 10/302,370, filed on Nov. 22, 2002 now U.S. Pat. No. 7,673,282, entitled “Enterprise Information Unification”, which is a continuation-in-part of assignee's application U.S. Ser. No. 10/159,516, filed on May 31, 2002 now abandoned, entitled “Data Query and Location through a Central Ontology Model,” which is a continuation-in-part of application U.S. Ser. No. 10/104,785, filed on Mar. 22, 2002 now U.S. Pat. No. 7,146,399, entitled “Run-Time Architecture for Enterprise Integration with Transformation Generation,” which is a continuation-in-part of application U.S. Ser. No. 10/053,045, filed on Jan. 15, 2002 now abandoned, 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 now U.S. Pat. No. 7,093,200, 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 now U.S. Pat. No. 7,099,885, entitled “Method and System for Collaborative Ontology Modeling.”

[0002] The present invention relates to data schema, and in particular to deriving transformations for transforming data from one schema to another.

[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] 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.

[0005] 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.

[0006] 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.

[0007] Properties are specific associations of things with other things. Properties include:

• • [0008] Relations between things that are part of each other, for example, between a PC and its flat panel screen;
  • [0009] Relations between things that are related through a process such as the process of creating the things, for example, a book and its author;
  • [0010] Relations between things and their measures, for example, a thing and its weight.

[0011] 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.

[0012] 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.

[0013] 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.

[0014] 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.

[0015] 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.

[0016] In a typical mathematical model, a basic ontology comprises:

• • [0017] A set C, the elements of which are called “class symbols;”
  • [0018] For each CεC, a plain language definition of the class C;
  • [0019] A set P, the elements of which are called “property symbols;”
  • [0020] For each PεF:
    • [0021] a plain language definition of P;
    • [0022] a class symbol called the source of P; and
    • [0023] a class symbol called the target of P; and
  • [0024] A binary transitive reflexive anti-symmetric relation, I, called the inheritance relation on C×C.

[0025] 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.

[0026] 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.

[0027] If I(C₁, C₂) then C₁ is referred to as a subclass of C₂, and C₂ is referred to as a superclass of C₁. Also, C₁ is said to inherit from C₂.

[0028] A distinguished universal class “Being” is typically postulated to be a superclass of all classes in C.

[0029] Variations on an ontology model may include:

• • [0030] Restrictions of properties to unary properties, these being the most commonly used properties;
  • [0031] The ability to specify more about properties, such as multiplicity and invertibility.

[0032] 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.

[0033] 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.

[0034] 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.

[0035] 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.

[0036] 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₁)⊂B(C₂) whenever C₁ is a subclass of C₂. Property symbols with source C₁ and target C₂ correspond to properties with source B(C₁) and target B(C₂). It is noted that if class C₁ inherits from class C, then every instance of C₁ 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₁.

[0037] 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.

[0038] Provision is made for both 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.

[0039] In terms of formal mathematics, for a set S, set[S] is P(S), the power set of S; bag[S] is N^S, where N is the set of non-negative integers; and list[S] is

[0040] $\underset{n=1}{\bigcup ^{\infty }}{S}^n.$
There are natural mappings

[0041]
Specifically, for a sequence (s₁, s₂, . . . , s_n)εlist[S], φ(s₁, s₂, . . . , s_n) is the element fεbag[S] that is the “frequency histogram” defined by f(s)=#{1≦i≦n: s_i=s}; and for fεbag[S], ψ(f)εset[S] is the subset of S given by the support of f, namely, supp(f)={sεS: f(s)>0}. It is noted that the composite mapping φψ maps a the sequence (s₁, s₂, . . . , s_n) into the set of its elements {s₁, s₂, . . . , s_n}. For finite sets S, set[S] is also finite, and bag[S] and list[S] are countably infinite.

[0042] A general reference on ontology systems is Sowa, John F., “Knowledge Representation,” Brooks/Cole, Pacific Grove, Calif., 2000.

[0043] 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.

[0044] A general reference on relation databases and SQL is the document “Oracle 9i: SQL Reference,” available on-line at http://www.oracle.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.

[0045] 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.

[0046] There is thus a need for a tool that can transform data conforming with a first schema into data that conforms with a second schema.

[0047] 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 conforming 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.

[0048] 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 with the source data schema into data conforming with the target data schema.

[0049] 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:

• • [0050] (i) mapping the source and target RDBS into a common ontology model;
  • [0051] (ii) representing table columns of the source and target RDBS in terms of properties of the ontology model;
  • [0052] (iii) deriving expressions for target table columns in terms of source table columns; and
  • [0053] (iv) converting the expressions into one or more SQL queries.

[0054] 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.

[0055] The present invention applies to N relational database schema, where N≧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² mappings, the present invention requires at most N mappings.

[0056] 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.

[0057] 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:

• • [0058] (i) mapping the source and target XML schema into a common ontology model;
  • [0059] (ii) representing elements and attributes of the source and target XML schema in terms of properties of the ontology model;
  • [0060] (iii) deriving expressions for target XML elements and XML attributes in terms of source XML elements and XML attributes; and
  • [0061] (iv) converting the expressions into an XSLT script.

[0062] There is thus provided in accordance with a preferred embodiment of the present invention a method for mapping data schemas into an ontology model, including providing an ontology model including classes and properties of classes, providing a data schema, identifying a primary data construct within the data schema, identifying a secondary data construct within the primary data construct, mapping the primary data construct to a corresponding class of the ontology model, and mapping the secondary data construct to a property of the corresponding class of the ontology model.

[0063] There is moreover provided in accordance with a preferred embodiment of the present invention a method for mapping data schemas into an ontology model, including providing an ontology model including classes and properties of classes, each property having associated therewith a target class, providing a data schema, identifying a primary data construct within the data schema, identifying a secondary data construct within the primary data construct, mapping the primary data construct to a corresponding class of the ontology model, and mapping the secondary data construct to an inverse of a property whose target class is the corresponding class of the ontology model.

[0064] There is additionally provided in accordance with a preferred embodiment of the present invention a method for mapping data schemas into an ontology model, including providing an ontology model including classes and properties of classes, and including inheritance relationships for superclasses, providing a data schema, identifying a primary data construct within the data schema, identifying a secondary data construct within the primary data construct, mapping the primary data construct to a corresponding class of the ontology model, and mapping the secondary data construct to a property of a superclass of the corresponding class of the ontology model.

[0065] There is further provided in accordance with a preferred embodiment of the present invention a method for mapping data schemas into an ontology model, including providing an ontology model including classes and properties of classes, and including inheritance relationships for superclasses, providing a data schema, identifying a primary data construct within the data schema, identifying a secondary data construct within the primary data construct, mapping the primary data construct to a corresponding class of the ontology model, and mapping the secondary data construct to an inverse of a property whose target class is a superclass of the corresponding class of the ontology model.

[0066] There is yet further provided in accordance with a preferred embodiment of the present invention a method for mapping data schemas into an ontology model, including providing an ontology model including classes and properties of classes, providing a data schema, identifying a primary data construct within the data schema, identifying a secondary data construct within the primary data construct, mapping the primary data construct to a corresponding class of the ontology model, and mapping the secondary data construct to a composition of properties, one of which is a property of the corresponding class of the ontology model.

[0067] There is additionally provided in accordance with a preferred embodiment of the present invention a system for mapping data schemas into an ontology model, including a memory for storing an ontology model including classes and properties of classes, and a data schema, a schema parser for identifying a primary data construct within the data schema, and identifying a secondary data construct within the primary data construct, and a schema mapper for mapping the primary data construct to a corresponding class of the ontology model, and for mapping the secondary data construct to a property of the corresponding class of the ontology model.

[0068] There is further provided in accordance with a preferred embodiment of the present invention a system for mapping data schemas into an ontology model, including a memory for storing an ontology model including classes and properties of classes, each property having associated therewith a target class, and a data schema, a schema parser for identifying a primary data construct within the data schema, and identifying a secondary data construct within the primary data construct, and a schema mapper for mapping the primary data construct to a corresponding class of the ontology model, and mapping the secondary data construct to an inverse of a property whose target class is the corresponding class of the ontology model.

[0069] There is yet further provided in accordance with a preferred embodiment of the present invention a system for mapping data schemas into an ontology model, including a memory for storing an ontology model including classes and properties of classes, and including inheritance relationships for superclasses, and a data schema, a schema parser for identifying a primary data construct within the data schema, and identifying a secondary data construct within the primary data construct, and a schema mapper for mapping the primary data construct to a corresponding class of the ontology model, and mapping the secondary data construct to a property of a superclass of the corresponding class of the ontology model.

[0070] There is moreover provided in accordance with a preferred embodiment of the present invention a system for mapping data schemas into an ontology model, including a memory for storing an ontology model including classes and properties of classes, and including inheritance relationships for superclasses, and a data schema, a schema parser for identifying a primary data construct within the data schema, and identifying a secondary data construct within the primary data construct, and a schema mapper for mapping the primary data construct to a corresponding class of the ontology model, and mapping the secondary data construct to an inverse of a property whose target class is a superclass of the corresponding class of the ontology model.

[0071] There is additionally provided in accordance with a preferred embodiment of the present invention a system for mapping data schemas into an ontology model, including a memory for storing an ontology model including classes and properties of classes, and a data schema, a schema parser for identifying a primary data construct within the data schema, and identifying a secondary data construct within the primary data construct, and a schema mapper for mapping the primary data construct to a corresponding class of the ontology model, and mapping the secondary data construct to a composition of properties, one of which is a property of the corresponding class of the ontology model.

[0072] There is yet further provided in accordance with a preferred embodiment of the present invention a method for mapping schemas for metadata into a metamodel for metadata, including providing a metamodel for metadata including atomic constructs and composite constructs, providing a schema for metadata, identifying a primary and a secondary metadata construct within the schema for metadata, and mapping the primary and the secondary metadata constructs to corresponding composite and atomic constructs of the metamodel, respectively.

[0073] There is moreover provided in accordance with a preferred embodiment of the present invention a system for mapping schemas for metadata into a metamodel for metadata, including a memory for storing a metamodel for metadata including atomic constructs and composite constructs, and a schema for metadata, a metaschema parser for identifying a primary metadata construct and a secondary metadata construct within the schema for metadata, and a metaschema mapper for mapping the primary metadata construct and the secondary data construct to a composite construct and an atomic construct of the metamodel, respectively.

[0074] There is additionally provided in accordance with a preferred embodiment of the present invention a method for mapping a given data schema into a generic data schema, including providing a business data schema that represents at least one type of business data instance in terms of alphanumeric values and links to business data instances, providing a plurality of generic instance mappings, defining a mapping from the business data schema into a generic data schema, and representing the mapping from the business data schema into the generic data schema in terms of the generic instance mappings.

[0075] There is further provided in accordance with a preferred embodiment of the present invention a system for mapping a given data schema into a generic data schema, including a memory for storing a business data schema that represents at least one type of business data instance in terms of alphanumeric values and links to business data instances, and including a plurality of generic instance mappings, a mapping generator for defining a mapping from the business data schema into a generic data schema, and a mapping analyzer for representing the mapping from the business data schema into the generic data schema in terms of the generic instance mappings.

[0076] There is yet further provided in accordance with a preferred embodiment of the present invention a computer-readable storage medium storing program code for causing a computer to perform the steps of providing an ontology model including classes and properties of classes, providing a data schema, identifying a primary data construct within the data schema, identifying a secondary data construct within the primary data construct, mapping the primary data construct to a corresponding class of the ontology model, and mapping the secondary data construct to a property of the corresponding class of the ontology model.

[0077] There is moreover provided in accordance with a preferred embodiment of the present invention a computer-readable storage medium storing program code for causing a computer to perform the steps of providing an ontology model including classes and properties of classes, each property having associated therewith a target class, providing a data schema, identifying a primary data construct within the data schema, identifying a secondary data construct within the primary data construct, mapping the primary data construct to a corresponding class of the ontology model, and mapping the secondary data construct to an inverse of a property whose target class is the corresponding class of the ontology model.

[0078] There is additionally provided in accordance with a preferred embodiment of the present invention a computer-readable storage medium storing program code for causing a computer to perform the steps of providing an ontology model including classes and properties of classes, and including inheritance relationships for superclasses, providing a data schema, identifying a primary data construct within the data schema, identifying a secondary data construct within the primary data construct, mapping the primary data construct to a corresponding class of the ontology model, and mapping the secondary data construct to a property of a superclass of the corresponding class of the ontology model.

[0079] There is further provided in accordance with a preferred embodiment of the present invention a computer-readable storage medium storing program code for causing a computer to perform the steps of providing an ontology model including classes and properties of classes, and including inheritance relationships for superclasses, providing a data schema, identifying a primary data construct within the data schema, identifying a secondary data construct within the primary data construct, mapping the primary data construct to a corresponding class of the ontology model, and mapping the secondary data construct to an inverse of a property whose target class is a superclass of the corresponding class of the ontology model.

[0080] There is yet further provided in accordance with a preferred embodiment of the present invention a computer-readable storage medium storing program code for causing a computer to perform the steps of providing an ontology model including classes and properties of classes, providing a data schema, identifying a primary data construct within the data schema, identifying a secondary data construct within the primary data construct, mapping the primary data construct to a corresponding class of the ontology model, and mapping the secondary data construct to a composition of properties, one of which is a property of the corresponding class of the ontology model.

[0081] There is moreover provided in accordance with a preferred embodiment of the present invention a computer-readable storage medium storing program code for causing a computer to perform the steps of providing an ontology model including classes and properties of classes, providing a data schema, identifying a primary data construct within the data schema, identifying a secondary data construct within the primary data construct, mapping the primary data construct to a corresponding class of the ontology model, and mapping the secondary data construct to a composition of properties, one of which is a property of the corresponding class of the ontology model.

[0082] There is additionally provided in accordance with a preferred embodiment of the present invention a computer-readable storage medium storing program code for causing a computer to perform the steps of providing a business data schema for representing at least one type of business data instance in terms of alphanumeric values and links to business data instances, providing a plurality of generic instance mappings, defining a mapping from the business data schema into a generic data schema, and representing the mapping from the business data schema into the generic data schema in terms of the generic instance mappings.

[0083] There is further provided in accordance with a preferred embodiment of the present invention a computer-readable storage medium storing program code for causing a computer to perform the steps of providing a metamodel for metadata including atomic constructs and composite constructs, providing a schema for metadata, identifying a primary and a secondary metadata construct within the schema for metadata, and mapping the primary and the secondary metadata constructs to corresponding composite and atomic constructs of the metamodel, respectively.

[0113] 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.

[0114] 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, and 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.

[0115] 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.

[0116] 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 the common ontology is preferably build 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.

[0117] 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.

[0118] 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.

[0119] 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.

[0120] 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.

[0121] 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.

[0122] 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.

[0123] 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.

[0124] The initial ontology model and the imported data schema are used by an ontology builder 430 for generating a common ontology model, into which the imported data schema 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.

[0125] Applications of the present invention include inter alia:

• • [0126] integrating between two or more applications that need to share data;
  • [0127] transmitting data from a database schema across a supply chain to a supplier or customer using a different database schema;
  • [0128] 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;
  • [0129] loading a data warehouse database for off-line analysis of data from multiple databases;
  • [0130] synchronizing two databases;
  • [0131] migrating data when a database schema is updated;
  • [0132] moving data from an old database or database application to a replacement database or database application, respectively.

[0133] 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.

[0134] 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.

[0135] 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.

[0136] For enterprise applications, SQL queries generated by the present invention are preferably deployed within an Enterprise Application Integration infrastructure.

[0137] 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.

[0138] The present invention applies to N relational database schema, where N≧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.

[0139] In distinction to prior art approaches that require on the order of N² mappings, the present invention requires at most N mappings.

[0140] A “mapping” from an RDBS into an ontology model is defined as:

• • [0141] (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
  • [0142] (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.
    A mapping from an RDBS into an ontology model need not be subjective. 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.

[0143] 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).

[0144] 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. 5 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.

[0145] 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.

[0146] 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.

[0147] 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).

[0148] 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.

[0149] 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.

[0150] 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:

• • [0151] (i) mapping the source and target RDBS into a common ontology model;
  • [0152] (ii) representing fields of the source and target RDBS in terms of properties of the ontology model, using symbols for properties;
  • [0153] (iii) deriving expressions for target symbols in terms of source symbols; and
  • [0154] (iv) converting the expressions into one or more SQL queries.

[0155] 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.

[0156] 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 and generating documents conforming with the target XML schema from documents conforming with the source XML schema.

[0157] 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.

[0158] Preferably, this is used to deploy XSLTs 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.

[0159] Reference is now made to FIG. 8, which is a simplified illustration of use of a preferred embodiment of the present invention to deploy XSLT scripts within an EAI product such as Tibco.

[0160] Applicant has developed a software application, named COHERENCE™, which implements a preferred embodiment of the present invention to transform data from one schema to another. Coherence enables a user

• • [0161] to import source and target RDBS;
  • [0162] to build an ontology model into which both the source and target RDBS can be mapped;
  • [0163] to map the source and target RDBS into the ontology model; and
  • [0164] to impose constraints on properties of the ontology model.
    Once the mappings are defined, Coherence generates an SQL query to transform the source RDBS into the target RDBS.

[0165] 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.

[0166] 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.

[0167] 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.

[0168] 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.”

[0169] 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. 9B is a package named WeatherFahrenheit, which includes a class named City.

[0170] 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.

[0171] 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.

[0172] 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.

[0173] 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.

[0174] 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.

[0175] As such, the target RDBS is

[0176]

TABLE I
Towns
Town	Celcius	Year

and the source RDBS is

[0177]

TABLE II
Cities
Fahrenheit	City	Year

[0178] 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

[0179]

INSERT INTO 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);

accomplishes the desired transformation.

[0180] 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:

• • [0181] Database Name 1020: What Oracle refers to as an SID (System Identifier).
  • [0182] Host Name 1030: The name of an Oracle 8i server (or Global Database Name).
  • [0183] Port 1040: Port number
  • [0184] Username 1050: The username of a user with privileges to the relevant schemas.
  • [0185] Password 1060: The password of the user with privileges to the relevant schemas.
  • [0186] 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.
  • [0187] 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.
  • [0188] Output File 1090: A name for the .SML file generated.

[0189] 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.

[0190] 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.

[0191] 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_5.

[0192] 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.

[0193] 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.

[0194] 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 distanceInMiles( ) 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.

[0195] 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.

[0196] 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.

[0197] 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.

[0198] In a first example, a target table is of the following form:

[0199]

TABLE III
Target Table T for First Example
	Child_Name	Mother_Name	School_Location	Form

[0200] Four source tables are given as follows:

[0201]

TABLE IV
Source Table S1 for First Example
Name	School_Attending	Mother_NI_Number

[0202]

TABLE V
Source Table S2 for First Example
	NI_Number	Name	Region	Car_Number

[0203]

TABLE VI
Source Table S3 for First Example
Name	Location	HeadTeacher

[0204]

TABLE VII
Source Table S4 for First Example
Name	Year	Form

[0205] 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:

[0206]

TABLE VIII
Unique Properties within Ontology for First Example
	Property	Property Index
	name(Child)	6
	national_insurance_number(Person)	4
	name(School)	10

[0207] The mapping of the target schema into the ontology is as follows:

[0208]

TABLE IX
Mapping from Target schema to Ontology for First Example
		Property
schema	Ontology	Index
T	Class: Child
T.Child_Name	Property: name(Child)	6
T.Mother_Name	Property: name(mother(Child))	3o5
T.School_Location	Property: location(school_attending	12o9
	(Child))
T.Form	Property: current_school_form(Child)	8

[0209] The symbol o is used to indicate composition of properties. The mapping of the source schema into the ontology is as follows:

[0210]

TABLE X
Mapping from Source schema to Ontology for First Example
schema	Ontology	Property Index
S1	Class: Child
S1.Name	Property: name(Child)	6
S1.School_Attending	Property: name(school_attending(Child))	10o9
S1.Mother_NI_Number	Property: national_insurance_number(mother(Child))	4o5
S2	Class: Person
S2.NI_Number	Property: national_insurance_number(Person)	4
S2.Name	Property: name(Person)	3
S2.Region	Property: region_of_residence(Person)	1
S2.Car_Number	Property: car_registration_number(Person)	2
S3	Class: School
S3.Name	Property: name(School)	10
S3.Location	Property: location(School)	12
S3.HeadTeacher	Property: name(headteacher(School))	3o11
S4	Class: Child
S4.Name	Property: name(Child)	6
S4.Year	Property: year_of_schooling(Child)	7
S4.Form	Property: current_school_form(Child)	8

[0211] The indices of the source properties are:

[0212]

TABLE XI
Source Symbols for First Example
Source Table	Source Symbols
S1	10o9o6−1
	4o5o6−1
S2	3o4−1
	1o4−1
	2o4−1
S3	12o10−1
	3o11o10−1
S4	7o6−1
	8o6−1

[0213] The symbols in Table XI relate fields of a source table to a key field. Thus in table S₁ the first field, S₁.Name is a key field. The second field, S₁.School_Attending is related to the first field by the composition 10o9o6⁻¹, and the third field, S₁.Mother_NI_Number is related to the first field by the composition 4o5o6⁻¹. In general, if a table contains more than one key field, then expressions relative to each of the key fields are listed.

[0214] The inverse notation, such as 6⁻¹ 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:

[0215]

TABLE XII
Target Symbols for First Example
Target Table	Target Symbols	Paths
T	3o5o6−1	(3o4−1) o (4o5o6−1)
	12o9o6−1	(12o10−1) o (10o9o6−1)
	8o6−1	(8o6−1)

[0216] Based on the paths given in Table XII, the desired SQL query is:

[0217]

	INSERT INTO T(Child_Name, Mother_Name,
	School_Location, Form)
	(SELECT
		S1.Name AS Child_Name,
		S2.Name AS Mother_Name,
		S3.Location AS School_Location,
		S4.Form AS Form
	FROM
		S1, S2, S3, S4
	WHERE
		S2.NI_Number = S1.Mother_NI_Number AND
		S3.Name = S1.School_Attending AND
		S4.Name = S1.Name);

[0218] 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,

• • [0219] Rule 1: When a target symbol is represented using a source symbols, say (aob⁻¹), 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.
  • [0220] Rule 2: When a target symbol is represented as a composition of source symbols, say (aob⁻¹) o (boc⁻¹), where aob⁻¹ is taken from a first source table, say S₁, and boc⁻¹ is taken from a second source table, say S₂, then S₁ and S₂ must be joined in the SQL query by the respective columns mapping to b.
  • [0221] Rule 3: When a target symbol is represented using a source symbols, say (aob⁻¹), from a source table, S, and is not composed with another source symbol of the form boc⁻¹, then table S must be joined to the target table through the column mapping to b.

[0222] 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.

[0223]

TABLE XIII
Sample Source Table S1 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

[0224]

TABLE XIV
Sample Source Table S2 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

[0225]

TABLE XV
Sample Source Table S3 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

[0226]

TABLE XVI
Sample Source Table S4 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

[0227]

TABLE XVII
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

[0228] In a second example, a target table is of the following form:

[0229]

TABLE XVIII
Target Table T for Second Example
	Name	Department	Supervisor	Room#

[0230] Four source tables are given as follows:

[0231]

TABLE XIX
Source Table S1 for Second Example
	Emp_ID#	Name	Department

[0232]

TABLE XX
Source Table S2 for Second Example
	Employee_Name	Supervisor	Project

[0233]

TABLE XXI
Source Table S3 for Second Example
	ID#	Room_Assignment	Telephone#

[0234]

TABLE XXII
Source Table S4 for Second Example
	Department	Budget

[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:

[0236]

TABLE XXIII
Unique Properties within Ontology for Second Example
	Property	Property Index
	name(Employee)	3
	ID#(Employee)	4

[0237] The mapping of the target schema into the ontology is as follows:

[0238]

TABLE XXIV
Mapping from Target schema to Ontology for Second Example
		Property
schema	Ontology	Index
T	Class: Employee
T.Name	Property: name(Employee)	3
T.Department	Property:	8o7
	code(departmental_affiliation(Employee))
T.Supervisor	Property: name(supervisor(Employee))	3o6
T.Room#	Property: room_number(Employee)	1

[0239] The mapping of the source schema into the ontology is as follows:

[0240]

TABLE XXV
Mapping from Source schema to Ontology for Second Example
schema	Ontology	Property Index
S1	Class: Employee
S1.Emp_ID#	Property: ID#(Employee)	4
S1.Name	Property: name(Employee)	3
S1.Department	Property: code(departmental_affiliation(Employee))	8o7
S2	Class: Employee
S2.Employee_Name	Property: name(Employee)	3
S2.Supervisor	Property: name(supervisor(Employee))	3o6
S2.Project	Property: project_assignment(Employee)	5
S3	Class: Employee
S3.ID#	Property: ID#(Employee)	4
S3.Room_Assignment	Property: room_number(Employee)	1
S3.Telephone#	Property: tel#(Employee)	2
S4	Class: Department
S4.Department	Property: code(Department)	8
S4.Budget	Property: budget_amount(Department)	9

[0241] The indices of the source properties are:

[0242]

TABLE XXVI
Source Symbols for Second Example
	Source Table	Source Symbols
	S1	3o4−1
		8o7o4−1
		4o3−1
		8o7o3−1
	S2	3o6o3−1
		5o3−1
	S3	1o4−1
		2o4−1
	S4	9o8−1

[0243] The indices of the target properties, keyed on Name are:

[0244]

TABLE XXVII
Target Symbols for Second Example
	Target Table	Target Symbols	Paths
	T	8o7o3−1	(8o7o3−1)
		3o6o3−1	(3o6o3−1)
		1o3−1	(1o4−1) o (4o3−1)

[0245] Based on the paths given in Table XXVII, the desired SQL query is:

[0246]

	INSERT INTO T(Name, Department, Supervisor, Room#)
	(SELECT
		S1.Name AS Name,
		S1.Department AS Department,
		S2.Supervisor AS Supervisor,
		S3.Room_Assignment AS Room#
	FROM
		S1, S2, S3
	WHERE
		S2.Employee_Name = S1.Name AND S3.ID# =
		S1.Emp_ID#);

[0247] It is noted that Table S₄ 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.

[0248]

TABLE XXVIII
Sample Source Table S1 for Second Example
	Emp_ID#	Name	Department
	198	Patricia	SW
	247	Eric	QA
	386	Paul	IT

[0249]

TABLE XXIX
Sample Source Table S2 for Second Example
	Employee_Name	Supervisor	Project
	Eric	John	Release 1.1
	Patricia	George	Release 1.1
	Paul	Richard	Release 1.1

[0250]

TABLE XXX
Sample Source Table S3 for Second Example
	ID#	Room_Assignment	Telephone#
	386	10	106
	198	8	117
	247	7	123

[0251]

TABLE XXXI
Sample Target Table T for Second Example
	Name	Department	Supervisor	Room#
	Patricia	SW	George	8
	Eric	QA	John	7
	Paul	IT	Richard	10

[0252] In a third example, a target table is of the following form:

[0253]

TABLE XXXII
Target Table T for Third Example
FlightID	DepartingCity	ArrivingCity

[0254] Two source tables are given as follows:

[0255]

TABLE XXXIII
Source Table S1 for Third Example
Index	APName	Location

[0256]

TABLE XXXIV
Source Table S2 for Third Example
FlightID	FromAirport	ToAirport

[0257] 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:

[0258]

TABLE XXXV
Unique Properties within Ontology for Third Example
	Property	Property Index
	name(Airport)	1
	ID(Flight)	6

[0259] The mapping of the target schema into the ontology is as follows:

[0260]

TABLE XXXVI
Mapping from Target schema to Ontology for Third Example
		Property
schema	Ontology	Index
T	Class: Flight
T.FlightID	Property: ID#(Flight)	6
T.DepartingCity	Property: location(from_airport(Flight))	2o4
T.ArrivingCity	Property: location(to_airport(Flight))	2o5

[0261] The mapping of the source schema into the ontology is as follows:

[0262]

TABLE XXXVII
Mapping from Source schema to Ontology for Third Example
schema	Ontology	Property Index
S1	Class: Airport
S1.Index	Property: Index(Airport)	3
S1.APName	Property: name(Airport)	1
S1.Location	Property: location(Airport)	2
S2	Class: Flight
S2.FlightID	Property: ID#(Flight)	6
S2.FromAirport	Property: name(from_airport(Flight))	1o4
S2.ToAirport	Property: name(to_airport(Flight))	1o5

[0263] The indices of the source properties are:

[0264]

TABLE XXXVIII
Source Symbols for Third Example
	Table	Source Symbols
	S1	1o3−1
		2o3−1
		3o1−1
		2o1−1
	S2
		1o4o6−1
		1o5o6−1

[0265] The indices of the target properties, keyed on FlightID are:

[0266]

TABLE XXXIX
Target Symbols for Third Example
	Table	Target Symbols	Paths
	T	2o4o6−1	(2o1−1) o (1o4o6−1)
		2o5o6−1	(2o1−1) o (1o5o6−1)

[0267] Since the path (2o1⁻¹) appears in two rows of Table XXXIX, it is necessary to create two tables for S₁ in the SQL query. Based on the paths given in Table XXXVII, the desired SQL query is:

[0268]

INSERT INTO T(FlightID,
DepartingCity, ArrivingCity)
(SELECT	S2.FlightID AS FlightID,
	S11.Location AS DepartingCity,
	S12.Location AS ArrivingCity
FROM	S1 S11, S1 S12, S2
WHERE	S11.APName = S2.FromAirport AND
	S12.APName = S2.ToAirport);

[0269] In general,

• • [0270] Rule 4: When the same source symbol is used multiple times in representing target symbols, each occurrence of the source symbol must refer to a different copy of the source table containing it.

[0271] When applied to the following sample source data, Tables XL and XLI, the above SQL query produces the target data in Table XLII.

[0272]

TABLE XL
Sample Source Table S1 for Third Example
	Index	APName	Location
	1	Orly	Paris
	2	JFK	New York
	3	LAX	Los Angeles
	4	HNK	Hong Kong
	5	TLV	Tel Aviv
	6	Logan	Boston

[0273]

TABLE XLI
Sample Source Table S2 for Third Example
	FlightID	FromAirport	ToAirport
	001	Orly	JFK
	002	JFK	LAX
	003	TLV	HNK
	004	Logan	TLV

[0274]

TABLE XLII
Sample Target Table T for Third Example
	FlightID	DepartingCity	ArrivingCity
	001	Paris	New York
	002	New York	Los Angeles
	003	Tel Aviv	Hong Kong
	004	Boston	Tel Aviv

[0275] In a fourth example, a target table is of the following form:

[0276]

TABLE XLIII
Target Table T for Fourth Example
ID	Name	Father_Name

[0277] One source table is given as follows:

[0278]

TABLE XLIV
Source Table S for Fourth and Fifth Examples
ID	Name	Father_ID

[0279] The underlying ontology is illustrated in FIG. 15. The dotted portions of the ontology in FIG. 15 are additional ontology structure that is transparent to the relational database schema. The unique properties of the ontology are:

[0280]

TABLE XLV
Unique Properties within Ontology for Fourth and Fifth Examples
	Property	Property Index
	name(Person)	1
	ID#(Person)	2

[0281] The mapping of the target schema into the ontology is as follows:

[0282]

TABLE XLVI
Mapping from Target schema to Ontology for Fourth Example
schema	Ontology	Property Index
T	Class: Person
T.ID	Property: ID#(Person)	2
T.Name	Property: name(Person)	1
T.Father_Name	Property: name(father(Person))	1o3

[0283] The mapping of the source schema into the ontology is as follows:

[0284]

TABLE XLVII
Mapping from Source schema to Ontology for Fourth and
Fifth Examples
	schema	Ontology	Property Index
	S	Class: Person
	S.ID	Property: ID#(Person)	2
	S.Name	Property: name(Person)	1
	S.Father_ID	Property: ID#(father(Person))	2o3

[0285] The indices of the source properties are:

[0286]

TABLE XLVIII
Source Symbols for Fourth and Fifth Examples
	Table	Source Symbols
	S1	1o2−1
		2o3o2−1

[0287] The indices of the target properties, keyed on ID are:

[0288]

TABLE XLIX
Target Symbols for Fourth Example
	Table	Target Symbols	Paths
	T	1o2−1	(1o2−1)
		1o3o2−1	(1o2−1) o (2o3o2−1)

[0289] Based on the paths given in Table XLIX, the desired SQL query is:

[0290]

INSERT INTO T(ID, Name, Father_ID)
(SELECT	S1.ID AS ID,
	S1.Name AS Name,
	S2.ID AS Father_ID
FROM	S S1, S S2
WHERE	S2.ID = S1.Father_ID);

[0291] In a fifth example, the target property of Father_Name in the fourth example is changed to Grandfather_Name, and the target table is thus of the following form:

[0292]

TABLE L
Target Table T for Fifth Example
ID	Name	Grandfather_Name

[0293] One source table is given as above in Table XLIV.

[0294] The underlying ontology is again illustrated in FIG. 15. The unique properties of the ontology are as above in Table XLV.

[0295] The mapping of the target schema into the ontology is as follows:

[0296]

TABLE LI
Mapping from Target schema to Ontology for Fifth Example
		Prop-
		erty
schema	Ontology	Index
T	Class: Person
T.ID	Property: ID#(Person)	2
T.Name	Property: name(Person)	1
T.Grandfather_Name	Property: name(father(father(Person)))	1o3o3

[0297] The mapping of the source schema into the ontology is given in Table XLVII above.

[0298] The indices of the source properties are given in Table XLVIII above.

[0299] The indices of the target properties, keyed on ID are:

[0300]

TABLE LII
Target Symbols for Fifth Example
	Table	Target Symbols	Paths
	T	1o2−1	(1o2−1)
		1o3o3o2−1	(1o2−1) o (2o3o2−1) o
			(2o3o2−1)

[0301] Based on the paths given in Table LII, the desired SQL query is:

[0302]

	INSERT INTO T(ID, Name,
	Grandfather_ID)
	(SELECT	S1.ID AS ID, S1.Name AS Name,
		S3.ID AS Grandfather_ID
	FROM	S S1, S S2, S S3
	WHERE	S3.ID = S2.Father_ID AND
		S2.ID = S1.Father_ID);

[0303] In a sixth example, a target table is of the following form:

[0304]

TABLE LIII
Target Table T for Sixth Example
ID	Name	Dogs_Previous_Owner

[0305] Two source tables are given as follows:

[0306]

TABLE LIV
Source Table S1 for Sixth Example
ID	Name	Dog

[0307]

TABLE LV
Source Table S2 for Sixth Example
Owner	Name	Previous_Owner

[0308] The underlying ontology is illustrated in FIG. 16. The dotted portions of the ontology in FIG. 16 are additional ontology structure that is transparent to the relational database schema. The unique properties of the ontology are:

[0309]

TABLE LVI
Unique Properties within Ontology for Sixth Example
	Property	Property Index
	ID#(Person)	2
	name(Dog)	6

[0310] The mapping of the target schema into the ontology is as follows:

[0311]

TABLE LVII
Mapping from Target schema to Ontology for Sixth Example
		Property
schema	Ontology	Index
T	Class: Person
T.ID	Property: ID#(Person)	2
T.Name	Property: name(Person)	1
T.Dogs_Previous—	Property: previous_owner(dog(Person))	5o3
Owner

[0312] The mapping of the source schema into the ontology is as follows:

[0313]

TABLE LVIII
Mapping from Source schema to Ontology for Sixth Example
		Property
schema	Ontology	Index
S1	Class: Person
S1.ID	Property: ID#(Person)	2
S1.Name	Property: name(Person)	1
S1.Dog	Property: name(dog(Person))	6o3
S2	Class: Dog
S2.Owner	Property: name(owner(Dog))	1o4
S2.Name	Property: name(Dog)	6
S2.Previous_Owner	Property: name(previous_owner(Dog))	1o5

[0314] The indices of the source properties are:

[0315]

TABLE LIX
Source Symbols for Sixth Example
	Table	Source Symbols
	S1	1o2−1
		6o3o2−1
	S2	1o4o6−1
		1o5o6−1

[0316] The indices of the target properties, keyed on ID are:

[0317]

TABLE LX
Target Symbols for Sixth Example
	Table	Target Symbols	Paths
	T	1o2−1	(1o2−1)
		5o3o2−1	(1o5o6−1) o (6o3o2−1)

[0318] Based on the paths given in Table LX, the desired SQL query is:

[0319]

INSERT INTO T(ID, Name, Dogs—
Previous_Owner)
(SELECT	S1.ID AS ID, S1.Name AS Name,
	S2.Previous_Owner AS Dogs—
	Previous_Owner
FROM	S1, S2
WHERE	S2.Name = S1.Dog);

[0320] In a seventh example, a target table is of the following form:

[0321]

TABLE LXI
Target Table T for Seventh Example
	ID	Name	Email	Department

[0322] Five source tables are given as follows:

[0323]

TABLE LXII
Source Table S1 for Seventh Example
	ID	Department

[0324]

TABLE LXIII
Source Table S2 for Seventh Example
	ID	Email

[0325]

TABLE LXIV
Source Table S3 for Seventh Example
	ID	Name

[0326]

TABLE LXV
Source Table S4 for Seventh Example
	ID	Email

[0327]

TABLE LXVI
Source Table S5 for Seventh Example
	ID	Department

[0328] The underlying ontology is illustrated in FIG. 17. The dotted portions of the ontology in FIG. 17 are additional ontology structure that is transparent to the relational database schema. The unique properties of the ontology are:

[0329]

TABLE LXVII
Unique Properties within Ontology for Seventh Example
	Property	Property Index
	ID#(Person)	2

[0330] The mapping of the target schema into the ontology is as follows:

[0331]

TABLE LXVIII
Mapping from Target schema to Ontology for
Seventh Example
schema	Ontology	Property Index
T	Class: Person
T.ID	Property: ID#(Person)	2
T.Name	Property: name(Person)	1
T.Email	Property: e-mail(Person)	3
T.Department	Property: department(Person)	4

[0332] The mapping of the source schema into the ontology is as follows:

[0333]

TABLE LXIX
Mapping from Source schema to Ontology for
Seventh Example
schema	Ontology	Property Index
S1	Class: Employee
S1.ID	Property: ID#(Employee)	2
S1.Department	Property: department(Employee)	4
S2	Class: Employee
S2.ID	Property: ID#(Employee)	2
S2.Email	Property: e-mail(Employee)	3
S3	Class: Employee
S3.ID	Property: ID#(Employee)	2
S3.Name	Property: name(Employee)	1
S4	Class: Employee
S4.ID	Property: ID#(Employee)	2
S4.Email	Property: e-mail(Employee)	3
S5	Class: Employee
S5.ID	Property: ID#(Employee)	2
S5.Department	Property: department(Employee)	4

[0334] The indices of the source properties are:

[0335]

TABLE LXX
Source Symbols for Seventh Example
	Table	Source Symbols
	S1	4o2−1
	S2	3o2−1
	S3	1o2−1
	S4	3o2−1
	S5	4o2−1

[0336] The indices of the target properties, keyed on ID are:

[0337]

TABLE LXXI
Target Symbols for Seventh Example
	Table	Target Symbols	Paths
	T	1o2−1	(1o2−1)
		3o2−1	(3o2−1)
		4o2−1	(4o2−1)

[0338] Based on the paths given in Table LXXI, the desired SQL query is:

[0339]

INSERT INTO T(ID, Name, Email, Department)
(SELECT
	S1.ID AS ID, S3.Name AS Name,
	S2.Email AS Email,
	S1.Department AS Department
FROM
	S1, S2, S3
WHERE
	S2. ID = S1.ID AND S3.ID = S1.ID
UNION
SELECT
	S1.ID AS ID,
	S3.Name AS Name,
	S4.Email AS Email,
	S1.Department AS Department
FROM
	S1, S3, S4
WHERE
	S3.ID = S1.ID AND S4.ID = S1.ID
UNION
SELECT
	S1.ID AS ID,
	S3.Name AS Name,
	S2.Email AS Email,
	S5.Department AS Department
FROM
	S1, S2, S3, S5
WHERE
	S2.ID = S1.ID AND S3.ID = S1.ID AND S5.ID = S1.ID
UNION
SELECT
	S1.ID AS ID,
	S3.Name AS Name,
	S4.Email AS Email,
	S5.Department AS Department
FROM
	S1, S3, S4, S5
WHERE
	S2.ID = S1.ID AND S3.ID = S1.ID AND
	S4.ID = S1.ID AND S5.ID = S1.ID);

[0340] In general,

• • [0341] Rule 5: When a source symbol used to represent a target symbol is present in multiple source tables, each such table must be referenced in an SQL query and the resultant queries joined.

[0342] When applied to the following sample source data, Tables LXXII, LXXIII, LXXIV, LXXV and LXXVI, the above SQL query produces the target data in Table LXXVII.

[0343]

TABLE LXXII
Sample Source Table S1 for Seventh Example
ID	Department
123	SW
456	PdM
789	SW

[0344]

TABLE LXXIII
Sample Source Table S2 for Seventh Example
ID	Email
123	jack@company
456	jan@company
789	jill@company

[0345]

TABLE LXXIV
Sample Source Table S3 for Seventh Example
ID	Name
123	Jack
456	Jan
789	Jill
999	Joe
111	Jim
888	Jeffrey

[0346]

TABLE LXXV
Sample Source Table S4 for Seventh Example
ID	Email
999	joe@company
111	jim@company
888	jeffrey@company

[0347]

TABLE LXXVI
Sample Source Table S5 for Seventh Example
ID	Department
999	Sales
111	Business_Dev
888	PdM

[0348]

TABLE LXXVII
Sample Target Table T for Seventh Example
ID	Name	Email	Department
123	Jack	jack@company	SW
456	Jan	jan@company	PdM
789	Jill	jill@company	SW
111	Jim	jim@company	Business_Dev
888	Jeffrey	jeffrey@company	PdM
999	Joe	joe@company	Sales

[0349] In an eighth example, a target table is of the following form:

[0350]

TABLE LXXVIII
Target Table T for Eighth Example
	Emp_Name	Emp_Division	Emp_Tel_No

[0351] Two source tables are given as follows:

[0352]

TABLE LXXIX
Source Table S1 for Eighth Example
Employee_Division	Employee_Tel#	Employee_Name	Room#

[0353]

TABLE LXXX
Source Table S2 for Eighth Example
	Name	Employee_Tel	Division

[0354] The underlying ontology is illustrated in FIG. 18. The dotted portions of the ontology in FIG. 18 are additional ontology structure that is transparent to the relational database schema. The unique properties of the ontology are:

[0355]

TABLE LXXXI
Unique Properties within Ontology for Eighth Example
	Property	Property Index
	name(Employee)	1

[0356] The mapping of the target schema into the ontology is as follows:

[0357]

TABLE LXXXII
Mapping from Target schema to Ontology for Eighth Example
		Property
schema	Ontology	Index
T	Class: Employee
T.Emp_Name	Property: name(Employee)	1
T.Emp_Division	Property: division(Employee)	4
T.Emp_Tel_No	Property: telephone_number(Employee)	2

[0358] The mapping of the source schema into the ontology is as follows:

[0359]

TABLE LXXXIII
Mapping from Source schema to Ontology for Eighth Example
		Property
schema	Ontology	Index
S1	Class: Employee
S1.Employee_Division	Property: division(Employee)	4
S1.Employee_Tel#	Property:	2
	telephone_number(Employee)
S1.Employee_Name	Property: name(Employee)	1
S1.Employee_Room#	Property:	3
	room_number(Employee)
S2	Class: Employee
S2.Name	Property: name(Employee)	1
S2.Employee_Tel	Property:	2
	telephone_number(Employee)
S2.Division	Property: division(Employee)	4

[0360] The indices of the source properties are:

[0361]

TABLE LXXXIV
Source Symbols for Eighth Example
	Table	Source Symbols
	S1	4o1−1
		2o1−1
		3o1−1
	S2	2o1−1
		4o1−1

[0362] The indices of the target properties, keyed on Emp_Name are:

[0363]

TABLE LXXXV
Target Symbols for Eighth Example
	Table	Target Symbols	Paths
	T	4o1−1	(4o1−1)
		2o1−1	(2o1−1)

[0364] Since each of the source tables S₁ and S₂ suffice to generate the target table T, the desired SQL is a union of a query involving S₁ alone and a query involving S₂ alone. Specifically, based on the paths given in Table LXXXV, the desired SQL query is:

[0365]

	INSERT INTO T(Emp_Name, Emp_Division, Emp_Tel_No)
	(SELECT
		S1.Employee_Name AS Emp_Name,
		S1.Employee_Division AS Emp_Division,
		S1.Employee_Tel# AS Emp_Tel_No
	FROM
		S1
	UNION
	SELECT
		S2.Employee_Name AS Emp_Name,
		S2.Employee_Division AS Emp_Division,
		S2.Employee_Tel# AS Emp_Tel_No
	FROM S2);

[0366] In general,

• • [0367] Rule 6: When one or more source tables contain source symbols sufficient to generate all of the target symbols, then each such source table must be used alone in an SQL query, and the resultant queries joined. (Note that Rule 6 is consistent with Rule 5.)

[0368] When applied to the following sample source data, Tables LXXXVI and LXXXVII, the above SQL query produces the target data in Table LXXXVIII.

[0369]

TABLE LXXXVI
Sample Source Table S1 for Eighth Example
Employee_Division	Employee_Tel#	Employee_Name	Room#
Engineering	113	Richard	10
SW	118	Adrian	4
Engineering	105	David	10

[0370]

TABLE LXXXVII
Sample Source Table S2 for Eighth Example
	Name	Employee_Tel	Division
	Henry	117	SW
	Robert	106	IT
	William	119	PdM
	Richard	113	Engineering

[0371]

TABLE LXXXVIII
Sample Target Table T for Eighth Example
	Emp_Name	Emp_Division	Emp_Tel_No
	Tom	Engineering	113
	Adrian	SW	118
	David	Engineering	105
	Henry	SW	117
	Robert	IT	106
	William	PdM	119

[0372] In a ninth example, a target table is of the following form:

[0373]

TABLE LXXXIX
Target Table T for Ninth Example
	City	Temperature

[0374] Two source tables are given as follows:

[0375]

TABLE XC
Source Table S1 for Ninth Example
	City	Temperature

[0376]

TABLE XCI
Source Table S2 for Ninth Example
	City	C_Temperature

[0377] The underlying ontology is illustrated in FIG. 19. The dotted portions of the ontology in FIG. 19 are additional ontology structure that is transparent to the relational database schema. The properties temperature_in_Centrigade and temperature_in_Fahrenheit are related by the constraint:
Temperature_in_Centrigade(City)= 5/9*(Temperature_in_Fahrenheit(City)−32)

[0378] The unique properties of the ontology are:

[0379]

TABLE XCII
Unique Properties within Ontology for Ninth Example
	Property	Property Index
	name(City)	1

[0380] The mapping of the target schema into the ontology is as follows:

[0381]

TABLE XCIII
Mapping from Target schema to Ontology for Ninth Example
		Property
schema	Ontology	Index
T	Class: City
T.City	Property: name(City)	1
T.Temperature	Property: temperature_in_Centigrade(City)	2

[0382] The mapping of the source schema into the ontology is as follows:

[0383]

TABLE XCIV
Mapping from Source schema to Ontology for Ninth Example
		Property
schema	Ontology	Index
S1	Class: City
S1.City	Property: name(City)	1
S1.Temperature	Property: temperature_in_Fahrenheit	3
	(City)
S2	Class: City
S2.City	Property: name(City)	1
S2.C_Temperature	Property: temperature_in_Centrigade	2
	(City)

[0384] The indices of the source properties are:

[0385]

TABLE XCV
Source Symbols for Ninth Example
	Table	Source Symbols
	S1
		3o1−1
	S2
		2o1−1

[0386] The indices of the target properties, keyed on City are:

[0387]

TABLE XCVI
Target Symbols for Ninth Example
	Table	Target Symbols	Paths
	T
		2o1−1	5/9 * ((3o1−1) - 32)
			(2o1−1)

[0388] Since each of the source tables S₁ and S₂ suffice to generate the target table T, the desired SQL is a union of a query involving S₁ alone and a query involving S₂ alone. Specifically, based on the paths given in Table XCVI, the desired SQL query is:

[0389]

INSERT INTO T(City, Temperature)
	(SELECT
		S1.City AS City,
		5/9 * (S1.Temperature - 32) AS Temperature
	FROM
		S1
	UNION
	SELECT
		S2.City AS City, S2.Temperature AS Temperature
	FROM
		S2);

[0390] In general,

• • [0391] Rule 7: When a target symbol can be expressed in terms of one or more source symbols by a dependency constraint, then such constraint must appear in the list of target symbols.

[0392] When applied to the following sample source data, Tables XCVII and XCVIII, the above SQL query produces the target data in Table XCIX.

[0393]

TABLE XCVII
Sample Source Table S1 for Ninth Example
	City	Temperature
	New York	78
	Phoenix	92
	Anchorage	36
	Boston	72

[0394]

TABLE XCVIII
Sample Source Table S2 for Ninth Example
	City	C._Temperature
	Moscow	12
	Brussels	23
	Tel Aviv	32
	London	16

[0395]

TABLE XCIX
Sample Target Table T for Ninth Example
	City	Temperature
	New York	25.5
	Phoenix	33.3
	Anchorage	2.22
	Boston	22.2
	Moscow	12
	Brussels	23
	Tel Aviv	32
	London	16

[0396] In a tenth example, a target table is of the following form:

[0397]

TABLE C
Target Table T for Tenth Example
	Product	Price

[0398] Two source tables are given as follows:

[0399]

TABLE CI
Source Table S1 for Tenth Example
	SKU	Cost

[0400]

TABLE CII
Source Table S2 for Tenth Example
	Item	Margin

[0401] The underlying ontology is illustrated in FIG. 20. The dotted portions of the ontology in FIG. 20 are additional ontology structure that is transparent to the relational database schema. The properties price, cost_of_production and margin are related by the constraint:
price(Product)=cost_of_production(Product)*margin(Product).

[0402] The unique properties of the ontology are:

[0403]

TABLE CIII
Unique Properties within Ontology for Tenth Example
	Property	Property Index
	SKU(Product)	1

[0404] The mapping of the target schema into the ontology is as follows:

[0405]

TABLE CIV
Mapping from Target schema to Ontology for Tenth Example
	schema	Ontology	Propert Index
	T	Class: Product
	T.Product	Property: SKU(Product)	1
	T.Price	Property: price(Product)	4

[0406] The mapping of the source schema into the ontology is as follows:

[0407]

TABLE CV
Mapping from Source schema to Ontology for Tenth Example
schema	Ontology	Property Index
S1	Class: Product
S1.SKU	Property: SKU(Product)	1
S1.Cost	Property: cost_of_production(Product)	2
S2	Class: Product
S2.Item	Property: SKU(Product)	1
S2.Margin	Property: margin(Product)	3

[0408] The indices of the source properties are:

[0409]

TABLE CVI
Source Symbols for Tenth Example
	Table	Source Symbols
	S1
		2o1−1
	S2
		3o1−1

[0410] The indices of the target properties, keyed on Product are:

[0411]

TABLE CVII
Target Symbols for Tenth Example
	Table	Target Symbols	Paths
	T
		4o1−1	(2o1−1) * (3o1−)

[0412] Based on the paths given in Table CVII, the desired SQL query is:

[0413]

INSERT INTO T(Product, Price)
(SELECT
	S1.SKU AS Product, (S1.Cost) * (S2.Margin) AS Price
FROM
	S1, S2
WHERE
	S2.Item = S1.SKU);

[0414] When applied to the following sample source data, Tables CVIII and CVIX, the above SQL query produces the target data in Table CX.

[0415]

TABLE CVIII
Sample Source Table S1 for Tenth Example
	SKU	Cost
	123	2.2
	234	3.3
	345	4.4
	456	5.5

[0416]

TABLE CIX
Sample Source Table S2 for Tenth Example
	Item	Margin
	123	1.2
	234	1.1
	345	1.04
	456	1.3

[0417]

TABLE CX
Sample Target Table T for Tenth Example
	Product	Price
	123	2.86
	234	3.96
	345	4.84
	456	5.72

[0418] In an eleventh example, a target table is of the following form:

[0419]

TABLE CXI
Target Table T for Eleventh Example
	ID#	Full_Name

[0420] One source table is given as follows:

[0421]

TABLE CXII
Source Table S for Eleventh Example
ID#	First_Name	Last_Name

[0422] The underlying ontology is illustrated in FIG. 21. The dotted portions of the ontology in FIG. 21 are additional ontology structure that is transparent to the relational database schema. The properties full_name, first_name and last_name are related by the constraint:
full_name(Person)=first_name(Person)∥last_name(Person),
where ∥ denotes string concatenation.

[0423] The unique properties of the ontology are:

[0424]

TABLE CXIII
Unique Properties within Ontology for Eleventh Example
	Property	Property Index
	ID#(Product)	1

[0425] The mapping of the target schema into the ontology is as follows:

[0426]

TABLE CXIV
Mapping from Target schema to Ontology for Eleventh Example
	schema	Ontology	Property Index
	T	Class: Person
	T.ID#	Property: ID#(Person)	1
	T.Full_Name	Property: full_name(Person)	4

[0427] The mapping of the source schema into the ontology is as follows:

[0428]

TABLE CXV
Mapping from Source schema to Ontology for Eleventh Example
	schema	Ontology	Property Index
	S	Class: Person
	S.ID#	Property: ID#(Person)	1
	S.First_Name	Property: first_name(Person)	2
	S.Last_Name	Property: last_name(Person)	3

[0429] The indices of the source properties are:

[0430]

TABLE CXVI
Source Symbols for Eleventh Example
	Table	Source Symbols
	S	2o1−1
		2o1−1

[0431] The indices of the target properties, keyed on ID# are:

[0432]

TABLE CXVII
Target Symbols for Eleventh Example
	Table	Target Symbols	Paths
	T	4o1−1	(2o1−1) || (3o1−1)

[0433] Based on the paths given in Table CXVII, the desired SQL query is:

[0434]

INSERT INTO T(ID#, Full_Name)
(SELECT	S.ID# AS ID#,
	(S.First_Name) || (S.Last_Name)
	AS Full_Name
FROM	S);

[0435] When applied to the following sample source data, Table CXVIII, the above SQL query produces the target data in Table CXIX.

[0436]

TABLE CXVIII
Sample Source Table S for Eleventh Example
	ID#	First_Name	Last_Name
	123	Timothy	Smith
	234	Janet	Ferguson
	345	Ronald	Thompson
	456	Marie	Baker
	567	Adrian	Clark

[0437]

TABLE CXIX
Sample Target Table T for Eleventh Example
ID#	Full_Name
123	Timothy Smith
234	Janet Ferguson
345	Ronald Thompson
456	Marie Baker
567	Adrian Clark

[0438] A source XML schema for books is given by:

[0439]

	<?xml version=“1.0” encoding=“UTF-8”?>
	<xs:schema xmlns:xs=“http://www.w3.org/2001/XMLschema”>
	<xs:element name=“book” type=“Book”/>
	<xs:complexType name=“Book”>
	<xs:sequence>
	<xs:element name=“name” type=“xs:string”/>
	<xs:element name=“author” type=“Author”/>
	</xs:sequence>
	</xs:complexType>
	<xs:complexType name=“Author”>
	<xs:attribute name=“name”/>
	</xs:complexType>
	</xs:schema>

[0440] A target XML schema for documents is given by:

[0441]

	<?xml version=“1.0” encoding=“UTF-8”?>
	<xs:schema xmlns:xs=“http://www.w3.org/2001/XMLschema”>
	<xs:element name=“document” type=“Document”/>
	<xs:complexType name=“Document”>
	<xs:all>
	<xs:element name=“writer” type=“xs:string”/>
	</xs:all>
	<xs:attribute name=“title”/>
	</ xs:complexType>
	</xs:schema>

[0442] A common ontology model for the source and target XML schema is illustrated in FIG. 22. A mapping of the source XML schema into the ontology model is given by:

[0443]

TABLE CXX
Mapping from Source schema to Ontology for Twelfth and Thirteenth
Examples
schema	Ontology	Property Index
complexType: book	Class: Book
element: book/name/text()	Property: name(Book)	1
element: book/author	Property: author(Book)	2
complexType: author	Class: Person
element: author/@name	Property: name(Person)	3

A mapping of the target XML schema into the ontology model is given by:

[0444]

TABLE CXXI
Mapping from Target schema to Ontology for Twelfth Example
		Property
schema	Ontology	Index
complexType: document	Class: Book
element: document/writer/	Property: name(auhor(Book))	3o2
text()
attribute: document/@title	Property: name(Book)	1

Tables CXX and CXXI use XPath notation to designate XSL elements and attributes.

[0445] Based on Tables CXX and CXXI, an XSLT transformation that maps XML documents that conform to the source schema to corresponding documents that conform to the target schema should accomplish the following tasks:

• • [0446] 1. document/@title←book/name/text( )
  • [0447] 2. document/writer/text( )←book/author/@name
    Such a transformation is given by:

[0448]

<?xml version=“1.0” encoding=“UTF-8”?>
<xsl:stylesheet version=“1.0” xmlns:xsl=“http://www.w3.org/1999/XSL/Transform”>
<xsl:output method=“xml” version=“1.0” encoding=“UTF-8” indent=“yes”/>
<xsl:template match=“/”>
	<document>
	<xsl:for-each select=“.//book[position( ) = 1]”>
	<xsl:attribute name=“title”>
	<xsl:value-of select=“name( )”/>
	</xsl:attribute>
	<xsl:element name=“writer”>
	<xsl:value-of select=“author/@name” />
	</xsl:element>
	</xsl:for-each>
	</document>
</xsl:template>
</xsl:stylesheet>

[0449] A source XML schema for books is given by:

[0450]

<?xml version=“1.0” encoding=“UTF-8”?>
<xs:schema xmlns:xs=“http://www.w3.org/2001/XMLschema”>
<xs:element name=“book” type=“Book”/>
<xs:complexType name=“Book”>
	<xs:sequence>
	<xs:element name=“name” type=“xs:string”/>
	<xs:element name=“author” type=“Author” minOccurs=“0” maxOccurs=“unbounded”/>
	</xs:sequence>
</xs:complexType>
<xs:complexType name=“Author”>
	<xs:attribute name=“name”/>
</xs:complexType>
</xs:schema>

[0451] A target XML schema for documents is given by:

[0452]

<?xml version=“1.0” encoding=“UTF-8”?>
<xs:schema xmlns:xs=“http://www.w3.org/2001/XMLschema”>
<xs:element name=“document” type=“Document”/>
<xs:complexType name=“Document”>
	<xs:choice>
	<xs:element name=“writer” type=“xs:string” minOccurs=“1” maxOccurs=“unbounded”/>
	<xs:element name=“title” type=“xs:string”/>
	<xs:element name=“ISBN” type=“xs:string” />
	</xs:choice>
</xs:complexType>
</xs:schema>

[0453] A common ontology model for the source and target XML schema is illustrated in FIG. 23. A mapping of the source XML schema into the ontology model is given by Table CXVIII above. A mapping of the target XML schema into the ontology model is given by:

[0454]

TABLE CXXII
Mapping from Target schema to Ontology for Thirteenth Example
		Property
schema	Ontology	Index
complexType: document	Class: Book
element: document/writer/	Property: name(author(Book))	3o2
text()
element: document/title/text()	Property: name(Book)	1
element: document/ISBN/	Property: ISBN(Book)	4
text()

[0455] Based on Tables CXX and CXXII, an XSLT transformation that maps XML documents that conform to the source schema to corresponding documents that conform to the target schema should accomplish the following tasks:

• • [0456] 1. document/title/text( )←book/name/text( )
  • [0457] 2. document/writer/text( )←book/author/@name
    Such a transformation is given by:

[0458]

<?xml version=“1.0” encoding=“UTF-8”?>
<xsl:stylesheet version=“1.0” xmlns:xsl=“http://www.w3.org/1999/XSL/Transform”>
<xsl:output method=“xml” version=“1.0” encoding=“UTF-8” indent=“yes”/>
<xsl:template match=“/”>
	<document>
	<xsl:apply-templates select=“book” />
	</document>
</xsl:template>
<xsl:template match=“book”>
	<xsl:choose>
	<xsl:when test=“author”>
	<xsl:for-each select=“author”>
	<xsl:element name=“writer”>
	<xsl:value-of select=“@name”/>
	</xsl:element>
	</xsl:for-each>
	</xsl:when>
	<xsl:when test=“name”>
	<xsl:element name=“title”>
	<xsl:value-of select=“name/text( )”/>
	</xsl:element>
	</xsl:when>
	</xsl:choose>
</xsl:template>
</xsl:stylesheet>

[0459] A source XML schema for books is given by:

[0460]

<?xml version=“1.0” encoding=“UTF-8”?>
<xs:schema xmlns:xs=“http://www.w3.org/2001/XMLschema”>
<xs:element name=“library” type=“Library”/>
<xs:complexType name=“Library”>
	<xs:sequence>
	<xs:element name=“source” type=“Source” minOccurs=“0” maxOccurs=“unbounded”/>
	</xs:sequence>
</xs:complexType>
<xs:complexType name=“Source”>
	<xs:sequence>
	<xs:element name=“review” type=“Review” minOccurs=“0” maxOccurs=“unbounded”/>
	<xs:element name=“article” type=“Article” minOccurs=“0” maxOccurs=“unbounded”/>
	<xs:element name=“letter” type=“Letter” minOccurs=“0” maxOccurs=“unbounded”/>
	</xs:sequence>
</xs:complexType>
<xs:complexType name=“Review”>
	<xs:sequence>
	<xs:element name=“author” type=“xs:string” minOccurs=“0” maxOccurs=“unbounded”/>
	</xs:sequence>
	<xs:attribute name=“title”/>
</xs:complexType>
<xs:complexType name=“Article”>
	<xs:sequence>
	<xs:element name=“writer” type=“xs:string” minOccurs=“0” maxOccurs=“unbounded”/>
	</xs:sequence>
	<xs:attribute name=“name”/>
</xs:complexType>
<xs:complexType name=“Letter”>
	<xs:sequence>
	<xs:element name=“sender” type=“xs:string” minOccurs=“0” maxOccurs=“unbounded”/>
	</xs:sequence>
	<xs:attribute name=“name”/>
	<xs:attribute name=“subject”/>
	<xs:attribute name=“receiver”/>
</xs:complexType>
</xs:schema>

[0461] A first target XML schema for documents is given by:

[0462]

<?xml version=“1.0” encoding=“UTF-8”?>
<xs:schema xmlns:xs=“http://www.w3.org/2001/XMLschema”>
<xs:element name=“storage” type=“Storage”/>
<xs:complexType name=“Storage”>
	<xs:sequence>
	<xs:element name=“articles” type=“Documents”/>
	<xs:element name=“reviews” type=“Documents”/>
	<xs:element name=“letters” type=“Letters”/>
	</xs:sequence>
</xs:complexType>
<xs:complexType name=“Documents”>
	<xs:sequence>
	<xs:element name=“document” type=“Document” minOccurs=“0”
	maxOccurs=“unbounded”/>
	</xs:sequence>
</xs:complexType>
<xs:complexType name=“Letters”>
	<xs:sequence>
	<xs:element name=“letter” type=“Letter” minOccurs=“0” maxOccurs=“unbounded”/>
	</xs:sequence>
</xs:complexType>
<xs:complexType name=“Document”>
	<xs:sequence>
	<xs:element name=“author” type=“xs:string” minOccurs=“0” maxOccurs=“unbounded”/>
	</xs:sequence>
	<xs:attribute name=“title”/>
</xs:complexType>
<xs:complexType name=“Letter”>
	<xs:sequence>
	<xs:element name=“author” type=“xs:string” minOccurs=“0” maxOccurs=“unbounded”/>
	</xs:sequence>
	<xs:attribute name=“name”/>
	<xs:attribute name=“subject”/>
	<xs:attribute name=“receiver”/>
</xs:complexType>
</xs:schema>