數(shù)據倉庫分析系統(tǒng)整體設計方案

1、1 / 128 文檔可自由編輯 目目 錄錄 一、概述一、概述.2 2 二、四科室需求二、四科室需求.3 3 1、風險科需求 .3 2、市場科需求 .13 3、業(yè)務管理科需求 .14 4、計劃資金科需求 .15 三、需求分析三、需求分析.2323 1、維表 .23 2、事實表 .23 3、事務業(yè)務處理過程及業(yè)務術語 .23 4、主鍵 .24 5、外鍵 .24 四、系統(tǒng)結構圖及業(yè)務數(shù)據流圖四、系統(tǒng)結構圖及業(yè)務數(shù)據流圖.2 25 5 1、系統(tǒng)結構圖 .25 2、數(shù)據流圖 .26 五、源數(shù)據表結構五、源數(shù)據表結構.2727 1、BCS 系統(tǒng).27 2、CARDPOOL系統(tǒng) .34 3、NAS 系統(tǒng).3

2、6 4、BCS 系統(tǒng)報表.37 六、生成表結構六、生成表結構.3939 七、碼表結構七、碼表結構.4343 八、結果表結構八、結果表結構.5050 九、數(shù)據表創(chuàng)建方法九、數(shù)據表創(chuàng)建方法.5151 1、BCS 系統(tǒng).51 2、CARDPOOL系統(tǒng) .57 3、NAS 系統(tǒng).58 4、生成表 .58 2 / 128 文檔可自由編輯 5、碼表 .62 十、數(shù)據處理過程十、數(shù)據處理過程.6868 1、目錄結構 .68 2、流程說明 .68 十一、問題及處理方法十一、問題及處理方法.8080 3 / 128 文檔可自由編輯 一、概述一、概述 Bill Inmon（數(shù)據倉庫之父）在Building the

3、 Data Warehouse （John Wiley 主鍵 ALTER TABLE DAT_CARD_FINA ADD CONSTRAINT ACCT_PK PRIMARY KEY (ACCT_NO) USING INDEX STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0) TABLESPACE INDX; 外鍵 ALTER TABLE DAT_CARD_FINA ADD CONSTRAINT FINA_ISSUE_FK FOREIGN KEY (ISSUE_CODE) REFERENCES COD_BOCBJ_BRANCH (ISSUE_CODE); 表

4、2. 卡信息表 CREATE TABLE DAT_CARD_INFO (CARD_NO CHAR(16) NOT NULL ENABLE, 79 / 128 文檔可自由編輯 ACCT_NO CHAR(12), CARD_STATUS CHAR(1), STATUS_DATE DATE, EXP_DATE DATE, CARD_HOLDER VARCHAR2(20), EMBOSS_NAME VARCHAR2(20), PASS_OFFSET CHAR(6), SEX CHAR(1), ID_TYPE CHAR(1), ID_NUMBER CHAR(18), BIRTH_DATE DATE, N

5、ATIONALITY CHAR(3), MARRIED CHAR(1), TITLE_CODE CHAR(2), SIGNATURE_NO CHAR(6), HOME_ADDRESS VARCHAR2(40), HOME_ZIP_CODE CHAR(6), HOME_TEL_NO VARCHAR2(12), EMPLOYER_NAME VARCHAR2(30), OWNERSHIP CHAR(1), EMPLOYER_TEL_NO VARCHAR2(12), EMPLOYER_ADDRESS VARCHAR2(40), EMPLOYER_ZIP_CODE CHAR(6), ATM_FUNCTI

6、ON CHAR(1), TELLER CHAR(3), SUPERVISOR CHAR(3) PCTFREE 15 PCTUSED 60 TABLESPACE SOURCE STORAGE (INITIAL 25M NEXT 25M PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 200); 主鍵 ALTER TABLE DAT_CARD_INFO ADD CONSTRAINT CARD_PK PRIMARY KEY (CARD_NO) USING INDEX STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 0) 80 / 128 文

7、檔可自由編輯 TABLESPACE INDX; 外鍵 ALTER TABLE DAT_CARD_INFO ADD CONSTRAINT CARD_ACCT_FK FOREIGN KEY (ACCT_NO) REFERENCES DAT_CARD_FINA (ACCT_NO); ALTER TABLE DAT_CARD_INFO ADD CONSTRAINT CARD_STATUS_FK FOREIGN KEY (CARD_STATUS) REFERENCES COD_CARD_STATUS (CARD_STATUS); ALTER TABLE DAT_CARD_INFO ADD CONSTRA

