WO2001086504A2

WO2001086504A2 - Browser oriented method to view contents of a plurality of databases

Info

Publication number: WO2001086504A2
Application number: PCT/US2001/014581
Authority: WO
Inventors: Terry P. Lindsey
Original assignee: Orillion Corporation
Priority date: 2000-05-05
Filing date: 2001-05-04
Publication date: 2001-11-15
Also published as: EP1407382A2; WO2001086504A3; JP2001318816A; AU2001261215A1

Abstract

A machine-executed method and apparatus for displaying relationships between records in one or more databases in a browseable format is disclosed. Each database comprises one or more tables. Each table includes one or more fields and one or more records. The method entails: (a) displaying, on a display, a field value of a selected record in a table of a selected database; (b) displaying on the display a list of possible relationships between the selected record and other records in the database, where each possible relationship defines a pathway from the selected record to a list of other records; (c) receiving a user input signal designating one of the possible relationships as a selected relationship; (d) adding to the display at least a portion of a list of records that are related to the selected record through the selected relationship, referred to as the related-records list; and (e) adding to the display a list of possible relationships between the related-records list and other records in the database, where each possible relationship defines a pathway from the related-records list to a list of other records.

Description

BROWSER ORIENTED METHOD TO VIEW CONTENTS

OF A PLURALITY OF DATABASES

BACKGROUND OF THE INVENTION

FIELD OF THE INVENTION The present invention relates generally to the field of software applications.

More particularly, it concerns a software program for browsing the records of one or more arbitrary relational databases.

DESCRIPTION OF RELATED ART

A database frequently takes the form of a collection of files for storing related in-formation. A relational database is a database in which a collection of tables contains database records where each record within a single table is comprised of a set of fields common to all records in that table. in some relational databases, the data can be accessed along complex dimensions using the standard query language (SQL). Such databases make it convenient to look up information based on specifications derived from a semantic frame.

Furthermore, each database has a data structure. A data structure is defined as a physical or logical relationship among data elements, designed to support specific data manipulation functions. Some data structures are in a browseable format. Thus, the contents or data contained in the structure is browseable. Browsing the contents or data of most databases on a table by table basis is not an easy task, due to the large number of records contained in the tables. Furthermore, a database user may wish to cross- reference one data record in one relational database with another record in another database. To do this the user would have to browse through the records of each database one at a time. Such method is cumbersome and time consuming. Alternatively, the user may develop a special program to input all these records that are stored in various databases and combine them into one database. Such a task, however, is likely to be inefficient, costly and time consuming. As relationships among data stored in separate databases become more important, the value in the ability to view these data all at once becomes more evident. The present invention gives a database user the ability to view the data stored in arbitrary relational databases at once. SUMMARY OF THE INVENTION

A machine-executed method and apparatus are provided to display relationships between records in a database in a browseable format. Each database comprises a plurality of tables. Each table contains one or more fields and one or more records. The method includes several steps. The first step consists of selecting a record, followed by displaying a field value of the selected record on a display, such as a computer screen.

Then, a list of possible relationships between the selected record and other records in the database is displayed. Each possible relationship defines a pathway to or from the selected record from or to a list of one or more other records. That is, each pathway associates one record in a table with one or more records in another table through the use of keyed field relationships (i.e., primary keys, foreign keys, composite keys, secondary keys, etc.; the foregoing types of keys are well known in the database arts.)

The next step is to receive an electronic input signal from the user, designating one of the possible relationships as a selected relationship. The input signal may be received by highlighting the selected relationship with a mouse and clicking or double- clicking on it.

As a result of receiving the electronic input signal, at least a portion of a list of records that are related to the selected record through the selected relationship, referred to as the "related-records list," is added to what is already displayed on the screen. Along with the related-records list, a list of possible relationships between the related- records list and other records in the database is also added to the display, where each possible relationship defines a pathway from the related-records list to a list of other records.

If the user wishes to view a list of records that are related to a record in the related-records list and a list of possible relationships between that record and other records in the database, the user will send another input signal designating the record in the related-records list as the new selected record.

In a second aspect of the invention, a machine-executed method and apparatus is provided for displaying relationships between records of one or more relational databases in a browseable format. Each database may have its own arbitrary structure. One database might be a Microsoft Access database. Another database might be an Oracle database. Each database comprises a plurality of tables. Each table contains one or more fields and each field contains one or more records. Each table and each field has a name. The method of the second aspect of the invention includes several steps. The first step is determining the respective structures of the plurality of relational databases, e.g., Oracle, Sybase, Microsoft Access, etc.

