Sun Java System Application Server Enterprise Edition 8.1 2005Q2 Upgrade and Migration Guide

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

# 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

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

- © 2010, Oracle Corporation and/or its affiliates