GATE CSE 2002 | Question: 15

Question

A university placement center maintains a relational database of companies that interview students on campus and make job offers to those successful in the interview. The schema of the database is given below:
$$\begin{array}{|ll|}\hline \text{COMPANY(}\underline{\text{cname}}\text{, clocation)} & \text{STUDENT(}\underline{\text{srollno}}\text{, sname, sdegree)} \\
\text{INTERVIEW(}\underline{\text{cname, srollno}}, \text{idate)} & \text{OFFER(}\underline{\text{cname, srollno}}\text{, osalary)} \\\hline   \end{array}$$
The COMPANY relation gives the name and location of the company. The STUDENT relation gives the student’s roll number, name and the degree program for which the student is registered in the university. The INTERVIEW relation gives the date on which a student is interviewed by a company. The OFFER relation gives the salary offered to a student who is successful in a company’s interview. The key for each relation is indicated by the underlined attributes

1. Write a relational algebra expressions (using only the operators $\bowtie, \sigma, \pi, \cup, -$ ) for the following queries.
   1. List the $rollnumbers$ and $names$ of students who attended at least one interview but did not receive $any$ job offer.
   2. List the $rollnumbers$ and $names$ of students who went for interviews and received job offers from $every$ company with which they interviewed.
2. Write an SQL query to list, for each degree program in which more than $five$ students were offered jobs, the name of the degree and the average offered salary of students in this degree program.

Answer 1

(I will write only useful attributes in relation which are required)
Ex:    INTERVIEW

company name	student roll
A	1
B	1
C	1
A	2
B	2
A	3

OFFER

company name	student roll
A	1
B	1
C	1
A	2

So the student with rolls 1,2,3 interviewed.  Student 1 did sit for all companies, got the job in all companies A,B,C.
Student 2 sat for A,B, got job in A only. Student 3 sat for A,  did not get.


a) Part i) :  

1

2

3

minus

1

2

equals to
 

3

∏scrollno (Interview)  -  ∏scrollno( Offer)

You got the required student's roll numbers but to print their names, store that in Temp and join with Student table.
∏ scrollno,sname ( Temp ⋈  Student)

a) Part ii) :   Those who got interviewed (includes those who got jobs in all,some,none)
Now interviewed - offer = those who did not get jobs or got in some.

B	2
A	3

Now again subtract whatever you got from all students of the interview again

1

2

3

minus

2

3

equals to
 

1

But note that it is not an intersection. You may think.... A-(A-B) so intersection.
But it is not...  We are doing A-B on all tuples.
But the next subtraction is done on a particular attribute. (It became distinct since we focused on it only)

∏scrollno (Interview)  -  ∏scrollno( Interview - Offer)

You got the required student's roll numbers but to print their names, store that in Temp and join with Student table.
∏ scrollno,sname ( Temp ⋈  Student)

b) select s.sdegree,AVG(o.osalary) from Student s,Offer o where s.srollno=o.srollno having count(distinct s.srollno)>5 group by s.sdegree;

Comments

Most clear solution and easy to understand.

But in (b) part it should be count(distinct s.rollno)>5

---

@Ayush Edited. Thank u.

---

in the second part:

∏scrollno (Interview)  -  ∏scrollno( Interview - Offer) 

You should have done it as:

$Temp1<-(π_{cname,srollno}(INTERVIEW))-(π_{cname,srollno}(OFFER))$ // some or none offers

$Temp2<-(π_{srollno}(INTERVIEW)) - (π_{srollno}(TEMP))$$

Now, Temp2 further can be joined with Student to get student names,

 

---

why did you use distinct in the query part?

---

@Ahwan silly but just for perfect answer having clause is used to filter group so having condition should be after group by.  

---

Should be selected as best answer

---

The offer table contains 1,2. Whay in 2nd part of question u wrote 2,3

---

@Deepak Poonia In (ii) part can we use divide operation 
 

---

@JAINchiNMay, In SQL, we do not have Division Operation.

There is no direct way to express division in SQL. We can write this query, but to do so, we will have to express our query through double negation and existential quantifiers.

---

@Deepak Poonia we have division operator in relational algebra?

---

@JAINchiNMay

Yes, we have Division Operator in Relational Algebra. It is not a basic operator as it can be written in form of other basic operations. But we have Operator $\div$ in relational algebra.

Answer 2

1. Answer part (i)
             
   $\pi$ $_{srollno,sname}$ ( $\sigma$ $_{student.srollno=interview.srollno}$  ( Student $\Join$ Interview ) )  −    $\pi$        $_{srollno,sname}$  ( $\sigma$ $_{srollno = offer.srollno}$  ( Offer $\Join$ Student ))
   
   Answer part (ii)
   
   Temp = $\pi$ $_{srollno,cname}$ (Interview ) − $\pi$ $_{srollno,cname}$ (Offer)
   Temp will store those students roll no who where interviewed but still did not get the job atleast in some companies.
   Temp1 = $\pi$ $_{srollno}$ {$\sigma$ $_{student.srollno=Interview.srollno \wedge student.srollno=offer.srollno \wedge offer.cname=Interview.cname }$ (Student $\Join$ Interview $\Join$ Offer) }
   
   Temp1 will contain all those students who appeared for interview into different companies and their interview turned into offer letters .
   Answer: Temp1  − $\pi$ $_{srollno}$ (Temp)
   
   This will result in students who got the job in all the companies they sat for interview .
    
2. Select sdegree , avg(salary) from student , Offer where Student.srollno = Offer.srollno group by sdegree having count(distinct student.srollno) > 5

Comments

$Part \ a-$
$i)  TEMP \leftarrow \pi_{srollno}(INTERVIEW)-\pi_{srollno}(OFFER)\\RESULT \leftarrow \pi_{srollno,sname}(Student \Join TEMP)$

$ii)  TEMP \leftarrow \pi_{srollno}(INTERVIEW)- \pi_{srollno}[\pi_{cname,srollno}(INTERVIEW)-\pi_{cname,srollno}(OFFER)]\\RESULT \leftarrow \pi_{srollno,sname}(Student \Join TEMP)$

---

I think simpler solution for A can be Project cname,srollno(Interview - (Interview JOIN Offer))

First we will join all students who have given interview and received job offer then we can subtract all students who received job offer from all students who have given interview and at the end we project the results!!

 

Please tell me if it's correct

---

@Shaik Masthan @Riya Roy(Arayana)

in answer part 1 a 

why we are not subtracting only the projection of sroll from offer relation from the join of student and interview.

 

(i.e. why are we taking the join of offer and student when we can get the sroll directly from offer relation)

Answer 3

SELECT sdegree Degree,AVG(salary) AvgSalary
FROM Student S
INNER JOIN Offer O
	ON S.scrollno = O.scrollno
GROUP BY sdegree
HAVING COUNT(*) > 5

Answer 4

PLEASE CORRECT ME IF I'M WRONG.