please write both relational algebra expression and SQL for each of the followin

Question

please write both relational algebra expression and SQL for each of the following queries.

Branch(branch_name, branch_city, assets)

Customer(customer_name, customer_street, customer_city)

Loan(loan_number, branch_name, amount)

Borrower(customer_name, loan_number)

Account(account_number, branch_name, balance)

Depositor(customer_name, account_number)

List all the customers who live in NY together with their account balances.

2. List all branches whose average account balances are more than $10,000.

3. List the customers who have account(s) in a branch city that is different where he lives.

4. List the customers who have both loan and account in the same branch.

5. List the customers who have both loan and account in the bank.

Explanation / Answer

(1)

SELECT c.customer_name,a.balance FROM Customer c,Depositor d,Account a

WHERE c.customer_name=d.customer_name AND d.account_number = a.account_number

AND c.customer_city ='NY';

c.customer_name,a.balance(c.customer_city='NY'  ((c(Customer) c.customer_name = d.customer_named(Depositor)) d.account_number = a.account_numbera(Account)))

(2)

SELECT branch_name

FROM Account GROUP BY branch_name

HAVING AVG(balance) > 10000;

branch_name(avg(balance)>10000 (branch_name(Account)))

(3)

SELECT DISTINCT customer_name

FROM Customer JOIN Depositor USING(customer_name) JOIN Account USING(account_number) JOIN Branch USING(branch_name)

WHERE customer_city != branch_city;

customer_name(customer_city != branch_city ((c(Customer) c.customer_name = d.customer_named(Depositor)) d.account_number = a.account_numbera(Account)) a.branch_name = b.branch_name b(Branch))

(4)

SELECT DISTINCT c.customer_name

FROM Customer c,Loan l,Borrower b,Account a,Depositor d

WHERE c.customer_name = b.customer_name AND b.loan_number = l.loan_number AND c.customer_name = d.customer_name AND d.account_number = a.account_number

AND l.branch_name = a.branch_name;

customer_name(l.branch_name = a.branch_name (((c(Customer) c.customer_name = b.customer_nameb(Borrower)) b.loan_number = l.loan_numberl(Loan)) c.customer_name= d.customer_name d(Depositor)) d.account_number = a.account_numbera(Account)))

(5)

SELECT DISTINCT c.customer_name

FROM Customer c,Borrower b,Depositor d

WHERE c.customer_name = b.customer_name AND c.customer_name = d.customer_name ;

customer_name((c(Customer) c.customer_name = b.customer_nameb(Borrower))  c.customer_name= d.customer_name d(Depositor))

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.