The next step is integrating the respective structures of the plurality of relational databases into a front-end integrated data structure. This step is comprised of (a) removing the conflicting names of tables and fields in the front-end integrated data structure, and (b) performing a join operation to establish a "link" between selected fields in at least two respective tables each in a separate respective database.

In removing the conflicting names, a name is selected, followed by displaying a cross-reference list of uses of the selected name in the plurality of relational databases. The selected name is then edited with another name more suitable to the user, referred to as an "alias." Once the selected name is renamed with its "alias" name, the "alias" name is displayed in lieu of the selected name. No changes are made to the physical database.

In performing the join operation, a "link" is formed between the selected fields when the selected fields are joined in a "virtual" table. When two or more database tables are joined to create a "virtual" table, by definition, the database tables contain a common field. For purposes of the "virtual" table, a selected field in one database table is designated as a "primary key," while the corresponding selected field in the other joined database table is designated as a "foreign key." Once the structures of the plurality of relational databases are integrated into the front-end integrated data structure, a record from the front-end integrated data structure is selected, and its field value is displayed.

Next, a list of possible relationships between the selected record and other records in the plurality of relational databases is displayed. Each possible relationship defines a pathway from the selected record to a list of other records. That is, each pathway associates one record in a table with one or more records in another table through the use of keyed field relationships (i.e., primary keys, foreign keys, composite keys, secondary keys, etc.; the foregoing types of keys are well known in the database arts.)

Next, an electronic input signal is received from the user, designating one of the possible relationships as the selected relationship. The input signal may be received by highlighting the selected relationship and clicking or double-clicking on it.

As a result of receiving the electronic input signal, at least a portion of a list of records that are related to the selected record through the selected relationship, referred to as the "related-records list," is added to what is already displayed on the screen. Along with the related-records hst, a list of possible relationships between the related- records list and other records in the database is also added to the display, where each possible relationship defines a pathway from the related-records list to a list of other records.

If the user wishes to view a list of records that are related to a record in the related-records list and a list of possible relationships between that record and other records in the plurality of relational databases, the user will send another input signal designating the record in the related-records list as the new selected record. The present invention has many advantages, among them is the ability to view the data structures of one or more relational databases in a browseable format. In addition to browsing the data structure, the present invention provides the ability to view the database contents, i.e., the records. The present invention provides flexibility to the database user to choose any record as the starting point. From that record, the user may view other records related to it. This feature of the invention makes it easy to cross-reference one record in one table in a relational database with another record in a second table, possibly in another relational database. Another significant advantage of the present invention is the flexibility to rename the fields and tables of the various relational databases with other names more suitable to the user.

BRIEF DESCRIPTION OF THE DRAWINGS The following drawings form part of the present specification and are included to further demonstrate certain aspects of the present invention. The invention may be better understood by reference to one or more of these drawings in combination with the detailed description of specific embodiments presented herein.

Fig. 1 schematically depicts a hypothetical plurality of relational databases and a structural breakdown of one of the databases.

Fig. 2 illustrates a display of a root directory and a corresponding list of table names in a browsing window.

Fig. 3 illustrates a display which outlines a user removing conflicts among names. Fig. 4 illustrates a display in which a new subdirectory is created.

Fig. 5 illustrates a display in which a table is dragged and dropped into a subdirectory.

Fig. 6 illustrates a display in which a "virtual table" is created by using a link between fields. Fig. 7 illustrates a display of hierarchical containment tree of folders of a table.

Fig. 8 illustrates a display of information contained in an intrinsic field.

Fig. 9 illustrates a display of information contained in an associative field.

Fig. 10 illustrates a display of the process of selecting a record as a starting point. Fig. 11 illustrates a display of the process of obtaining records associated to the starting point record.

While the invention is susceptible to various modifications and alternative forms, specific embodiments thereof have been shown by way of example in the drawings and are described in detail in this specification. It should be understood, however, that the description of specific embodiments is not intended to limit the invention to the particular forms disclosed. The intention is to cover all modifications, equivalents, and alternatives falling within the spirit and scope of the invention as defined by the appended claims.

DETAILED DESCRIPTION OF THE SPECIFIC EMBODIMENTS

Illustrative embodiments of the invention are described below. In the interest of clarity, not all features of an actual implementation are described in this specification. It will of course be appreciated that in the development of any such actual embodiment, numerous implementation-specific decisions must be made to achieve the developers' specific goals, such as compliance with system-related and business-related constraints, which will vary from one implementation to another. Moreover, it will be appreciated that such a development effort, even if complex and time-consuming, would be a routine undertaking for those of ordinary skill in the art having the benefit of this disclosure.

