Question

Given the following schema, write the Relational Algebra and SQL statements for the given conditions:Depositor (customer_name, account_number) Borrower (customer_name, loan_number) Loan ( branch_name, loan_number, amount) Account (branch_name, account_number, balance) Branch(branch_name, branch_city, assets) Customer (customer_name, customer_street, customer_city) 1. Find all the customers who have a loan and an account 2. Find the minimum account balance at the Downtown branch. 3. Find the number of tuples in the customer relation. 4. Find the names of all the account numbers and the branch names whose balance is over BD1000. 5. Find the sum of the loans whose branch name is Mianus. 6. Find the number of customers who have a loan. 7. Find the maximum amount of loan taken by a customer. 8. Find the names of the branches and their branch cities whose assets are greater than 50000 and less than 80000.

Answer 1

Solution:

1: Find all the customers who have a loan and an account

Relational Algebra :   ∏_{customer_name} (Borrower) ∩ ∏_{customer_name} (Depositor)

SQL: select distinct customer_name from Borrower as b1
where exists (select customer_name from Depositor as b2
where b1.customer_name = b2.customer_name);

2: Find the minimum account balance at the Downtown branch.

Answer :

Relational Algebra : g_min(balance)(σ_{branch_name="Downtown"}(Account))

SQL: select min(balance) from Account where branch_name="Downtown";

3 : Find the number of tuples in the customer relation

Answer: Relation Algebra : g_count(*)(Customer)

SQL select count(*) from Customer ;

4: Find the names of all the account numbers and the branch names whose balance is over BD1000.

Answer: Relationall Algebra : ∏customer_name, branch_name( σ_balance>1000(DepositorAccount) )

SQL : Select customer_name, branch_name

From Depositor natural join Account Where balance>1000;

5 : Find the sum of the loans whose branch name is Mianus.

Relational Alegbra :  g_sum(amount)(σ_{branch_name="Mianus"} (Loan))

SQL : select sum(amount) from Loan where branch_name="Mianus" ;

6:  Find the number of customers who have a loan .

Answer :

Relational Algebra : g_{count(customer_name)}(borrowerloan)

SQL: select distinct count(customer_name) from borrower natural join loan ;

7: Find the maximum amount of loan taken by a customer.

Relational Algebra : g_max(amount)(Loan Customer)

SQL: select max(amount) from loan natural join customer ;

8: Find the names of the branches and their branch cities whose assets are greater than 50000 and less than 80000.

Answer : Relational Algebra :  ∏_{branch_name,branch_city} (σ_{asset>50000 AND asset< 80000}(Branch))

SQL : select branch_name,branch_city where asset between 50000 and 80000;

Hope you understood the answers for any queries please write in the coment section.