Raghuramkrishnan 3e Exercise 5.1 Question no-5 page no-175 SQL

Question

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

Comments

In RA it will be

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

---

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

Answer 1

https://gateoverflow.in/?qa=blob&qa_blobid=16701151949787819818

Comments

where is table??

Answer 2

https://gateoverflow.in/?qa=blob&qa_blobid=5814885073351823572

Answer 3

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);

 

 

Comments

@srestha

Please check this answer.. Is it correct?

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.