The National Bank of Erehwon handles money and maintains bank accounts on behalf of clients. A client is a person who does business with the bank. A client may have any number of accounts, and an account may belong to multiple clients (e.g., spouses, business partners). The client record is used for identification and contact data. For each account, the bank maintains the current balance on hand. Clients are identified by a five digit number starting with 10001. Accounts are identified by a seven digit number starting with 1000001. When an account is first opened, its balance is set to zero. During the course of day-to-day business, Erehwon Bank applies transactions to accounts, including deposits, withdrawals, bill payments, and debit purchases or returns. For each transaction, the date and time, amount, and account are recorded, along with reference data applicable to that type of transaction: Deposits and withdrawals require the branch number to be recorded. Bill payments, and debit purchases or returns require the merchant number. Building and Populating the Schema Develop a database in Oracle to implement the Erehwon Bank scenario. Code the SQL DDL to build all the tables, complete with constraints and default values (except for lookup of the Ref_Nbr for a Transaction ... that will require procedural code in step 4). Choose appropriate data types for each of the columns. Add those indexes that you believe would be useful. Include comments in your SQL script(s) to document any decisions you made. Populate your database with the test data. In your SQL INSERT statements, you must make use of sequences for generating key values for the Client, Account, and Transaction tables. You must use the TO_DATE function to get proper date and time values for the transactions.
The National Bank of Erehwon handles money and maintains bank accounts on behalf of clients.
-
A client is a person who does business with the bank.
-
A client may have any number of accounts, and an account may belong to multiple clients (e.g., spouses, business partners).
-
The client record is used for identification and contact data.
-
For each account, the bank maintains the current balance on hand.
-
Clients are identified by a five digit number starting with 10001.
-
Accounts are identified by a seven digit number starting with 1000001.
When an account is first opened, its balance is set to zero. During the course of day-to-day business, Erehwon Bank applies transactions to accounts, including deposits, withdrawals, bill payments, and debit purchases or returns. For each transaction, the date and time, amount, and account are recorded, along with reference data applicable to that type of transaction:
-
Deposits and withdrawals require the branch number to be recorded.
-
Bill payments, and debit purchases or returns require the merchant number.
Building and Populating the Schema
Develop a
Populate your database with the test data.
-
In your SQL INSERT statements, you must make use of sequences for generating key values for the Client, Account, and Transaction tables.
-
You must use the TO_DATE function to get proper date and time values for the transactions.
Step by step
Solved in 2 steps
