BCDM

1. 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)

2. 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)

3. BCDM Example. Relation Employee with Schema: (name,salary,T) “Andrea was earning 60K at valid times 10, 11, 12 Such 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 12 11 10 12 13 TT

4. BCDM Example. Relation Employee with Schema: (name,salary,T) “Andrea was earning 60K at valid times 10, 11, 12 Such 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 12 11 10 12 13 UC TT

5. BCDM Bitemporal relation: set of bitemporal tuples. Constraint: Value equivalent tuples are not allowed. (Bitemporal) DB: set of (bitemporal) relations

6. BCDM Semantics (another viewpoint) (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)}

7. 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)

8. BCDM Semantics of UC e.g., the DB’s clock thicks time 14

9. UC semantics

10. Deletion

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

12. Insertion

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

14. BCDM Algebraic Operators (Ex. Projection) πD(r)={z | ∃xr (z[D]=x[D]) ∧ ∀ yr (y[D]=z[D] ⇒ y[T] z[T]) ∧ ∀ tz[T] ∃yr (y[D]=z[D] ∧ ty[T])} • No value-equivalent tuple generated • (uniqueness of representation!) • Coalescing!

15. Example

16. Example

17. BCDM Algebraic Operators Properties BCDM algebraic operators are a consistent extension of SQL’s ones (reducibility and equivalence)

18. BCDM Reducibility ρtT rT ρtT (rT) opT op op(ρtT (rT)) ρtT = ρtT(opT (rT)) opT (rT)

19. BCDM Equivalence τt r τt(r) op opT τt op(r) τt(op(r))=opT (τt(r))

20. BCDM PROBLEM • Semantically clear but ….. inefficient • (not suitable for a “direct” implementation) • Not 1-NF • (2) UC • (at each thick of the clock, all current tuples should be updated!)

21. 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.

22. 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!

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

24. Semantics of TSQL2 representation

25. From BCDM to TSQL2

26. Property

27. Insertion and Deletion in TSQL2

28. An example of implementation: TSQL2(Snodgrass et al., 1995) • 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

29. Problem: no uniqueness of representation 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. BCDM SEMANTICS TSQL2 (a) TSQL2 (b)

30. Problem: no uniqueness of representation VT 4 3 2 1 10 11 12 13 TT TSQL2 implementation: “covering” rectangles

31. Problem: no uniqueness of representation VT 4 3 2 1 10 11 12 13 TT TSQL2 Representation (a)

32. Problem: no uniqueness of representation VT 4 3 2 1 10 11 12 13 TT TSQL2 Representation (b)

33. Problem: no uniqueness of representation VT 4 3 2 1 10 11 12 13 TT Other TSQL2 Representations!!

34. Problem: no uniqueness of representation Potentially, an enormous problem! e.g., Return all employees earning more than 50K for at most 3 consecutive time chronons ?

35. Problem: no uniqueness of representation One must grant that the temporal DB implementation respects its underlying semantics, independently of the representation Given two “semantically equivalent” temporal DBs, and given any sequence of operations, the results are always “semantic equivalent” op1, …, opk DB1 DB1’ op1, …, opk DB2 DB2’ Otherwise …..We cannot trust DB’s results!

36. Problem: no uniqueness of representation 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

37. Problem: no uniqueness of representation Solution. Step 2. Definition of manipulation and algebraic operators that preserve snapshot equivalence e.g., proofs given about TSQL2 (bitemporal) operators opBi rB1 opBi(rB1) snapshot equivalent snapshot equivalent opBi rB2 opBi(rB2)

38. Snapshot Equivalence Transaction-timeslice operator σBt1(r) = {z(n+1) | x r (z[A]=x[A]  z[Tv] = {t2 | (t1, t2)  x[T]}  z[Tv] } Valid-timeslice operator

39. Snapshot Equivalence

40. Uniqueness of representation (BCDM)

41. Timeslice operators in TSQL2

42. TSQL2 property