DETERMINING QUERY ENTITIES FOR AN ABSTRACT DATABASE FROM A PHYSICAL DATABASE TABLE

• US 20080228800A1
• Filed: 03/13/2007
• Published: 09/18/2008
• Est. Priority Date: 03/13/2007
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Dettinger, Richard D., Kulack, Frederick A.
• Granted Patent: US 8,326,852 B2
• Time in Patent Office: Days
• US Class Current: 707/102
• CPC Class Codes: G06F 16/2423 Interactive query statement specification based on a database schema

Abstract

Embodiments of the invention provide techniques for determining query entities in physical tables for an abstract database. A query entity may be created to store a particular set of data. The query entity may be defined in terms of the structure of the physical database, such as an SQL statement, used to generate the query entity as needed. The fields included in a given query entities may be determined based on the presence (or absence) of values in the columns of a physical database table. In addition, logical fields having access method which map to the fields included in a query entity may be generated.

Claims

1. A method of determining, from a physical database table having one or more fields, a set of query entities for a database abstraction model, comprising:

identifying a selection of an entity field, from the fields of the physical database table;

determining a set of unique values stored in the entity field;

identifying a selection of one or more of the unique values from the set of unique values, wherein each selected value identifies a different query entity to be generated; and

for each selected unique value;

identifying a selection of one or more additional fields of the database table to include in that query entity,

generating a query entity definition used to generate the query entity, wherein the generated query entity includes database records having the unique value for that query entity and the selected one or more additional fields included in that query entity, and

storing the query entity definition as an object of the database abstraction model, wherein the database abstraction model logically describes a database that includes the physical table.

2. The method of claim 1, wherein the query entity definition is an SQL statement used to generate a database table having the entity field and one or more additional database fields.

3. The method of claim 1, wherein the query entity definition further includes an identification field used to link the entity query to the other tables in the physical database.

4. The method of claim 1, further comprising, generating a logical field having an access method mapping the logical field to one of the additional fields of one of the query entities.

5. The method of claim 4, wherein the access method is one of a simple, filtered and composed access method type.

6. The method of claim 1, wherein the step of identifying a selection of one or more additional fields comprises:

identifying fields of the database table that are populated with data values related to the unique value; and

including the identified fields in the selection of one or more additional fields.

7. The method of claim 1, further comprising:

presenting the fields included in the query entity definition to a user in a graphical user interface (GUI), wherein the GUI is configured to allow the user to modify which fields are included in the query entity definition.

8. The method of claim 7, further comprising:

receiving a request to access a given query entity;

retrieving the query entity definition stored in the data abstraction model;

generating the query entity from the query entity definition;

presenting a display of the data records in the query entity.

9. A computer-readable storage medium containing a program which, when executed, performs an operation for determining, from a physical database table having one or more fields, a set of query entities for a database abstraction model, comprising:

identifying a selection of an entity field, from the fields of the physical database table;

determining a set of unique values stored in the entity field;

identifying a selection of one or more of the unique values from the set of unique values, wherein each selected value identifies a different query entity to be generated; and

for each selected unique value;

identifying a selection of one or more additional fields of the database table to include in that query entity,

generating a query entity definition used to generate the query entity, wherein the generated query entity includes database records having the unique value for that query entity and the selected one or more additional fields included in that query entity, and

storing the query entity definition as an object of the database abstraction model, wherein the database abstraction model logically describes a database that includes the physical table.

10. The computer-readable storage medium of claim 9, wherein the query entity definition is an SQL statement used to generate a database table having the entity field and one or more additional database fields.

11. The computer-readable storage medium of claim 9, wherein the query entity definition further includes an identification field used to link the entity query to the other tables in the physical database.

12. The computer-readable storage medium of claim 9, wherein the operation further comprises, generating a logical field having an access method mapping the logical field to one of the additional fields of one of the query entities.

13. The computer-readable storage medium of claim 12, wherein the access method is one of a simple, filtered and composed access method type.

14. The computer-readable storage medium of claim 9, wherein the step of identifying a selection of one or more additional fields comprises:

identifying fields of the database table that are populated with data values related to the unique value; and

including the identified fields in the selection of one or more additional fields.

15. The computer-readable storage medium of claim 9, further comprising:

presenting the fields included in the query entity definition to a user in a graphical user interface (GUI), wherein the GUI is configured to allow the user to modify which fields are included in the query entity definition.

16. The computer-readable storage medium of claim 15, wherein the operation further comprising:

receiving a request to access a given query entity;

retrieving the query entity definition stored in the data abstraction model;

generating the query entity from the query entity definition; and

presenting a display of the data records in the query entity.

17. A system, comprising:

a processor; and

a memory containing a program which, when executed by the processor, performs an operation for determining, from a physical database table having one or more fields, a set of query entities for a database abstraction model, the operation comprising;

identifying a selection of an entity field, from the fields of the physical database table;

determining a set of unique values stored in the entity field;

identifying a selection of one or more of the unique values from the set of unique values, wherein each selected value identifies a different query entity to be generated; and

for each selected unique value;

identifying a selection of one or more additional fields of the database table to include in that query entity,

generating a query entity definition used to generate the query entity, wherein the generated query entity includes database records having the unique value for that query entity and the selected one or more additional fields included in that query entity, and

storing the query entity definition as an object of the database abstraction model, wherein the database abstraction model logically describes a database that includes the physical table.

18. The system of claim 17, wherein the query entity definition is an SQL statement used to generate a database table having the entity field and one or more additional database fields.

19. The system of claim 17, wherein the query entity definition further includes an identification field used to link the entity query to the other tables in the physical database.

20. The system of claim 17, system, wherein the operation further comprises, generating a logical field having an access method mapping the logical field to one of the additional fields of one of the query entities.

21. The system of claim 20, wherein the access method is one of a simple, filtered and composed access method type.

22. The system of claim 17, wherein the step of identifying a selection of one or more additional fields comprises:

identifying fields of the database table that are populated with data values related to the unique value; and

including the identified fields in the selection of one or more additional fields.

23. The system of claim 17, further comprising:

presenting the fields included in the query entity definition to a user in a graphical user interface (GUI), wherein the GUI is configured to allow the user to modify which fields are included in the query entity definition.

24. The system of claim 23, wherein the operation further comprising:

receiving a request to access a given query entity;

retrieving the query entity definition stored in the data abstraction model;

generating the query entity from the query entity definition; and

presenting a display of the data records in the query entity.