logo

Database for a Typical Bank

1 Pages586 Words71 Views
   

Added on  2019-09-18

About This Document

This database tracks customers and accounts for a typical bank, including customer type, account type, and transactions. Procedures and triggers are used to ensure data integrity. The system can add new customers and accounts, handle transactions, display balances by state, update customer addresses, and display transactions above a certain amount. An extra credit trigger logs address changes.

Database for a Typical Bank

   Added on 2019-09-18

ShareRelated Documents
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). The bank has various types of accounts that we will track (savings, checking, business). A customer may have multiple accounts of each type. Accounts also have a balance. An account may be owned 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 transaction consists of the amount, type (deposit, withdrawal, transfer), date, and account(s) for the transaction. Requirements:1.(Using a procedure) System should be able to add new customers ensuring that no duplicates are entered, and type is valid. A message should be returned with the success or failure of the action. 2.(Using a procedure) System should be able to add a new account for an existing customer. Given a customer ID, and an account type, the system should verify that the customer id, and account type are valid, then create a new account. A message should be returned with the success or failure of the action. 3.(Using a procedure and trigger) System should be able to handle valid transactions on valid accounts for valid customers maintaining the integrity of the account balances. Specifically, the system should allow deposits to accounts (must have account number, amount), withdrawals (must have account number, amount) from accounts and transfersbetween accounts (must have 2 account numbers and an amount). When these transactions are made, the account balance must be updated (via trigger). No account balance may go below zero. A message should be returned with the success or failure of the action. 4.(Using a procedure) Given a state, display the sum of the balances by state. 5.(Using a procedure) Given a customer id and a new address, update the customer’s address. 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, and amount 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 the account balance and one for validation. 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 other table constraints as appropriate.Important: Each requirement should translate into at least one procedure depending on how youdecide to implement the requirement. #3 will require at least one procedure and one trigger.
Database for a Typical Bank_1

End of preview

Want to access all the pages? Upload your documents or become a member.

Related Documents
Banking Scenario- Database for Typical Bank ERD
|5
|958
|336

Customer and ATM machine - Doc
|10
|1416
|337

BANKING SCENARIO PROCEDURES Faisal Jahdlai 08 December, 2016 BANKING SCENARIO PROCEDURES Faisal Jahdali 08 December, 2016 ER DIAGRAM 2 CREATE AND INSERT TABLE COMMANDS 2 SQL PROCEDURES Faisal Jahdlai
|19
|1901
|149

SQl Procedures and ER Diagram Assignment
|20
|1871
|385

Collins ATM System Sample Assignment
|10
|1672
|50

Banking Assignment - Procedures Database SQL
|20
|1754
|421