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

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….