Building Java Enterprise Applications

The Accounts Store

The SQL scripts in this section handle the creation of the accounts store under different databases, and are equivalent to the script shown in Example 3-3 of the text.

Cloudscape

Th e SQL script shown in Example A-6 creates the accounts store and constraints on Cloudscape databases.

Example A-6. SQL Script for Creating the Accounts Store on Cloudscape Databases

-- ACCOUNT_TYPES table CREATE TABLE ACCOUNT_TYPES ( ACCOUNT_TYPE_ID INT PRIMARY KEY NOT NULL, ACCOUNT_TYPE VARCHAR(20) NOT NULL ); -- ACCOUNTS table CREATE TABLE ACCOUNTS ( ACCOUNT_ID INT PRIMARY KEY NOT NULL, USER_ID INT NOT NULL, ACCOUNT_TYPE_ID INT NOT NULL, BALANCE FLOAT NOT NULL, CONSTRAINT USER_ID_FK FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID), CONSTRAINT ACCOUNT_TYPE_ID_FK FOREIGN KEY (ACCOUNT_TYPE_ID) REFERENCES ACCOUNT_TYPES (ACCOUNT_TYPE_ID) ); -- TRANSACTIONS table CREATE TABLE TRANSACTIONS ( TRANSACTION_ID INT PRIMARY KEY NOT NULL, ACCOUNT_ID INT NOT NULL, AMOUNT FLOAT NOT NULL, DATE_TIME DATE NOT NULL, CONSTRAINT ACCOUNT_ID_FK FOREIGN KEY (ACCOUNT_ID) REFERENCES ACCOUNTS (ACCOUNT_ID) ); -- FUNDS table CREATE TABLE FUNDS ( FUND_ID INT PRIMARY KEY NOT NULL, NAME VARCHAR(20) NOT NULL, DESCRIPTION VARCHAR(200) ); -- INVESTMENTS table CREATE TABLE INVESTMENTS ( INVESTMENT_ID INT PRIMARY KEY NOT NULL, FUND_ID INT NOT NULL, ACCOUNT_ID INT NOT NULL, INITIAL_AMOUNT FLOAT NOT NULL, YIELD FLOAT, CONSTRAINT FUND_ID_FK FOREIGN KEY (FUND_ID) REFERENCES FUNDS (FUND_ID), CONSTRAINT ACCOUNT_ID_FK2 FOREIGN ...