Logical schema to allow access to a relational database without using knowledge of the database structure

• US 5,701,453 A
• Filed: 07/01/1993
• Issued: 12/23/1997
• Est. Priority Date: 07/01/1993
• Status: Expired due to Term

• Current Assignee: International Business Machines Corporation
• Original Assignee: Informix Software, Inc. (International Business Machines Corporation)
• Inventors: Mayfield, Kevin B., Jackson, Brian D., Maloney, Christopher W., Tracy, Kent A., Mills, Mark A.
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): Alam, Hosain T.
• Application Number:
  US08/086,001
• Time in Patent Office: 1,636 Days
• Field of Search: 395/600, 395/425, 395/400, 395/700, 395/161, 395/602, 395/604, 395/611, 395/613, 395/607, 395/348, 395/352, 364/419, 364/578, 364/192
• US Class Current: 707/714
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/2428 Query predicate definition using graphical user interfaces, including menus and forms G06F16/2423 takes precedence
• CPC Class Codes: Y10S 707/954 Relational
• CPC Class Codes: Y10S 707/956 Hierarchical
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

Logical schemas are used to allow an end user the ability to access and manipulate relational database data without knowledge of the structure of the relational database. A logical schema is first created specifying which tables are available to an end user, and the relationships between columns of those tables. The logical schema defines a structure for the data fields having a master level and a plurality of detail levels. An end user may manipulate the logical schema using a graphical interface to build customized forms, reports, and queries. An end user is not required to be familiar with a database query language, such as SQL, or the structure of the relational database.

Claims

1. A method for providing access to data stored in a plurality of relational database tables, each of said relational database tables having at least one field, comprising the steps of:

specifying at least one logical relationship by;

selecting a pair of tables having a common field from among said plurality of relational database tables;

selecting the common field of said pair of tables as a join field; and

selecting a join operator for said join field to define a join between said pair of tables,

the logical relationship being one of One-to-One, One-to-Zero-or-One, One-to-Zero-or-More, and One-to-One-or-More; and

creating a logical schema defining a multilevel hierarchy of the tables in the specified logical relationships, wherein a first table and a second table of a pair of tables in a logical relationship are at an equal hierarchy level if the logical relationship is one of One-to-One and One-to-Zero-or-One and the first table is at a higher hierarchy level than the second table if the logical relationship is one of One-to-Zero-or-More One-to-One-or-More.

2. The method of claim 1, wherein said join operators are chosen from a set including:

equals, not equal, greater than, greater than or equal to, less than, and less than or equal to.

3. The method of claim 1, further comprising the steps of;

displaying fields of tables in the logical schema multilevel hierarchy, the fields being arranged by hierarchy levels of the corresponding tables;

receiving input for a value of at least one of the displayed fields; and

querying said relational database tables based on the received input and the logical relationships included in the logical schema.

4. The method of claim 3, further comprising the step of aliasing a field in said tables in said logical schema to provide a descriptive name for said field, wherein the step of displaying fields displays the aliased name for said field.

5. A computer system comprising:

a memory for storing a logical schema for providing an interface for a user to access data stored in a plurality of relational database tables, each of said relational database tables having at least one field, wherein the logical schema comprises a plurality of logical schema tables, each corresponding to a relational database table and participating in at least one logical relationship, each logical relationship comprising a pair of logical schema tables, a loin field common to both of the logical schema tables in the pair, and a loin operator for joining the pair of logical schema tables based on the join operator, the logical relationship being one of One-to-One, One-to-Zero-or-One, One-to-Zero-or-More, and One-to-One-or-More, the logical schema defining a multilevel hierarchy of the logical schema tables wherein a first table and a second table of a pair of tables in a logical relationship are at an equal hierarchy level if the logical relationship is one of One-to-One and One-to-Zero-or-One and the first table is at a higher hierarchy level than the second table if the logical relationship is one of the One-to-Zero-or-More and One-to-One-or-More; and

a display for displaying fields of the logical schema tables.

6. A computer-implemented method for providing a hierarchical view of data stored in a plurality of relational database tables comprising the steps of:

providing a logical schema for at least two logical schema tables, each logical schema table corresponding to a relational database table, wherein the logical schema is defined by pairwise logical relationships between the logical schema tables, each logical schema table participating in at least one logical relationship, wherein a logical relationship is defined by a join operator on a join field common to both logical tables in the pair, and a logical relationship is one of One-to-One, One-to-Zero-or-One, One-to-Zero-or-More, and One-to-One-or-More; and

establishing a multilevel hierarchy among the logical schema tables, wherein a first table and a second table of the pair of tables in each logical relationship are at an equal hierarchy level if the logical relationship is one of One-to-One and One-to-Zero-or-One, and the first table is at a higher hierarchy level than the second table if the logical relationship is one of One-to-Zero-or-More and One-to-One-or-More.

7. The method of claim 6, further comprising the step of displaying the logical schema as a multilevel hierarchy.

8. The method of claim 7, further comprising the step of displaying selected fields selected from the logical schema tables, the fields being arranged by hierarchy levels of the corresponding logical schema tables.

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

receiving input for a value of at least one of the displayed fields;

querying the relational database tables for data satisfying the input and the joins defining the logical relationships in the logical schema; and

displaying data resulting from the query.

10. The method of claim 9, wherein the step of displaying data resulting from the query displays data corresponding to the fields selected for display, grouped in accordance with the hierarchy levels of the fields.

11. The method of claim 10, wherein data resulting from the query for each hierarchy level are displayed in a scrolling table.