edited by
5,431 views
32 votes
32 votes

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.
edited by

5 Answers

45 votes
45 votes

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

edited by
21 votes
21 votes
  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
edited by
2 votes
2 votes
SELECT sdegree Degree,AVG(salary) AvgSalary
FROM Student S
INNER JOIN Offer O
	ON S.scrollno = O.scrollno
GROUP BY sdegree
HAVING COUNT(*) > 5

Related questions

33 votes
33 votes
2 answers
1
Kathleen asked Sep 15, 2014
5,599 views
The following table refers to search items for a key in $B$-trees and $B^+$ trees.$$\begin{array}{|ll|ll|} \hline & \textbf {B-tree} & & \textbf {B}^+\text{-tree} \\\hl...