CSc 8710, DDLP Fall 2003 Exam 3 - Take Home (Due: 11 December, 2003) ------------------------------------------- (1) Consider the following two programs P1 and P2: P1: p(X), q(X) :- r(X). r(a) V r(b). P2: p(X) :- r(X). q(X) :- r(X). r(a) V r(b). (a) Calculate Herbrand Base and the Extended Herbrand Base for P1. (b) Find minimal models of P1 and P2. (c) Find the minimal model-state for P1 and P2. (2) Mine association rules by hand from the contact-lenses dataset by faithfully following the Apriori algorithm with minimal support = 20% and minimal confidence = 90%. That is, start by generating candidate itemsets and frequent itemsets level by level and after all frequent itemsets have been generated, produce from them all the rules with confidence greater than or equal to the min. confidence. SHOW IN DETAIL ALL THE STEPS OF THE PROCESS. (3) Consider the company database. Write Oracle trigger(s) to do the following: Whenever an employee's project assignments are changed, check if the total hours per week spent on the employee's projects are less than 30 or greater than 40. If so, notify the employee's direct supervisor by email(?). (4) Consider the following data: Locations locid city state country 1 Madison WI USA 2 Fresno CA USA 3 Chennai TN India Products pid pname category price 11 Lee Jeans Apparel 25 12 Zord Toys 18 13 Biro Pen Stationery 2 Sales pid timeid locid sales 11 1 1 25 11 2 1 8 11 3 1 15 12 1 1 30 12 2 1 20 12 3 1 50 13 1 1 8 13 2 1 10 13 3 1 10 11 1 2 35 11 2 2 22 11 3 2 10 12 1 2 26 12 2 2 45 12 3 2 20 13 1 2 20 13 2 2 40 13 3 2 5 (a) Show the result of pivoting the Sales relation on pid and timeid. (b) Write a collection of SQL queries to obtain the same result as in (a). (c) Show the result of pivoting the Sales relation on pid and locid.