Relational Algebra queries for Students, Teachers and Teaches

Consider the relational database:

  • Students (Roll, SName, SAddress, SContact, SFee)
  • Teachers (TID, TName, TSalary, TAddress)
  • Teaches (Roll, TID)

 

Write the relational algebra for the following:

  1. Find the Roll of all students who are taught by teacher "Raman".
  2. Delete all students who are from Biratnagar.
  3. Increase the fee of students who are from Kathmandu by 2%.
  4. Find the name of teacher who lives in Kathmandu and get salary greater than Rs. 100000.
  5. Change the name of student to Sushila whose roll number is 10.

i. Find the Roll of all students who are taught by teacher "Raman".

π Roll ( σ TName = 'Raman' ( Teaches Teachers ) )

 

ii. Delete all students who are from Biratnagar.

Temp1 σ SAddress = 'Biratnagar' ( Students )
Students Students - Temp1

 

OR

Students Students - σ SAddress = 'Biratnagar' ( Students )

 

iii. Increase the fee of students who are from Kathmandu by 2%.

Temp1 π Roll, SName, SAddress, SContact, SFee = SFee * 1.02 (σ SAddress = 'Kathmandu' ( Students ))
Temp2 σ SAddress ≠ 'Kathmandu' ( Students )
Students Temp1 Temp2

 

OR

Students π Roll, SName, SAddress, SContact, SFee = SFee * 1.02 (σ SAddress = 'Kathmandu' ( Students )) σ SAddress ≠ 'Kathmandu' ( Students )

 

iv. Find the name of teacher who lives in Kathmandu and get salary greater than Rs. 100000.

π TName ( σ TAddress = 'Kathmandu' ^ TSalary > 100000 ( Teachers ) )

 

v. Change the name of student to Sushila whose roll number is 10.

Temp1 π Roll, SName = 'Sushila', SAddress, SContact, SFee ( σ Roll = 10 ( Students ) )
Temp2 σ Roll ≠ 10 ( Students )
Students Temp1 Temp2

 

OR

Students π Roll, SName = 'Sushila', SAddress, SContact, SFee ( σ Roll = 10 ( Students ) ) σ Roll ≠ 10 ( Students )

Asked in Year
2018
Course
BBM
University
TU