Sun Java System Application Server Platform Edition 8.1 2005Q2 Update 2 Upgrade and Migration Guide

- Previous: Chapter 8 Migration Tools and Resources
- Next: Application Navigation and Logic

## 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

- Previous: Chapter 8 Migration Tools and Resources
- Next: Application Navigation and Logic

- © 2010, Oracle Corporation and/or its affiliates