ejbbank.ddl

噶额外噶外骨骼感广泛高热感 就 啊啊

echo --- create the EJBBANK database ---
CREATE DATABASE EJBBANK

echo --- connect to EJBBANK database ---
CONNECT TO EJBBANK

echo --- drop tables and triggers ---
DROP TABLE ITSO.TRANSRECORD
DROP TABLE ITSO.CUSTACCT
DROP TABLE ITSO.CUSTADDRESS
DROP TABLE ITSO.CHECKING
DROP TABLE ITSO.SAVINGS
DROP TABLE ITSO.ACCOUNT
DROP TABLE ITSO.CUSTOMER
DROP TABLE ITSO.CUSTOMERINFO
DROP TRIGGER ITSO.INSERTACCOUNT
DROP TRIGGER ITSO.UPDATEACCOUNT

echo --- create tables ---
CREATE TABLE ITSO.CUSTOMER (                                \
   customerid    INTEGER     NOT NULL,                      \
   title         CHAR(3)     NOT NULL,                      \
   firstName     VARCHAR(30) NOT NULL,                      \
   lastName      VARCHAR(30) NOT NULL,                      \
   userID        CHAR(8),                                   \
   password      CHAR(8),                                   \
   address       BLOB(2000),                                \
                             PRIMARY KEY (CUSTOMERID)       \
)

CREATE TABLE ITSO.CUSTADDRESS (                             \
   customerid    INTEGER     NOT NULL,                      \
   street        CHAR(20),                                  \
   city          CHAR(12),                                  \
   state         CHAR(12),                                  \
   zipcode       CHAR(10),                                  \
                             PRIMARY KEY (CUSTOMERID)       \
)

CREATE TABLE ITSO.CUSTACCT (                                \
   customerid    INTEGER     NOT NULL,                      \
   accID         CHAR(8)     NOT NULL,                      \
                             PRIMARY KEY (CUSTOMERID,ACCID) \
)

CREATE TABLE ITSO.ACCOUNT (                                 \
   accid         CHAR(8)     NOT NULL,                      \
   balance       DEC(8,2)    NOT NULL DEFAULT 0.00,         \
   interest      INTEGER     NOT NULL DEFAULT 5,            \
   acctype       VARCHAR(8)  NOT NULL DEFAULT 'ACCOUNT',    \
   discriminator CHAR(1)     NOT NULL DEFAULT 'A',          \
   overdraft     DEC(8,2)    NOT NULL DEFAULT 200.00,       \
   minamount     DEC(8,2)    NOT NULL DEFAULT 100.00,       \
                             PRIMARY KEY (ACCID)            \
)

CREATE TABLE ITSO.CHECKING (                                \
   accid         CHAR(8)     NOT NULL,                      \
   overdraft     DEC(8,2)    NOT NULL DEFAULT 200.00,       \
                             PRIMARY KEY (ACCID)            \
)

CREATE TABLE ITSO.SAVINGS (                                 \
   accid         CHAR(8)     NOT NULL,                      \
   minamount     DEC(8,2)    NOT NULL DEFAULT 100.00,       \
                             PRIMARY KEY (ACCID)            \
)

CREATE TABLE ITSO.TRANSRECORD (                             \
   transid       TIMESTAMP   NOT NULL,                      \
   accid         CHAR(8)             ,                      \
   transtype     CHAR(1)     NOT NULL,                      \
   transamt      DEC(8,2)    NOT NULL,                      \
                             PRIMARY KEY (TRANSID)          \
)

CREATE TABLE ITSO.CUSTOMERINFO (                            \ 
   customerid    INTEGER     NOT NULL,                      \
   infoid        INTEGER     NOT NULL,                      \
   description   VARCHAR(50),                               \
   data          BLOB(10K),                                 \
                           PRIMARY KEY (CUSTOMERID, INFOID) \
)
        
echo --- referential integrity ---
ALTER TABLE ITSO.TRANSRECORD                                \
   ADD CONSTRAINT "AccountTransrecord" FOREIGN KEY (ACCID)  \
   REFERENCES ITSO.ACCOUNT ON DELETE RESTRICT

ALTER TABLE ITSO.CUSTACCT                                   \
   ADD CONSTRAINT "CAtoCustomer"  FOREIGN KEY (CUSTOMERID)  \
   REFERENCES ITSO.CUSTOMER ON DELETE RESTRICT

ALTER TABLE ITSO.CUSTACCT                                   \
   ADD CONSTRAINT "CAtoAccount"  FOREIGN KEY (ACCID)        \
   REFERENCES ITSO.ACCOUNT ON DELETE RESTRICT

ALTER TABLE ITSO.CUSTADDRESS                                \
   ADD CONSTRAINT "CustAddr"  FOREIGN KEY (CUSTOMERID)      \
   REFERENCES ITSO.CUSTOMER ON DELETE RESTRICT

ALTER TABLE ITSO.CHECKING                                   \
   ADD CONSTRAINT "CheckingAccount"  FOREIGN KEY (ACCID)    \
   REFERENCES ITSO.ACCOUNT ON DELETE RESTRICT

ALTER TABLE ITSO.SAVINGS                                    \
   ADD CONSTRAINT "SavingsAccount"  FOREIGN KEY (ACCID)     \
   REFERENCES ITSO.ACCOUNT ON DELETE RESTRICT

echo --- triggers -------------------
CREATE TRIGGER ITSO.INSERTACCOUNT NO CASCADE BEFORE INSERT            \
    ON ITSO.ACCOUNT referencing new as ACCT for each row mode db2sql  \
    BEGIN ATOMIC                                                      \
      SET ACCT.acctype =                                              \
         CASE                                                         \
           WHEN ACCT.discriminator = 'C' THEN 'CHECKING'              \
           WHEN ACCT.discriminator = 'S' THEN 'SAVINGS'               \
           WHEN ACCT.discriminator = 'A' THEN 'ACCOUNT'               \
         END;                                                         \
    END

CREATE TRIGGER ITSO.UPDATEACCOUNT NO CASCADE BEFORE UPDATE            \
    ON ITSO.ACCOUNT referencing new as ACCT for each row mode db2sql  \
    BEGIN ATOMIC                                                      \
      SET ACCT.acctype =                                              \
         CASE                                                         \
           WHEN ACCT.discriminator = 'C' THEN 'CHECKING'              \
           WHEN ACCT.discriminator = 'S' THEN 'SAVINGS'               \
           WHEN ACCT.discriminator = 'A' THEN 'ACCOUNT'               \
         END;                                                         \
    END

echo --- execute GRANT statements ---
GRANT CONNECT ON DATABASE          TO PUBLIC
GRANT ALL     ON ITSO.CUSTOMER     TO PUBLIC
GRANT ALL     ON ITSO.ACCOUNT      TO PUBLIC
GRANT ALL     ON ITSO.CHECKING     TO PUBLIC
GRANT ALL     ON ITSO.SAVINGS      TO PUBLIC
GRANT ALL     ON ITSO.TRANSRECORD  TO PUBLIC
GRANT ALL     ON ITSO.CUSTACCT     TO PUBLIC
GRANT ALL     ON ITSO.CUSTADDRESS  TO PUBLIC
GRANT ALL     ON ITSO.CUSTOMERINFO TO PUBLIC

echo --- connect reset ---
CONNECT RESET