CSc 8710, DDLP - Fall 2010
HW2 Solutions
-------------

Problem 1
------------------------------------------------------------------------

a) Retrieve the names of all employees in department 5 who work 
   more than 10 hours per week on the ProductX project.

{f,m,l|(exists a1,a2,a3,a4,a5,a8,a9,s,p,h )(
          employee (f,m,l,s,a1,a2,a3,a4,a5,5) and works_on(s,p,h) and
          projects('ProductX',p,a8,a9) and h > 10)
}

answer(F,M,L):- employee(F,M,L,S,_,_,_,_,_,5), works_on(S,P,H), H > 10,
                projects('ProductX',P,_,_).

project[FNAME,MINIT,LNAME] (
(
  (
    project[PNUMBER](select[PNAME='ProductX'](projects))
    join
    rename[SSN, PNUMBER, HOURS](select[HOURS > 10](works_on))
  )
  join
  select[DNO=5](employee)
)
);

select e.FNAME,e.MINIT,e.LNAME
from   employee e, works_on w, projects p
where  e.DNO = 5 and
       e.ssn = w.essn and w.pno = p.pnumber and
       p.pname = 'ProductX' and w.hours > 10;

------------------------------------------------------------------------
b) List the names of all employees who have a dependent with the 
   same first name as themselves.

{f,m,l|(exists a1,a2,a3,a4,a5,a6,a7,a8,a9,s)(
            employee (f,m,l,s,a1,a2,a3,a4,a5,a6) and 
            dependent(s,f,a7,a8,a9))}

answer(F,M,L) :- employee(F,M,L,S,_,_,_,_,_,_), dependent(S,F,_,_,_).

project[FNAME,MINIT,LNAME](
(employee
 join
 rename[SSN, FNAME] (project[ESSN,DEPENDENT_NAME](dependent))
)
);

select e.fname, e.minit, e.lname
from   employee e, dependent d
where  e.ssn = d.essn and e.fname = d.dependent_name;

------------------------------------------------------------------------
c) Find the names of employees who are directly supervised by 
   Franklin Wong.

{f,m,l|(exists a1,a2,a3,a4,a5,a6,a7,a8,a9,s,a10,a11,a12,a13)(
             employee (f,m,l,a5,a1,a2,a3,a4,s,a6) and 
             employee ('Franklin',a7,'Wong',s,a8,a9,a10,a11,a12,a13) 
       )
}

answer(F,M,L) :- employee(F,M,L,_,_,_,_,_,S,_), 
                 employee('Franklin',_,'Wong',S,_,_,_,_,_,_).

project[FNAME,MINIT, LNAME](
  employee
  join
    (
    rename[SUPERSSN](project[SSN](select[FNAME='Franklin' AND LNAME='Wong'](employee)))
    join
    (project[SSN,SUPERSSN](employee))
    )
);

select e.fname,e.minit,e.lname
from   employee e, employee e1
where  e.superssn = e1.ssn
and    e1.fname = 'Franklin' and e1.lname = 'Wong';

------------------------------------------------------------------------
d) Retrieve the names of employees who work on every project.

{f,m,l| (exists s,a1,a2,a3,a4,a5,a6)(
            employee (f,m,l,s,a1,a2,a3,a4,a5,a6) and 
            not((exists a7,p,a8,b1,b2,b3,b5,b6,b7,b8,b9,b10,a9)(
                  projects(a7,p,a8,a9) and 
                  employee (b1,b2,b3,s,b5,b6,b7,b8,b9,b10) and
                  not((exists h)(works_on(s,p,h))) 
                 ))  
         )
}

temp1(S,P) :- employee(_,_,_,S,_,_,_,_,_,_), projects(_,P,_,_).
temp2(S,P) :- works_on(S,P,_).
temp3(S) :- temp1(S,P), not temp2(S,P).
answer(F,M,L) :- employee(F,M,L,S,_,_,_,_,_,_), not temp3(S).

