Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
2000-12-18
2002-08-27
Corrielus, Jean M. (Department: 2172)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000, C707S793000, C707S793000, C707S793000, C707S793000, C707S793000, C711S110000
Reexamination Certificate
active
06442543
ABSTRACT:
FIELD OF THE INVENTION
The present invention is related to computer software and more specifically to computer database software.
BACKGROUND OF THE INVENTION
A collection of information is a database. Some databases logically organize data in a specific way. Data may be stored in tables, with each table containing one or more rows and one or more columns. Each column stores a particular type of data and the columns of each row are logically related. For example, an employee database may have information in each row related to a particular employee, with each row containing three columns. One column may hold social security numbers, another column may hold names and the third column may hold the grade of the employee. Thus, each row contains each of the three pieces of information for each employee.
In some cases, databases use multiple tables. For example, to track personnel records in a company, the table of employee information described above may be used in conjunction with a different table containing information about available positions. A third table relates the first two tables, describing the position each employee holds. For example, a company may have two managers and five other employees having the position of line employee. There are a total of seven employees in the employee table, and two positions in the position table: manager and line employee. A third table can relate each of the seven employees to one of the two available positions.
As information stored in the database changes, some databases replace the outdated information with the most up-to-date information. In such databases, outdated information may be lost forever once replaced. For example, if an employee is promoted to manager, the third table that relates employees to positions can be changed to show the employee in the new position, replacing any evidence in the table that the employee was ever a line employee.
Other databases are temporal databases, which can retain outdated information along with the up-to-date information. The outdated information is stored in a different row from the current information. For example, the table that relates employees to positions may have seven rows before the promotion, one for each employee. When the promotion occurs, a new eighth row is added relating the employee to the new position while retaining the row that also relates the employee to his or her old position.
The information in one or more tables of a temporal database has one or more date columns to identify the period the information in a particular row is or was valid. For example, the table relating employees to positions may have two date columns, a start date and an stop date for the row. When the employee is promoted to manager, the row relating the employee to a line-employee position has the start date as the hire date of the employee. The day he or she is promoted is inserted into the stop date, and a new row is added in the table relating employees to positions which contains an identifier of the employee and an identifier of the manager position. In addition, the start date contains the date of the employees first day as a manager. Because the table relating employees to positions now has two rows for the promoted employee, the table may be used to retrieve historical information, such as how many line employees the company had before the date of the employee's promotion to manager.
Information may be retrieved from a database using a query. A query is any command that retrieves information from a database. Some commercially available database products such as the Oracle8 database product commercially available from Oracle Corporation of Redwood Shores, Calif. interpret queries that conform to a standard query language called Structured Query Language, or SQL. Each database product may also allow certain variations from the SQL standard. To retrieve information from a database that implements SQL queries, a user of the database provides a query containing a “select” command and a description of the data the user desires to retrieve. The database product retrieves from the tables specified in the query the specified data for the user.
Queries may also be used to change information in a database. The user can provide a query containing an update command, a criteria for the data to be updated and a description of how the data to be updated is to be changed. The database program retrieves the specified data, changes it, and stores it back into the database. Other queries can insert or delete data in one or more tables.
SQL commands to change a database make data manipulation simpler for the user than it might otherwise be. However, SQL commands to change data in a temporal database can be much more complex to structure than queries for non-temporal databases because the data in temporal databases changes over time. A conventional SQL update command may not work properly because a conventional SQL update command assumes the data in all tables is static over time. However, commands to change a temporal database ordinarily need to take the time-varying nature of the data into account.
To update with a new value or values in a set of rows in a table that meet a specified criteria that are applicable during a particular period may not be possible using a conventional SQL command for several reasons. A single row may only meet the specified criteria for part of the time the row is applicable. The particular period may start or stop between the start date and stop date of one or more rows, causing a portion of any row to be within the particular period and another portion of the same row to be outside of the particular period. The new value specified may be undefined over some portion of the time one or more rows are applicable.
Although performing an update for a non-temporal database can often be performed using a single SQL update command, updating data in temporal databases is more complex for the reasons described above. It is not possible to provide a single SQL update command that appropriately deals with the special cases above. The task is sufficiently complex that a typical end user cannot perform it using even a few SQL commands. In many cases, a programmer is required to create an SQL program that can handle the complexity required to perform the update properly for the reasons described above. What is needed is a method and apparatus that can accept for a temporal database a command similar to an SQL update command for a non-temporal database, and cause the temporal database to be updated as specified in the command.
SUMMARY OF THE INVENTION
A method and apparatus temporally updates one or more tables according to a command that can be similar to a conventional SQL “UPDATE” command, that specifies a period of validity, specifies criteria for the table to be updated and specifies the update values with which to update the table. The command is intercepted by the method and apparatus and one or more queries and SQL program elements are substituted in place of the command. The queries and SQL program elements will, when submitted to a conventional database product, perform the command. The user need only generate a single SQL command, and is therefore freed from dealing with the complexity of converting a temporal command into the series of conventional queries and SQL program elements that would otherwise be required. The queries will identify periods of constant data over the period of applicability in either the table to be updated or the tables used to calculate the values that will be used to update the table, or both. The SQL program elements will use the result of the queries to potentially split each row in the table to be updated that is within the period of applicability into multiple rows if the row in the table to be updated spans more than one of the constant periods. Because during the effective dates of each of the resulting rows of the table to be changed, the values of the table to be changed and the values of any tables used to calculate changed values will be stati
Bair John
Snodgrass Richard T
Alam Shahid
Amazon.com Inc.
Corrielus Jean M.
Perkins Coie LLP
LandOfFree
Method and apparatus for changing temporal database information does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with Method and apparatus for changing temporal database information, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Method and apparatus for changing temporal database information will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-2893058