Computer system for creating semantic object models from existing relational database schemas

• US 5,819,086 A
• Filed: 06/07/1995
• Issued: 10/06/1998
• Est. Priority Date: 06/07/1995
• Status: Expired due to Fees

• Current Assignee: Micro Focus Incorporated (Open Text Corporation)
• Original Assignee: Wall Data, Inc. (Open Text Corporation)
• Inventors: Kroenke, David
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): RONES, CHARLES
• Application Number:
  US08/478,377
• Time in Patent Office: 1,217 Days
• Field of Search: 395/700, 395/157, 395/703, 395/613, 395/282.1, 395/280.4
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A computer system for creating a semantic object model from an existing relational database schema. The computer system analyzes the catalog information of the relational database schema and creates a semantic object for each table defined in the catalog. For each column defined within a table, a simple value attribute is added to the semantic object created for the table. The system then analyzes the relationship information stored in the catalog to create object link attributes that define relationships between two or more semantic object as well as to create multivalued group attributes and multivalued, simple value attributes. If the database catalog does not include the relational information, the user is prompted to indicate related semantic objects.

Claims

1. A computer system for creating a semantic object model from an existing relational database schema, comprising:

a memory having a database catalog stored therein, wherein the database catalog defines a plurality of relational database tables included within the database schema and at least one column included within each of the relational database tables;

a display device for displaying the semantic object model to a user;

a central processing unit, coupled to the memory and to the display device, the central processing unit including a computer program that causes the central processing unit to perform the following functions;

a) to analyze the database catalog to determine each relational database table defined in the existing relational database schema;

b) to create a semantic object within the semantic object model that corresponds to at least one of the relational database tables defined in the relational database schema;

c) to analyze each column defined in the relational database schema for the relational database table corresponding to the semantic object created; and

d) to create at least one simple value attribute within the semantic object created that corresponds to a column defined for the relational database table to which the semantic object corresponds.

2. The computer system of claim 1, wherein the database catalog includes relationship information that defines whether a column included in a table is a foreign key to a column included in another relational database table in the relational database schema, wherein the computer program further causes the central processing unit to:

a) analyze each column included within each relational database table to determine if each column is defined as a foreign key to another table in the relational database schema;

b) create a pair of object link attributes; and

c) add one object link attribute to a semantic object associated with the table that includes the column defined as the foreign key and to add another object link attribute of the paired object link attributes to a semantic object associated with the other relational database table.

3. The computer system of claim 2, wherein the database catalog defines which column of a table is a primary key, wherein the computer program further causes the central processing unit to:

a) analyze each column included within each relational database table to determine if a column is defined as both a foreign key to another relational database table in the relational database schema and is a primary key of a relational database table;

b) determine whether the relational table having the column that is both a foreign key to another table and is a primary key includes more than two columns; and

c) create a multivalued group attribute within a semantic object associated with the table to which the foreign key refers if the table having the column that is both a foreign key and a primary has more than two columns.

4. The computer system of claim 3, wherein the computer program further causes the central processing unit to:

a) analyze each column included in each relational database table to determine if a column is defined as both a foreign key to another relational database table in the relational database schema and is a primary key of the relational database table;

b) determine whether the relational table having the column that is both a foreign key to another table and is a primary key has two or fewer columns; and

c) create a multivalued, simple value attribute in the semantic object associated with the table to which the foreign key refers if the table having the column that is both the foreign key and a primary key has two or fewer columns.

5. The computer system of claim 1, wherein the computer program further causes the central processing unit to:

a) create a semantic object for each relational database table defined in the existing relational database schema.

6. The computer system of claim 5, wherein the database catalog includes relationship information that defines whether a column included in a table is a foreign key to another relational database table in the relational database schema, the computer program further causing the central processing unit to:

a) analyze each table defined in the relational database schema to determine if the table only includes columns that are defined as foreign keys to a pair of tables defined in the relational database schema;

b) to create a pair of multivalued object link attributes in the semantic objects associated with the tables referenced by the foreign keys; and

c) deleting from the semantic object model the semantic object created for the table only including columns defined as foreign keys.

7. A method of operating a computer system of the type including a central processing unit, a memory, a permanent storage mechanism and a display screen to produce a semantic object model that corresponds to an existing relational database schema stored on the permanent storage mechanism, comprising the steps of:

retrieving a database catalog that defines one or more relational database tables included in the existing relational database schema as well as one or more columns defined for each relational database table and placing the database catalog into the memory of the computer system;

analyzing the database catalog to determine each relational database table defined in the existing relational database schema;

