test

Sun Java System Application Server Enterprise Edition 8.1 2005Q2 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