Relational algebra expressions for Schemas ( Employee, Department, Students, Project )

Consider the following relational database:

  • Employee(empid, gender, name, dob, salary, address, deptno, start_date)
  • Department(dpetno, name)
  • Students(std_id, gender, name, dob, projectno, deptno)
  • Project(projectno, name, location)


Write Relational algebra expression for the following:

  1. Find employee name and address who works in "xyz company".
  2. Delete all employees whose salary is greater than Rs. 1,00,000.
  3. Update department name to "SitaGroup" where student "Sita" is involved.
  4. Find the average salary of all employees.
  5. Insert student record named "Gopal Shrestha" for department "Ram Group".

a. Find employee name and address who works in "xyz company".

π E.name, E.address ( σ D.name = "xyz company" ( ρ E (Employee) ⨝ ρ D (Department) ) )

 

b. Delete all employees whose salary is greater than Rs. 1,00,000.

Temp1 ← σ salary > 100000 ( Employee )
Employee ← Employee - Temp1


OR

Employee ← Employee - σ salary > 100000 ( Employee )

 

 

c. Update department name to "SitaGroup" where student "Sita" is involved.

Temp1 ← π D.deptno, D.name = "SitaGroup" ( σ s.name = "Sita" ( ρ D (Department) ⨝ ρ S (Students) ) )
Temp2 ← π D.deptno, D.name ( σ S.name ≠ "Sita" ( ρ D (Department) ⨝ ρ D (Students) ) )
Department ← Temp1 ∪ Temp2

 

d. Find the average salary of all employees.

𝓖 avg ( salary ) ( Employee )

 

e. Insert student record named "Gopal Shrestha" for department "Ram Group".

Department ← Department ∪ { 'D06', 'Ram Group' }
Students ← Students ∪ { 'S105', 'Male', 'Gopal Shrestha', '1997-04-06', 'P05', 'D06' }

 

Note: For queries/expressions related to the above relations, while accessing column names, it is necessary to access them with relation names. For eg., instead of only 'name', specify either Students.name or Department.name. This is because some of the column names are the same across multiple tables. Here, in questions a and c, we are using Rename(ρ) so that we can access the attributes with short names d, e and s for Department, Employee and Students. This is not mandatory.