ISI PCB 2017

Question

Consider the following relations:
STD CHOICES (Student ID, Course ID, Semester) and
COURSE ASSIGN (Teacher ID, Course ID, Semester).
The former indicates the choice of courses for students and
the latter indicates the courses assigned to teachers for different semesters. Note that each student may take multiple courses, each teacher can teach multiple courses and each
course can also be taught by multiple teachers.
Write the relational calculus/algebra/sql query(all three) expression to output the ID
for all the students who have not been taught by the same
teacher in more than one course across all semesters.

Answer 1

Note that each student may take multiple courses, each teacher can teach multiple courses and each course can also be taught by multiple teachers. Write the relational calculus expression to output the ID for all the students who have not been taught by the same teacher in more than one course across all semesters.
STD CHOICES			COURSE ASSIGN
Student ID	Course ID	Semester	Teacher ID	Course ID	Semester
1	1	1	1	1	1
1	2	1	1	2	1
1	3	1	1	3	1
2	1	1	2	1	1
2	2	1	2	2	1
2	3	1	2	3	1
1	1	2	1	1	2
1	2	2	1	2	2
1	3	2	1	3	2
2	1	2	2	1	2
2	2	2	2	2	2
2	3	2	2	3	2
3	4	1	3	4	1

 

SELECT T2.TeacherID, T1.[StudentID], T1.[CourseID]
FROM [dbo].[STDCHOICES] T1, [dbo].[COURSEASSIGN] T2 
group by T2.TeacherID, T1.[StudentID], T1.[CourseID] 
having count(T2.CourseID)=1 and count(T2.TeacherID) =1

Our Expected student Id should be Student Id=4.  As here each teacher can teach multiple courses and each course can also be taught by multiple teachers thus Relation COURSE ASSIGN

Teacher ID 1 and 2 will not be considered because they taught more than one subject through all semester. Only Teacher ID 4 will be considering. Please correct me if I am wrong.