Temporal Aggregates and Universal Quantification in Standard SQL

1. Temporal Database Paper Reading Report 資工所 在職碩一 P96922001 莊浚銘

2. Reporting Contents • Paper Subject & Author • Study Motivation • Paper Contents • Conclusion

3. Paper Subject & Author • Subject：Temporal Aggregates and Temporal Universal Quantification in Standard SQL • Published Date： 2006/06 • Author：Esteban Zimanyi, Dept. of Computer & Network Engineering, Universite Libre de Bruxelles • Web Site： http://www.sigmod.org/sigmod/record/issues/0606/p16-article-zimanyi.pdf

4. Study Motivation • To understand : • How a SQL database is extended with temporal functionality support • How temporal operations are implemented in standard SQL • What problems can be solved by temporal operations

5. Paper Contents • No consensus on TSQL standard : Several proposed temporal extensions of SQL, such as “TSQL2” and “SQL/Temporal”, have NOT reached acceptance in the standardization committees. • Standard SQL dominant : Nowadays, database practitioners MUST STILL use standard SQL for manipulating time-varying information. Querying and updating time-varying data using standard SQL is STILL a challenging task.

6. Paper Contents (Cont.) • Little temporal support : Current Database Management Systems, like SQL, provide little support for dealing with time-varying data. They only provide standard data types for encoding dates or timestamps. • What this paper aims for : With regard tousing standard SQL to manipulate temporal data,there has been studies showing how to define temporal join, projection and difference, but NOT how to deal with temporal aggregates as well as temporal universal quantifiers, this paper is devoted to the later issue.

7. Non-temporal temporal temporal temporal temporal Paper Contents (Cont.) • Temporal DB Schema : Temporal database design in standard SQL All circled columns are with the same data-type named “date” !That means it’s hard to distinguish temporal tables simply by DB data-type.

8. A closed-open representationis used! 即 [ d1 , d2 ) Paper Contents (Cont.) • Temporal DB Schema(Cont.) : Note that a special date ‘3000-01-01’ is used to denote currently-valid rows ! Also, data redundancy is inevitable before we coalesce it.

9. ? Paper Contents (Cont.) • Temporal Join : A temporal join is needed when the tables to be combined are temporal. • Question1:How do the salary and the affiliation of employees affect each other? We need to temporal join both tables because they are all temporal!

10. Should be FIVE? Paper Contents (Cont.) • Solution for Question1:To express a temporal join in SQL, we need FOUR select statements and complex inequality predicates verifying that the validity periods of the rows to be combined intersect.

11. via Temporal Join S DURING OVERLAP OVERLAP DURING A S A S A S A S (3) UNION instead? (1) “=” Missing? (2) EQUIVALENT Missing? A Paper Contents (Cont.) To get all intersected (即inter運算) intervals

12. via Temporal Join Incorrect ? for … Paper Contents (Cont.) To get all intersected (即inter運算) intervals

13. via Temporal Join Get the intersection of the validity periods by calling predefined SQL functions! Ensure the two validity periods overlap Paper Contents (Cont.) To get all intersected (即inter運算) intervals • Another solution for Question1:Note that temporal join can be written in a single statement, either using a Case statement or using functions.

14. DNumber is projected out to get employees’ total service life span! Both can be coalesced! Paper Contents (Cont.) • Temporal Coalescing : A complex and costly operation in SQL, similar to temporal Folding,Nesting and Packing proposed by other researchers.

15. 020101 030601 030801 000101 Gaps = Tuples’ date differences before performing joining The Gaps are all 0 in this case, so the 3rd tuple is what we want. But how to erase unwanted tuples? 1st Filtering 2nd Filtering Paper Contents (Cont.) • Temporal Coalescing Realization : Can be implemented in SQL as follows:

16. Gap>0! Gap 1st “Not Exists” Filtering (to erase tuples containing the Gap period!) 2nd “Not Exists” Filtering (to erase tuples following or preceding the original ones!) Final Result ! 020101 020601 030601 030801 790101 Paper Contents (Cont.) • Temporal Coalescing Realization(Cont.) : Try the SQL

17. Paper Contents (Cont.) • Temporal Aggregation : We need a three-step process to implement temporal aggregation. That process is : • Identifying the periods of time in which all values are constant. • Computing the aggregation over these periods. • Coalescing the result.

18. The non-temporal way time The temporal way Employee1 Employee2 Employee3 Salary Employee • Identifying the periods of time in which all values are constant. • Computing the aggregation over these periods. • Coalescing the result. Paper Contents (Cont.) • Temporal Aggregation(Cont.) : Suppose we want to find maximum salary among employees. Then the computing way will depend on whether the target table is temporal or not.

19. 1.Create a view named “SalChanges” So that we can gather the days in which a salary change may occur ! 2.Create a view named “SalPeriods” So that we can construct all “atomic” periods for later processing ! Note that there is no gap between any two of these day periods. • Identifying the periods of time in which all values are constant. • Computing the aggregation over these periods. • Coalescing the result. Paper Contents (Cont.) • Temporal Aggregation(Cont.) : Implementation of the temporal way to solve the “Maximum Salary Question”.

20. 3.Create a view named “TempMax” from step (i) So that we can get maximum salary for every atomic period ! Like this… So that we can get employee count for every atomic period ! Like this… Note that there could be a gap between any two of these periods !(No people on jobs) 4.Create a view named “TempCount” Given a zero count when no salary found ! • Identifying the periods of time in which all values are constant. • Computing the aggregation over these periods. • Coalescing the result. Paper Contents (Cont.) • Temporal Aggregation(Cont.) : Implementation of the temporal way to solve the “Maximum Salary Question”.

