Banking:. Create a database for a typical bank.. We wil
Added on - Sep 2019
Showing pages 1 to 1 of 1 pages
Banking: Create a database for a typical bank. We will track customers of the bank to include;last name, first name, address, and customer type (small business, large business, personal). Thebank has various types of accounts that we will track (savings, checking, business). A customermay have multiple accounts of each type. Accounts also have a balance. An account may beowned by one customer only. For each account you must keep track of all the transactions on theaccount. Transactions types consist of deposits, withdrawals, and transfers. Each transactionconsists of the amount, type (deposit, withdrawal, transfer), date, and account(s) for thetransaction.Requirements:1.(Using a procedure) System should be able to add new customers ensuring that noduplicates are entered, and type is valid. A message should be returned with the successor failure of the action.2.(Using a procedure) System should be able to add a new account for an existingcustomer. Given a customer ID, and an account type, the system should verify that thecustomer id, and account type are valid, then create a new account. A message should bereturned with the success or failure of the action.3.(Using a procedure and trigger) System should be able to handle valid transactions onvalid accounts for valid customers maintaining the integrity of the account balances.Specifically, the system should allowdepositsto accounts (must have account number,amount),withdrawals(must have account number, amount) from accounts andtransfersbetween accounts (must have 2 account numbers and an amount). When thesetransactions are made, the account balance must be updated (via trigger). No accountbalance may go below zero. A message should be returned with the success or failure ofthe action.4.(Using a procedure) Given a state, display the sum of the balances bystate.5.(Using a procedure) Given a customer id and a new address, update the customer’saddress. A message should be returned with the success or failure of the action.6.(Using a procedure) Given an amount, display the account number, last name, andamount of all transactions with an amount greater than the one passed into the procedure.7.[EXTRA CREDIT]: Write a trigger so that anytime a customer changes their address,the old address, new address, date, customer id and user ID are stored in a log.NOTE: You must use at least 2 triggers, one to maintain theaccount balanceand one forvalidation. The validation trigger may be to validate the account type, customer type, the balancenot going below zero, or not allowing duplicates. A check constraint is also required. Use othertable constraints as appropriate.Important: Each requirement should translate into atleastone procedure depending on how youdecide to implement the requirement. #3 will require atleastone procedure and one trigger.