Database/Relational Algebra question?

Question

I'm trying to find the following
find every department that has a professor who taught all courses offred by that department.
Using relational algebra and then using SQL.
The schema is as follows:
Student(Id:Integer, Name:String, Address:String, Status:String)
Professor(Id:Integer, Name:String, DeptId:String)
Course(DeptId:String, CrsCode:String, CrsName:String, Descr:String)
Transcript(StudId:Integer, CrsCode:String, Semester:String, Grade:String)
Teaching(ProfId:Integer, CrsCode: String, Semester:String)

Any help would be much appreciated, I'm starting think that it is near impossible to represent this in a one line relational algebra formula.

I know relational algebra, I'm just having problems finding a formula that will find the results for EVERY department. If a department was specified I think a simple / on a couple easy queries should do it . pi_Id,CrsCode(Natural Join(TEACHINGXPROFESSOR)/pi_crscode(sigma_dept='specific dept(COURSE)) If I'm that that should give me the Id of the professor who has taught every course in that specificed department.

but how would I get the same results listed all departments is the count was unknown and I had to do it in 1 relational alg. forumla.

I know relational algebra, I'm just having problems finding a formula that will find the results for EVERY department. If a department was specified I think a simple / on a couple easy queries should do it . pi_Id,CrsCode(Natural Join(TEACHINGXPROFESSOR)/pi_crscode(sigma_dept='specific dept(COURSE)) If I'm right that should give me the Id of the professor who has taught every course in that specificed department.

but how would I get the same results listed all departments is the count was unknown and I had to do it in 1 relational alg. forumla.

Answer 1

These seem as simple relationships.
Draw a diagram to see which subsumes which.

Answer 2

http://db.grussell.org/section010.html
http://db.grussell.org/ch9.html


BETWEEN (Inclusive) List all the courses which occurred during 1988 SELECT * FROM course WHERE cdate BETWEEN '01-JAN-1988' AND '31-DEC-1988'