CS240A Practice Midterm

1. CS240A Practice Midterm Feb 2011

2. Problem 1, 36 Points score(joeDoe, 81). score(janeDoe, 67). …. DB facts about students’ name & grade: • Our instructor computes the average grade, and then give and A to everyone above the average and B to the rest. Please write Datalog rules to return the A students and the B students: use arithmetic here. • Our instructor changes his mind and and gives and A to everyone above the median a B to the rest. Please write Datalog rules to return the A students and the B students: do not use arithmetic here. • Explain how the recursive rules for query i. and ii. would be implemented by the compiler. For recursive predicates and rules also show the rewritten rules.

3. score(joeDoe, 81).score(janeDoe, 67). • Our instructor computes the average grade, and then give and A to everyone above the average and B to the rest. Please write Datalog rules to return the A students and the B students: use arithmetic here. maxscore(N, S) ← score(N, S), ¬smaller(S). smaller(S) ← score(_, S1), S1 < S. minscore(N, S) ← score(N, S), ¬larger(S). larger(S) ← score(_, S1), S1 > S. gradeA(N) ← score(N, S), maxscore(_, H), minscore(_, L), S > (H + L)/2. gradeB(N) ← score(N, ), ¬gradeA(N). Compilation of this. This is a stratified program: maxscoreand minscore are computed in the lower strata. Then gradeA, and finally gradeB.

4. score(joeDoe, 81). score(janeDoe, 67). • Our instructor changes his mind and and gives and A to everyone above the median a B to the rest. Please write Datalog rules to return the A students and the B students: do not use arithmetic here. pairs(L, H) ← minscore(_, L), maxscore(_ , H). pairs(L1, H1) ← pairs(L, H), L < H, ¬between(L, L1), ¬between(H1, H). median(M) ← pairs(M, M). gradeA(N) ← score(N, S), median(M), S > M. gradeB(N) ← score(N, ), ¬gradeA(N). iii. Explain how the recursive rules for query i. and ii. would be implemented by the compiler. For recursive predicates and rules also show the rewritten rules. Compilation: lower strata minscore and maxscore, and between. Then pairs is recursive and has no bound argument. So the delta rules are: δpairs(L1, H1) ← δpairs(L, H), L < H, ¬between(L, L1), ¬between(H1, H). Then median and gradeA, and finally gradeB

5. Problem 2: 36 points • Complete the following declaration to make it into a bitemporal TSQL2 table (valid time granularity of days): CREATE TABLE empl(Eno integer, Sal integer, Dept char(6), Title char(6)) B. Say that after freezing everybody’s salary for long time, the city of Los Angeles has solved its money problems: Effective immediately, all the current employees will receive a retroactive pay raise of 6% for the last six month (i.e., a pay raise effective six months before now). Write TSQL2 update statements to record this raise. C. Of course, updating the database retroactively is not sufficient, and the city will also have to disburse money to compensate the employees for the last six months of lower salary. For that, the city needs, for all employees who were working six moths ago, their (Eno, Sal, Title) history for the last four years, as it was known six months ago. Write a TSQL2 query to generate this information.

6. Problem 2: 36 points • Complete the following declaration to make it into a bitemporal TSQL2 table (valid time granularity of days): CREATE TABLE empl(Eno integer, Sal integer, Dept char(6), Title char(6)) AS VALID DAY AND TRANSACTION B. Say that after freezing everybody’s salary for long time, the city of Los Angeles has solved its money problems: Effective immediately, all the current employees will receive a retroactive pay raise of 6% for the last six month (i.e., a pay raise effective six months before now). Write TSQL2 update statements to record this raise. UPDATE empl SET Sal TO Sal*1.06,VALID PERIOD(CURRENT_DATE - 6 MONTH, NOBIND(CURRENT_DATE)) WHERE VALID(emp) OVERLAPS CURRENT_DATE C. Of course, updating the database retroactively is not sufficient, and the city will also have to disburse money to compensate the employees for the last six months of lower salary. For that, the city needs, for all employees who were working six moths ago, their (Eno, Sal, Title) history for the last four years, as it was known six months ago. Write a TSQL2 query to generate this information. SELECT Eno, Sal, Title, VALID INTERSECT(VALID(emp), PERIOD(CURRENT_DATE – 48 MONTH, CURRENT_DATE)) FROM emp WHERE VALID(E) OVERLAPS CURRENT_DATE - 6 MONTH AND TRANSACTION(E) OVERLAPS CURRENT_DATE - 6 MONTH

7. Problem 3: 28 points empl(Eno, Ename, Dept) deptsize(Dept, EmplCount) Here deptsize is a concrete view keeping the count of the employees in the department. In fact, EmplCount must be larger than a certain integer—a constraint enforced by a check condition (which is not completely known since it can be different for different departments). We are writing active rules to deal with a set of updates recording the fact that several employees are being moved to different departments. • Write active DB2 rules that, for all departments, will automatically update EmplCount to the new counts for departments involved in the transfers. If any of those updated counts violate the IC constraints, then make sure that the whole transaction aborts (because of your rules or the ICs already enforced by the system). CREATE TRIGGER Step1 AFTER UPDATE ON empl FOR EACH STATEMENT UPDATE deptsize SET EmplCount TO EmplCount+ (SELECT count(*) FROM NEW WHERE NEW. Dept=deptsize.Dept) – (SELECT(count(*) FROM OLD WHERE OLD. Dept=deptsize.Dept)