schema.ddl

--<ScriptOptions statementTerminator=";"/>

CREATE TABLE "ACCOUNT" (
		"ACCID" BIGINT NOT NULL, 
		"ACCTYPE" CHAR(1 OCTETS) NOT NULL, 
		"BALANCE" DECIMAL(15 , 2) NOT NULL, 
		"LASTTXID" BIGINT NOT NULL
	)
	ORGANIZE BY ROW
	DATA CAPTURE NONE 
	COMPRESS NO;

CREATE TABLE "ACCTYPE" (
		"CODE" CHAR(1 OCTETS) NOT NULL, 
		"DESCRIPTION" VARCHAR(20 OCTETS) NOT NULL
	)
	ORGANIZE BY ROW
	DATA CAPTURE NONE 
	COMPRESS NO;

CREATE TABLE "CARDHISTORY" (
		"TXID" BIGINT NOT NULL, 
		"CCARDID" BIGINT NOT NULL, 
		"METHOD" INTEGER NOT NULL, 
		"ERRID" INTEGER
	)
	ORGANIZE BY ROW
	DATA CAPTURE NONE 
	COMPRESS NO;

CREATE TABLE "CREDITCARD" (
		"CCARDID" BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE -9223372036854775808 MAXVALUE 9223372036854775807 NO CYCLE CACHE 20 NO ORDER ), 
		"ACCID" BIGINT NOT NULL, 
		"CCNUMBER" BIGINT NOT NULL, 
		"EXPIRATION" INTEGER NOT NULL, 
		"CVV" INTEGER NOT NULL, 
		"PUBKEY" BIGINT
	)
	ORGANIZE BY ROW
	DATA CAPTURE NONE 
	COMPRESS NO;

CREATE TABLE "CUSTOMER" (
		"CUSTID" INTEGER NOT NULL, 
		"LASTNAME" VARCHAR(50 OCTETS) NOT NULL, 
		"FIRSTNAME" VARCHAR(50 OCTETS), 
		"ADDRESS" VARCHAR(80 OCTETS), 
		"CITY" VARCHAR(25 OCTETS), 
		"STATE" VARCHAR(32 OCTETS), 
		"ZIPCODE" VARCHAR(15 OCTETS), 
		"PHONE" VARCHAR(12 OCTETS), 
		"STATUS" CHAR(1 OCTETS) NOT NULL, 
		"PASSWORD" VARCHAR(16 OCTETS) NOT NULL, 
		"CUSTOMERTYPE" CHAR(1 OCTETS) NOT NULL, 
		"LASTLOGIN" TIMESTAMP NOT NULL
	)
	ORGANIZE BY ROW
	DATA CAPTURE NONE 
	COMPRESS NO;

CREATE TABLE "CUSTOMERACCS" (
		"ACCID" BIGINT NOT NULL, 
		"CUSTID" INTEGER NOT NULL, 
		"ACCNUM" INTEGER NOT NULL
	)
	ORGANIZE BY ROW
	DATA CAPTURE NONE 
	COMPRESS NO;

CREATE TABLE "CUSTOMERTYPE" (
		"CODE" CHAR(1 OCTETS) NOT NULL, 
		"DESCRIPTION" CHAR(20 OCTETS)
	)
	ORGANIZE BY ROW
	DATA CAPTURE NONE 
	COMPRESS NO;

CREATE TABLE "ERROR" (
		"ERRID" INTEGER NOT NULL, 
		"ERRORSTR" CHAR(50 OCTETS)
	)
	ORGANIZE BY ROW
	DATA CAPTURE NONE 
	COMPRESS NO;

CREATE TABLE "HISTORY" (
		"TXID" BIGINT NOT NULL, 
		"TIME" TIMESTAMP NOT NULL, 
		"TRANSTYPE" CHAR(1 OCTETS) NOT NULL, 
		"AMOUNT" DECIMAL(15 , 2) NOT NULL, 
		"REFTXID" BIGINT NOT NULL, 
		"ACCID" BIGINT NOT NULL
	)
	ORGANIZE BY ROW
	DATA CAPTURE NONE 
	COMPRESS NO;

CREATE TABLE "MERCHANT" (
		"MERCHANTID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE -2147483648 MAXVALUE 2147483647 NO CYCLE CACHE 20 NO ORDER ), 
		"MCC" INTEGER, 
		"MERCHANT_NAME" CHAR(254 OCTETS)
	)
	ORGANIZE BY ROW
	DATA CAPTURE NONE 
	COMPRESS NO;

CREATE TABLE "MERCHANTACC" (
		"ACCID" BIGINT NOT NULL, 
		"MERCHANTID" INTEGER NOT NULL, 
		"TOKEN" CHAR(32 OCTETS)
	)
	ORGANIZE BY ROW
	DATA CAPTURE NONE 
	COMPRESS NO;

CREATE TABLE "TRANSSTATUS" (
		"TXID" BIGINT NOT NULL, 
		"STATUS" CHAR(1 OCTETS) NOT NULL, 
		"STARTTIME" TIMESTAMP NOT NULL, 
		"STOPTIME" TIMESTAMP NOT NULL, 
		"CUSTID" INTEGER NOT NULL
	)
	ORGANIZE BY ROW
	DATA CAPTURE NONE 
	COMPRESS NO;

CREATE TABLE "TRANSSTATUSTYPE" (
		"CODE" CHAR(1 OCTETS) NOT NULL, 
		"DESCRIPTION" VARCHAR(20 OCTETS) NOT NULL
	)
	ORGANIZE BY ROW
	DATA CAPTURE NONE 
	COMPRESS NO;

