The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+14 votes
942 views

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:

COMPANY(cname, clocation) STUDENT (srollno, sname, sdegree)
INTERVIEW (cname, srollno, idate) OFFER(cname, srollno, osalary

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.
asked in Databases by Veteran (59.6k points)
edited by | 942 views

3 Answers

+14 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
answered by Loyal (7.4k points)
edited ago by
0

Arayana , for part B) they gave to list, for each degree program in which more than five students were offered jobs

As same student can get more than one job count(distinct studen.srollno) >5 should be there right.

because aggregate functions won't eliminate duplicates.

0
yes i think you r correct . edited.
0
Arjun sir please check part ii of A.
0
NICE ............
0
In a) i) Can we not take the difference of INTERVIEW and OFFER and then take join of this with STUDENT?
0

@Milicevic3306 @Milicevic3306

i think for second part of a answer is unnecessarily complicated  

( Interview ⋈ Offer ) as it is natural join , it will join on cname and srollno. 

thus will give us cname , srollno , idate , osalary of those students who got interviewed and got atleast one job offer.

then do

 

 π srollno,sname(student )⋈( Interview ⋈ Offer ) .will give required result .

Also did you notice that while doing 

Temp1  − π srollno (Temp) 

we are subtracting(difference ) one column from two column 

+1
$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)$
0
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
+10 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;

answered by Boss (11k points)
edited by
0
Most clear solution and easy to understand.

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

@Ayush Edited. Thank u.

+1 vote
SELECT sdegree Degree,AVG(salary) AvgSalary
FROM Student S
INNER JOIN Offer O
	ON S.scrollno = O.scrollno
GROUP BY sdegree
HAVING COUNT(*) > 5
answered by (213 points)

Related questions



Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true

40,855 questions
47,522 answers
145,898 comments
62,279 users