2. (40 points) The following database schema is part of a bank database Branch(bname, bcity, assets) Account(accountNo, bname, balance) Client(cID, cname, cstreet, c_city) Loan(loanNo, bname, amount) Borrower(cID, loanNo) Depositor(cID, accountNo) . Each bank branch is identified by the value of the attribute bname. The relation "Account" describes the account information each bank branch has. Each bank branch owns one or more accounts and an account number is unique across all bank branches . Each client is identified by the value of the attribute clD The relation "Loan" describes the loans approved by the branches. Each bank branch has one or more loans and a loan number is unique across all bank branches The relation "Borrower" describes the association between the clients and the loans they have. Each client can have one or more loans and each loan can belong to one or more clients The relation "Depositor, describes the association between the clients and the accounts they have. Each client can have one or more accounts and each account can belong to one or more clients. (a) Find the primary key and foreign key (if any) for each relation. (b) Pick one foreign key between two relations of your choice. Give one example of inserts and one example of deletes to these two relations, which can cause a violation of the referential integrity constraint. 3. (50 points) Using the same relations in Problem 2, provide the SQL statements for the following queries: (a) Create the tables for the "Branch" and "Account" relations. You can decide the data types and sizes of the attributes in these two relations as long as they are meaningful Please make sure to include all the necessary constraints. (b) Find the ID and names of the clients who live in the street containing the substring "Apt. #B-100", arranged in descending order of the client IDs (c)List the details of all loans approved by "Chase Bank" or "CitiBank" (d) Find all the account numbers of the client whose cID is c12345. 2. (40 points) The following database schema is part of a bank database Branch(bname, bcity, assets) Account(accountNo, bname, balance) Client(cID, cname, cstreet, c_city) Loan(loanNo, bname, amount) Borrower(cID, loanNo) Depositor(cID, accountNo) . Each bank branch is identified by the value of the attribute bname. The relation "Account" describes the account information each bank branch has. Each bank branch owns one or more accounts and an account number is unique across all bank branches . Each client is identified by the value of the attribute clD The relation "Loan" describes the loans approved by the branches. Each bank branch has one or more loans and a loan number is unique across all bank branches The relation "Borrower" describes the association between the clients and the loans they have. Each client can have one or more loans and each loan can belong to one or more clients The relation "Depositor, describes the association between the clients and the accounts they have. Each client can have one or more accounts and each account can belong to one or more clients. (a) Find the primary key and foreign key (if any) for each relation. (b) Pick one foreign key between two relations of your choice. Give one example of inserts and one example of deletes to these two relations, which can cause a violation of the referential integrity constraint. 3. (50 points) Using the same relations in Problem 2, provide the SQL statements for the following queries: (a) Create the tables for the "Branch" and "Account" relations. You can decide the data types and sizes of the attributes in these two relations as long as they are meaningful Please make sure to include all the necessary constraints. (b) Find the ID and names of the clients who live in the street containing the substring "Apt. #B-100", arranged in descending order of the client IDs (c)List the details of all loans approved by "Chase Bank" or "CitiBank" (d) Find all the account numbers of the client whose cID is c12345