Method and apparatus for implementing a hierarchical database management system (HDBMS) using a relational database management system (RDBMS) as the implementing apparatus

• US 5,974,407 A
• Filed: 09/29/1997
• Issued: 10/26/1999
• Est. Priority Date: 09/29/1997
• Status: Expired due to Fees

• Current Assignee: Sacks Consulting Incorporated
• Original Assignee: Sacks Consulting Incorporated
• Inventors: Sacks, Jerome E.
• Primary Examiner(s): Lintz, Paul R.
• Application Number:
  US08/939,574
• Time in Patent Office: 757 Days
• Field of Search: 707/2, 707/100, 707/1
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

The invention is directed to a computer implemented system and apparatus for construction of a Hierarchical Database Management System (HDBMS) using a commercially available Relational Database Management System (RDBMS) as the implementing apparatus. The invention uses one or more RDBMS tables to store rows of a hierarchical database implemented by the HDBMS. Composite keys, unique over all the tables, are used to maintain hierarchical families and maintain the hierarchical parent/child relationships within families. Root conditions and child conditions may be used to determine which tables may be used for adding root row starting a new family, and which tables may be used for adding a child row of a selected parent row. SQL select queries present hierarchical views of the hierarchical database. SQL action queries are used to perform the database functions of insert root, insert child, update row, and delete row and its descendents. Both relational databases and hierarchical databases can be implemented in the same application using the RDBMS and the imbedded HDBMS together, and standard RDBMS SQL can be used to process the hierarchical and relational data in an integrated manner.

Claims

1. A method of using a relational database management system (RDBMS) for managing rows of data in a hierarchical database, including the steps of:

A. creating a set of one or more hierarchical database data storage tables in the RDBMS, consisting of hierarchical key fields and user data fields for storing rows of data of the hierarchical database wherein each row of the hierarchical database is a row in one of the hierarchical database data storage tables;

B. specifying root conditions determining which of said hierarchical database data storage tables may be used to create a new row in said hierarchical database called a root row;

C. specifying child conditions, wherein for each row of the hierarchical database, called a parent row, the child conditions determine which of the hierarchical database data storage tables may be used to create a new child row of the parent row;

D. defining descendents of the root row and hierarchical families of the hierarchical database wherein;

the descendents of the root row consists of all children of the root row and all children of a previous descendent of the root row, and each hierarchical family consists of the root row and its descendents;

E. specifying data to be inserted in the hierarchical keys of the new root row, and the new child row of a selected parent row, that;

distinguishes each of the hierarchical families,

distinguishes each of the root rows, and

identifies the parent row of each child row;

F. creating a new hierarchical family in the hierarchical database by inserting the new root row into the hierarchical database;

G. creating the new child row into the hierarchical database of the selected parent row by inserting the new child row into the hierarchical database;

H. updating a selected hierarchical database row;

I. deleting the selected or a subsequent selected hierarchical database row and its descendants;

J. creating one or more SQL select queries, hereafter called hierarchical views, wherein;

applying each hierarchical view to the hierarchical database creates hierarchical view rows,

each of the hierarchical view rows contains information from a row of the hierarchical database,

each row in the hierarchical database has a corresponding row in the hierarchical view rows,

the hierarchical view rows of each hierarchical family are displayed contiguously,

each child row of the hierarchical view rows follows its parent row, and

each child row is visually identified with its parent.

K. retrieving requested hierarchical data.

2. The method of claim 1 wherein the creating of the hierarchical database data storage tables include the steps of:

A. specifying one or more of the hierarchical database data storage tables;

B. specifying one or more user fields and user fields properties for each hierarchical database data storage table for storing user entered data;

C. specifying fields for storing hierarchical keys in each hierarchical database data storage table that uniquely identify each row in the hierarchical database;

D. creating the hierarchical database data storage tables as tables in the RDBMS.

3. The method of claim 2 of specifying the root conditions, including the steps of:

A. specifying the root conditions that a table in the hierarchical database data storage tables may be used to create the root row of the hierarchical database;

B. storing the root conditions.

4. The method of claim 2 of specifying the child conditions, including the steps of:

A. specifying conditions such that the table in the hierarchical database data storage table may be used to create the child row of the selected parent row in the hierarchical database;

B. storing the child conditions.

5. The method of claim 4 of specifying data to be inserted in the hierarchical keys of the new root row, and the new child row of the selected parent row, that distinguishes each of the hierarchical families, distinguishes each of the root rows, and identifies the parent row of each child row includes the steps of:

A. creating data in one or more fields of the hierarchical keys that distinguishes each family;

B. creating data in a subsequent one or more fields of the hierarchical keys for identifying children of a given parent in a family composed of a strings satisfying the conditions that;

the strings for a row for the subsequent fields is the same for all root rows in the hierarchical database,

the strings of the parent row for the subsequent fields is contained in the strings of the corresponding child'"'"'s strings with an additional string component appended to one of the child row'"'"'s strings such that the hierarchical keys are unique within a hierarchical family and the parent row occurs before the child row when sorted by the hierarchical keys.

