Relational Algebra Expressions for Relations Account, Depositor, Branch

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:

  1. Find the name of all depositors who have opened at 'XYZ' branch.
  2. Delete all accounts at branches located in Kathmandu.
  3. Insert information in the database specifying that Jhon has Rs 13,000 in account 006000134T at the Lalitpur branch.
  4. Increase the interest by 2% to the account with a balance over Rs 10,000.
  5. Change the name of the customer to Sushila whose account number is 005000142T.

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.

  1. Add the branch Lalitpur first (assuming that the Lalitpur branch is not already present).
  2. Add an account with acc_number 006000134T (assuming it doesn't already exist).
  3. 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.

  1. Increase balance for those who have a balance over 10,000 using generalized projection. Store the tuples in a temporary table Temp1.
  2. List the tuples whose balance is less than or equal to 10,000. Store it in another temporary table Temp2.
  3. 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.

  1. Using generalized projection, change the name of the customer to Sushila whose acc_number is  '005000142T'. Store the tuple in a temporary table Temp1.
  2. List the tuples whose acc_number is NOT '005000142T'. Store it in another temporary table Temp2.
  3. 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))

 

 

Asked in Year
2021
Course
BBM
University
TU