bcdm

BCDM

Temporal Domains

- Time is linear and totally ordered- Chronons are the basic time unit- Time domains are isomorphic to subsets of the domain

of Natural numbers

DVT = {t1,t2, …, tk} (valid time) DTT = {t’1,t’2, …, t’h} {UC} (transaction time)DTT DVT (bitemporal chronons)

BCDM

Data

Attribute names: DA={A1, A2, …, An}Attribute domains DD={D1, D2, …, Dn}

Schema of a bitemporal relation:R = Ai1, Ai2, …, Aij T

Domain of a bitemporal relation:Di1 Di2 … Dij DTT DVT

Tuple of a relation r(R):x = (a1, a2, …, aj | tB)

BCDM

Example.Relation Employee with Schema: (name,salary,T)

“Andrea was earning 60K at valid times 10, 11, 12Such a tuple has been inserted into Employee at time 12, and is current now (say now=13)”

(Andrea, 60k | {(12,10), (12,11), (12,12),(13,10), (13,11), (13,12), ……})

VT

TT

10

12

12 13

11

BCDM

Example.Relation Employee with Schema: (name,salary,T)

“Andrea was earning 60K at valid times 10, 11, 12Such a tuple has been inserted into Employee at time 12, and is current now (say now=13)”

(Andrea, 60k | {(12,10), (12,11), (12,12),(13,10), (13,11), (13,12), (UC,10), (UC,11), (UC,12)})

VT

TT

10

12

12 13

11

UC

BCDM

Bitemporal relation: set of bitemporal tuples. Constraint: Value equivalent tuples are not allowed.

(Bitemporal) DB: set of (bitemporal) relations

BCDM

Semantics (another viewpoint)

Name Salary T

Andrea 60K {(12,10), (12,11), (12,12),(13,10), (13,11), (13,12), (UC,10), (UC,11), (UC,12)}

John 50K {(12,12),(12, 13)}

(12,10) {Employee(Andrea,60K)}(12,11) {Employee(Andrea, 60K)}(12,12) {Employee(Andrea, 60K), Employee(John,50K)}(12,13) {Employee(John,50K)}(13,10) {Employee(Andrea,60K)}(13,11) {Employee(Andrea, 60K)}……..(UC,12) {Employee(Andrea, 60K)}

BCDM

PROPERTIES

Consistent extension (of “classical” SQL DB)A temporal DB is a set of “classical” DBs, one for each bitemporal chronon

Uniqueness of representation(from the constraint about value equivalent tuples)

BCDM

Semantics of UCe.g., the DB’s clock thicks time 14

Name Salary T

Andrea 60K {(12,10), (12,11), (12,12),(13,10), (13,11),(13,12), (UC,10), (UC,11), (UC,12)}

John 50K {(12,12),(12, 13)}

Name Salary T

Andrea 60K {(12,10), (12,11), (12,12),(13,10), (13,11), (13,12), (14,10), (14,11), (14,12), (UC,10), (UC,11), (UC,12)}

John 50K {(12,12),(12, 13)}

UC semantics

Deletion

BCDM

deletion (e.g., at time 15)delete(Employee, (Andrea,60K))

Name Salary T

Andrea 60K {(12,10), (12,11), (12,12),(13,10), (13,11), (13,12), (14,10), (14,11), (14,12), (UC,10), (UC,11), (UC,12)}

John 50K {(12,12),(12, 13)}

Name Salary T

Andrea 60K {(12,10), (12,11), (12,12),(13,10), (13,11), (13,12), (14,10), (14,11), (14,12), (UC,10), (UC,11), (UC,12)}

John 50K {(12,12),(12, 13)}

Insertion

BCDM

insertion (e.g., at time 16)insert(Employee, (Andrea,60K|{12,13}))insert(Employee, (Mary,70K|{16}))

Name Salary T

Andrea 60K {(12,10), (12,11), (12,12),(13,10), (13,11), (13,12), (14,10), (14,11), (14,12), (UC,10), (UC,11), (UC,12)}

John 50K {(12,12),(12, 13)}

Name Salary T

Andrea 60K {(12,10), (12,11), (12,12),(13,10), (13,11), (13,12), (14,10), (14,11), (14,12), (16,12),(16,13),(UC,12),(UC,13) }

John 50K {(12,12),(12, 13)}

Mary 70K {(16,16),(UC,16)}

BCDM

πD(r)={z | ∃xr (z[D]=x[D]) ∧ ∀ yr (y[D]=z[D] ⇒ y[T] z[T]) ∧ ∀ tz[T] ∃yr (y[D]=z[D] ∧ ty[T])}

