3 IntroductionTemporal database: a database that contains historical data as well as current data.– Note: ‘historical’ is a misleading term – temporal databases may contain data regarding the future as well as the past.Extreme case: data is only inserted, never deleted from a temporal database (eg. vehicle position data in the ‘project’).So far, we have studied the other extreme - i.e. ‘snapshot’ databases.Distinguishing feature: the element of time.

4 IntroductionTemporal data: encoded representation of timestamped facts.– Each tuple must include at least one timestamp.Problem:What about queries that produce results that are not temporal? i.e. result of query is outside the domain of (temporal) database.eg. Get names of all people who have supplied something in the past.Redefine temporal database: database that includes, but is not limited to, temporal data.

5 MotivationQueries on time-varying data are difficult to express in SQL.Temporal databases provide build-in support for recording and querying such information.It is possible to use SQL to evaluate these queries, but performance is poor.

6 Motivation Most applications manage temporal data.If a temporal database is used for such data:Schemas, including integrity constraints are simpler.Queries are simplerApplication code is less complexeasier to understandeasier to produceeasier to maintain

8 Intervals An interval [s,e] is a set of times from time s to time e.Does interval [s,e] represent an infinite set?Assumption: Timeline is a finite sequence of discrete, indivisible time quanta.Time Quanta: smallest unit of time system can represent.Timepoints/point: time unit considered indivisible for our purpose.An interval is treated as a single type, not as pair of separate values.Interval can be open/closed w.r.t. start point/end point.eg. [d04,d10],[d04,d11),(d03,d10],(d03,d11)all represent the sequence of days from day4 to day10 inclusive.

12 Aggregate Operators EXPAND(X):Where X is a set. The output is also a set.Used to generate time quantum intervals.– The expanded form of X is the set of all intervals of the form [p,p] where p is a time point in some interval in X.e.g.:X1 = { [d01,d01],[d03,d05],[d04,d06] }X2 = { [d01,dp1],[d03,d04],[d05,d05],[d05,d06] }X3 = { [d01,d01],[d03,d03],[d04,d04],[d05,d05],[d06,d06] }Then EXPAND(X1) = EXPAND(X2) = X3

13 Aggregate Operators COLLAPSE(X):The collapsed form of X is the set Y of intervals of the same type such that(a) X & Y have the same unfolded form.(b) no two distinct members i1 and i2 of Y are such that (i1 MERGES i2) is true.e.g.:X1 = { [d01,d01],[d03,d05],[d04,d06] }X2 = { [d01,d01],[d03,d04],[d05,d05],[d05,d06] }X3 = { [d01,d01],[d03,d06] }Then COLLAPSE (X1) = COLLAPSE (X2) = X3

15 Example Given two temporal relations:S: Supplier S# was under contractduring the interval DuringSP: Supplier S# was able to supplypart P# during the interval DuringSPS#P#DuringS1P1[d04,d10]P7[d05,d10]P3[d09,d10]P5[d06,d10]S2[d02,d04]P9[d03,d03][d08,d10]S3S4P2[d06,d09][d04,d08]SS#DuringS1[d04,d10]S2[d02,d04][d07,d10]S3[d03,d10]S4S5[d02,d10]

16 Example 1Active supplier intervals: Get S#-DURING pairs for suppliers who have been able to supply at least one part during at least one interval of time, where DURING designates such an interval.PACK SP {S#,DURING} ON DURINGSPS#P#DuringS1P1[d04,d10]P7[d05,d10]P3[d09,d10]P5[d06,d10]S2[d02,d04]P9[d03,d03][d08,d10]S3S4P2[d06,d09][d04,d08]RESULTS#DuringS1[d04,d10]S2[d02,d04][d08,d10]S3S4

17 Example 2Inactive (passive) supplier intervals: Get S#-DURING pairs for suppliers who have been unable to supply any parts at all during at least one interval of time, where DURING designates such an interval.PACK( ( UNPACK S {S#,DURING} ON DURING )MINUS( UNPACK SP {S#,DURING} ON DURING ) )ON DURINGShorthand: U_MINUSRESULTS#DuringS2[d07,d07]S3[d03,d07]S5[d02,d10]

20 Persistent B-treeIn some applications we are interested in being able to access previous versions of data structureDatabasesGeometric data structuresPartial persistence:Update the current version (getting a new version)Query all versionsWe would like to have partial persistent B-tree withO(N/B) space – N is number of updates performedupdatequery in any version

21 Persistent B-tree East way to make B-tree partial persistentCopy structure at each operationMaintain “version-access” structure (B-tree)Good query in any version, butO(N/B) I/O updateO(N2/B) spaceupdatei+3ii+2i+1ii+1i+2

22 Persistent B-treeIdea: Elements augmented with “existence interval” and stored in one structurePersistent B-tree with parameter b:Directed graphNodes contain elements augmented with existence intervalAt any time t, nodes with elements alive at time t form B-tree with leaf and branching parameter b (i.e., each node/leaf has at least b/4 and at most b children/keys in them)B-tree with leaf and branching parameter b on indegree 0 nodesIf b=B: Query at any time t in I/Os

23 Persistent B-tree: UpdatesUpdates performed as in B-treeTo obtain linear space we maintain new-node invariant:New node contains between and alive elements and no dead elements

28 Persistent B-tree AnalysisUpdate:Search and “rebalance” on one root-leaf pathSpace: O(N/B)At least updates in leaf in existence intervalWhen leaf u diesAt most two other nodes are createdAt most one block over/underflow one level up (in parent(u))During N updates we create:leavesnodes i levels up blocks

30 Interval Management Problem:Maintain N intervals with unique endpoints dynamically such that stabbing query with point x can be answered efficientlyAs in (one-dimensional) B-tree case we are interested inspaceupdatequeryx

31 Interval Management: Static SolutionSweep from left to right maintaining persistent B-treeInsert interval when left endpoint is reachedDelete interval when right endpoint is reachedQuery x answered by reporting all intervals in B-tree at “time” xspacequeryconstruction using buffer techniqueDynamic with insert bound using logarithmic methodx

33 External Logarithmic Method IdeaDecrease number of subsets Vito logB N to get queryProblem: Since there are not enough elements in V0,V1, … Vi-1 to build ViSolution: We allow Vi to contain any number of elements  BiInsert: Find first Di such that and construct newDi from elements in V0,V1, … ViWe move elementsIf Di constructed in O((|Vi|/B)logB |Vi|) = O(Bi-1logB N) I/Os every moved element charged O(logB N) I/OsElement moved O(logB N) times  amortized

35 Planar Point Location Static problem:Store planar subdivision with N segments on disk such that region containing query point q can be found I/O-efficientlyWe concentrate on vertical ray shooting querySegments can store regions it boundsSegments do not have to form subdivisionDynamic problem:Insert/delete segments(we will not discuss this)q

36 Static SolutionVertical line imposes above-below order on intersected segmentsSweep from left to right maintainingpersistent B-tree on above-below orderLeft endpoint: Insert segmentRight endpoint: Delete segmentQuery q answered by successor query on B-tree at time qxspacequeryq

37 Static Solution Note: Not all segments comparable!qNote: Not all segments comparable!Have to be careful about what we compareProblem: Routing elements in internal nodes of leaf oriented B-treesLuckily we can modify persistent B-tree to use regular (live) elements as routing elementsHowever, buffer technique construction cannot be usedOnly I/O construction algorithmCannot be made dynamic using logarithmic method