Consider the relational database:
- Account(acc_number, branch_name, balance)
- Depositor(cust_name, acc_number)
- Branch(branch_name, city)
Write the Relational Algebra for the following:
- Find the name of all depositors who have opened at 'XYZ' branch.
- Delete all accounts at branches located in Kathmandu.
- Insert information in the database specifying that Jhon has Rs 13,000 in account 006000134T at the Lalitpur branch.
- Increase the interest by 2% to the account with a balance over Rs 10,000.
- Change the name of the customer to Sushila whose account number is 005000142T.
A: Relational Algebra for Relations Account, Depositor, Branch - 41
a. Find the name of all depositors who have opened at 'XYZ' branch.
Attribute to find: name of all depositors. ie; cust_name (from relation Depositor)
Given condition: who have opened at 'XYZ' branch. ie; branch_name = 'XYZ' (from relation Account)
The given condition and attribute to find are from different relations. Thus natural join is needed.
π cust_name ( σ branch_name = 'XYZ' ( Account ⨝ Depositor ) )
b. Delete all accounts at branches located in Kathmandu.
Tuple to delete is from the Account table.
Given condition (branches located in Kathmandu ie; city = 'Kathmandu') is present in another table Branch.
Hence natural join is required.
Account ← Account - π acc_number, branch_name, balance (σ city = 'Kathmandu' ( Branch ⨝ Account ))
c. Insert information in the database specifying that Jhon has Rs 13,000 in account 006000134T at the Lalitpur branch.
- Add the branch Lalitpur first (assuming that the Lalitpur branch is not already present).
- Add an account with acc_number 006000134T (assuming it doesn't already exist).
- Add Depositor John's record.
Branch ← Branch ∪ {'Lalitpur', 'Kathmandu'}
Account ← Account ∪ {'006000134T', 'Lalitpur', 13000}
Depositor ← Depositor ∪ {'John', '006000134T'}
d. Increase the interest by 2% to the account with a balance over Rs 10,000.
- Increase balance for those who have a balance over 10,000 using generalized projection. Store the tuples in a temporary table Temp1.
- List the tuples whose balance is less than or equal to 10,000. Store it in another temporary table Temp2.
- Combine Temp1 and Temp2 using Union operation and assign them to the Account table.
Temp1 ← π acc_number, branch_name, balance * 1.02 ( σ balance > 10000 (Account))
Temp2 ← σ balance ≤ 10000 (Account)
Account ← Temp1 ∪ Temp2
OR we can combine all 3 steps in one statement.
Account ← π acc_number, branch_name, balance * 1.02 ( σ balance > 10000 (Account)) ∪ ( σ balance ≤ 10000 (Account))
e. Change the name of the customer to Sushila whose account number is 005000142T.
- Using generalized projection, change the name of the customer to Sushila whose acc_number is '005000142T'. Store the tuple in a temporary table Temp1.
- List the tuples whose acc_number is NOT '005000142T'. Store it in another temporary table Temp2.
- Combine Temp1 and Temp2 using Union operation and assign them to the Account table.
Temp1 ← π cust_name = 'Sushila', acc_number ( σ acc_number = '005000142T' (Depositor))
Temp2 ← σ acc_number ≠ '005000142T' (Depositor)
Depositor ← Temp1 ∪ Temp2
OR we can combine all 3 steps in one statement.
Depositor ← π cust_name = 'Sushila', acc_number ( σ acc_number = '005000142T' (Depositor)) ∪ ( σ acc_number ≠ '005000142T' (Depositor))