project[FNAME,MINIT,LNAME](
(
  employee
  join
(project[SSN](employee)
minus
project[SSN](
  (
    ( project[SSN] (employee)
      times
      project[PNUMBER](projects)
    )
    minus
    rename[SSN,PNUMBER](project[ESSN,PNO](works_on))
  )
)
)
)
);

select e.fname, e.minit, e.lname
from   employee e
where  not exists(
         select p.pnumber 
         from   projects p
         where  not exists(
                  select w1.essn 
                  from   works_on w1
                  where  w1.pno = p.pnumber and w1.essn = e.ssn));

------------------------------------------------------------------------
e) Retrieve the names of employees who do not work on any project.

{f,m,l|(exists s,a1,a2,a3,a4,a5,a6)(
            employee (f,m,l,s,a1,a2,a3,a4,a5,a6) and 
            not((exists p,h)(works_on(s,p,h)))
       )
}

temp(S) :- works_on(S,_,_).
answer(F,M,L):- employee(F,M,L,S,_,_,_,_,_,_), not temp(S).

project[FNAME,MINIT,LNAME](
(employee
join
    (project[SSN](employee)
    minus
    rename[SSN](project[ESSN](works_on))
    )
)
);

select fname,minit,lname
from   employee 
where  not exists (
          select essn 
          from   works_on 
          where  ssn = essn);

------------------------------------------------------------------------
f) Retrieve the names and addresses of all employees who work on at 
   least one project located in Houston but whose department has no 
   location in Houston.

{f,m,l,a|(exists s,a2,a3,a4,a5,a6,d,p,a6,a7,a8)(
            employee (f,m,l,s,a2,a,a3,a4,a5,d) and works_on(s,p,a6) and 
            projects (a7,p,'Houston',a8) and 
            not(dept_locations(d,'Houston')) 
         )
}

temp(D) :- dept_locations(D,'Houston').
answer(F,M,L,A):-
  employee(F,M,L,S,_,A,_,_,_,D),
  works_on(S,P,_),
  projects(_,P,'Houston',_),
  not temp(D).

project[FNAME,MINIT,LNAME,ADDRESS](
(
(
(rename[SSN, PNUMBER](project[ESSN,PNO](works_on))
join
rename[PNUMBER,DUMBER](project[PNUMBER,DNUM](select[PLOCATION='Houston'](projects)))
)
join
rename[FNAME,MINIT,LNAME,SSN,BDATE,ADDRESS,SEX,SALARY,SUPERSSN,DNUMBER](employee)
)

join

(project[DNUMBER](dept_locations)
minus
project[DNUMBER](select[DLOCATION='Houston'](dept_locations)))
)
);

select e.fname, e.minit, e.lname, e.address
from   employee e, projects p, works_on w
where  e.ssn = w.essn and p.pnumber = w.pno and p.plocation = 'Houston' and 
       not exists (
          select d.dnumber 
          from   dept_locations d
          where  e.dno = d.dnumber and d.dlocation = 'Houston');

------------------------------------------------------------------------
g) Retrieve the last names of all department managers who have no dependents.

{ l | (exists a1,a2,s,a4,a5,a6,a7,a8,a9,a10,a11,a12)(
            employee(a1,a2,l,s,a4,a5,a6,a7,a8,a9) and department(a10,a11,s,a12) and
               not((exists a13,a14,a15,a16)(dependent(s,a13,a14,a15,a16)))
      )
}

temp(S) :- dependent(S,_,_,_,_).
answer(L):- employee(_,_,L,S,_,_,_,_,_,_), department(_,_,S,_), not temp(S).

project[LNAME](
(
employee
join
(rename[SSN](project[MGRSSN](department))
 minus
 rename[SSN](project[ESSN](dependent))
)
)
);

select e.lname
from   employee e, department d 
where  e.ssn = d.mgrssn and 
         not exists(
             select essn 
             from   dependent 
             where  d.mgrssn = essn);
 
------------------------------------------------------------------------