$1.$ You are required to create a relational MySQL database $($Use the recommended tools available in school$)$ to capture details of the members of a club. The database should have two related tables for storing member details $($member number, name, address, date joined, account balance, account bill$),$ and transaction $($transaction number, member number, transaction amount, and transaction type$).$ The transaction type should either be a payment or a bill to the account. Name the two tables with your student ID number as follows:

$$ member$\_$studID

$$ transaction$\_$studID

When a new member joins the club they are required to place a deposit of an amount equivalent to your student number $($e$.$g$.$ Ksh $654321)$ with the club. Member details are stored in the member table and the account details updated with the deposit. When a member makes use of a service at the club they can pay immediately or later. A member can also deposit some money to their account. In each case the transaction table is updated with a log indicating the type of transaction and the amount and the member table updated accordingly$(10$ Marks$)$

$2.$ Create a function that checks if the member exists and returns the answer.Create a trigger that checks if an UPDATE action will leave the account with a balance below half the required deposit $($half your student ID number$)$ and returns an error if it does.$(10$ Marks$)$

$3.$ Create the following procedures in the database$(15$ Marks$)$

$$ addMember$$ which adds new details to member table and adds a transaction entry. The procedure should call a function to check if a member exists before adding them and act accordingly.

$$ addBill$$ which calls a function to check if the member exists. It then adds a transaction entry and then deducts money from the members account. The procedure should make use of a transaction to do this. The transaction should use an UPDATE statement to trigger a check for the account balance.

$$ addPayment$-$ which calls the function to check if the member exists. It then adds money to the members account and adds a transaction entry.

$4.$ Add yourself as a member to your database and two transactions to test the application. Upload the database script $($sql file$).(5$ Marks$)$

$5.$ Describe how the transactions you created are managed by the database to avoid consistency issues in a multiuser environment. Provide relevant examples. $(10$ Marks$)$