8、INT CARD_TITLE_FK FOREIGN KEY (TITLE_CODE) REFERENCES COD_TITLE_CODE (TITLE_CODE); ALTER TABLE DAT_CARD_INFO ADD CONSTRAINT CARD_OWNERSHIP_FK FOREIGN KEY (OWNERSHIP) REFERENCES COD_OWNERSHIP (OWNERSHIP); 表3. 保證金表 CREATE TABLE DAT_GUARANTEE_ACCT (ACCT_NO CHAR(12) NOT NULL ENABLE, GUARANTEE_STATUS CHA

9、R(1), STATUS_DATE DATE, ISSUE_CARD_DATE DATE, GUARANTEE_BAL NUMBER(9, 2) PCTFREE 15 PCTUSED 60 TABLESPACE SOURCE STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 200); 外鍵 81 / 128 文檔可自由編輯 ALTER TABLE DAT_GUARANTEE_ACCT ADD CONSTRAINT GUARANT_ACCT_FK FOREIGN KEY (ACCT_NO) REFERENCES

10、DAT_CARD_FINA (ACCT_NO); 表4. 黑卡表 CREATE TABLE DAT_HOT_CARD (CARD_NO CHAR(16), EXP_DATE DATE, PROCESS_CODE CHAR(1), TELLER_CODE CHAR(3), ENTRY_DATE DATE) PCTFREE 15 PCTUSED 60 TABLESPACE SOURCE STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 1 MINEXTENTS 1 MAXEXTENTS 200); 外鍵 ALTER TABLE DAT_HOT_CARD ADD CON

11、STRAINT HOT_CARD_FK FOREIGN KEY (CARD_NO) REFERENCES DAT_CARD_INFO (CARD_NO); ALTER TABLE DAT_HOT_CARD ADD CONSTRAINT HOT_PROCESS_FK FOREIGN KEY (PROCESS_CODE) REFERENCES COD_PROCESS_CODE (PROCESS_CODE); 索引 CREATE INDEX HOT_CARD_ENTRY_DATE ON DAT_HOT_CARD (ENTRY_DATE) TABLESPACE INDX; 表5. 大客戶表 82 /

12、128 文檔可自由編輯 CREATE TABLE DAT_VIP_AUTH (ACCT_NO CHAR(12) NOT NULL ENABLE, AUTH_LIMIT_AMT NUMBER(8, 0) PCTFREE 15 PCTUSED 60 TABLESPACE SOURCE STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 200); 外鍵 ALTER TABLE DAT_VIP_AUTH ADD CONSTRAINT VIP_AUTH_FK FOREIGN KEY (ACCT_NO) REFERENCES

13、 DAT_CARD_FINA (ACCT_NO); 表6. 商戶信息表 CREATE TABLE DAT_MERCHANT_INFO (MERCHANT_NO CHAR(10) NOT NULL ENABLE, MERCHANT_STATUS CHAR(1), STATUS_DATE DATE, MERCHANT_NAME_CHN VARCHAR2(30), MERCHANT_NAME_ENG VARCHAR2(30), SIC_CODE CHAR(4), OWNERSHIP CHAR(1), ADDRESS VARCHAR2(40), ZIP_CODE CHAR(6), AFFILIATED

14、 CHAR(6), EXP_DATE DATE, CONTACT_PERSON VARCHAR2(20), CONTACT_PERSON_TEL VARCHAR2(12), AUTH_MODE CHAR(1), AUTH_TEL VARCHAR2(12), BULLETIN_NUMBER CHAR(3), FLOOR_AMT_RMB NUMBER(6, 0), 83 / 128 文檔可自由編輯 DISC_RATE_OPTION CHAR(1), DISC_RATE_RMB NUMBER(6, 2), RELATED_BANK1_NAME VARCHAR2(30), RELATED_BANK1_

15、ACCT VARCHAR2(12), RELATED_BANK1_CURRENCY CHAR(2), RELATED_BANK2_NAME VARCHAR2(30), RELATED_BANK2_ACCT VARCHAR2(12), RELATED_BANK2_CURRENCY CHAR(2), TELLER_CODE CHAR(3), SUPERVISOR CHAR(3) PCTFREE 15 PCTUSED 60 TABLESPACE SOURCE STORAGE (INITIAL 3M NEXT 3M PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 200);

16、 主鍵 ALTER TABLE DAT_MERCHANT_INFO ADD CONSTRAINT MERCHANT_PK PRIMARY KEY (MERCHANT_NO) USING INDEX STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0) TABLESPACE INDX; 外鍵 ALTER TABLE DAT_MERCHANT_INFO ADD CONSTRAINT MERCHANT_OWNERSHIP_FK FOREIGN KEY (OWNERSHIP) REFERENCES COD_OWNERSHIP (OWNERSHIP); ALTER