Figure 1 shows a plurality of relational databases and their typical structural breakdown. Each database is broken down to m number of tables, as shown by Table; of DBi up to Table_m of DB Each table is further broken down to a set of fields. Each field can be organized as Fieldi, Field₂, up to Field_t. Each field contains one or more records, e.g., Recordπ, Record^, and Record_lt, where each record consists of the same set of fields (whether or not any particular field in a given record actually contains non- null data).

One aspect of the present invention is the collection and display of structure information from several arbitrary relational databases organized according to different file format specifications. For example, one database might be a Microsoft Access database 110, another database might be an Oracle database 120, still another might be a Sybase database 130. In accordance with the invention, the structure information may be displayed in a browseable representation. Once the structure information has been collected and displayed, it can be manipulated in virtually any desired way, as well known to those of ordinary skill having the benefit of this disclosure. One approach to implementing this aspect of the invention is to establish a file that contains configuration information describing the databases it represents. Such a file can be named by its user, in the same fashion that word processing documents are saved. The file may also be updated, edited, and re-saved. A first step of building this browseable structure for one or more relational databases is to describe each database to be viewed to the program, thereby determining the structure of the databases. To accomplish this, the program user may enter information describing the database name, such as, its EP address (or server name) and access methodology (e.g., JDBC, SQL, IP Sockets, etc.). This information may be entered using a data-entry routine in a suitably designed computer program (e.g., a dialog box with fill-in-tlie-blank fields, drop-down menus, check boxes, radio buttons, and the like), and saved in the file. (Alternatively, of course, the information may be entered manually by directly editing the appropriate file or files, e.g., with a text editor.) If a list or table of such database information is already maintained, the information may be entered by designating the particular databases from the list or table, e.g., with point-and-click or drag-and-drop techniques. The saved information may be used whenever database access is required.

Referring to Figure 2, when the information has been provided to the program, the program accesses the data dictionary of each database and extracts table structures, field names, data types, definitions, and all other structural information. (Alternatively, such information can be provided as described in the previous paragraph.) This information may be displayed to the user in a browsing window 200, as illustrated in Figure 2. Prior to any editing by the user, the program displays the extracted database as a list of table names in a root directory. As an example, Figure 2 illustrates a list of table names 210 in a root directory 220. Compared to a standard PC file browser, the table names correspond to a special kind of subcategory. When one of these table names is opened for browsing, the field names of the selected table are listed as if they were files, e.g., the list of field names 230 in Figure 2. Thus, each field name corresponds to a leaf on an inverted tree. Initially, the tree is likely to be flat and may be rather burdensome to manipulate; however, as further structure is added, navigation through the tree becomes more intuitive and rapid. Referring to Figure 3, one of the problems with integrating a data structure from an arbitrary set of relational databases is that some databases may use the same table names or field names for different record-content definitions and some may use different names in different databases for the same or similar content definitions. (By analogy, the word "chips" in the United States refers to potato chips but in England the same word refers to what Americans would call French fries, i.e., the same name is used to identify different content definitions. As another analogy, in United States postal addresses, the numbers immediately after the state are called the ZIP Code; in other countries, the same kind of number may be referred to as a postal code, i.e., different names are used to label the same or similar content definitions.)

Figure 3 illustrates a display in which a user may remove conflicts among names. To remove conflicts among names, the user sends an electronic signal by selecting a name, e.g., selecting table name My_Name_C by clicking or slow-double- clicking on it, and editing or replacing the name with a name more suitable to the user. Such edited or replacement names are called "aliases" and do not affect the original database, but can be used by the user to reference the table name or field name for which it is an alias.

As shown in Figure 4, the user may create new subdirectories by selecting the directory in which the new subdirectory is to be established, then selecting "New" from the "File" menu 400. The program then presents the user with a choice of several new things that can be created: "Folder" will be one choice. Upon selecting "Folder", the program creates a new subdirectory called "New_Dir_l," (or "New_Dir_2," if "New_Dir_l" already exists, and so on), within the selected directory. The new subdirectory is initially empty. At any subsequent time, the user may select the new subdirectory, e.g., by performing a slow-double-click on "New_Dir_l," and enter a more appropriate name. The user may then select one or more tables or directories and drag-and-drop them into the newly formed subdirectory. For example, as shown in Figure 5, table My_Name_C was selected and dragged-and-dropped into the subdirectory My Name XYZ. The newly created subdirectory has the same structure as the selected tables. By repeating the steps displayed in Figures 3-5, a new browseable integrated data structure can be developed to act as a front-end to the table structures contained in the databases. This front-end allows the users to manipulate data from a common interface. The selected front-end interface can resemble a conventional browser of the kind widely used in browsing file systems (see, e.g., Windows Explorer). Alternatively, the data can be provided to a conventional Web server and "served up" to one or more Web browsers as well known to those of ordinary skill having the benefit of this disclosure.