6. The method of claim 5 of creating the new hierarchical family in the hierarchical database by inserting the new root row into the hierarchical database including the steps of:

A. selecting one of the tables of the hierarchical database data storage tables satisfying the root conditions;

B. inserting user data into a new row of the selected table;

C. inserting data into the hierarchical keys for identifying the new root row as the root row of the new family;

D. saving the new root row in the RDBMS.

7. The method of claim 6 of creating the new child row in the hierarchical database of the selected parent row including the steps of:

A. identifying the row of the hierarchical database as the parent row using the child conditions;

B. selecting one of the allowable child tables in the hierarchical database data storage table of the selected parent row using the child conditions;

C. inserting user data to the new row of the selected child table;

D. inserting data into the hierarchical keys to the new row that identifies the child row as a child of the selected parent row;

E. saving the new child row in RDBMS.

8. The method of claim 2 of updating the selected hierarchical database row includes the steps of:

A. selecting the row of the hierarchical database to be updated;

B. changing the data in one or more of the user fields of the selected row;

C. saving the selected row in the RDBMS.

9. The method of claim 6 of deleting the selected hierarchical database row and its descendants includes the steps of:

A. selecting the row of the hierarchical database to be deleted;

B. deleting the selected row in deleting and all its descendent rows in the RDBMS.

10. The method of claim 7 of creating the hierarchical views includes the steps of:

A. specifying a number of hierarchical views;

B. creating for each hierarchical view a SQL select query for each table in the hierarchical database data storage tables that;

includes the hierarchical key fields,

may include information from a number of fields in the table, and

may include descriptive text concerning the number of the fields in the table;

C. creating the hierarchical view as a union query composed of the SQL select query for each table in the hierarchical database data storage tables;

D. ordering the union query so rows from the same family are contiguous and each child in a family follows its parent;

E. storing the hierarchical views.

11. The method of claim 10 of retrieving requested hierarchical data includes the steps of:

A. creating a SQL select query applied to hierarchical database data storage tables that returns zero or more rows containing user requested information;

B. executing the query.

12. An apparatus for implementing a hierarchical database management system (HDBMS) used for creating and maintaining hierarchical databases using a relational database management system (RDBMS) as an implementing apparatus comprising;

a computer system for receiving hierarchical database information provided by a user, said computer system including,

a database application for converting said hierarchical database information to a database consistent format;

HDBMS software including SQL statements with interface to said RDBMS;

said HDBMS software being responsive to said database application for issuing commands for the creating and managing of said hierarchical database using said hierarchical database information in said database consistent format;

said RDBMS for executing said commands issued by said HDBMS software;

said HDBMS software being further responsive to said RDBMS for receiving and processing results of said executed commands;

said database application for receiving and converting said HDBMS software results to computer format;

said computer system displaying said computer format.

13. The apparatus of claim 12 wherein said RDBMS comprises:

a commercially available RDBMS.

14. The apparatus of claim 13 wherein said HDBMS software comprises:

a means of creating a set of one or more hierarchical database data storage tables in said RDBMS, consisting of hierarchical key fields and user data fields for storing rows of data of said hierarchical database wherein each row of said hierarchical database is a row in one of said hierarchical database data storage tables.

15. The apparatus of claim 14 wherein said HDBMS software further comprises:

a means of specifying root conditions determining which of said hierarchical database data storage tables may be used to create a new row in said hierarchical database called a root row.

16. The apparatus of claim 15 wherein said HDBMS software further comprises:

a means of specifying child conditions, wherein for said each row of said hierarchical database, called a parent row, said child conditions determine which of said hierarchical database data storage tables may be used to create a new child row.

17. The apparatus of claim 16 wherein said HDBMS software further comprises:

a means of defining descendents of said root row and hierarchical families of said hierarchical database wherein said descendents of said root row consists of all children of said root row and all children of a previous descendent of said root row, each hierarchical family consisting of said root row and its descendents.

18. The apparatus of claim 17 wherein said HDBMS software further comprises:

a means of specifying data to be inserted in said hierarchical keys of said new root row, and said new child row of a selected parent row, that distinguishes each of said hierarchical families, distinguishes each of said root rows, and identifies said parent row of each child row.

19. The apparatus of claim 18 wherein said HDBMS software further comprises:

a means of creating a new hierarchical family in said hierarchical database by inserting said new root row into said hierarchical database.

20. The apparatus of claim 19 wherein said HDBMS software further comprises:

a means of creating said new child row into said hierarchical database of said selected parent row by inserting said new child row into said hierarchical database.

21. The apparatus of claim 19 wherein said HDBMS software further comprises:

a means of updating a selected hierarchical database row.

22. The apparatus of claim 19 wherein said HDBMS software further comprises:

a means of deleting said selected or a subsequent selected hierarchical database row and its descendants.

23. The apparatus of claim 20 wherein said HDBMS software further comprises:

a means of creating one or more SQL select queries, hereafter called hierarchical views, wherein;

