CSc 8710, DBLP - Fall 2010
HW2 Solutions

Problem 2
------------------------------------------------------------------------------------
(a) Retrieve the names of students enrolled in the "Automata" class in
    the term of "Fall 1996".

{ f,m,l | (exists s)(students(s,f,l,m) and 
                     (exists c)(catalog(c,'Automata') and 
                                (exists n,a,b,c,d)(courses('f96',n,c,a,b,c,d) and 
                                                       enrolls(s,'f96',n)
                                                      )
                               )
                    )
}

answer(F,L,M) :- 
  students(S,F,L,M),
  catalog(C,'Automata'),
  courses('f96',N,C,_,_,_,_),
  enrolls(S,'f96',N).

project[fname,lname,minit](
  (project[sid](
    (project[term,secno]((select[ctitle='Automata'](catalog)) join
                         (select[term='f96'](courses)))
    ) join (enrolls))
  ) join (students)
);

select S.fname, S.lname, S.minit
from   students S, catalog C, courses O, enrolls E
where  C.ctitle='Automata' and C.cno=O.cno and O.term='f96' and 
       O.term=E.term and O.secno=E.secno and E.sid=S.sid;

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

(b) Retrieve the SID values of students who have enrolled in CSc226 as well as
    CSc 227.

{ s | (exists f,l,m)(
         students(s,f,l,m) and 
         (exists t1,n1,t2,n2)(enrolls(s,t1,n1) and enrolls(s,t2,n2) and 
                      (exists a1,b1,c1,d1)(courses(t1,n1,'csc226',a1,b1,c1,d1)) and 
                         (exists a2,b2,c2,d2)(courses(t2,n2,'csc227',a2,b2,c2,d2))))}

temp1(S) :- courses(T,N,'csc226',_,_,_,_), enrolls(S,T,N).
temp2(S) :- courses(T,N,'csc227',_,_,_,_), enrolls(S,T,N).
answer(S) :- temp1(S), temp2(S).

(project[sid]((project[term,secno](select[cno='csc226'](courses)))join(enrolls)))
intersect
(project[sid]((project[term,secno](select[cno='csc227'](courses)))join(enrolls)));

select E.sid
from   enrolls E, Courses C1, Courses C2
where  E.term=C1.term and E.secno=C1.secno and C1.cno='csc226' and 
       E.term=C2.term and E.secno=C2.secno and C2.cno='csc227';

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

(c) Retrieve the SID values of studennts who have enrolled in CSc226 or CSc 227.

{ s | (exists f,l,m)(students(s,f,l,m) and 
         (exists t1,n1,a1,b1,c1,d1)(enrolls(s,t1,n1) and 
                                    courses(t1,n1,'csc226',a1,b1,c1,d1)) 
         or 
         (exists t2,n2,a2,b2,c2,d2)(enrolls(s,t2,n2) and
                                    courses(t2,n2,'csc227',a2,b2,c2,d2))
       )
};

temp1(S) :- courses(T,N,'csc226',_,_,_,_), enrolls(S,T,N).
temp2(S) :- courses(T,N,'csc227',_,_,_,_), enrolls(S,T,N).
answer(S) :- temp1(S).
answer(S) :- temp2(S).

(project[sid]((project[term,secno](select[cno='csc226'](courses)))join(enrolls)))
union
(project[sid]((project[term,secno](select[cno='csc227'](courses)))join(enrolls)));

select E.sid
from enrolls E
where not E.sid in(
	select E1.sid
	from enrolls E1
	where not E1.sid in(
		select E2.sid
		from enrolls E2, courses C1
		where C1.cno='csc226' and C1.term=E2.term and C1.secno=E2.secno
	)
	and not E1.sid in(
		select E3.sid
		from enrolls E3, courses C2
		where C2.cno='csc227' and C2.term=E3.term and C2.secno=E3.secno
	)
);

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

(d) Retrieve the names of students who have not enrolled in any class.

{f,l,m|(exists s)(students(s,f,l,m) and 
                   not ((exists t,n)(enrolls(s,t,n)))
       )
};

temp(S) :- enrolls(S,_,_).
answer(F,L,M) :- students(S,F,L,M), not temp(S).

project[fname,lname,minit](
  ((project[sid](students))
    minus
   (project[sid](enrolls))
  )
  join
  (students)
);

select S.fname,S.lname,S.minit
from   students S
where  not exists(
	select E.sid
	from enrolls E
	where S.sid=E.sid);

------------------------------------------------------------------------------------
(e) Retrieve the names of students who have enrolled in all courses in the
    catalog table.

{f,l,m|(exists s)(students(s,f,l,m) and 
         not((exists c,t,f1,l1,m1)(catalog(c,t) and students(s,f1,l1,m1) and 
                       not((exists t,n,a,b,c,d)(courses(t,n,c,a,b,c,d) and 
                                                enrolls(s,t,n)))))
       )
};

temp1(S,C) :- students(S,_,_,_), catalog(C,_).
temp2(S,C) :- enrolls(S,T,N), courses(T,N,C,_,_,_,_).
temp3(S) :- temp1(S,C), not temp2(S,C).
temp4(S) :- temp2(S,_), not temp3(S).
answer(F,L,M) :- students(S,F,L,M), temp4(S).

project[fname,lname,minit](
((project[sid](students))
  minus
 (project[sid](
   ((project[sid](students))times(project[cno](catalog)))
    minus
   (project[sid,cno]((enrolls)join(project[term,secno,cno](courses))))
   )
  )
)
join(students)
);

select S.fname,S.lname,S.minit
from   students S
where  not exists(
	select C.cno
	from   catalog C
	where  not exists(
		select E.sid
		from   courses O,enrolls E
		where  C.cno=O.cno and O.term=E.term and 
                       O.secno=E.secno and E.sid=S.sid));

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