Referring to Figure 6, with the program embodiment of the present invention the user is able to create new "virtual" tables by creating a reference called a "link." In a specific embodiment, links are created, as shown in Figure 6, by selecting a field name and dragging-and-dropping the selected field name into an empty directory, creating a "virtual" table. In Figure 6, for instance, the field name My_Field_Name_2 is dragged- and-dropped into the empty directory New Dir l. By following this procedure, any empty directory may be designated as a "virtual" table. In this case, the field names are assigned to a "virtual" table.

When two or more database tables are joined to create a "virtual" table, by definition, the database tables contain a common field. For purposes of the "virtual" table, the field in one of the database tables is designated as a "primary key," while the corresponding field in the other joined database table is designated as a "foreign key." (The terms primary keys and foreign keys are well known in the database arts.) Furthermore, field names from different databases may be associated in a "virtual" table so long as there is sufficient structural information to perform the "join."

Referring to Figure 7, once the browseable integrated data structure is completed, the user may view the directories, tables, or fields of the integrated data structure by selecting a desired category of folders. Each category displays a particular set of information. Also, each category is displayed as a folder within a hierarchical containment tree of folders. That is, each folder contains the categories below it within the hierarchy. For example, if a directory-name is selected, the contents of the directory, in the form of subdirectory names and table names, will appear together with other selected information. On the other hand, if a table name is selected, the contents of the table, in the form of field names will appear together with other selected information. As an example, Figure 7 shows that the field names Field_Name_l to Field_Name_4 are displayed on the browsing window 200 when the table name My_Name_C is selected. Referring to Figures 8 and 9, if a field name is selected, its field descriptor information, which consists of a cross reference information and other related documentation, will appear. Two types of fields exist: intrinsic and associative. My_Field_Name_2 in Figure 8 is an example of an intrinsic field. An intrinsic field contains information that pertains to a descriptor of the record of which it is a part and does not point to any other database record. In one embodiment, a conflict in the field definitions is displayed on the screen. In Figure 8, for example, Intrinsic Field Detail lists that information. The field names will appear in both the database name and their alias name, if an alias has been entered or provided. Associative fields, on the other hand, contain information that pertains to a relationship between a record in a table and a record or set of records in another table. An example of an associative field is a foreign key. Figure 9 shows an example of information contained in an associative field, e.g., My_Field_Name_l. The database and table names where the field name appears may also be displayed.

Another important aspect of the present invention is the ability to allow users to browse not just the data structure of the plurality of arbitrary relational databases but also the contents, i.e., the records, of each database. It is believed that browsing the contents of most databases on a table by table basis has been thought to be difficult, if not impossible, primarily due to the large number of records contained in each table. The ability to browse through records of various databases is primarily based on the insight that if one begins with an individual record in the database and browses through the related records, the number of records displayed in any single column on the browser will remain within reasonable display limits.

This browsing capability is based on the foreign key structure of the database.

The foreign keys describe associations in the database and allow the database users to associate one record in a table with one or more records in another table. The browsing starts from a single record in a table and steps through a foreign key association to a set of related records in another table, which could be located in the same database or a different database. The user may browse through another set of records by selecting another record from this set of related records and steps through another foreign key association, which will lead him to another set of related records. Referring to Figure 10, in another embodiment of the present invention, the user can choose any record as his starting point for browsing from the integrated data structure. He may choose his starting point by first selecting a database name if he desires to view records from more than one database. Then, he selects a table name, followed by a field name in that table that is a primary key. Figure 10 illustrates a display which shows the starting point record 1010, i.e., CDRP-3X1DCS, the table name 1020, i.e., Element_Name, in the column header, and the primary key value 1030, i.e., CDRP-3X1DCS, in a column under the Entities section 1040 of the browser. The Associations section 1050, which is directly above the Entities section 1040, contains some or all relationships or associations 1060 (e.g., Locations, DS1 Physical Ports, DS3 Physical Ports, and Components) between the starting point record 1010 and other records in the databases.

Referring to Figure 11, in another embodiment of the present invention, when the user selects one of the displayed associations 1060 to specify the browsing direction for navigation, the program displays the list of related records 1110 that are associated with the starting point record 1010 by means of the specific browsing direction. For example, when the user, as illustrated in Figure 11, selected DS3 Physical Ports as the browsing direction, the list of related records CDRP-3X1DCS-472 to CDRP-3X1DCS- 481 is displayed. If the user desires to view other records that are associated to a record from this list of related records 1110, he simply repeats the previous steps by first selecting that record as the new starting point record.

