```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
%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
Salary
INTEGER,
Dno
INTEGER,
From Date,
To
Date)
CHAR(20),
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
----------- -------------------- -------------------- ----------- ---------- ---------- ----------333445555 Wong
Franklin
40000 12/08/1955 12/08/1956
333445555 Wong
Franklin
40000 12/08/1956 12/08/1957
333445555 Wong
Franklin
40000 12/08/1957 12/08/1958
123456789 Smith
John
30000 01/09/1965 01/09/1966
123456789 Smith
John
40000 01/09/1966 01/09/1967
123456789 Smith
John
40000 01/09/1968 01/09/1969
123456789 Smith
John
40000 01/09/1969 01/09/1970
123456789 Smith
John
45000 01/09/1979 01/09/1990
999887777 Zelaya
Alicia
25000 07/19/1998 07/19/1999
999887777 Zelaya
Alicia
26000 07/19/1999 07/19/2001
333445555 Wong
Franklin
40000 12/08/1955 12/08/1957
333445555 Wong
Franklin
40000 12/08/1956 12/08/1958
123456789 Smith
John
40000 01/09/1968 01/09/1970
333445555 Wong
Franklin
40000 12/08/1955 12/08/1958
CNT
0
0
0
0
0
0
0
0
0
0
1
1
1
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 histdept
select * 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.
CREATE TRIGGER HireEmployee
AFTER INSERT ON EMP
FOR EACH ROW
INSERT INTO EHist
VALUES(Eno, Sal, Title,
CURRENTDATE, 9999-12-31)
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
% we use 9999-12-31 to denote 'until changed’ AFTER UPDATE ON EMP
CREATE TRIGGER FireEmployee
FOR EACH ROW
AFTER DELETE ON EMP
INSERT INTO EHist
FOR EACH ROW
VALUES(Eno, Sal, Title,
CURRENTDATE, 9999-12=31)
UPDATE EHist SET To = CURRENTDATE
WHERE EHist.Eno = OLD.Eno
AND EHist.To = 9999-12-31
CREATE TRIGGER ChangeEmployee1
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.*)=1
OR B.start<=max(previous(B.end))
```