Hwrk Week 5: solutions

CS240A Fall 2014

Exercise 6.1

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

Answers

%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 0333445555 Wong Franklin 40000 12/08/1956 12/08/1957 0333445555 Wong Franklin 40000 12/08/1957 12/08/1958 0123456789 Smith John 30000 01/09/1965 01/09/1966 0123456789 Smith John 40000 01/09/1966 01/09/1967 0123456789 Smith John 40000 01/09/1968 01/09/1969 0123456789 Smith John 40000 01/09/1969 01/09/1970 0123456789 Smith John 45000 01/09/1979 01/09/1990 0999887777 Zelaya Alicia 25000 07/19/1998 07/19/1999 0999887777 Zelaya Alicia 26000 07/19/1999 07/19/2001 0333445555 Wong Franklin 40000 12/08/1955 12/08/1957 1333445555 Wong Franklin 40000 12/08/1956 12/08/1958 1123456789 Smith John 40000 01/09/1968 01/09/1970 1333445555 Wong Franklin 40000 12/08/1955 12/08/1958 2

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 histdeptselect * from histdep

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.

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

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)

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.*)=1OR B.start<=max(previous(B.end))