17、 TABLE DAT_MERCHANT_INFO ADD CONSTRAINT MERCHANT_AUTH_FK FOREIGN KEY (AUTH_MODE) REFERENCES COD_AUTH_MODE (AUTH_MODE); 84 / 128 文檔可自由編輯 表7. POS信息表 CREATE TABLE DAT_POS_INFO (MERCHANT_NO CHAR(10) NOT NULL ENABLE, POS_NO VARCHAR2(8) NOT NULL ENABLE, POS_STATUS CHAR(1), STATUS_DATE DATE, MODEL CHAR(8),

18、 SERIAL_NO CHAR(8), DIAL_TYPE CHAR(1), TEL_NO CHAR(12), TELLER_CODE CHAR(3), SUPERVISOR CHAR(3) PCTFREE 15 PCTUSED 60 TABLESPACE SOURCE STORAGE (INITIAL 500K NEXT 500K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 200); 主鍵 ALTER TABLE DAT_POS_INFO ADD CONSTRAINT POS_PK PRIMARY KEY (POS_NO) USING INDEX STORA

19、GE (INITIAL 50K NEXT 50K PCTINCREASE 0) TABLESPACE INDX; 外鍵 ALTER TABLE DAT_POS_INFO ADD CONSTRAINT POS_MERCHANT_FK FOREIGN KEY (MERCHANT_NO) REFERENCES DAT_MERCHANT_INFO (MERCHANT_NO); 表8. 授權交易表 CREATE TABLE DAT_TRAN_AUTH (TRAN_ID CHAR(1), BCS_TRAN_TYPE CHAR(1), AUTH_STATUS CHAR(1), 85 / 128 文檔可自由編

20、輯 CARD_NO CHAR(16) NOT NULL ENABLE, AUTH_DATE DATE, AUTH_NO CHAR(6) NOT NULL ENABLE, AUTH_TIME CHAR(8), AUTH_AMT NUMBER(8, 0), ACCT_BAL NUMBER(10, 2), AUTH_AMT_TOTAL NUMBER(8, 0), MERCHANT_NO CHAR(10), POS_NO CHAR(8), TELLER_CODE CHAR(8), SUPERVISOR_CODE CHAR(3) PCTFREE 15 PCTUSED 60 TABLESPACE SOUR

21、CE STORAGE (INITIAL 300K NEXT 300K PCTINCREASE 1 MINEXTENTS 1 MAXEXTENTS 200); 外鍵 ALTER TABLE DAT_TRAN_AUTH ADD CONSTRAINT AUTH_CARD_FK FOREIGN KEY (CARD_NO) REFERENCES DAT_CARD_INFO (CARD_NO); ALTER TABLE DAT_ TRAN_AUTH ADD CONSTRAINT AUTH_TRAN_TYPE_FK FOREIGN KEY (BCS_TRAN_TYPE) REFERENCES COD_BCS

22、_TRAN_TYPE (BCS_TRAN_TYPE); ALTER TABLE DAT_TRAN_AUTH ADD CONSTRAINT AUTH_MERCHANT_FK FOREIGN KEY (MERCHANT_NO) REFERENCES DAT_MERCHANT_INFO (MERCHANT_NO); ALTER TABLE DAT_TRAN_AUTH ADD CONSTRAINT AUTH_POS_FK FOREIGN KEY (POS_NO) REFERENCES DAT_POS_INFO (POS_NO); CREATE INDEX TRAN_AUTH_DATE ON DAT_T

23、RAN_AUTH (AUTH_DATE) TABLESPACE INDX; 86 / 128 文檔可自由編輯 索引 CREATE INDEX TRAN_AUTH_DATE ON DAT_TRAN_AUTH (AUTH_DATE) TABLESPACE INDX; 表9. 長城卡交易表 CREATE TABLE DAT_TRAN_HOLD (TRAN_ID CHAR(1), BCS_TRAN_TYPE CHAR(2), ACCT_NO CHAR(12) NOT NULL ENABLE, POSTED_DATE DATE, CARD_SUB_NO CHAR(2), BILLING_DATE CHA

24、R(2), TRAN_AMT NUMBER(12, 2), ACCT_BAL NUMBER(12, 2), PARTICULAR VARCHAR2(10), AUTH_NO CHAR(6), MERCHANT_NO CHAR(10), INVOICE_DATE DATE, TELLER_SEQ_NO CHAR(5), SEQ_NO CHAR(5), TELLER_NO CHAR(3) PCTFREE 15 PCTUSED 60 TABLESPACE SOURCE STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 1 MINEXTENTS 1 MAXEXTENTS

25、200); 外鍵 ALTER TABLE DAT_TRAN_HOLD ADD CONSTRAINT HOLD_ACCT_FK FOREIGN KEY (ACCT_NO) REFERENCES DAT_CARD_FINA (ACCT_NO); ALTER TABLE DAT_ TRAN_HOLD ADD CONSTRAINT HOLD_TRAN_TYPE_FK FOREIGN KEY (BCS_TRAN_TYPE) REFERENCES COD_BCS_TRAN_TYPE (BCS_TRAN_TYPE); 87 / 128 文檔可自由編輯 ALTER TABLE DAT_TRAN_HOLD AD

26、D CONSTRAINT HOLD_MERCHANT_FK FOREIGN KEY (MERCHANT_NO) REFERENCES DAT_MERCHANT_INFO (MERCHANT_NO); 索引 CREATE INDEX TRAN_HOLD_INVOICE_DATE ON DAT_TRAN_HOLD (INVOICE_DATE) TABLESPACE INDX; 表10. 透支補正報表表 CREATE TABLE RPT_TRAN_SLIP_LIST (SEQ_NO CHAR(3) NOT NULL ENABLE, CARD_NO CHAR(12), BCS_TRAN_TYPE CH

27、AR(2), OVERDRAFT_PUNISH_INT NUMBER(10, 2), OVERDRAFTBAL_BASE NUMBER(13, 2), OVERDRAFT_RATE NUMBER(5, 3), OVERDRAFT_DATE DATE, OVERDRAFT_DAYS NUMBER(4, 0), REPORT_DATE DATE) PCTFREE 15 PCTUSED 60 TABLESPACE SOURCE STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 1 MINEXTENTS 1 MAXEXTENTS 200); 外鍵 ALTER TA

28、BLE RPT_TRAN_SLIP_LIST ADD CONSTRAINT TRAN_SLIP_CARD_FK FOREIGN KEY (CARD_NO) REFERENCES DAT_CARD_INFO (CARD_NO); 索引 CREATE INDEX TRAN_SLIP_REPORT_DATE ON RPT_TRAN_SLIP_LIST 88 / 128 文檔可自由編輯 (REPORT_DATE) TABLESPACE INDX; 表11. 長城卡交易報表表 CREATE TABLE RPT_TRAN_ALL_LIST (REF_NO CHAR(8) NOT NULL ENABLE,

29、ACCT_NO CHAR(12) NOT NULL ENABLE, CARD_SUB_NO CHAR(2), BCS_TRAN_TYPE CHAR(2), TRAN_ID CHAR(1), TRAN_AMT NUMBER(10, 2), INVOICE_DATE DATE, REPORT_DATE DATE) PCTFREE 15 PCTUSED 60 TABLESPACE SOURCE STORAGE (INITIAL 200K NEXT 200K PCTINCREASE 1 MINEXTENTS 1 MAXEXTENTS 200); 外鍵 ALTER TABLE RPT_TRAN_ALL_

30、LIST ADD CONSTRAINT TRAN_ALL_ACCT_FK FOREIGN KEY (ACCT_NO) REFERENCES DAT_CARD_FINA (ACCT_NO); ALTER TABLE RPT_TRAN_ALL_LIST ADD CONSTRAINT TRAN_ALL_TYPE_FK FOREIGN KEY (BCS_TRAN_TYPE) REFERENCES COD_BCS_TRAN_TYPE (BCS_TRAN_TYPE); 索引 CREATE INDEX TRAN_ALL_INVOICE_DATE ON RPT_TRAN_ALL_LIST (INVOICE_D

31、ATE) TABLESPACE INDX; 89 / 128 文檔可自由編輯 CREATE INDEX TRAN_ALL_REPORT_DATE ON RPT_TRAN_ALL_LIST (REPORT_DATE) TABLESPACE INDX; 2 2、CardpoolCardpool 系統(tǒng)系統(tǒng) 表1. Cardpool日志表 CREATE TABLE DAT_CARDPOOL_LOG (EDC_TRAN_TYPE CHAR(2), CARD_NO VARCHAR2(20), MERCH_NO VARCHAR2(15), POS_NO VARCHAR2(8), EXP_DATE DATE,

32、 RESPONSE_CODE CHAR(2), AUTH_NO CHAR(6), TRAN_AMT NUMBER(12, 2), CURRENCY_CODE CHAR(3), INVOICE_DATE DATE, INVOICE_TIME DATE, MCC_CODE CHAR(4), TRANS_MODE CHAR(3), INVOICE_NO CHAR(6), REFERENCE_NO VARCHAR2(12), CARD_TYPE CHAR(2) PCTFREE 15 PCTUSED 60 TABLESPACE SOURCE STORAGE (INITIAL 2M NEXT 2M PCT

33、INCREASE 1 MINEXTENTS 1 MAXEXTENTS 200); 外鍵 ALTER TABLE DAT_CARDPOOL_LOG ADD CONSTRAINT CARDPOOL_TRAN_TYPE_FK FOREIGN KEY (EDC_TRAN_TYPE) REFERENCES COD_EDC_TRAN_TYPE (EDC_TRAN_TYPE); 90 / 128 文檔可自由編輯 ALTER TABLE DAT_ CARDPOOL_LOG ADD CONSTRAINT CARDPOOL_MERCH_FK FOREIGN KEY (MERCH_NO) REFERENCES DA

34、T_MERCH_INFO (MERCH_NO); 索引 CREATE INDEX CARDPOOL_LOG_INVOICE_DATE ON DAT_CARDPOOL_LOG (INVOICE_DATE) TABLESPACE INDX; 表2. EDC商戶信息表 CREATE TABLE DAT_MERCH_INFO (MERCH_NO VARCHAR2(15) NOT NULL ENABLE, MERCHANT_NO VARCHAR2(15) NOT NULL ENABLE, MERCHANT_NAME_CHN VARCHAR2(24), MERCHANT_NAME_ENG VARCHAR2

35、(30), AMEX_NO VARCHAR2(15), DINERS_NO VARCHAR2(15), JCB_NO VARCHAR2(15), RELATED_BANK_NAME VARCHAR2(20), RELATED_BANK_ACCT VARCHAR2(25), MCC_CODE CHAR(4), VISA_FEE NUMBER(4, 2), MASTER_FEE NUMBER(4, 2), AMEX_FEE NUMBER(4, 2), DINERS_FEE NUMBER(4, 2), JCB_FEE NUMBER(4, 2), GW_FEE NUMBER(4, 2), BUILD_

36、DATE DATE) PCTFREE 15 PCTUSED 60 TABLESPACE SOURCE STORAGE (INITIAL 3M NEXT 3M PCTINCREASE 1 MINEXTENTS 1 MAXEXTENTS 200); 91 / 128 文檔可自由編輯 主鍵 ALTER TABLE DAT_MERCH_INFO ADD CONSTRAINT MERCH_PK PRIMARY KEY (MERCH_NO) USING INDEX STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0) TABLESPACE INDX; 外鍵 ALTE

37、R TABLE DAT_MERCH_INFO ADD CONSTRAINT MERCH_MERCHANT_FK FOREIGN KEY (MERCAHNT_NO) REFERENCES DAT_MERCHANT_INFO (MERCHANT_NO); 索引 CREATE INDEX MERCH_INFO_BUILD_DATE ON DAT_MERCH_INFO (BUILD_DATE) TABLESPACE INDX; 3 3、NASNAS 系統(tǒng)系統(tǒng) 表1. NAS授權交易表 CREATE TABLE DAT_NAS_MONITOR (CARD_NO CHAR(16) NOT NULL DIS

38、ABLE, EXP_DATE DATE, NAS_TRAN_TYPE CHAR(4), TRAN_AMT NUMBER(6, 0), AUTH_TIME DATE, MERCHANT_NO CHAR(10), BRANCH_ID CHAR(4), AUTH_NO VARCHAR2(8), RESPONSE_CODE CHAR(2), BUILD_DATE DATE) PCTFREE 15 PCTUSED 60 92 / 128 文檔可自由編輯 TABLESPACE SOURCE STORAGE (INITIAL 200K NEXT 200K PCTINCREASE 1 MINEXTENTS 1

39、 MAXEXTENTS 200); 外鍵 ALTER TABLE DAT_NAS_MONITOR ADD CONSTRAINT MONITOR_TRAN_TYPE_FK FOREIGN KEY (NAS_TRAN_TYPE) REFERENCES COD_NAS_TRAN_TYPE (NAS_TRAN_TYPE); ALTER TABLE DAT_NAS_MONITOR ADD CONSTRAINT MONITOR_MERCHANT_FK FOREIGN KEY (MERCHANT_NO) REFERENCES DAT_MERCHANT_INFO (MERCHANT_NO); ALTER TA

40、BLE DAT_NAS_MONITOR ADD CONSTRAINT MONITOR_BOC_FK FOREIGN KEY (BRANCH_ID) REFERENCES COD_BOC_BRANCH (BRANCH_ID); 索引 CREATE INDEX NAS_MONITOR_BUILD_DATE ON DAT_NAS_MONITOR (BUILD_DATE) TABLESPACE INDX; 4 4、生成表、生成表 表1.每天變動帳戶余額表 CREATE TABLE CHG_CARD_FINA (ACCT_NO CHAR(12), ACCT_STATUS CHAR(1), STATUS_

41、DATE DATE, GUARANTEE CHAR(1), GUARANTEE_STATUS CHAR(1), GUARANTEE_STATUS_DATE DATE, GUARANTEE_BAL NUMBER(9, 2), ISSUE_CODE CHAR(2), DEBIT NUMBER(9, 93 / 128 文檔可自由編輯 2), CREDIT NUMBER(9, 2), CURRENT_BAL NUMBER(12, 2), TRAN_COUNT NUMBER(3, 0), MERCHANT_COUNT NUMBER(3, 0), BUILD_DATE DATE) PCTFREE 15 P

42、CTUSED 60 TABLESPACE CHANGE STORAGE (INITIAL 10M NEXT 10M PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 249); 外鍵 ALTER TABLE CHG_CARD_FINA ADD CONSTRAINT CHANGE_ACCT_FK FOREIGN KEY (ACCT_NO) REFERENCES DAT_CARD_FINA (ACCT_NO); ALTER TABLE CHG_CARD_FINA ADD CONSTRAINT CHANGE_FINA_STATUS_FK FOREIGN KEY (ACCT_

43、STATUS) REFERENCES COD_ACCT_STATUS (ACCT_STATUS); ALTER TABLE CHG_CARD_FINA ADD CONSTRAINT CHANGE_FINA_ISSUE_FK FOREIGN KEY (ISSUE_CODE) REFERENCES COD_BOCBJ_BRANCH (ISSUE_CODE); 索引 CREATE INDEX CARD_FINA_BUILD_DATE ON CHG_CARD_FINA (BUILD_DATE) TABLESPACE INDX; 表2.每天變動帳戶透支表 CREATE TABLE FAT_ACCT_OV

44、ER (ACCT_NO CHAR(12), OVERDRAFT_DATE DATE, 94 / 128 文檔可自由編輯 OVERDRAFTLAST_DATE DATE, OVERDRAFT_LAST_BAL NUMBER(11, 2), CURRENT_BAL NUMBER(12, 2), OVERDRAFTBAL_TOTAL NUMBER(14, 2), ISSUE_CODE CHAR(2), BUILD_DATE DATE) PCTFREE 15 PCTUSED 60 TABLESPACE CHANGE STORAGE (INITIAL 10M NEXT 10M PCTINCREASE 0

45、 MINEXTENTS 1 MAXEXTENTS 249); 外鍵 ALTER TABLE FAT_ACCT_OVER ADD CONSTRAINT ACCT_OVER_ACCT_FK FOREIGN KEY (ACCT_NO) REFERENCES DAT_CARD_FINA (ACCT_NO); ALTER TABLE FAT_ACCT_OVER ADD CONSTRAINT ACCT_OVER_ISSUE_FK FOREIGN KEY (ISSUE_CODE) REFERENCES COD_BOCBJ_BRANCH (ISSUE_CODE); 索引 CREATE INDEX ACCT_O

46、VER_BUILD_DATE ON FAT_ACCT_OVER (BUILD_DATE) TABLESPACE INDX; 表3.每天變動帳戶透支信息表 CREATE TABLE FAT_OVER_INFO (ACCT_NO CHAR(12), CARD_ATTRIBUTE CHAR(1), OVERDRAFT_STATUS CHAR(1), OVERDRAFT_AMT NUMBER(12, 2), OVERDRAFT_CHANGE_AMT NUMBER(8, 2), OVERDRAFT_RISK_GRADE CHAR(1), 95 / 128 文檔可自由編輯 OVERDRAFT_DATE D

47、ATE, OVERDRAFT_DAYS NUMBER(4), OVERDRAFT_PUNISH_INT NUMBER(8, 2), ISSUE_CODE CHAR(2), BUILD_DATE DATE) PCTFREE 15 PCTUSED 60 TABLESPACE USR STORAGE (INITIAL 10M NEXT 10M PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 249); 外鍵 ALTER TABLE FAT_OVER_INFO ADD CONSTRAINT OVER_INFO_ACCT_FK FOREIGN KEY (ACCT_NO) RE

48、FERENCES DAT_CARD_FINA (ACCT_NO); ALTER TABLE FAT_OVER_INFO ADD CONSTRAINT OVER_INFO_ATTRIBUTE_FK FOREIGN KEY (CARD_ATTRIBUTE) REFERENCES COD_CARD_ATTRIBUTE (CARD_ATTRIBUTE); ALTER TABLE FAT_OVER_INFO ADD CONSTRAINT OVER_INFO_STATUS_FK FOREIGN KEY (OVERDRAFT_STATUS) REFERENCES COD_OVERDRAFT_STATUS (

49、OVERDRAFT_STATUS); ALTER TABLE FAT_OVER_INFO ADD CONSTRAINT OVER_INFO_GRADE_FK FOREIGN KEY (RISK_GRADE) REFERENCES COD_RISK_GRADE (RISK_GRADE); ALTER TABLE FAT_OVER_INFO ADD CONSTRAINT OVER_INFO_ISSUE_FK FOREIGN KEY (ISSUE_CODE) REFERENCES COD_BOCBJ_BRANCH (ISSUE_CODE); 索引 96 / 128 文檔可自由編輯 CREATE IN

50、DEX OVER_INFO_BUILD_DATE ON FAT_OVER_INFO (BUILD_DATE) TABLESPACE INDX; 表4. 每天變動卡信息表 CREATE TABLE CHG_CARD_INFO (CARD_NO CHAR(16), ACCT_NO CHAR(12), CARD_STATUS CHAR(1), STATUS_DATE DATE, DEBIT NUMBER(9, 2), CREDIT NUMBER(9, 2), TRAN_COUNT NUMBER(3, 0), MERCHANT_COUNT NUMBER(3, 0), BUILD_DATE DATE)

51、PCTFREE 15 PCTUSED 60 TABLESPACE CHANGE STORAGE (INITIAL 10M NEXT 10M PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 249); 外鍵 ALTER TABLE CHG_CARD_INFO ADD CONSTRAINT CHANGE_CARD_FK FOREIGN KEY (CARD_NO) REFERENCES DAT_CARD_INFO (CARD_NO); ALTER TABLE CHG_CARD_INFO ADD CONSTRAINT CHANGE_CARD_ACCT_FK FOREIGN

52、KEY (ACCT_NO) REFERENCES DAT_CARD_FINA (ACCT_NO); ALTER TABLE CHG_CARD_INFO ADD CONSTRAINT CARD_STATUS_FK FOREIGN KEY (CARD_STATUS) REFERENCES COD_CARD_STATUS (CARD_STATUS); 索引 97 / 128 文檔可自由編輯 CREATE INDEX CARD_INFO_BUILD_DATE ON CHG_CARD_INFO (BUILD_DATE) TABLESPACE INDX; 表5.每天變動商戶信息表 CREATE TABLE CHG_MERCHANT_INFO (MERCHANT_NO CHAR(10) NOT NULL ENABLE, M