Consider the Teachers-Students-Courses Database with the following 5 relations:
ID: 3619974 • Letter: C
Question
Consider the Teachers-Students-Courses Database with the following 5 relations:
Professors (pid, tname, dept, ext.)
Students (sid, sname, major-dept, year)
Courses (cid, cname, dept, credithours)
Enrollment (sem, year, sid, cid)
Teach(pid, cid, sem, year)
where Professors have id’s (pid), names (pname), dept that they work (dept), and a telephone extension (ext). Similary, Students have id, name, major-dept and year (i.e, freshman, sophomore, etc). Attributes of Courses and Enrollment are self explanatory Assume that cid’s are unique.
Get pnames and pids of professors who teach every course offered by their department. (Using Relational Algebra to express)
Explanation / Answer
I know how to do it now. ?(Temp 1,ppid,cid(Teach)) ?(Temp 2,pcid,dept(Courses)) ?(Temp 3,ppid,dept(Professors)) ?(Temp 4,Temp 1?Temp 3) ?(Temp 5, ?Temp 4.cid=Temp 2.cid and Temp 4.dept?Temp 2.dept(Temp 4×Temp 2)) ?(Temp 6, ppid (Temp 5)) Temp 6: The professors who teach courses provided by other department. ?(Temp 7, ppid (Temp 1)) ?(Temp 8, ppid (Temp 3)) ?(Temp 9,Temp 8-Temp 7) Temp 9: The professors who don’t teach any courses. ?(Temp 10,Temp 8-Temp 9) Answer: Temp 10-Temp 6