Patent Application
20060235820
The invention relates to methods and systems for accessing a hierarchical database and particularly to accessing these databases using a relational query. Even more particularly the invention relates to mechanisms for rapidly accessing a hierarchical database after receiving a relational query from a requester and rapidly returning those target records identified by the relational query to the requester.
Many companies and governments use hierarchical databases for capture and retrieval of data associated with transactions, particularly business transactions performed by the company or governmental body. A hierarchical database uses a hierarchical schema for storing information known as the parent/child model. A hierarchical schema may be represented as a tree structure, where each parent node may have a plurality of child nodes, while each child node may have only one parent node.
Another commonly used database is the relational database which is a tabular database having the data defined so that it can be reorganized and accessed in a number of different ways. In a relational database, data records are maintained in data tables or collection of rows all having the same columns. Each row is a data record and each column holds information of a particular type of data for the data records. Data records may be indexed using unique indices or keys that join different data records in different tables together.
Relational databases are particularly useful because the information stored therein may be accessed using a relational query language. One such query language, SQL (Structured Query Language) SQL IS A TRADEMARK OF INTERNATIONAL BUSINESS MACHINES CORPORATION, is widely used and understood by relational database users.
Unfortunately, asking fundamental relational questions of a hierarchical database is not possible without providing additional capabilities beyond what is normally available. For example, many companies and other organizations support their operations by maintaining two databases, a hierarchical database and a relational one, along with associated support staffs. This approach is costly and cumbersome to maintain.
Hoth et al. in U.S. patent application Ser. No. 2004/0030716 A1 describe a method for providing a relational schema in a hierarchical database. A bridging table is created to describe and document the interconnections between entities in a hierarchical database. The Hoth patent application noted above is incorporated herein by reference in its entirety.
While the method described by Hoth does provide the desired capability, it is often slow in response time due to the overhead required in forming the bridging table, and in delivering query responses back to the client.
An improvement in query response time is needed to satisfy customer demands for query capability with their hierarchical databases.
It is therefore a principal object of the present invention to provide a method of rapidly providing response data from a hierarchical database to a client query, presented in a relational query language.
It is another object to provide a system having such a rapid response query capability.
These and other objects are attained in accordance with one embodiment of the present invention wherein there is provided a method of querying a hierarchical database, comprising the steps of defining a plurality of many to many relationships for the hierarchical database, creating a bridging table having records to transform the many to many relationships between a first and second entity into one to many relationships between the first entity and the bridging table, and one to many relationships between the bridging table and the second entity, storing the bridging table in a memory in an integrated circuit chip, receiving a relational query from a requester, parsing the relational query by instructions executed on the integrated circuit chip, accessing each of the records in the bridging table and if the each record meets the query, storing a pointer to a target record in the hierarchical database, and reading all the target records or the pointers and transferring the read target records or the pointers to the requester.
In accordance with another embodiment of the invention, there is provided a system for querying a hierarchical database comprising means for defining a plurality of many to many relationships for the hierarchical database, an integrated circuit chip having a memory and an instruction processor, means for creating a bridging table stored on the integrated circuit chip, the bridging table having records to transform the many to many relationships between a first and second relationship into one to many relationships between the first relationship and the bridging table, and one to many relationships between the bridging table and the second relationship, means for receiving a relational query from a requester, means for parsing the relational query by instructions executed by instruction processor on the integrated circuit chip, means for accessing each of the records in the bridging table and if the each record meets the query, storing a pointer to a target record in the hierarchical database, and means for reading all the target records or the pointers and transferring the read target records or the pointers to the requester.
For a better understanding of the present invention, together with other and further objects, advantages and capabilities thereof, reference is made to the following disclosure and the appended claims in connection with the above-described drawings.
In
The many to many relationships from step 12 must therefore be transformed into one to many relationships in order to be compatible with the hierarchical database structure. One method of transformation is through use of a bridging table created in step 14. The bridging table is structured so that the many to many relationships are replaced, for example, with a plurality of one to many relationships between a first entity and the bridging table, and a second plurality of one to many relationships between the bridging table and a second entity.
The internal model is converted into a physical model in step 38 adapted to the requirements of the underlying database. If, for example, the underlying database is part of LOTUS NOTES, forms and views may be created. Data may then be entered into the forms to populate corresponding tables to create the underlying data structure. Data may also be stored in underlying data tables.
In step 40, interconnections between interconnected entities are registered using a joining table. The joining table may comprise paths between the entities identified in step 32 including those paths between entities that are interconnected using a bridging table. Each entry in the joining table is derived from the internal model created in step 36. Each entry in the joining table defines how data associated with a specific entity may be retrieved departing from another entity.
In step 42, meta-data is created for each entity, defining the types of data that may be extracted from a corresponding entity. Data may be retrieved for displaying or presenting to a user. Generating the meta-data comprises generating a table documenting the entities, the interconnection between the entities, or the data flow between the entities.
All of the steps listed above for
In
Processor core 54 includes an instruction processor for executing programming instructions. For example, processor core may execute instructions for parsing a relational query, or instructions for reading target records. The instructions may be stored on semiconductor chip 52. For example, instructions may be stored in cache 56, or flash memory 58, or within the processor core 54 itself. Furthermore, instructions may also be stored on storage 62 and retrieved as needed to practice the present invention. Frequently executed instructions are stored in cache 56, or within processor core 54 itself. Less frequently executed instructions may be stored in flash memory 58 or storage 62. Those of ordinary skill in the semiconductor design arts will recognize such tradeoffs and optimizations in data storage may be made without departing from the spirit of the present invention.
Semiconductor chip 52 may be mounted singly or in combination with other chips on a conventional or special single or multi-chip, chip carrier. The chip carrier is mounted in a preferred embodiment on a plug-in card for positioning in a mainframe box. The plug-in card preferably is adapted to provide attachment to an array of hard drives via ribbon cable or other means, and includes attachment to an I/O bus within the mainframe box.
Returning to
In step 18, a relational query is received from a requester. The requester may be a user who formulates his relational query using the SQL query language. Users typically expect to be able to ask business intelligence questions using a relational query to a database. The query may be entered at a workstation on which the hierarchial database, custom semiconductor chip and all other software and hardware elements of the present invention are self-contained. More typically, though, the database is located on a server computer and the user enters his query at a remotely connected workstation, terminal device, laptop computer, palm device, cellular telephone, or other portable device.
The relational query travels across the interconnection to custom semiconductor chip 52 where it is parsed in step 20 by instructions executed in processor core 54. Parsing allows the query to be matched to records in the stored bridging table or data table in step 22. For each record which meets the parsed query, a pointer is stored to a target record in the hierarchial database. Pointers may be stored anywhere on semiconductor chip 52. For example, the pointers may be stored in a stack in cache 56 or flash 58 memory of semiconductor chip 52. Pointers may also be stored external to chip 52, e.g., in storage area network (SAN) 62, or in any storage media location. Instructions for accessing the bridging and data table records and comparing each to the parsed query may be executed in processor core 54.
In step 24, the contents of the stack, e.g., the pointers from step 22, may be returned to the requester. In step 24, processor core 54 may also read the target records from the hierarchial database and transfer these records to the requester. The hierarchial database may be located on a hard drive or on SAN 62. When the requester is remotely located, the target records would normally be sent back to the requesting device, however, this is not required. Those skilled in the art will recognize that the target records, once retrieved, may be further processed into a report and that the report or target records themselves can be transferred to the requester at any desired location.
Reading the volumes of target records from the hierarchical database can be a time consuming, performance limiting operation. Consequently, a customized addressing algorithm as shown in
In step 74, paths are set up to link to the hardware where the database is located. For example, if the database is located on a hard drive, then communication links to the hard drive control unit are initialized in step 74.
In step 76, a hierarchical read is done all the way to the target segments using the data table addresses, bridging table, meta data, and table interconnections described above.
In step 78, the target segments are transferred to the requestor. As noted for step 24, the pointers may alternatively be returned to the requester. In step 80, the addressing algorithm terminates.
While there have been shown and described what are at present considered to be the preferred embodiment of the invention, it will be obvious to those skilled in the art that various changes and modifications may be made therein without departing from the scope of the invention as defined by the appended claims.
