1 / 14

Hwrk Week5: solutions

Hwrk Week5: solutions. CS240 Winter 2013. Temporal Curiosity Items. Julian: (365*4+1)/4= 365.250000 Gregorian: (400*365+97)/400= 365.242500 Jalaali: (33*365+8)/33= 365.242424 Actual solar year :  365.242198 …

yanni
Télécharger la présentation

Hwrk Week5: solutions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Hwrk Week5: solutions CS240 Winter 2013

  2. Temporal Curiosity Items • Julian: (365*4+1)/4= 365.250000 • Gregorian: (400*365+97)/400= 365.242500 • Jalaali: (33*365+8)/33= 365.242424 • Actual solar year:  365.242198 … • Is "honey moon" a celestial body or a month and what is origin of the expression?

  3. Blue Moon A blue moon is a second full moon in a single calendar month, which happens every two to three years. The rarity of this astronomical event derives from the length of the lunar cycle, 29.53 days,[1] so most months contain only one full moon. The usage of the term blue moon to describe it results from a misinterpretation of the traditional definition of that term in the March 1946 issue of Sky and Telescope.[2] Due to the rarity of a blue moon, the term "blue moon" is used colloquially to mean a rare event, as in the phrase "once in a blue moon".[3]

  4. Exercises 6.3 from the ADS textbook. Erratum: switch the two columns under Drug and Dosage

  5. Exercise 6.1

  6. The Join using CASE % we will now take the temporal join of the two tables: ! echo temporal join select distinct E.SSN, E.LNAME, E.FNAME, E.Salary, D.DNO, case when E.From < D. From then D.From else E.from END as FFrom, case when E.To > D.To then D.To else E.To END as TTo from HistEmp as E, HistDep as D where E.SSn=D.SSN and E.To >D.From and D.To >E.From SSN LNAME FNAME SALARY DNO FFROM TTO ----------- -------------------- -------------------- ----------- ----------- ---------- ---------- 333445555 Wong Franklin 40000 2 12/08/1955 12/08/1956 333445555 Wong Franklin 40000 3 12/08/1956 12/08/1957 333445555 Wong Franklin 40000 4 12/08/1957 12/08/1958 123456789 Smith John 30000 5 01/09/1965 01/09/1966 123456789 Smith John 40000 5 01/09/1966 01/09/1967 123456789 Smith John 40000 4 01/09/1968 01/09/1969 123456789 Smith John 40000 6 01/09/1969 01/09/1970 123456789 Smith John 45000 6 01/09/1979 01/09/1990 999887777 Zelaya Alicia 25000 2 07/19/1998 07/19/1999 999887777 Zelaya Alicia 26000 2 07/19/1999 07/19/2001 10 record(s) selected.

  7. Coalesce in Datalog ehist(Eno, Sal, Title, From, To) coal(Eno,Title, Fr, To) :- ehist(Eno, Title, Fr, To). coal(Eno,T, Fr, To) :- coal(Eno, T, Fr1, To1), ehist(Eno, T, Fr2, To2) Fr1<= To2, Fr2< To1, smaller(Fr1, Fr2, Fr), larger(To1, To2, To). mxps(Eno,T, Fr, To) :- coal(Eno,T, Fr, To), ~ lgr(Eno,T, Fr, To). lgr(Eno,T, Fr, To) :- coal(Eno,T1, Fr1, To1), Fr1<Fr, To1>=To. lgr(Eno,T, Fr, To) :- coal(Eno,T1, Fr1, To1), Fr1<=Fr, To1>To. larger(X, Y, X) :- X>=Y. smaller(X, Y, Y) :- X<=Y.

  8. Write a recursive SQL query to coalesce the periods after Sal is projected out from EHist(Eno, Sal, Title, From, To) with coal(SSN, LNAME, FNAME, Salary, Start, End, CNT) As ((select SSN, LNAME, FNAME, Salary, From, To, 0 from histemp) union all (select coal.SSN, coal.LNAME, Coal.FNAME, Coal.Salary, Start, To, CNT+1 from coal, histemp where CNT<5 and histemp.SSN = coal.SSN and histemp.Salary=coal.Salary and Start <= From and From <= End and End < To)) select distinct * from coal • Give a simpler SQL expression for the temporal joins of ADS example 5.9 Answers

  9. %Write a recursive SQL query to coalesce the periods after Sal is projected out from %EHist(Eno, Sal, Title, From, To) CREATE TABLE HistEmp( SSN CHAR(11) NOT NULL, LName CHAR(20), FName CHAR(20), Salary INTEGER, Dno INTEGER, From Date, To Date) insert into HistEmp values ('123456789', 'Smith', 'John', 30000, 5, '01/09/1965', '01/09/1966'), ('123456789', 'Smith', 'John', 40000, 5, '01/09/1966', '01/09/1967'), ('123456789', 'Smith', 'John', 40000, 4, '01/09/1968', '01/09/1969'), ('123456789', 'Smith', 'John', 40000, 6, '01/09/1969', '01/09/1970'), ('123456789', 'Smith', 'John', 45000, 6, '01/09/1979', '01/09/1990'), ('333445555', 'Wong', 'Franklin', 40000, 2, '12/08/1955', '12/08/1956'), ('333445555', 'Wong', 'Franklin', 40000, 3, '12/08/1956', '12/08/1957'), ('333445555', 'Wong', 'Franklin', 40000, 4, '12/08/1957', '12/08/1958'), ('999887777', 'Zelaya', 'Alicia', 25000, 2, '07/19/1998', '07/19/1999'), ('999887777', 'Zelaya', 'Alicia', 26000, 2, '07/19/1999', '07/19/2001'); % The coalesce query with coal(SSN, LNAME, FNAME, Salary, Start, End, CNT) As ((select SSN, LNAME, FNAME, Salary, From, To, 0 from histemp) union all (select coal.SSN, coal.LNAME, Coal.FNAME, Coal.Salary, Start, To, CNT+1 from coal, histemp where CNT<5 and histemp.SSN = coal.SSN and histemp.Salary=coal.Salary and Start <= From and From <= End and End < To)) select distinct * from coal % from the records above, produces ---------------------------------------------------------------------------------------------- SSN LNAME FNAME SALARY START END CNT ----------- -------------------- -------------------- ----------- ---------- ---------- ----------- 333445555 Wong Franklin 40000 12/08/1955 12/08/1956 0 333445555 Wong Franklin 40000 12/08/1956 12/08/1957 0 333445555 Wong Franklin 40000 12/08/1957 12/08/1958 0 123456789 Smith John 30000 01/09/1965 01/09/1966 0 123456789 Smith John 40000 01/09/1966 01/09/1967 0 123456789 Smith John 40000 01/09/1968 01/09/1969 0 123456789 Smith John 40000 01/09/1969 01/09/1970 0 123456789 Smith John 45000 01/09/1979 01/09/1990 0 999887777 Zelaya Alicia 25000 07/19/1998 07/19/1999 0 999887777 Zelaya Alicia 26000 07/19/1999 07/19/2001 0 333445555 Wong Franklin 40000 12/08/1955 12/08/1957 1 333445555 Wong Franklin 40000 12/08/1956 12/08/1958 1 123456789 Smith John 40000 01/09/1968 01/09/1970 1 333445555 Wong Franklin 40000 12/08/1955 12/08/1958 2

  10. Store the results of Colalescing % We will now run the same query and eliminate tuples with non-maximal periods with coal(SSN, LNAME, FNAME, Salary, Start, End, CNT) As ((select SSN, LNAME, FNAME, Salary, From, To, 0 from histemp) union all (select coal.SSN, coal.LNAME, Coal.FNAME, Coal.Salary, Start, To, CNT+1 from coal, histemp where CNT<5 and histemp.SSN = coal.SSN and histemp.Salary=coal.Salary and Start <= From and From <= End and End < To)) select distinct * from coal where not exists (select * from coal as C where C.SSN=coal.SSN and C.Salary=coal.Salary and C.start <= Coal.start and C.end>=coal.end and (C.start <Coal.start or C.end>coal.end)) % We now stores these results into HistSal CREATE TABLE HistSal( SSN CHAR(11) NOT NULL, LName CHAR(20), FName CHAR(20), Salary INTEGER, From Date, To Date); % We now do the same for departments % We now do the same for departments insert into HistDep(SSN, LNAME, FNAME, DNO, From, To) with coal(SSN, LNAME, FNAME, DNO, Start, End, CNT) As ((select SSN, LNAME, FNAME, DNO, From, To, 0 from histemp) union all (select coal.SSN, coal.LNAME, Coal.FNAME, Coal.DNO, Start, To, CNT+1 from coal, histemp where CNT<5 and histemp.SSN = coal.SSN and histemp.DNO=coal.DNO and Start <= From and From <= End and End < To)) select distinct SSN, LNAME, FNAME, DNO, Start, End from coal where not exists (select * from coal as C where C.SSN=coal.SSN and C.DNO=coal.DNO and C.start <= Coal.start and C.end>=coal.end and (C.start <Coal.start or C.end>coal.end)); % the content of histdept select * from histdep

  11. Here EHist(Eno, Sal, Title, From, To) is a concrete view that stores the transaction time history for the relation EMP(Eno, Sal, Title). The concrete view must be maintained by active DB2 rules. Please write those rules. CREATE TRIGGER HireEmployee AFTER INSERT ON EMP FOR EACH ROW INSERT INTO EHist VALUES(Eno, Sal, Title, CURRENTDATE, 9999-12-31) % we use 9999-12-31 to denote 'until changed’ CREATE TRIGGER FireEmployee AFTER DELETE ON EMP FOR EACH ROW UPDATE EHist SET To = CURRENTDATE WHERE EHist.Eno = OLD.Eno AND EHist.To = 9999-12-31 CREATE TRIGGER ChangeEmployee1 AFTER UPDATE ON EMP FOR EACH ROW UPDATE EHist SET To = CURRENTDATE WHERE EHist.Eno = OLD.Eno AND EHist.To = 9999-12-31 CREATE TRIGGER ChangeEmployee2 AFTER UPDATE ON EMP FOR EACH ROW INSERT INTO EHist VALUES(Eno, Sal, Title, CURRENTDATE, 9999-12=31)

  12. events(itemNo, SensorNo, SensorType, Time) SELECT AitemNo FROM events MATCH-RECOGNIZE ( PARTITION BY itemNO ORDER BT Time MEASURES A.itemNo As AitemNo PATTERN (A B* C+ D) DEFINE B AS (B.SensorNo = A.SensorNo) C AS (C.SensorNo <> A.SensorNo) D AS (D.SensorNo = A.SensorNo AND D.Time-A.Time <= 1 Day) )T B* says that the item can stay in the original position for a while. Then it moves & it is detected other sensors (C*), until it returns to the original position (D)

  13. Example 6: From EmpUH to EmpTD: Temporal Join SELECT Empno, B.start, ets(A.end, B.end), nnv(A.Title, B.Title), nnv(A.deptno, B.deptno) FROM EmpTD PARTITION BY Empno ORDER BY start, Type AS PATTERN(A B+) WHERE A.Type <> B.Type

  14. Example 7: Coalescing Expressed using Kleene-closure SELECT empno, first(B.start), max(B.end) FROM EmpTD AS PARTITION BY empno ORDER BY start AS PATTERN (B+) WHERE count(B.*)=1 OR B.start<=max(previous(B.end))

More Related