edited by
6,112 views
12 votes
12 votes

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. 

What is the relational algebra expression containing one or more of $\{ \sigma, \pi, \times, \rho, -\}$ which is equivalent to SQL query.

  select eno  from EMP|INVOLVED where EMP.eno=INVOLVED.eno  and INVOLVED.pno=3
edited by

3 Answers

Best answer
25 votes
25 votes
$\pi_{eno}\left( \sigma_{\text{EMP.eno}=\text{INVOLVED.eno}\wedge \text{INVOLVED.pno=3}} (\text{EMP} \times \text{INVOLVED})\right)$
edited by
6 votes
6 votes

i)πeno(EMP ⨝EMP.eno=INVOLVED.eno  and INVOLVED.pno=3INVOLVED)
   
                     
projects employee involved on some project  
                            ↓

ii)πeno(INVOLVED)−πeno((πeno(INVOLVED)×πpno(PROJ)−INVOLVED)
               ↑                  

         All empolyee involved in the projects
  (Projects All empolyee involved in the projects)-( projects employee involved on some project)=
       
Projects employee involved on the all project


                          Employee except max age
                   ↓

iii)πage(EMP)−πage(σEage<EMP.age(ρE(EMP)×EMP))
      
   ↑   
All emplyee

(All emplyee) - ( Employee except max age) = Employee with maximum age

3 votes
3 votes

A)σpno=3(emp΀ involved)

B)i)it returns eno of employees incolved in all project

ii)it returns  max age (ie age of senior most employee)

Related questions