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

@Jaydipsinh Zala

no , it will be involved on the all projects as INVOLVE table contains emp no of those who are enrolled in atleast 1 project.

---

Yes Harshit you are right .👍

---

@pradeep_reddy I don't think so you are correct as if the employee is not in any project then he/she will not be in Involved table at all. Since we are taking first eno from Involved and then subtracting all the stuff in RHS from that relation intance(i.e., all the employees involved in atleast one project). Therefore you can't get employee IDs, which are not involved at all with any project, in the final answer.

Answer 2

1.

SET of all those ' eno' whos pair with every ' pno ' of PROJ in INVOLVED (table).

INVOLVED (eno, pno) ÷ PROJ (pno)

2.

Maximum of all ages of employees  or  Minimum of all ages of employees. depending on which age we select

 

Comments

we have to consider minimum of all ages of employees. because we didn't rename the age.