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:
- Find the Roll of all students who are taught by teacher "Raman".
- Delete all students who are from Biratnagar.
- Increase the fee of students who are from Kathmandu by 2%.
- Find the name of teacher who lives in Kathmandu and get salary greater than Rs. 100000.
- Change the name of student to Sushila whose roll number is 10.
A: Relational Algebra queries for Students, Teachers and Teaches: 63
i. Find the Roll of all students who are taught by teacher "Raman".
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 )