[AccessD] Temporal database

Hi all

I just noticed that perhaps _the_ prime acteur in this field, Richard
T. Snodgrass, offers one of his books free to download:

"Developing Time-Oriented Database Applications in SQL"
including the cd-rom with code for IBM DB2 Universal Database, Ingres,
Informix-Universal Server, Microsoft Access, Microsoft SQL Server,
Sybase
SQLServer, Oracle8 Server, and UniSQL. 

http://www.cs.arizona.edu/~rts/publications.html

Happy reading!

/gustav

>>> Gustav at cactus.dk 22-11-2006 16:45 >>>
Hi Arthur and Shamil

"We need to be able to roll back the database to any given PIT without
actually having to do a restore. We just want to specify a date (Jan 1
2004) and without changing a single line of code, run the reports."
>>> 
> You mean "virtual rollback" of course? IOW a "point in time aware"
view/SP/UDF?

Note that to implement a temporal database in full (I guess we will
loose JC here), not two but _four_ time fields must exist:

<quote>

In the literature, two time lines of interest have been mentioned,
transaction time and valid time. The valid time line represents when a
fact is valid in modelled world (i.e. when it was believed) and the
transaction time line represents when a transaction was performed. A
bitemporal database is a combination of valid time and transaction
time
databases where these two time lines are considered to be orthogonal.
(Snodgrass & Ahn 1986)

</quote>

Again, accounting can display the beauty of this: Consider writing an
invoice; you do this, of course, "today", while the invoice date may
be
either past, present, or future. Later, selecting on transaction dates
will reveal wether this invoice was created or not; selecting on valid
date will show wether this invoice was issued or not.

You may put it in another perspective: Transaction dates record by
themselves an audit trail which allows you to, at any time, to perform
a
dynamic virtual rollback to any previous time, while the valid date
will
show you the (approved) state of the data at that time. Specifically,
this allows you to correct previous recordings (say, a wrongly typed
invoice number of a supplier's invoice), or record a future change of
a
customer's address and - until he moves - to issue invoices correctly
addressed to his present address.

/gustav

>>> artful at rogers.com 22-11-2006 15:10:35 >>>
My personal fave in this category is the Kimball Group. Specifically,
for MS SQL, I recommend "The Microsoft Data Warehouse Toolkit" by Joy
Mundy and Warren Thornthwaite. There is extensive discussion therein
about SCDs (slowly changing dimensions).

I will check out your sources, too.

A.

----- Original Message ----
From: Gustav Brock <Gustav at cactus.dk>
To: accessd at databaseadvisors.com 
Sent: Wednesday, November 22, 2006 8:41:01 AM
Subject: [AccessD] Temporal database (was: Stored Procedure not
producing results)

Hi Arthur

I don't recall you have written about PITA here, but what you describe
is generally known as a temporal database.
This is well-known and has been dealt with by some great capacities in
the database world, Michael Boehlen, Christian Jensen, Richard
Snodgrass
and Andreas Steiner.

http://www.cs.arizona.edu/people/rts/timecenter/timecenter.html 

A brief explanation and introduction can be found here:

http://www.timeconsult.com/TemporalData/TemporalData.html 

Note the link to TimeDB, a Java implementing of "A Bitemporal
Relational DBMS" for Oracle 10g

Also, look up my previous post(ings) on these:

http://databaseadvisors.com/pipermail/accessd/2005-May/034503.html 
http://databaseadvisors.com/pipermail/accessd/2005-May/034455.html 

This is a very exciting area. For some applications it won't even
require that much more data storage (accounting). 
A simplified approach is a write-only database (which means write and
read but neither delete nor update) which both Caché and MySQL offers.

Now, wouldn't it be nice if only TSQL2 could be implemented in Access:

http://www.cs.arizona.edu/people/rts/tsql2.html 

/gustav