703 views

Class(name, meets_at, room, fid)

Faculty(fid, fname, deptid)

Find the names of faculty members who teach in every room in which some class is taught

In RA it will be

$\prod _{fname}\left ( Class\bowtie Faculty \right )\div \prod _{name}\left ( Class \right )$
edited by

found a good resource

https://www.geeksforgeeks.org/sql-division/

implementation 2 has been used in most of the solution manuals of standard textbooks

implementation 1 is a bit confusing so I tried to modify it a bit. please correct me if I'm wrong

R(x, y) ÷ S(y)

select * from R where x NOT IN

(select x from

(select x, y from

(select distinct x from R CROSS JOIN select y from S)

EXCEPT

(select x, y from R)

)

)

taking table , we need to verify it

### 1 comment

where is table??

Faculty table:

Classroom table:

Result table:

SQL query :

select fname from faculty NATURAL JOIN classroom group by faculty.fid
having count(fid)=(select count(distinct room) from classroom);


by

@srestha

Tables given in book :
Class( name: string, meets_at: time, room: string, fid: integer)
Faculty (fid: integer, fnarne: string, deptid: integer)

I assumed all rooms are utilised ( there is no NULL room ).

I assumed all rooms are utilised

there may be some room, where no class has taught, like store room

@srestha

oh. More than one class can be taken in a room (at different times ) and there is meets_at (Class start time) and fid so i thought that all rooms are for teaching purposes only. I will think of a different solution then and update the answer.

You have missed the case in which same faculty teaches more than one topic in the same room at different times.

Example:

 name fid room DS-Theory 7 1105 Algo-Theory 7 1105

According to your query, faculty name with fid=7 will also come into the result but it should not.

So taking the above case into consideration, I think below SQL query should be the correct one:

SQL Query

 select fname from faculty NATURAL JOIN classroom group by faculty.fid having count(distinct room)=(select count(distinct room) from classroom);

Please correct, If I am mistaken.