The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+14 votes
752 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 (scrollno, 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 operator $\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.4k points)
retagged | 752 views

3 Answers

+13 votes
Answer a ) part i)

$\prod$ $_{srollno,sname}$ ( $\sigma$ $_{student.srollno=interview.srollno}$  ( Student $\Join$ Interview ) )  −    $\prod$ $_{srollno,sname}$  ( $\sigma$ $_{srollno = offer.srollno}$  ( Offer $\Join$ Student ))

Answer part ii )

Temp = $\prod$ $_{srollno,cname}$ (Interview ) − $\prod$ $_{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 = $\prod$ $_{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  − $\prod$ $_{srollno}$ (Temp)

This will result in students who got the job in all the companies they sat for interview .

Answer part b )

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.2k points)
edited 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?
+5 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 (10.3k points)
edited by
+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

35,487 questions
42,746 answers
121,457 comments
42,138 users