test

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

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