applying each hierarchical view to said hierarchical database creates hierarchical view rows,

each of said hierarchical view rows contains information from a row of said hierarchical database,

each row in said hierarchical database has a corresponding row in said hierarchical view rows,

said hierarchical view rows of each hierarchical family are displayed contiguously,

each child row of said hierarchical view rows follows its parent row, and

each child row is visually identified with its parent.

24. The apparatus of claim 23 wherein said HDBMS software further comprises:

a means of retrieving requested hierarchical data.

25. The apparatus of claim 23 wherein said database application comprises:

a database application with developer added functionality for receiving user information specifying and managing information about and in said hierarchical database, and for outputting information from said hierarchical database.

26. The apparatus of claim 25 wherein said computer system comprises:

one or more client computers used by users of the system for entering said hierarchical database data, receiving said hierarchical database information and storing data and software of said HDBMS software;

a server computer for storing said RDBMS software and some elements of said HDBMS, which may be a same computer as said client computers;

a communication means for exchanging information among said computers if said server is not said client computer;

devices for displaying hierarchical and relational database output.

27. A method for implementing a hierarchical database management system (HDBMS) used for creating and maintaining hierarchical databases using a relational database management system (RDBMS) and HDBMS software including SQL statements with interface to said RDBMS, including the steps of:

A. inputting user provided hierarchical database information into a computer system;

B. applying the hierarchical database information to a database application for use by the HDBMS software;

C. using the HDBMS software for issuing commands for creating and maintaining the hierarchical database provided by the hierarchical database information;

D. using an RDBMS for executing the commands of the HDBMS software for creating and maintaining the hierarchical database;

E. outputting requested elements of the data to the database application from the RDBMS;

F. displaying the requested elements of the data on the computer system.

28. The method of claim 27 wherein implementing the computer system includes the steps of:

A. implementing one or more client computers used by users of the system for entering hierarchical database data, receiving hierarchical database information and storing data and software of the HDBMS;

B. implementing a server computer for storing the RDBMS and some elements of the HDBMS, which may be a same computer as the client computers;

C. implementing a communication means for exchanging information among the computers if the server is not the client computer;

D. implementing devices for displaying hierarchical and relational database output.

29. The method of claim 28 wherein implementing the database application includes the steps of:

A. implementing a database application with developer added functionality for receiving user information;

B. specifying and managing information about and in said hierarchical database; and

for

C. outputting information from said hierarchical database.

30. The method of claim 29 wherein implementing the HDBMS software includes:

A. creating a set of one or more hierarchical database data storage tables in the RDBMS, consisting of hierarchical key fields and the user data fields for storing rows of data of the hierarchical database wherein each row of the hierarchical database is a row in one of the hierarchical database data storage tables.

31. The method of claim 30 wherein the HDBMS software further includes:

A. specifying root conditions determining which of said hierarchical database data storage tables may be used to create a new row in said hierarchical database called a root row.

32. The method of claim 31 wherein the HDBMS software further includes:

A. specifying child conditions, wherein for each row of the hierarchical database, called a parent row, the child conditions determine which of the hierarchical database data storage tables may be used to create a new child row.

33. The method of claim 32 wherein the HDBMS software further includes the steps of:

A. defining descendents of a root row and hierarchical families of the hierarchical database wherein the descendents of the root row consists of all children of the root row and all children of a previous descendent of the root row,

B. defining each hierarchical family consisting of a root row and its descendents.

34. The method of claim 33 wherein the HDBMS software further includes the steps of:

A. specifying data to be inserted in the hierarchical keys of the new root row, and the new child row of a selected parent row, that

B. distinguishes each of the hierarchical families, distinguishes each of the root rows, and identifies the parent row of each child row.

35. The method of claim 34 wherein the HDBMS application further includes:

A. creating a new hierarchical family in the hierarchical database by inserting the new root row into the hierarchical database.

36. The method of claim 35 wherein the HDBMS software further includes:

A. creating the new child row into the hierarchical database of a selected parent row by inserting the new child row into the hierarchical database.

37. The method of claim 35 wherein the HDBMS software further includes:

A. updating a selected hierarchical database row.

38. The method of claim 36 wherein the HDBMS software further includes:

A. deleting the selected or a subsequent selected hierarchical database row and its descendants.

39. The method of claim 36 wherein the HDBMS software further includes the steps of:

A. creating one or more SQL select queries, hereafter called hierarchical views, wherein;

B. applying each hierarchical view to the hierarchical database creates hierarchical view rows such that;

each of the hierarchical view rows contains information from a row of the hierarchical database,

each row in the hierarchical database has a corresponding row in the hierarchical view rows,

the hierarchical view rows of each hierarchical family are displayed contiguously,

each child row of the hierarchical view rows follows its parent row, and

each child row is visually identified with its parent.

40. The method of claim 39 wherein the HDBMS software further includes:

A. retrieving requested hierarchical data.

41. The method of claim 40 wherein implementing the RDBMS includes the steps of:

A. implementing on the server a commercially available RDBMS.