GATE CSE 2001 | Question: 21-a

Question

Consider a relation examinee (regno, name, score), where regno is the primary key to score is a real number.

Write a relational algebra using $( \Pi, \sigma, \rho, \times)$ to find the list of names which appear more than once in examinee.

Comments

Can anyone please post SQL query for this?

---

equivalent SQL query :-

select e1.name

from examine e1

where exist ( select *

              from examine e2

              where e1.regno ≠ e2.regno and e1.name = e2.name );

---

Thank you

Answer 1

$\pi_{\text{exm1.name}}(σ_{(\text{exm1.regno} \neq \text{examinee.regno}) \wedge (\text{emp1.name} = \text{emp2.name}) })(ρ _{\text{exm1}}(\text{examinee}) \times  \text{examinee}) $

Comments

@Devesh_Kumar

The query is designed only to select the tuples where registration numbers are not same, but name is same.

So, it'll obviously give correct result for two different persons with same name.

---

What is emp1 and emp2 in ur answer

---

Hi @Ayush Upadhyaya@Shaik Masthan
How above RA will ensure that two person having same name appear more than once in examination?

Answer 2

1) $\Pi_{name}\left ( \sigma _{count(cnt)>1}\left ( _{name}g_{count(regno)\ as \ cnt}\left ( examinee \right ) \right ) \right )$

2) $SELECT_{regno}FROM\;examinee\; HAVING\;(score>avg(score))$

$\Pi _{regno}\left ( \sigma _{score>avg(score)}\left ( examinee \right ) \right )$

3) $SELECT_{centr\_code}FROM\;examinee\;join\;appears WHERE\;(score>80)$

$\Pi _{center\_code}\left ( \sigma _{score>80}\left ( examinee\Join appears \right ) \right )$

Comments

Having can't be used without group by, this way in the second query.

---

@Sheshang 

I dont think so standard relational algebra allows aggregate functions.

---

2.

 

​​​​​​