branch (branch-name, branch-city, assets)
customer (customer-name, customer-street, customer-city)
account (account-number, branch-name, balance)
loan (loan-number, branch-name, amount)
depositor (customer-name, account-number)
borrower (customer-name, loan-number)
1. (10 points) Imagine that the bank would like to add a trigger named try_to_keep_em that decreases the loan amount
for all loans owed by a given customer by 15% when that customer attempts to withdraw all their money from their
savings account, as an incentive for them to keep their money in the bank. In a Jupyter Notebook, write code in
Python, Java or C to create this trigger on the account table in the database.
2. (10 points) Patricia Richmond lives on NW 40th St. in Brooklyn. As a new customer, she opened a savings
account with the account number A-402 at the Brighton branch containing $1,200 from her recent tax return. She
also received a loan of $5,000, identified by the loan number L-42, from the same branch, as seed capital for a small
business she would like to open. Write code in Python,
that issues the necessary SQL statements on the
bankdb database to reflect these changes.
3. (5 points) Upon leaving the bank, Patricia had an emergency and had to withdraw all the money in her savings
account. Issue one or more SQL statements on the database to reflect this withdrawal. Also, to see if your trigger
worked, issue a query to report the total amount she owes the bank for her loan (L-42). Make sure to use queries to
show the amount in her account before and after the changes were made.
Bonus
Write a procedure named currency_style that accepts a numeric value and a value representing the number of
decimal places and formats the value as currency. For example call currency_style (2300, 2) returns $2,300.00.
Test your procedure via code in Python, Java or C with the amount remaining in Patricia's account and 3 DP as
input.
Hey, Mr. DB Here!
Sorry to show up when you are working.
I just wanted to give you some tips.
For question 2, you will need to
add data to at least 5 tables.
Also, remember you may test your work
in MySQL Workbench before writing the code.
Purchase answer to see full
attachment