Question

1. Consider the bank database with the relational schema below to write the SQL commands for each of the following queries: branch (branch_name, branch_city, assets) customer (ID, customer_name, customer_street, customer_city) loan (loan_number, branch_name, amount) borrower (ID, loan_number) account (account_number, branch_name, balance) depositor (ID, account_number, d_date) a) Find the name of each branch located in "Makurdi". b) Find the ID of each borrower who has a loan in "Suleja" c) Find all the account numbers that received a deposit in Makurdi branch on 20th June, 2022. d) Find the name of all customers who have taken a loan and the loan amount they have taken. e) Write the Data Definition Language (DDL) Command to create the "loan" table. f) Write the SQL command to update all account balances by 1%. g) Write the SQL command to insert a new customer's record. (Use your own specific ID, name, and any other dummy value as necessary to answer this question correctly). h) Write the equivalent Relational Algebra Expression for Question 1a. i) Write the equivalent Relational Algebra Expression for Question 1b. j) Write the equivalent Relational Algebra Expression for Question 1c. k) Is the ER-diagram below violating any of the rules of normalization? Justify your answer by explaining how there is no violation or identify the rule it violating and how can it can be resolved. ACCOUNT #account_number balance date_opened bank_location BANK #bank_number name

          1. Consider the bank database with the relational schema below to write the SQL commands for each of the
following queries:
branch (branch_name, branch_city, assets)
customer (ID, customer_name, customer_street, customer_city)
loan (loan_number, branch_name, amount)
borrower (ID, loan_number)
account (account_number, branch_name, balance)
depositor (ID, account_number, d_date)
a) Find the name of each branch located in "Makurdi".
b) Find the ID of each borrower who has a loan in "Suleja"
c) Find all the account numbers that received a deposit in Makurdi branch on 20th June, 2022.
d) Find the name of all customers who have taken a loan and the loan amount they have taken.
e) Write the Data Definition Language (DDL) Command to create the "loan" table.
f) Write the SQL command to update all account balances by 1%.
g) Write the SQL command to insert a new customer's record. (Use your own specific ID, name,
and any other dummy value as necessary to answer this question correctly).
h) Write the equivalent Relational Algebra Expression for Question 1a.
i) Write the equivalent Relational Algebra Expression for Question 1b.
j) Write the equivalent Relational Algebra Expression for Question 1c.
k) Is the ER-diagram below violating any of the rules of normalization? Justify your answer by
explaining how there is no violation or identify the rule it violating and how can it can be
resolved.
ACCOUNT
#account_number
*balance
*date_opened
*bank_location
BANK
#bank_number
*name

1. Consider the bank database with the relational schema below to write the SQL commands for each of the
following queries:
branch (branchname, branchcity, assets)
customer (ID, customername, customerstreet, customercity)
loan (loannumber, branchname, amount)
borrower (ID, loannumber)
account (accountnumber, branchname, balance)
depositor (ID, accountnumber, ddate)
a) Find the name of each branch located in "Makurdi".
b) Find the ID of each borrower who has a loan in "Suleja"
c) Find all the account numbers that received a deposit in Makurdi branch on 20th June, 2022.
d) Find the name of all customers who have taken a loan and the loan amount they have taken.
e) Write the Data Definition Language (DDL) Command to create the "loan" table.
f) Write the SQL command to update all account balances by 1%.
g) Write the SQL command to insert a new customer's record. (Use your own specific ID, name,
and any other dummy value as necessary to answer this question correctly).
h) Write the equivalent Relational Algebra Expression for Question 1a.
i) Write the equivalent Relational Algebra Expression for Question 1b.
j) Write the equivalent Relational Algebra Expression for Question 1c.
k) Is the ER-diagram below violating any of the rules of normalization? Justify your answer by
explaining how there is no violation or identify the rule it violating and how can it can be
resolved.
ACCOUNT
#accountnumber
*balance
*dateopened
*banklocation
BANK
#banknumber
*name

Added by Denise M.

Question

Please give Ace some feedback