allocating space in the memory to create a semantic object for each relational database table defined in the existing relational database schema;

analyzing the database catalog to determine each column included within each table in the existing relational database schema;

allocating space in the memory to create a simple value attribute for each column included within each relational database table;

lining each simple value attribute created for the columns included in a relational database table with the semantic object created for the relational database table; and

displaying a visual representation of at least some of the semantic objects and simple value attributes created on the display screen.

8. The method of claim 7, wherein the database catalog includes relationship information that defines whether a column in a table is a foreign key to another table in the existing relational database schema and primary key information that defines which columns included in a relational table are defined as a primary key of the relational table, the method further comprising the steps of:

analyzing the catalog to determine whether a column included in a table is both a foreign key to another relational table in the existing relational database schema and is defined as a primary key of a relational database table;

analyzing the catalog to determine if a table having a column that is both a foreign key and a primary key includes more than one non-foreign key column;

allocating space in the memory to create a multivalued group attribute if a table has a column that is both a foreign key and a primary key; and

linking the multivalued group to the semantic object created for the relational database table referenced by the foreign key.

9. The method of claim 8, further comprising the steps of:

allocating space in the memory to create a multivalued, simple value attribute if a table has a column that is defined as a foreign key and a primary key wherein the table has only one non-foreign key column; and

linking the multivalued, simple value attribute to the semantic object created for the relational database table referenced by the foreign key.

10. The method of claim 9, further comprising the steps of:

analyzing the database catalog to determine if a table exists having only columns that are defined as foreign keys;

allocating space in the memory for a pair of multivalued object link attributes for each table having only columns defined as foreign keys; and

lining the pair of multivalued, object link attributes to the relational database tables referenced by the foreign keys of the table.

11. The method of claim 10, further comprising the step of:

deleting from memory the space allocated for each semantic object created for a relational database table having only columns defined as foreign keys.

12. The method of claim 8, further comprising the step of:

deleting from memory the space allocated for each semantic object created for a relational database table having a column that is defined as a foreign key and a primary key and wherein the table includes more than two columns.

13. The method of claim 9, further comprising the step of:

deleting from memory the space allocated for each semantic object created for a relational database table having a column that is a foreign key and a primary key wherein the table has only two columns.

14. The method of claim 7, further comprising the steps of:

allocating space within the memory to store a list of each semantic object created; and

adding a reference to the list each time a semantic object is created.

15. The method of claim 7, further comprising the step of:

allocating space in the memory to create an object link profile for each semantic object created.

16. The method of claim 7, wherein the database catalog provides information indicating the primary keys of a relational database table but does not provide relationship information, the method further comprising the steps of:

displaying a dialog box on the display screen that allows a user to indicate two semantic objects in the semantic object model that are related;

prompting the user to indicate whether the semantic objects are related through a one-to-one, one-to-many, many-to-one or many-to-many relationship; and

recording the relationship information entered by the user.

17. The method of claim 16, further comprising the steps of:

determining whether the user indicated that the relationship between the related semantic objects was many-to-many;

prompting the user to indicate an intersection table in the existing relational database schema having columns that contain foreign keys to other relational tables in the existing relational database schema;

allocating space in the memory to create a pair of multivalued object link attributes; and

linking each object link attribute of the pair of multivalued object link attributes to the semantic objects created for the relational database tables referenced by the foreign keys.

18. The method of claim 17, further comprising the steps of:

determining if the relationship is one-to-one and if so allocating space in the memory to create a pair of object link attributes; and

linking each object link attribute of the pair of object link attributes to the semantic objects related by the one-to-one relationship.

19. The method of claim 16, further comprising the steps of:

determining whether the user indicated that the relationship between the related semantic objects was one-to-many or many-to-one;

determining a semantic object associated with a one side of the one-to-many relationship and a semantic object associated with a many side of the relationship;

prompting a user to indicate a relational table in the existing relational data that has a foreign key to a relational table associated with the semantic object on the one side of the relationship;

determining whether the table indicated has more than one non-foreign key column and whether the foreign key is part of the primary key of the table;

allocating space in the memory to create a multivalued group attribute if the table indicated has more than two columns and the foreign key was part of the primary key of the table;

allocating space in the memory to create a multivalued simple value attribute if the table indicated has only one non-foreign key column and the foreign key was part of the primary key of the table; and

linking the multivalued group or multivalued simple value attribute to the semantic objects related by the one-to-many relationship.

20. A computer readable memory that can be used to direct a computer to function as defined in claim 7 when used by the computer.