System and method for transforming a relational database to a hierarchical database

• US 6,665,677 B1
• Filed: 10/02/2000
• Issued: 12/16/2003
• Est. Priority Date: 10/01/1999
• Status: Expired due to Term

• Current Assignee: Fair Isaac Corporation
• Original Assignee: Infoglide Software Corp. (Fair Isaac Corporation)
• Inventors: Wotring, Steve C., Ripley, John R.
• Primary Examiner(s): Robinson, Greta
• Assistant Examiner(s): PANNALA, SATHYANARAYA R
• Application Number:
  US09/677,477
• Time in Patent Office: 1,170 Days
• Field of Search: 707/1-10, 707/100-200
• US Class Current: 1/1
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database

Abstract

The present invention comprises a computer-implemented method for transforming data in a relational database to a hierarchical database. It comprises creating an import map that maps each relational database field to a hierarchical field in the hierarchical database using a relational database schema and a hierarchical database schema, using the import map to import data from the relational database; and transforming the relational data into hierarchical documents. The method further comprises creating a hierarchical database schema that corresponds to the relational database schema. The hierarchical documents may be stored in computer memory or on disk.

Claims

1. A computer-implemented method for transforming data in a relational database to a hierarchical database comprising:

creating an import map that maps each relational database field to a hierarchical field in the hierarchical database using a relational database schema and a hierarchical database schema, comprising;

creating a hierarchical database schema, comprising at least one compound and at least one simple object;

for each compound object, defining an SQL statement, which expresses a 1 to n relationship of the compound object to its parent object and which expresses source fields available for child objects of the compound object;

relating each simple object to at least one source field name in its parent compound object;

extracting the source field names from the relational database for all compound objects using the SQL statement; and

allowing the simple object to access the source field names of its parent compound object to determine the source field names the simple object can map to,

using the import map to import data from the relational database; and

transforming the relational data into hierarchical documents, comprising;

traversing the import map and each time an SQL statement is encountered, executing the SQL statement which results in a compound object dataset; and

traversing each compound object dataset and finding the relational data for each simple object based on the dataset,

whereby the relational data is transformed directly into hierarchical documents without using an intermediate step of transforming to a generic data type.

2. The method of claim 1, further comprising creating a hierarchical database schema that corresponds to the relational database schema.

3. The method of claim 1, wherein the creating a hierarchical database schema comprising at least one compound and at least one simple object is based on a relational database key structure where the relational database key structure has at least one primary key and at least one foreign key.

4. The method according to claim 1 further comprising applying a data scrubbing algorithm to the relational data and storing the scrubbed data in the hierarchical document.

5. The method according to claim 1 further comprising storing the hierarchical documents on a storage medium selected from the group consisting of computer memory or disk.

6. The method according to claim 5 wherein the hierarchical document is stored on a storage medium selected from the group consisting of computer memory or disk.

7. The method of claim 1 wherein the creating of a hierarchical document schema comprises:

step 1;

determining a relationship between a first table in the relational database and a second table in the relational database using a primary key;

step 2;

forming a compound object in a hierarchical document that is associated with the first table;

step 3;

forming an object selected from the group consisting of a compound object and a simple object that is associated with the second table; and

step 4;

repeating steps 1 through 3 until all tables within the relational database are associated with an object in the hierarchical document.

8. A computer-readable medium containing instructions for transforming data in a relational database to a hierarchical database comprising:

creating an import map that maps each relational database field to a hierarchical field in the hierarchical database using a relational database schema and a hierarchical database schema, comprising;

creating a hierarchical database schema, comprising at least one compound and at least one simple object;

for each compound object, defining an SQL statement, which expresses a 1 to n relationship of the compound object to its parent object and which expresses source fields available for child objects of the compound object;

relating each simple object to at least one source field name in its parent compound object;

extracting the source field names from the relational database for all compound objects using the SQL statement; and

allowing the simple object to access the source field names of its parent compound object to determine the source field names the simple object can map to,

using the import map to import data from the relational database; and

transforming the relational data into hierarchical documents, comprising

traversing the import map and each time an SQL statement is encountered, executing the SQL statement which results in a compound object dataset; and

traversing each dataset and finding the relational data for each simple object based on the compound object dataset,

whereby the relational data is transformed directly into hierarchical documents without using an intermediate step of transforming to a generic data type.

9. A computer-readable medium containing a collection of hierarchical objects in an import map, the import map comprising:

at least one compound object comprising;

an indicator of a relational database and a field name of relational data associated with the compound object;

a structured query language (SQL) statement;

a source field name extracted from the relational database using the SQL statement; and

at least one simple object comprising;

a field name of relational data associated with the simple object;

the relational data extracted from the relational database and stored in the simple object.