CREATE TABLE "TRANSTYPE" (
		"CODE" CHAR(1 OCTETS) NOT NULL, 
		"DESCRIPTION" VARCHAR(20 OCTETS)
	)
	ORGANIZE BY ROW
	DATA CAPTURE NONE 
	COMPRESS NO;

CREATE INDEX "ACCHISTORY"
	ON "HISTORY"
	("ACCID"		ASC)
	PCTFREE 10
	MINPCTUSED 0
	ALLOW REVERSE SCANS
	PAGE SPLIT SYMMETRIC
	COMPRESS NO;

CREATE INDEX "CUSTOMERACCS_CUSTID"
	ON "CUSTOMERACCS"
	("CUSTID"		ASC)
	PCTFREE 10
	MINPCTUSED 0
	ALLOW REVERSE SCANS
	PAGE SPLIT SYMMETRIC
	COMPRESS NO;

CREATE INDEX "MERCHANTACC_IDX"
	ON "MERCHANTACC"
	("ACCID"		ASC)
	MINPCTUSED 0
	ALLOW REVERSE SCANS
	PAGE SPLIT SYMMETRIC
	COLLECT SAMPLED DETAILED STATISTICS
	COMPRESS NO;

CREATE INDEX "TRANS"
	ON "TRANSSTATUS"
	("CUSTID"		ASC)
	PCTFREE 10
	MINPCTUSED 0
	ALLOW REVERSE SCANS
	PAGE SPLIT SYMMETRIC
	COMPRESS NO;

CREATE UNIQUE INDEX "HISTORY_IDX"
	ON "HISTORY"
	("REFTXID"		ASC)
	MINPCTUSED 0
	ALLOW REVERSE SCANS
	PAGE SPLIT SYMMETRIC
	COLLECT SAMPLED DETAILED STATISTICS
	COMPRESS NO;

CREATE UNIQUE INDEX "IDX_CARDHISTORY_CCARDID_TXID"
	ON "CARDHISTORY"
	("CCARDID"		ASC, 
	  "TXID"		DESC)
INCLUDE ("ERRID",  "METHOD")
	MINPCTUSED 0
	ALLOW REVERSE SCANS
	PAGE SPLIT SYMMETRIC
	COLLECT SAMPLED DETAILED STATISTICS
	COMPRESS NO;

ALTER TABLE "CARDHISTORY" ADD CONSTRAINT "CARDHISTORY_CCARDID_FK" FOREIGN KEY
	("CCARDID")
	REFERENCES "CREDITCARD"
	("CCARDID")
	ON DELETE CASCADE;

ALTER TABLE "CARDHISTORY" ADD CONSTRAINT "CARDHISTORY_ERRID_FK" FOREIGN KEY
	("ERRID")
	REFERENCES "ERROR"
	("ERRID")
	ON DELETE CASCADE;

ALTER TABLE "CARDHISTORY" ADD CONSTRAINT "CARDHISTORY_TXID_FK" FOREIGN KEY
	("TXID")
	REFERENCES "HISTORY"
	("TXID")
	ON DELETE CASCADE;

ALTER TABLE "CARDHISTORY" ADD CONSTRAINT "CH_H_REFTEID_FK" FOREIGN KEY
	("TXID")
	REFERENCES "HISTORY"
	("REFTXID")
	ON DELETE CASCADE;

ALTER TABLE "CREDITCARD" ADD CONSTRAINT "CREDITCARD_ACCOUNT_FK" FOREIGN KEY
	("ACCID")
	REFERENCES "ACCOUNT"
	("ACCID")
	ON DELETE CASCADE;

ALTER TABLE "CUSTOMER" ADD CONSTRAINT "CC1057761983893" FOREIGN KEY
	("CUSTOMERTYPE")
	REFERENCES "CUSTOMERTYPE"
	("CODE");

ALTER TABLE "CUSTOMERACCS" ADD CONSTRAINT "CC1057697195714" FOREIGN KEY
	("CUSTID")
	REFERENCES "CUSTOMER"
	("CUSTID");

ALTER TABLE "CUSTOMERACCS" ADD CONSTRAINT "CC1057697217826" FOREIGN KEY
	("ACCID")
	REFERENCES "ACCOUNT"
	("ACCID");

ALTER TABLE "MERCHANTACC" ADD CONSTRAINT "MERCHANTACC_ACCOUNT_FK" FOREIGN KEY
	("ACCID")
	REFERENCES "ACCOUNT"
	("ACCID")
	ON DELETE CASCADE;

ALTER TABLE "MERCHANTACC" ADD CONSTRAINT "MERCHANTACC_MERCHANT_FK" FOREIGN KEY
	("MERCHANTID")
	REFERENCES "MERCHANT"
	("MERCHANTID")
	ON DELETE CASCADE;

ALTER TABLE "TRANSSTATUS" ADD CONSTRAINT "CC1057697710154" FOREIGN KEY
	("CUSTID")
	REFERENCES "CUSTOMER"
	("CUSTID");

ALTER TABLE "TRANSSTATUS" ADD CONSTRAINT "CC1057705416675" FOREIGN KEY
	("TXID")
	REFERENCES "HISTORY"
	("TXID");

ALTER TABLE "TRANSSTATUS" ADD CONSTRAINT "CC1057762058640" FOREIGN KEY
	("STATUS")
	REFERENCES "TRANSSTATUSTYPE"
	("CODE");