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 |