Database Schema
The iBank database schema is derived from the following business rules:
-
The iBank company has local branches in major cities.
-
A Branch manages all customers within its regional area.
-
A Customer has one or more accounts held at their regional branch.
-
A customer Account is uniquely identified by the branch code and account number, and also holds the number of the customer to which it belongs. The current cleared balance available is also stored with the account.
-
Accounts are of a particular Account Type that is used to distinguish between several kinds of accounts (checking account, savings account, etc.).
-
Each Account Type stores a number of particulars that apply to all accounts of this type (regardless of branch or customer) such as interest rate and allowed overdraft limit.
-
Every time a customer receives or pays money into/from one of their accounts, the transaction is recorded in a global transaction log, the Transaction History.
-
The Transaction History stores details about individual transactions, such as the relevant branch code and account number, the date the transaction was posted (recorded), a code identifying the type of transaction and a complementary description of the particular transaction, and the amount for the transaction.
-
Transaction types allow different types of transactions to be distinguished, such as cash deposit, credit card payment, fund transfer between accounts, and so on.
The entity-relationship diagram shown below illustrates these business rules.
Figure 9–1 Database Schema
iBank Database Schema Tables
This section lists the tables in the iBank database schema.
Table 9–1 BRANCH Table|
Column Name |
Datatype |
Nullable/Default |
Description |
|---|---|---|---|
|
BRANCH_CODE |
CHAR(4) |
NOT NULL |
4-digit code identifying the branch |
|
BRANCH_NAME |
VARCHAR(40) |
NOT NULL |
Name of the branch |
|
BRANCH_ADDRESS1 |
VARCHAR(60) |
NOT NULL |
Branch postal address, street address, 1st line |
|
BRANCH_ADDRESS2 |
VARCHAR(60) |
Branch postal address, street address, 2nd line |
|
|
BRANCH_CITY |
VARCHAR(30) |
NOT NULL |
Branch postal address, City |
|
BRANCH_ZIP |
VARCHAR(10) |
NOT NULL |
Branch postal address, Zip code |
|
BRANCH_STATE |
CHAR(2) |
NOT NULL |
Branch postal address, State abbreviation |
Table 9–2 CUSTOMER Table
|
Column Name |
Datatype |
Nullable/Default |
Description |
|---|---|---|---|
|
CUST_NO |
INT |
NOT NULL |
iBank customer number (global) |
|
BRANCH_CODE |
CHAR(4) |
NOT NULL |
References this customer’s branch |
|
CUST_USERNAME |
VARCHAR(16) |
NOT NULL |
Customer’s login username |
|
CUST_PASSWORD |
VARCHAR(10) |
NOT NULL |
Customer’s login password |
|
CUST_EMAIL |
VARCHAR(40) |
Customer’s e-mail address |
|
|
CUST_TITLE |
VARCHAR(3) |
NOT NULL |
Customer’s courtesy title |
|
CUST_GIVENNAMES |
VARCHAR(40) |
NOT NULL |
Customer’s given names |
|
CUST_SURNAME |
VARCHAR(40) |
NOT NULL |
Customer’s family name |
|
CUST_ADDRESS1 |
VARCHAR(60) |
NOT NULL |
Customer postal address, street address, 1st line |
|
CUST_ADDRESS2 |
VARCHAR(60) |
Customer postal address, street address, 2nd line |
|
|
CUST_CITY |
VARCHAR(30) |
NOT NULL |
Customer postal address, City |
|
CUST_ZIP |
VARCHAR(10) |
NOT NULL |
Customer postal address, Zip code |
|
CUST_STATE |
CHAR(2) |
NOT NULL |
Customer postal address, State abbreviation |
Table 9–3 ACCOUNT_TYPE Table
|
Column Name |
Datatype |
Nullable/Default |
Description |
|---|---|---|---|
|
ACCTYPE_ID |
CHAR(3) |
NOT NULL |
3-letter account type code |
|
ACCTYPE_DESC |
VARCHAR(30) |
NOT NULL |
Account type description |
|
ACCTYPE_INTERESTRATE |
DECIMAL(4,2) |
DEFAULT 0.0 |
Annual interest rate |
Table 9–4 ACCOUNT Table
|
Column Name |
Datatype |
Nullable/Default |
Description |
|---|---|---|---|
|
ACCOUNT | |||
|
BRANCH_CODE |
CHAR(4) |
NOT NULL |
branch code (primary-key part 1) |
|
ACC_NO |
CHAR(8) |
NOT NULL |
account no. (primary-key part 2) |
|
CUST_NO |
INT |
NOT NULL |
Customer to whom accounts belongs |
|
ACCTYPE_ID |
CHAR(3) |
NOT NULL |
Account type, references ACCOUNT_TYPE |
|
ACC_BALANCE |
DECIMAL(10,2) |
DEFAULT 0.0 |
Cleared balance available |
Table 9–5 TRANSACTION_TYPE Table
|
Column Name |
Datatype |
Nullable/Default |
Description |
|---|---|---|---|
|
TRANSTYPE_ID |
CHAR(4) |
NOT NULL |
A 4-letter transaction type code |
|
TRANSTYPE_DESC |
VARCHAR(40) |
NOT NULL |
Human-readable description of code |
Table 9–6 TRANSACTION_HISTORY Table
|
Column Name |
Datatype |
Nullable/Default |
Description |
|---|---|---|---|
|
TRANS_ID |
LONGINT |
NOT NULL |
Global transaction serial no |
|
BRANCH_CODE |
CHAR(4) |
NOT NULL |
key referencing ACCOUNT part 1 |
|
ACC_NO |
CHAR(8) |
NOT NULL |
key referencing ACCOUNT part 2 |
|
TRANSTYPE_ID |
CHAR(4) |
NOT NULL |
References TRANSACTION_TYPE |
|
TRANS_POSTDATE |
TIMESTAMP |
NOT NULL |
Date & time transaction was posted |
|
TRANS_DESC |
VARCHAR(40) |
Additional details for the transaction |
|
|
TRANS_AMOUNT |
DECIMAL(10,2) |
NOT NULL |
Money amount for this transaction |
The TRANSACTION_HISTORY table is shown below.
Table 9–7 TRANSACTION_HISTORY Table|
Column Name |
Datatype |
Nullable/Default |
Description |
|---|---|---|---|
|
TRANS_ID |
LONGINT |
NOT NULL |
Global transaction serial no |
|
BRANCH_CODE |
CHAR(4) |
NOT NULL |
key referencing ACCOUNT part 1 |
|
ACC_NO |
CHAR(8) |
NOT NULL |
key referencing ACCOUNT part 2 |
|
TRANSTYPE_ID |
CHAR(4) |
NOT NULL |
References TRANSACTION_TYPE |
|
TRANS_POSTDATE |
TIMESTAMP |
NOT NULL |
Date & time transaction was posted |
|
TRANS_DESC |
VARCHAR(40) |
Additional details for the transaction |
|
|
TRANS_AMOUNT |
DECIMAL(10,2) |
NOT NULL |
Money amount for this transaction |
- © 2010, Oracle Corporation and/or its affiliates