21. To get maximum salary for each period, we can coalesce the view “TempMax”. Using the SQL in previous section to Coalesce ! Final result ! • Identifying the periods of time in which all values are constant. • Computing the aggregation over these periods. • Coalescing the result. Paper Contents (Cont.) • Temporal Aggregation(Cont.) : Implementation of the temporal way to solve the “Maximum Salary Question”.

22. Divided into 2 layers: Affiliation & Salary Evolve Time • Identifying the periods of time in which all values are constant. • Computing the aggregation over these periods. • Coalescing the result. Paper Contents (Cont.) • Temporal Aggregation(Cont.) : Similar way to solve the “Maximum Salary by Department Question”.

23. 1.Create a view named “Aff_Sal” So that we can get a temporal join of affiliation and salary, which yields the days in which a change of max salary of a department may occur ! So that we can gather the days by department , in which a salary change may occur ! Intersected periods only 2.Create a view named “SalChangesDep” from step 1 • Identifying the periods of time in which all values are constant. • Computing the aggregation over these periods. • Coalescing the result. Paper Contents (Cont.) • Temporal Aggregation(Cont.) : Similar way to solve the “Maximum Salary by Department Question”.

24. 3.Create a view named “SalPeriodsDep” from step 2 So that we can construct all “atomic” periods by department for later processing ! Note that there is no gap between any two of these day periods. Like this.. • Identifying the periods of time in which all values are constant. • Computing the aggregation over these periods. • Coalescing the result. Paper Contents (Cont.) • Temporal Aggregation(Cont.) : Similar way to solve the “Maximum Salary by Department Question”.

25. 4.Create a view named “TempMaxDep” 5. Query the view Final Result ! from step 1 from step 3 • Identifying the periods of time in which all values are constant. • Computing the aggregation over these periods. • Coalescing the result. Paper Contents (Cont.) • Temporal Aggregation(Cont.) : Similar way to solve the “Maximum Salary by Department Question”.

26. The non-temporal version of above query That’s, finding supermen! Recall that we’ve seen the “Not Exists,Not Exits” pattern before. It’s mainly used to erase tuples containing any “GAP” period. Based on the DB schema at the top-right corner,we can construct an E-R model as follows: 1 1 n 1 1 Employee has Affiliation has Department has n 1 1 has has Controls n n n n 1 1 Salary WorksOn has Project has Key table Key table Key table :inferred entity Paper Contents (Cont.) • Temporal Universal Quantifier : Needed in many usual queries, such as “List the employees that work in all projects controlled by the department to which they are affiliated”.

27. 1 N? n 1 1 Employee has Affiliation has Department has n 1 1 has has Controls n n n n 1 1 Salary WorksOn has Project has Temporal ? Temporal ? Paper Contents (Cont.) Temporal ? • Temporal Universal Quantifier(Cont.) : Considering whether the tables WorksOn,Affiliation, and Controls are temporal or not. Four cases arise when doing previous query.

28. The view below: Pattern found again! 1 Superman found by using.. 1 n 1 1 Employee has Affiliation has Department has n 1 1 has has Controls n n n n 1 1 Salary WorksOn has Project has Paper Contents (Cont.) • Temporal Universal Quantifier(Cont.) :

29. 1 1 n 1 1 Employee has Affiliation has Department has n 1 1 construct all “atomic” periods for later processing ! has has Controls n n n n 1 1 Salary WorksOn has Project has Paper Contents (Cont.) • Temporal Universal Quantifier(Cont.) :

30. 1 1 n 1 Pattern found again! 1 Employee has Affiliation has Department has n 1 1 has has Controls n n n n 1 1 Salary WorksOn has Project has Paper Contents (Cont.) • Temporal Universal Quantifier(Cont.) :

31. Temporal join 1 n n 1 Project independent ! 1 Employee has Affiliation has Department has n 1 1 has has Controls n n n n 1 1 Salary WorksOn has Project has Paper Contents (Cont.) • Temporal Universal Quantifier(Cont.) :

32. 1 n n 1 Project independent ! 1 Employee has Affiliation has Department has n 1 1 has has Controls n n n n 1 1 Salary WorksOn has Project has Paper Contents (Cont.) • Temporal Universal Quantifier(Cont.) :

33. 1st Temporal Join 2nd Temporal Join 1 n n 1 1 Employee has Affiliation has Department has n 1 1 has has Controls n n n n 1 1 Salary WorksOn has Project has Paper Contents (Cont.) • Temporal Universal Quantifier(Cont.) :

34. 1 n n 1 1 Employee has Affiliation has Department has n 1 1 has has Controls n n n n 1 1 Salary WorksOn has Project has Paper Contents (Cont.) • Temporal Universal Quantifier(Cont.) :

35. 1 n n 1 1 Employee has Affiliation has Department has n 1 1 has has Controls n n n n 1 1 Salary WorksOn has Project has Paper Contents (Cont.) • Temporal Universal Quantifier(Cont.) :

36. Conclusion • The cost to implement temporal queries in standard SQL is high due to the adoption of constructing various complex “Views”, both in coding and execution time. • To reduce execution time, this paper suggest using T-SQL procedures with cursors instead. But the detail belongs to the future work of this paper. • The best solution, however, is to have the DBMS provide such time-varying facilities in a native way, since that would increase both database performance and application development productivity.

37. The End