CSc 8710 DDLP - Fall 2010
Homework 2 Solutions
-------------------------

Problem 3

-----------------------------------------------------------------------------
a) Retrieve part numbers of parts that are supplied to exactly 
   two projects

{p|(exists a1,j1,j2,a2,a3,a4)(
       supply(a1,p,j1,a2) and supply(a3,p,j2,a4) and j1 <> j2 and 
       not((exists a5,a6,a7,a8,a9,a10,j3,j4,j5)(
           supply(a5,p,j3,a6) and supply(a7,p,j4,a8) and supply(a9,p,j5,a10) and 
           j3 <> j4 and j4 <> j5 and j3 <> j5))
    )
}

temp1(P):- supply(_,P,J1,_), supply(_,P,J2,_), J1 <> J2.
temp2(P):- supply(_,P,J1,_), supply(_,P,J2,_), supply(_,P,J3,_),
           J1 <> J2 , J1 <> J3, J2 <> J3.
answer(P):- temp1(P),not temp2(P).


project[PNO](select[JNO1 <> JNO2](
  (rename[PNO,JNO1](project[PNO,JNO](supply))
  join
  rename[PNO,JNO2](project[PNO,JNO](supply))
  )
))
minus
project[PNO](select[JNO1<>JNO2 and JNO2<>JNO3 and JNO1<>JNO3](
 ((rename[PNO,JNO1](project[PNO,JNO](supply))
   times
   rename[PNO,JNO2](project[PNO,JNO](supply))
  )
  times
  rename[PNO,JNO3](project[PNO,JNO](supply))
 )
));

select s.pno 
from   supply s, supply s1
where  s.pno = s1.pno and s.jno <> s1.jno
       and not exists(
          select s2.pno 
          from   supply s2, supply s3, supply s4
          where  s.pno = s2.pno and
                 s2.pno = s3.pno and s3.pno = s4.pno and
                 s2.jno <> s3.jno and s3.jno <> s4.jno and s2.jno <> s4.jno);

-----------------------------------------------------------------------------

b) Retrieve supplier names of suppliers who supply more than two parts 
   to project 'J1'

{ n |(exists p1,p2,p3,a1,a2,a3,s,c)(suppliers(s,n,c) and 
        supply(s,p1,'J1',a1) and supply(s,p2,'J1',a2) and supply(s,p3,'J1',a3) and 
        p1 <> p2 and p2 <> p3 and p1 <> p3)}

answer(S):- 
  supply(S,P1,'J1',_), supply(S,P2,'J1',_),supply(S,P3,'J1',_), 
  P1 <> P2, P2 <> P3, P1 <> P3.


project[SNAME]((
  rename[SNO1,SNAME](project[SNO,SNAME](suppliers))
  join
  project[SNO1](select[PNO1 <> PNO2  and PNO2 <> PNO3 and PNO1 <> PNO3 and 
                       JNO1 = 'J1' and JNO2 = 'J1' AND JNO3 = 'J1'](
    ((rename[SNO,PNO1,JNO1](project[SNO,PNO,JNO](supply))
    join
    rename[SNO,PNO2,JNO2](project[SNO,PNO,JNO](supply))
    )
    join
    rename[SNO,PNO3,JNO3](project[SNO,PNO,JNO](supply))
    )
  ))
)
);

select sp.sname 
from   suppliers sp, supply s1, supply s2, supply s3
where  sp.sno = s1.sno and s1.sno = s2.sno and s2.sno = s3.sno and
       s1.pno <> s2.pno and s2.pno <> s3.pno and s1.pno <> s3.pno and 
       s1.jno = 'J1' and s2.jno = 'J1' and s3.jno = 'J1';

-----------------------------------------------------------------------------

c) Retrieve part numbers of parts that are supplied by every supplier.

{p| (exists a1,a2,a3)(parts(p,a1,a2,a3) and 
            not((exists s,a4,a5,b1,b2,b3)(suppliers(s,a4,a5) and parts(p,b1,b2,b3) and
                   not((exists a7,a8)(supply(s,p,a7,a8))) 
                ))
    )
}

temp1(S,P) :- suppliers(S,_,_), parts(P,_,_,_).
temp2(S,P) :- supply(S,P,_,_).
temp3(P) :- temp1(S,P), not temp2(S,P).  
answer(P) :- parts(P,_,_,_), not temp3(P).

(project[PNO](parts)
  minus
  project[PNO](((project[SNO](suppliers) times project[PNO](parts))
                minus
                project[SNO,PNO](supply)))
);

select p.pno 
from  parts p
where not exists(
        select s.sno 
        from   suppliers s
        where  not exists(
          select sp.sno 
          from   supply sp
          where  s.sno = sp.sno and p.pno = sp.pno));

-----------------------------------------------------------------------------

d) Retrieve project names of projects that are supplied only by 
   suppliers S1.

{ p |(exists j,a1,a2,a3)(projects (j,p,a1) and 
         not((exists a6,a4,a5,s)(supply(s,a4,j,a5) and s <> 'S1'))
     )
}

temp(J) :- supply(S,_,J,_), S <> 'S1'.
answer(M) :- projects (J,M,_), not temp(J).

project[JNAME](
(projects
join
(
project[JNO](projects)
minus
project[JNO](select[SNO <>'S1'](supply))
)
)
);

select p.jname 
from   projects p
where  not exists(
         select s.jno
         from   supply s
         where  s.sno <> 'S1' and p.jno = s.jno);

-----------------------------------------------------------------------------

e) Retrieve supplier names of suppliers who supply at least two 
   different parts each to at least two different projects.

{ sname | (exists s,a1,p1,j1,a2,p2,j2,a3)(suppliers(s,sname,a1) and 
              supply (s,p1,j1,a2) and supply(s,p2,j1,a3) and p1 <> p2 and
              supply (s,p3,j2,a2) and supply(s,p4,j2,a3) and p3 <> p4 and
              j1 <> j2
           )
}


answer(M):-
  suppliers(S,M,_),
  supply(S,P1,J1,_), supply(S,P2,J1,_), P1 <> P2,
  supply(S,P3,J2,_), supply(S,P4,J2,_), P3 <> P4,
  J1 <> J2.


project[sname](
select[jno1<>jno2] (
(rename[sno,jno1] (project[sno,jno](
    select [pno1<>pno2](
        (rename[sno,pno1,jno] (project [sno,pno,jno] (supply)))
        join
        (rename[sno,pno2,jno] (project [sno,pno,jno] (supply)))
    )
))
join
rename[sno,jno2] (project[sno,jno](
    select [pno1<>pno2](
        (rename[sno,pno1,jno] (project [sno,pno,jno] (supply)))
        join
        (rename[sno,pno2,jno] (project [sno,pno,jno] (supply)))
    )
)))
)
join
(suppliers)
);

select sp.sname 
from   suppliers sp, supply s1, supply s2, supply s3, supply s4
where  sp.sno = s1.sno and s1.sno = s2.sno and 
       s2.sno = s3.sno and s3.sno = s4.sno and 
       s1.jno = s2.jno and s1.pno <> s2.pno and 
       s3.jno = s4.jno and s3.pno <> s4.pno and 
       s1.jno <> s3.jno;



-----------------------------------------------------------------------------