Changing table records in a database management system

Details Changing table records in a database management system Changing table records in a database management system

2001-07-03

2003-12-16

Coby, Frantz (Department: 2171)

Data processing: database and file management or data structures

Database design

Data structure types

C707S793000

Reexamination Certificate

active

06665654

ABSTRACT:

BACKGROUND OF THE INVENTION
1. Technical Field
The present invention relates to a method and system for updating and deleting records in tables generally in a database management system, and particularly in a business data warehouse, due to changes in source files which are logically associated with the tables.
2. Related Art
Updating and deleting records in a large table in a business data warehouse can be prohibitively time consuming. Thus, there is a need for a method and system for efficiently updating and deleting records in a large table in a business data warehouse.
SUMMARY OF THE INVENTION
The present invention provides a system for updating a tableset T of a family, in relation to
N source files denoted as S
1
, S
2
, . . . , S
N
, said N at least 1, said tableset T having M tables T
1
, T
2
, . . . , T
M
, said M at least 1, said N source files logically associated with said M tables, said family having a common family key K
F
across the M tables and across the N source files,
a fileset S
A
comprising files S
1A
, S
2A
, . . . , S
NA
respectively embodying S
1
, S
2
, . . . , S
N
at a time t
A
such that T
1
, T
2
, . . . , T
M
at the time t
A
accurately reflects S
A,
and
a fileset S
B
comprising files S
1B
, S
2B
, . . . , S
NB
respectively embodying S
1
, S
2
, . . . , S
N
at a time t
B
, said t
B
and t
A
being different times,
the system comprises:
a Delta program adapted to generate a dataset D of non-redundant family keyvalues embodying K
F
, each said non-redundant family keyvalue drawn from selected Update records of S
1B
relative to S
1A
, said selected Update records not having any redundant family keyvalues, for i=1, 2, . . . , N;
a Trigger program adapted, for i=1, 2, . . . , N: to step through all records of S
1B
, including to compare the family keyvalue K
FVR
within each record of S
1B
against the family keyvalues in the dataset D and to save in a file &Dgr;S
i
each said record of S
iB
for which K
FVR
equals one of the family keyvalues K
FV
in the dataset D;
a Bridge program adapted to generate files &Dgr;T
1
, &Dgr;T
2
, . . . , &Dgr;T
M
that includes the data in &Dgr;S
1
, &Dgr;S
2
, . . . , &Dgr;S
N
in a form that is compatible with the M tables T
1
, T
2
, . . . , T
M
, respectively; and
at least one Load program adapted to update the tables T
1
, T
2
, . . . , T
M
with the data in &Dgr;T
1
, &Dgr;T
2
, . . . , &Dgr;T
M
, respectively.
The present invention provides a method for updating a tableset T of a family, in relation to
N source files denoted as S
1
, S
2
, . . . , S
N
, said N at least 1, said tableset T having M tables T
1
, T
2
, . . . , T
M
, said M at least 1, said N source files logically associated with said M tables, said family having a common family key K
F
across the M tables and across the N source files,
a fileset S
A
comprising files S
1A
, S
2A
, . . . , S
NA
respectively embodying S
1
, S
2
, . . . , S
N
at a time t
A
such that T
1
, T
2
, . . . , T
M
at the time t
A
accurately reflects S
A
, and
a fileset S
B
comprising files S
1B
, S
2B
, . . . , S
NB
respectively embodying S
1
, S
2
, . . . , S
N
at a time t
B
, said t
B
and t
A
being different times,
the method comprises:
executing a Delta program, including generating a dataset D of non-redundant family keyvalues embodying K
F
, each said non-redundant family keyvalue drawn from selected Update records of S
iB
relative to S
iA
, said selected Update records not having any redundant family keyvalues, for i=1, 2, . . . , N;
executing a Trigger program, including for i=1, 2, . . . , N: stepping through all records of S
iB
, including comparing the family keyvalue K
FVR
within each record of S
iB
against the family keyvalues in the dataset D and saving in a file &Dgr;S
i
each said record of S
iB
for which K
FVR
equals one of the family keyvalues K
FV
in the dataset D;
executing a Bridge program, including generating files &Dgr;T
1
, &Dgr;T
2
, . . . , &Dgr;T
M
that includes the data in &Dgr;S
1
, &Dgr;S
2
, . . . , &Dgr;S
N
in a form that is compatible with the M tables T
1
, T
2
, . . . , T
M
, respectively; and
executing at least one Load program, including updating the tables T
1
, T
2
, . . . , T
M
with the data in &Dgr;T
1
, &Dgr;T
2
, . . . , &Dgr;T
M
, respectively.
The present invention provides a system for deleting deletion-targeted records in a tableset T of a family, in relation to
N source files denoted as S
1
, S
2
, . . . , S
N
, said N at least 1, said tableset T having M tables T
1
, T
2
, . . . , T
M
, said M at least 1, said N source files logically associated with said M tables, said family having a common family key K
F
across the M tables and across the N source files,
a fileset S
A
comprising files S
1A
, S
2A
, . . . , S
NA
respectively embodying S
1
, S
2
, . . . , S
N
at a time t
A
such that T
1
, T
2
, . . . , T
M
at the time t
A
accurately reflects S
A
, and
a fileset S
B
comprising files S
1B
, S
2B
, . . . , S
NB
respectively embodying S
1
, S
2
, . . . , S
N
at a time t
B
, said t
B
and t
A
being different times,
the system comprises:
a Delta program adapted to generate a dataset D
DEL
of non-redundant family keyvalues embodying K
F
, each said non-redundant family keyvalue drawn from selected Delete records of S
iB
relative to S
iA
, said selected Delete records not having any redundant family keyvalues, for i=1,2, . . . , N; and
at least one Load program adapted to delete the deletion-targeted records in the tables T
1
, T
2
, . . . , T
M
, each said deletion-targeted record having one of the non-redundant family keyvalues that exists in the dataset D
DEL
.
The present invention provides a method for deleting deletion-targeted records in a tableset T of a family, in relation to
N source files denoted as S
1
, S
2
, . . . , S
N
, said N at least 1, said tableset T having M tables T
1
, T
2
, . . . , T
M
, said M at least 1, said N source files logically associated with said M tables, said family having a common family key K
F
across the M tables and across the N source files,
a fileset S
A
comprising files S
1A
, S
2A
, . . . , S
NA
respectively embodying S
1
, S
2
, . . . , S
N
at a time t
A
such that T
1
, T
2
, . . . , T
M
at the time t
A
accurately reflects S
A
, and
a fileset S
B
comprising files S
1B
, S
2B
, . . . , S
NB
respectively embodying S
1
, S
2
, . . . , S
N
at a time t
B
, said t
B
and t
A
being different times,
the method comprises:
executing a Delta program, including generating a dataset D
DEL
of non-redundant family keyvalues embodying K
F
, each said non-redundant family keyvalue drawn from selected Delete records of S
iB
relative to S
iA
, said selected Delete records not having any redundant family keyvalues, for i=1, 2, . . . , N; and
executing at least one Load program, including deleting the deletion-targeted records in the tables T
1
, T
2
, . . . , T
M
, each said deletion-targeted record having one of the non-redundant family keyvalues that exists in the dataset D
DEL
.
The present invention provides a method and associated system for efficiently updating and deleting records in a large table in a database of a database management system (DBMS) such as in a business data warehouse of a DBMS.


REFERENCES:
patent: 5781911 (1998-07-01), Young et al.
patent: 5826253 (1998-10-01), Bredenberg
patent: 6032158 (2000-02-01), Mukhopadhyay et al.
patent: 6151602 (2000-11-01), Hejlsberg et al.
patent: 6167405 (2000-12-01), Rosensteel, Jr. et al.
patent: 6169985 (2001-01-01), Almgren et al.
Han-Chieh Wei and Elmasri, R (1999), Study and comparison of schema versioning and database conversion techniques for bi-temporal databases, pp. 88-98.*
Leo Mark and Nick Roussopoulos, (1990), Interoperability of multiple autonomous databases, pp. 267-293.*
Inderpal Singh Mumick, Dallan Qauss, Barinderpal Singh Mumick, (1997), Maintenance of Data cubes and summary tables in a warehouse, pp. 100-111.

Affiliated with

Also associated with

Rating

Changing table records in a database management system does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Changing table records in a database management system, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Changing table records in a database management system will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-3180081