edited by
14,715 views
43 votes
43 votes

Given the relations

  • employee (name, salary, dept-no), and
  • department (dept-no, dept-name,address),

Which of the following queries cannot be expressed using the basic relational algebra operations $\left(\sigma, \pi,\times ,\Join, \cup, \cap,-\right)$?

  1. Department address of every employee
  2. Employees whose name is the same as their department name
  3. The sum of all employees' salaries
  4. All employees of a given department
edited by

3 Answers

Best answer
50 votes
50 votes

Possible solutions, relational algebra:

(a) Join relation using attribute dpart_no.

  • $\Pi_{\text{address}} (\text{emp} \bowtie  \text{depart})$ OR 
  • $\Pi_{\text{address}} (\sigma_{ \text{emp}.\text{depart_no.}=\text{depart}.\text{depart_no}.} (\text{emp} \times \text{depart}))$

(b)

  • $\Pi_{\text{name} } (\sigma_{\text{emp}.\text{depart_no.}=\text{depart}.\text{depart_no.} \wedge \text{emp.name} = \text{depart}.\text{depart_name}} (\text{emp} \times \text{depart}))$ OR
  • $\Pi_{\text{name}} (\text{emp} \bowtie _{ \text{ emp.name} = \text{depart}.\text{depart_name}} \text{depart})$

(d) Let the given department number be $x$ 

  • $\Pi_{\text{name}} (\sigma_{ \text{emp}.\text{depart_no.}=\text{depart}.\text{depart_no.} \wedge \text{depart_no.} = x} (\text{emp} \times \text{depart}))$ OR 
  • $\Pi_{\text{name}} (\text{emp} \bowtie _{\text{ depart_no}.=x} \text{depart}) $

(c) We cannot generate relational algebra of aggregate functions using basic operations. We need extended operations here. 

Option (c).

edited by
18 votes
18 votes
aggregate functions are not supported by relational algebra ie. sum,average,maximum,minimum and count.So c is the answer
–5 votes
–5 votes
(b)

we need to do a self join, for which we need rename operator(row symbol)
Answer:

Related questions

38 votes
38 votes
1 answer
1
Kathleen asked Sep 23, 2014
16,787 views
Consider the join of a relation $R$ with a relation $S$. If $R$ has $m$ tuples and $S$ has $n$ tuples then the maximum and minimum sizes of the join respectively are$m+n$...
30 votes
30 votes
5 answers
2
Kathleen asked Sep 14, 2014
14,524 views
Given the following relation instance.$$\begin{array}{|l|l|}\hline \text{X} & \text{Y} & \text{Z} \\\hline \text{1} & \text{4} & \text{2} \\ \text{1} & \text{5} & \te...