In another embodiment of the present invention, the user may also specify the column in which the related records are to be displayed. Yet, in another embodiment, the user may refer back to any previously displayed information by using the slider 1120 at the bottom of the screen. It will be apparent to those of ordinary skill having the benefit of this disclosure that any of the foregoing variations may be implemented by programming one or more suitable general-purpose computers having appropriate hardware. The programming may be accomplished through the use of a program storage device readable by the computer and encoding a program of instructions executable by the computer for performing the operations described above. The program storage device may take the form of, e.g., one or more floppy disks; a CD ROM or other optical disk; a magnetic tape; a read-only memory chip (ROM); and other forms of the kind well-known in the art or subsequently developed. The program of instructions may be "object code," i.e., in binary form that is executable more-or-less directly by the computer; in "source code" that requires compilation or interpretation before execution; or in some intermediate form such as partially compiled code. The precise forms of the program storage device and of the encoding of instructions is immaterial here.

The particular embodiments disclosed above are illustrative only, as the invention may be modified and practiced in different but equivalent manners apparent to those skilled in the art having the benefit of the teachings. Furthermore, no limitations are intended to the details of construction or design shown, other than as described in the claims below. It is therefore evident that the particular embodiments disclosed above may be altered or modified and all such variations are considered within the scope and spirit of the invention. Accordingly, the protection sought is as set forth in the claims below.

Claims

WHAT IS CLAIMED IS:

1. A method of displaying relationships between records in a database, the database comprising a plurality of tables, each table containing one or more records divided into one or more fields, the method comprising:

(a) determining the respective structures of the plurality of relational databases;

(b) displaying, on a display, a field value of a selected record in a table of the database; (c) displaying on the display a list of possible relationships between the selected record and other records in the database, where each possible relationship defines a pathway from the selected record to a list of other records;

(d) receiving a user input signal designating one of the possible relationships as a selected relationship; (e) adding to the display at least a portion of a list of records that are related to the selected record through the selected relationship, referred to as the related- records list; and

(f) adding to the display a list of possible relationships between the related-records list and other records in the database, where each possible relationship defines a pathway from the related-records list to a list of other records.

2. The method of claim 1 , further comprising:

(g) receiving a user input signal designating a record in the related-records list as a new selected record; and

(h) repeating, for the new selected record, the operations recited in subparagraphs (d) through (f) of claim 1.

3. A method of displaying relationships between records of a plurality of relational databases of arbitrary structure, each database comprising a plurality of tables, each table containing one or more records divided into one or more fields, each table having a name, and each field having a name, the method comprising: (a) determining the respective structures of the plurality of relational databases; (b) integrating the respective structures of the plurality of relational databases into a front-end integrated data structure;

(c) displaying, on a display, a field value of a selected record in one of the plurality of tables in the plurality of relational databases; (d) displaying on the display a list of possible relationships between the selected record and other records in the plurality of relational databases, where each possible relationship defines a pathway from the selected record to a list of other records;

(e) receiving a user input signal designating one of the possible relationships as a selected relationship; (f) adding to the display at least a portion of a list of records that are related to the selected record through the selected relationship, referred to as the related- records list; and

(g) adding to the display a list of possible relationships between the related-records list and other records in the plurality of relational databases, where each possible relationship defines a pathway from the related-records list to a list of other records.

4. The method of claim 3, further comprising:

(a) receiving a user input signal designating a record in the related-records list as a new selected record; and

(b) repeating, for the new selected record, the operations recited in subparagraphs (e) through (g) of claim 3.

5. The method of claim 3, wherein integrating the respective structures comprises: removing the conflicting names of tables and fields in the front-end integrated data structure; and performing a join operation to establish a link among selected fields in at least two respective tables.

6. The method of claim 5, wherein removing the conflicting names comprises: displaying, for a selected name, a cross-reference list of uses of the selected name in the plurality of relational databases; receiving at least one editing command from the user, the at least one editing command designating an operation to be performed on a selected table or on a selected field; and executing the at least one editing command.

7. The method of claim 6, wherein the at least one editing command comprises: receiving an alias designator which designates an alias for a selected name; and displaying the alias in lieu of the selected name.

8. The method of claim 7, further comprising: storing a representation of the relationship between the alias and the selected name.

9. A computer-readable medium encoded with instructions that, when executed by a computer, perform a method in accordance with a specified one of claims 1 through 8.

10. A computer programmed to perform a method in accordance with a specified one of claims 1 through 8.