GATE CSE 1997 | Question: 76-b

Question

Consider the following relational database schema:

• EMP (eno name, age)
• PROJ (pno name)
• INVOLVED (eno, pno)

EMP contains information about employees. PROJ about projects and involved about which employees involved in which projects. The underlined attributes are the primary keys for the respective relations. 

State in English (in not more than 15 words) 

What the following relational algebra expressions are designed to determine 

1. $\Pi_{eno}(\text{INVOLVED}) - \Pi_{eno} ((\Pi_{eno}(\text{INVOLVED}) \times \Pi_{pno}(\text{PROJ}))-\text{INVOLVED})$

2. $\Pi_{age}(\text{EMP})-\Pi_{age} (\sigma _{ E.age<Emp.age}( (\rho E(\text{EMP}) \times \text{EMP})) $

(Note: $\rho E(\text{EMP}) $ conceptually makes a copy of $\text{EMP}$ and names it $E$ ($\rho$ is called the rename operator))

Answer 1

1.  $\Pi_{eno}(INVOLVED)$$-\Pi_{eno}((\Pi_{eno}(INVOLVED)\times \Pi_{pno}(PROJ)−INVOLVED)$

   • $\Pi_{eno}(INVOLVED)-$ All employees involved in projects $\quad \to (A)$
   • $\Pi_{eno}((\Pi_{eno}(INVOLVED)\times \Pi_{pno}(PROJ)−INVOLVED)-$ gives all employee who are not involved in at least one project. $\quad \to (B)$
   • $A-B =$ employee No. of employees involved on the all project. (Division Operator)

---

2.  $\Pi_{age}(EMP)−\Pi_{age}(σ_{Eage<EMP.age}(ρE(EMP)\times EMP))$

   • $\Pi_{age}(EMP)-$ Age of all employees $\quad \to (C)$
   • $\Pi_{age}(σ_{Eage<EMP.age}(ρE(EMP)\times EMP)) -$ Employees who have age less than at least one other employee $\quad \to (D)$
   • $C- D =$ Maximum of all ages of employees.

Comments

TYPO-

 πage(EMP)−Πage(σEage<EMP.age(ρE(EMP)×EMP))πage(EMP)− Age of all employees →(C)   

should be 

 πage(EMP)- Age of all employees →(C) 

---

@Prashant sir,

In second query Table EMP is renamed to E but attributes are still (emp name,age)

So after  join =>  | emp_name | age | emp_name | age |

In D part how to you take which age is Projected ???

---

I didn't get the solution of ii part.

(ii) 

$=π_{age}(EMP)−π_{E.age}(E.age<EMP.age(ρE(EMP)×EMP))$

$π_{E.age}(E.age<EMP.age(ρE(EMP)×EMP))$ : Returns all ages except the youngest one

$=π_{age}(EMP)$ : All Ages

Their difference will be: All ages - (all ages except the youngest one) = Youngest Age

There overall query returns the youngest age.

---

chauhansunil20th $\Pi_{age} (σ_{Eage<EMP.age} (ρE(EMP)\times EMP))$  ~> Employees who have age less than at least one other employee MEANS return ALL employee except the one who's age is not less than any one MEANS all employee except the oldest one

---

@ register_user_19 yes, you're correct.

---

In the second part, projection operator selects the age, based on which table's age(E or Emp) is selected, the answer could change from max to min, but since age's table isnt mentioned, isnt this question ambigous?

---

@shreejeetp

Even I had the same doubt.

If E.age is selected then answer is max of ages.

If Emp.age is selected then answer is min of ages.

---

@rohith1001 it wouldn't be min of ages if it is EMP.age. It would be rather be all the ages which are not max.

---

Lets take an example:

Age

1

2

3

$\Pi _{E.age}(\sigma_{E.age<Emp.age}((\rho E(EMP) \times EMP))$ would give ages which is lesser than atleast one other employee's age i;e

Age

1

2

$\Pi _{Emp.age}(\sigma_{E.age<Emp.age}((\rho E(EMP) \times EMP))$ would give ages which is greater than atleast one other employee's age i;e

Age

2

3

Subtract the results from the emp table we get max(age) when we have E.age and min(age) when we have Emp.age.

---

Yes, you're right. EMP.age would indeed lead to min age.

Thanks! (Y)

---

In 1st :

Πeno(INVOLVED)−Πeno((Πeno(INVOLVED)×Πpno(PROJ)−INVOLVED)

Can someone pls correct the brackets?

---

Mitali gupta corrected

---

Part (i) is incorrect. Suppose an employee is not involved in any project, he/she does not have an entry in INVOLVED table. As B in your answer does not include such employees, the output also contains employees who are not involved in any project. Please correct me if I am wrong.

---

i also think so

---

Can some one give an example for the first part, I could not figure it out….