in Databases edited by
703 views
1 vote
1 vote

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 Databases edited by
by
703 views

4 Comments

In RA it will be

$\prod _{fname}\left ( Class\bowtie Faculty \right )\div \prod _{name}\left ( Class \right )$
0
0
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)

                        )

            )

0
0
taking table , we need to verify it
0
0

3 Answers

0 votes
0 votes

1 comment

where is table??
0
0
0 votes
0 votes
0 votes
0 votes

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

 

 

4 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 ).

0
0

I assumed all rooms are utilised

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

0
0

@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.

0
0

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.

0
0

Related questions