(1) This trigger ensures that the price of any book does not exceed
    the number of pages it contains. In case the price is updated
    to a higher value, it it set equal to the number of pages.
    I guess, the publishers do not want to exceed $1 per page!

(2a) create or replace trigger doubleMiles
     before insert on ffMiles
     for each row
     when (new.awardOrUse = 'a')
     declare 
       totAwarded integer;
       totUsed integer;
     begin
       if ((:new.flightNum != 'BONUS') and
           (:new.flightNum != 'CREDIT')) then
         select sum(miles) into   totAwarded
         from   ffMiles
         where passengerName=:new.passengerName and awardOrUse = 'a';
         select sum(miles) into   totUsed
         from   ffMiles
         where passengerName=:new.passengerName and awardOrUse = 'u';
         if (totUsed is null) then
           totUsed := 0;
         end if;
         if (totAwarded-totUsed) >= 100000 then
             insert into ffMiles values
               (:new.passengerName,:new.flightDate,'BONUS',:new.miles,'a');
           end if;
       end if;
     end;
     
     
(2b) create or replace trigger payMiles
     before insert on ffMiles
     for each row
     when (new.awardOrUse = 'u')
     declare 
       totAwarded integer;
       totUsed integer;
       totAvailable integer;
       extraMiles integer;
     begin
       select sum(miles) into   totAwarded
       from   ffMiles
       where passengerName=:new.passengerName and awardOrUse = 'a';
       select sum(miles) into   totUsed
       from   ffMiles
       where passengerName=:new.passengerName and awardOrUse = 'u';
       if (totUsed is null) then
         totUsed := 0;
       end if;
       totAvailable := totAwarded - totUsed;
       if (:new.miles <= 1.2*totAvailable) and 
          (:new.miles > totAvailable)  then
         extraMiles := :new.miles - totAvailable;
         insert into passengerCharge values
             (:new.passengerName,:new.flightDate,:new.flightNum,
              extraMiles,0.025*extraMiles);
         insert into ffMiles values
             (:new.passengerName,:new.flightDate,'CREDIT',extraMiles,'a');
       end if;
     end;
    
(3) interface Zipcode(extent zipcodes, key(zip)){
      attribute int zip,
      attribute string city;
      relationship Set custs inverse Customer::zip,
      relationship Set emps inverse Employee::zip
    }
    interface Customer(extent customers, key(cno)){
      attribute int cno,
      attribute string cname;
      attribute string street;
      attribute string phone;
      relationship Zipcode zip inverse Zipcode::custs,
      relationship Set ords inverse Orders::cust
    }
    interface Employee(extent employees, key(eno)){
      attribute int eno,
      attribute string ename;
      attribute date hdate;
      relationship Zipcode zip inverse Zipcode::emps,
      relationship Set ords inverse Orders::emp
    }
    interface Order(extent orders, key(ono)){
      attribute int ono,
      attribute date received;
      attribute date shipped;
      relationship Customer cust inverse Customer::ords,
      relationship Employee emp inverse Employee::ords,
      relationship Set lineItems inverse Odetail::ord
    }
    interface Odetail(extent odetails){
      attribute int qty,
      relationship Order ord inverse Orders::lineItems,
      relationship Set prts inverse Part::ordered
    }
    interface Part(extent parts, key(pno)){
      attribute int pno,
      attribute string pname;
      attribute int qoh,
      attribute double price,
      attribute int olevel,
      relationship Set ordered inverse Odetail::prts
    }

(4a) Get names of members who have purchased ORCL shares.

     select m.name
     from   members m
     where  exists t in m.transactions:
              t.sTransaction.symbol="ORCL" and t.trans_type=buy;

(4b) Get names of members who currently own ONLY ORCL shares.

     select m.name
     from   members m
     where  forall t in m.portfolio(): t.symbol="ORCL";

(4c) Get company names of securities whose shares have been purchased by 
     ALL members.

     select s.cname
     from   securities s
     where  forall m in members:
              exists v in m.transactions:
                 v.sTransaction.symbol=s.symbol and v.trans_type=buy;

(4d) Get the names of members who currently own shares of ALL of the 
     companies that member with MID=11000 has purchased from.

     select m.name
     from   members m
     where  forall u in element(select n.transactions
                                from   members n
                                where  n.mid="11000"):
               exists v in m.portfolio(): u.sTransaction.symbol=v.symbol;

(4e) Get the names of members who have purchased shares ONLY from a subset 
     of the companies that member with MID=11000 has purchased shares from.

     select m.name
     from   members m
     where  forall u in m.transactions:
               u.trans_type=sell or
               exists v in element(select n.transactions
                                   from   members n
                                   where  n.mid="11000"):
                  u.sTransaction.symbol=v.sTransaction.symbol and
                  v.trans_type=buy;

(4f) Get the names of members who own shares from EXACTLY the same companies 
     that member with MID=11000 owns.

     select m.name
     from   members m
     where  (forall u in element(select n.portfolio()
                                from   members n
                                where  n.mid="11000"):
               exists v in m.portfolio(): u.symbol=v.symbol)
            and
            (forall u in m.portfolio():
               exists v in element(select n.portfolio()
                                   from   members n
                                  where  n.mid="11000"):
                  u.symbol=v.symbol);

(5) (a) VALID because all relationships are represented accurately.
    (b) INVALID because only 1 object can be associated with a Y object.
    (c) VALID because all relationships are represented accurately.
    (d) INVALID because relational scheme is not in 3NF (D value
           is repeated for each C value!).
    (e) INVALID because only 1 X object can be associated with a Y object.


(6a) answera(F,L) :- students(S,F,L),
                    courses(C,'Automata'),
                    enrolls(S,C).

(6b) temp1(S) :- enrolls(S,C1), enrolls(S,C2), C1 <> C2.
     answerb(F,L) :- students(S,F,L), enrolls(S,_), not temp1(S).

(6c) temp2(X,Y) :- students(X,_,_), enrolls(1111,Y).
     temp3(X) :- temp2(X,Y), not enrolls(X,Y).
     answerc(F,L) :- students(X,F,L), not temp3(X).

(6d) temp4(X) :- enrolls(X,Y), not temp2(X,Y).
     answerd(F,L) :- students(X,F,L), not temp4(X).

(6f) answere(F,L) :- answerc(F,L), answerd(F,L).

(7a) answer(X) :- r(X,17).

(7b) answer(X,Y,Z) :- r(X,Y), r(Y,Z).
     answer(X,Y,Z) :- r(X,Y), r(Z,Y).
     answer(X,Y,Z) :- r(X,Z), r(Y,Z).
     answer(X,Y,Z) :- r(X,Z), r(Z,Y).

(7c) temp1(X,Y) :- S(W,Z), R(X,Y), not R(X,Z).
     answer(X) :- R(X,Y), not temp1(X,Y).