Algebraic Operators(Ex. Projection)

- No value-equivalent tuple generated(uniqueness of representation!)

- Coalescing!

Example

BCDM

BCDM algebraic operators are a consistent extension of SQL’s ones(reducibility and equivalence)

Algebraic OperatorsProperties

BCDM

Reducibility

rT ρtT (rT)

ρtT

opT (rT)

opTop

op(ρtT (rT))

ρtT

ρtT(opT (rT))

=

BCDM

Equivalence

rτt τt(r)

opTop

op(r)τt

τt(op(r))=opT (τt(r))

BCDM

PROBLEM

Semantically clear but ….. inefficient (not suitable for a “direct” implementation)

(1) Not 1-NF

(2) UC (at each thick of the clock, all current tuples

should be updated!)

Task

An efficient implementation must be devised

The implementation must be proven to respect the semantics. Core issue here: efficient (1-NF) implementations hardly grant uniqueness of representation.

An example of implementation: TSQL2(Snodgrass et al., 1995)

Temporal attribute T four temporal attributes (TTS, TTE, VTS, VTE)

Attribute value: a timestamp or UC

Bitemporal tuple: A1,….An| TTS, TTE, VTS, VTE

Bitemporal relation: set of bitemporal tuples

Notice: value-equivalent tuples are allowed!


Name Salary T

Andrea 60K {(12,10), (12,11), (12,12),(13,10), (13,11), (13,12), (UC,10), (UC,11), (UC,12)}

John 50K {(12,12),(12, 13)}

Name Salary TTS TTE VTS VTE

Andrea 60K 12 UC 10 12

John 50K 12 12 12 13

SEMANTICS

BCDM

TSQL2

Semantics of TSQL2 representation

From BCDM to TSQL2

Property

Insertion and Deletion in TSQL2


Efficient implementation (data model):- 1-NF- UC managed efficiently- clear semantics (mapping onto BCDM)

BUT

to get efficiency, we loose the uniqueness of representation property

Problem: no uniqueness of representation

Name Salary T

Andrea 60K {(10,2), (10,3), (11, 2),(11,3), (12,1), (12,2),(12,3),(12,4),(13,1),(13,2),(13,3),(13,4)}


Andrea 60K 10 11 2 3






BCDM

SEMANTICSTSQL2 (a) TSQL2 (b)

Example. At time 10, the fact that Andrea earned 60K from 2 to 3 inserted in Employee. At time 12, such a tuple is updated: Andrea earned 60K from 1 to 4. At time 13, the tuple is (logically) deleted.


VT

TT

12

12 13

3

4

10 11

TSQL2 implementation: “covering” rectangles


VT

TT

12

12 13

3

4

10 11

TSQL2 Representation (a)


Andrea 60K 10 11 2 3



VT

TT

12

12 13

3

4

10 11

TSQL2 Representation (b)






VT

TT

12

12 13

3

4

10 11

Other TSQL2 Representations!!



Andrea 60K 10 11 2 3






Potentially, an enormous problem!

e.g., Return all employees earning more than 50K for at most 3 consecutive time chronons

Name TTS TTE VTS VTE

Andrea 12 UC 1 4?


One must grant that the temporal DB implementation respects its underlying semantics, independently of the representation

DB1

DB2

op1, …, opkDB1’

DB2’op1, …, opk

Given two “semantically equivalent” temporal DBs, and given any sequence of operations, the results are always “semantic equivalent”

Otherwise …..We cannot trust DB’s results!


Solution. Step 1. Formal definition of “semantic equivalence”

Snapshot equivalence:Informally: two relations (Databases) are snapshot equivalent if they are identical at each bitemporal chronon


Solution. Step 2. Definition of manipulation and algebraic operators that preserve snapshot equivalence

e.g., proofs given about TSQL2 (bitemporal) operators

rB1

rB2

opBi

opBi

opBi(rB

1)

opBi(rB

2)

snapshotequivalent

snapshotequivalent

Snapshot Equivalence

Valid-timeslice operator

σBt1(r) = {z(n+1) | x r (z[A]=x[A]

z[Tv] = {t2 | (t1, t2) x[T]} z[Tv] }

Transaction-timeslice operator

Snapshot Equivalence

Uniqueness of representation (BCDM)

Timeslice operators in TSQL2

TSQL2 property

bcdm

Documents

nf2 uc

bitemporal tuples

relation employee

valid time dtt

basic time unit time

bitemporal db

bcdmtemporal domains

relation rr