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.