System and method for generating horizontal view for SQL queries to vertical database

• US 6,763,350 B2
• Filed: 06/01/2001
• Issued: 07/13/2004
• Est. Priority Date: 06/01/2001
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Xu, Yirong, Somani, Amit, Agrawal, Rakesh
• Primary Examiner(s): Amsbury, Wayne
• Assistant Examiner(s): NGUYEN, CAM LINH T
• Application Number:
  US09/872,385
• Publication Number:
  US 20030110189A1
• Time in Patent Office: 1,138 Days
• Field of Search: 707/2-4, 707/100-102
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2445 Data retrieval commands; View definitions
• CPC Class Codes: G06F 16/2452 Query translation
• CPC Class Codes: G06F 16/24526 Internal representations for queries
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A database including vertical tables useful for storing large numbers of objects having potentially thousands of attributes in, e.g., e-commerce applications. To support querying the vertical database using conventional SQL, a horizontal view over the underlying vertical tables is defined, and then queries are posed against the view. The queries are automatically transformed and executed against the vertical tables. If desired, the query results can be transformed back to a horizontal format. In this way, it appears to the user that a conventional horizontal data format is being used.

Claims

1. A computer system, comprising:

a general purpose computer coupled to a relational database system characterized by at least one ternary vertical table, the computer including logic for undertaking method acts including;

defining a logical horizontal view over the vertical table;

receiving at least one SQL query against the horizontal view;

transforming the query to render a transformed query; and

executing the transformed query against the vertical table to generate an output.

2. The system of claim 1, wherein the query is transformed using at least one operator.

3. The system of claim 2, wherein the operator receives at least one vertical table with an associated list of attribute names as input and outputs the logical horizontal table having column labels equal to the attribute names.

4. The system of claim 3, wherein the operator is a v2h operator.

5. The system of claim 3, wherein the vertical table includes object identifications with corresponding attribute names and attribute values, and the operator executes a left outer join of a projection of distinct object identifiers of the vertical table with a sequence of left outer joins of a set of projections of attribute values from the vertical table.

6. The system of claim 5, wherein the transforming act undertaken by the computer includes executing the operator on the vertical table to render a result and then undertaking a desired set operation on the result.

7. The system of claim 1, wherein the transforming act undertaken by the computer includes executing at least one projection based on the vertical table.

8. The system of claim 1, wherein the transforming act undertaken by the computer includes executing at least one selection from the vertical table.

9. The system of claim 1, wherein the transforming act undertaken by the computer includes executing at least one table join using the vertical table.

10. The of claim 1, wherein the transforming act undertaken by the computer includes executing at least one aggregation.

11. The system of claim 1, wherein the method acts undertaken by the computer include executing a horizontal to vertical operator against the output to transform the output to a vertical format.

12. The system of claim 1, wherein the transforming act undertaken by the computer includes executing at least one cross product based on the vertical table.

13. The system of claim 1, wherein the transforming act undertaken by the computer includes executing at least one union based on the vertical table.

14. The system of claim 1, wherein the transforming act undertaken by the computer includes executing at least one intersection based on the vertical table.