Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
2000-09-01
2003-09-30
Alam, Shamid Al (Department: 2172)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000, C707S793000, C707S793000, C707S793000
Reexamination Certificate
active
06629094
ABSTRACT:
FIELD OF THE INVENTION
The present invention relates to answering relational database queries. In particular, the present invention provides a system and method which relates to answering relational database queries by rewriting the query using hierarchical relationships between data.
BACKGROUND OF THE INVENTION
A database is a collection of data, usually pertaining to some reasonably well defined purpose. In order to say that data has been stored in a database, as opposed to just being stored, certain conditions are typically satisfied. The data typically has a known format which is defined by metadata. Metadata is generally understood as data about data. The data in a database is typically stored, retrieved, and modified by a special type of computer program.
This special type of computer program is typically a database management system. A database management system (DBMS) is a special computer program built to store data into, retrieve data from, and modify data stored in a database.
A relational database management system is a type of database management system that stores information in tables, in rows and columns of data, and conducts searches. In a relational database, the rows of a table typically represent records (collections of information about separate items) and the columns typically represent fields (particular attributes of a record). In conducting searches, a relational database may match information from a field in one table with information in a corresponding field of another table to produce a third table that combines requested data from both tables. For example, if one table contains the fields EMPLOYEE-ID, LAST-NAME, FIRST-NAME, DEPT-ID, SALARY and HIRE-DATE, and another table contains the fields DEPT-ID, DEPT-NAME, and LOCATION, a relational database can match the DEPT-ID fields in the two tables to find such information as the names of all employees working in a department in a specified location. A relational database may use matching values in two tables to relate information in one to information in the other.
When querying a relational data base, a data sublanguage known as SQL is commonly used. SQL is a data sublanguage used in querying, updating, and managing relational databases. A data sublanguage is one that may be used in association with another computer language for the specialized purpose of accessing data. Examples of languages which SQL can be associated with include COBOL, Fortran, and C. In most cases, a computer application has a mixture of requirements. It is usually helpful if the application writer can build portions of the application by using a language well suited for that portion of the application, and reverting to a specialized data language only for those parts of the application that require it. In this way, the data language is often viewed as a data sublanguage with respect to the primary programming language, or host language. Although SQL is not a programming language in the same sense as C or Pascal, SQL can either be used in formulating interactive queries or be embedded in an application as instructions for handling data. The SQL standard also contains components for defining, altering, controlling, and securing data. SQL is designed for both technical and nontechnical users.
There are many relational data sublanguages. Examples of these relational data sublanguages include QUEL from Relational Technology, Inc. (ASK/Ingres), and RDML from Digital Equipment Corporation. However, SQL has been formally standardized for access to relational databases and is widely implemented and used. There are many variations of SQL.
One example of such a variation is RISQL™ from Red Brick Systems.
In the relational database model, the basic unit of data is typically considered the relation. In SQL, the relation is represented by a table, with a primary difference between a relation and a table being that a relation typically does not have duplicate rows while a table can. A relation is typically made up of one or more attributes. These attributes are represented in column form in SQL. Each column has associated with it a data type. A data type is a characteristic of a piece of data, such as a character string, an integer, or a floating point number. Data is typically stored in a table in tuples, referred to in SQL as rows. As a result, a SQL table contains columns identifying the individual data elements, typically by name, and rows representing records of related data.
FIGS. 1A-1D
show examples of relational database tables with attributes written in columns and data related to those attributes written in rows. For example, in
FIG. 1A
, the attributes include product ID, product type, and bar code.
FIGS. 1B-1D
show similar examples of tables related to sales, time, and results.
Specific operations can be performed on these tables, rows, and on individual data elements. One such operation is selection, which identifies a specific row or rows in a table. Selection is typically done by specifying one or more predicates that are used to filter a table to identify rows for which the predicate is true. Predicates are typically found in the “where” clause of an SQL query. For example, a selection operation could request the selection of product ID equal to 1. In the example shown in
FIG. 1
, this selection would select the first row of
FIG. 1A
with product ID “1”.
Another operation in the relational database model is called the join. A join operation is a way of combining data from two tables which is typically based on the relationships between the data in those tables. For example, the result in
FIG. 1D
is an example of join operations between the “product” table of
FIG. 1A
, the “sales” table of
FIG. 1B
, and the “time” table of FIG.
1
C. The product table of
FIG. 1A
identifies the product type by product ID, while the sales table identifies amount in dollars related to the product ID and a time key. The product table and the sales table are joined based on the product ID column. The sales table also associates a time key with each row, while the time table identifies a day with each time key. Accordingly, the sales table and the time table are joined based on the values of the time key. Note that the result in
FIG. 1D
is only a subset of possible result columns. Only an example of columns which have been selected is shown in FIG.
1
D.
FIG. 2
shows relationships between a fact table and its dimension tables. Fact tables and dimension tables are a subset of base tables. Base tables are database tables, while fact tables are base tables which record events, such as a sales event. A sales event may include information related to a time, a store, and a product. The tables from which this related information is stored are the fact table's dimension tables. For example, a time table, a store table, and a product table are the sales table's dimension tables.
In the example shown in
FIG. 2
, the fact table is a sales table and the dimension tables are a time table, a product table, and a store table. For instance, the sales fact table may include the total sales at a particular store, on a particular date, within a particular time frame, of a particular product. Placing repeated information in a separate table is a type of normalization.
Normalization eliminates redundancy. This class table in
FIG. 2
is a result of normalization, and eliminates redundancy in the product table.
Another concept in relational database models is functional dependency. A functional dependency is a many-to-one relationship between columns of values in database tables. A functional dependency from column x to column y is a constraint that requires two rows to have the same value for the y column if they have the same value for the x column. A functional dependency may be explicitly declared by a user, such as the database administrator.
Further, relational database models provide for an aggregation query, which is a query that requires the summarization or consolidation of rows in database tables, typically using a set function, such as
Colby Latha S.
Cole Richard L.
Haslam Edward P.
Jazayeri Nasi
Johnson Galt
Alam Shamid Al
Davda Jawaki K.
International Business Machines - Corporation
Konrap, Raynes Victor & Mann LLP
LandOfFree
System and method for rewriting relational database queries does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with System and method for rewriting relational database queries, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and System and method for rewriting relational database queries will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-3047872