数据仓库分析系统整体设计方案

目 录 一、概述 .2 二、四科室需求 .3 1、风险科需求 .3 2、市场科需求 .13 3、业务管理科需求 .14 4、计划资金科需求 .15 三、需求分析 .23 1、维表 .23 2、事实表 .23 3、事务业务处理过程及业务术语 .23 4、主键 .24 5、外键 .24 四、系统结构图及业务数据流图 .25 1、系统结构图 .25 2、数据流图 .26 五、源数据表结构 .27 1、BCS 系统 .27 2、CARDPOOL 系统 .34 3、NAS 系统 .36 4、BCS 系统报表 .37 六、生成表结构 .39 七、码表结构 .43 八、结果表结构 .50 九、数据表创建方法 .51 1、BCS 系统 .51 2、CARDPOOL 系统 .57 3、NAS 系统 .58 4、生成表 .58 5、码表 .62 十、数据处理过程 .68 1、目录结构 .68 2、流程说明 .68 十一、问题及处理方法 .80 一、概述 Bill Inmon（数据仓库之父）在Building the 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); 表2. 卡信息表 CREATE TABLE DAT_CARD_INFO (CARD_NO CHAR(16) NOT NULL ENABLE, 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, NATIONALITY 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_FUNCTION 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) 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 CONSTRAINT 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 CHAR(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); 外键 ALTER TABLE DAT_GUARANTEE_ACCT ADD CONSTRAINT GUARANT_ACCT_FK FOREIGN KEY (ACCT_NO) REFERENCES 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 CONSTRAINT 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. 大客户表 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 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 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), DISC_RATE_OPTION CHAR(1), DISC_RATE_RMB NUMBER(6, 2), RELATED_BANK1_NAME VARCHAR2(30), RELATED_BANK1_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); 主键 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 TABLE DAT_MERCHANT_INFO ADD CONSTRAINT MERCHANT_AUTH_FK FOREIGN KEY (AUTH_MODE) REFERENCES COD_AUTH_MODE (AUTH_MODE); 表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), 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 STORAGE (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), 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 SOURCE 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_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_TRAN_AUTH (AUTH_DATE) TABLESPACE INDX; 索引 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 CHAR(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 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); ALTER TABLE DAT_TRAN_HOLD ADD 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 CHAR(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 TABLE 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 (REPORT_DATE) TABLESPACE INDX; 表11. 长城卡交易报表表 CREATE TABLE RPT_TRAN_ALL_LIST (REF_NO CHAR(8) NOT NULL ENABLE, 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_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_DATE) TABLESPACE INDX; CREATE INDEX TRAN_ALL_REPORT_DATE ON RPT_TRAN_ALL_LIST (REPORT_DATE) TABLESPACE INDX; 2、 Cardpool 系统 表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, 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 PCTINCREASE 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); ALTER TABLE DAT_ CARDPOOL_LOG ADD CONSTRAINT CARDPOOL_MERCH_FK FOREIGN KEY (MERCH_NO) REFERENCES DAT_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(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_DATE DATE) PCTFREE 15 PCTUSED 60 TABLESPACE SOURCE STORAGE (INITIAL 3M NEXT 3M PCTINCREASE 1 MINEXTENTS 1 MAXEXTENTS 200); 主键 ALTER TABLE DAT_MERCH_INFO ADD CONSTRAINT MERCH_PK PRIMARY KEY (MERCH_NO) USING INDEX STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0) TABLESPACE INDX; 外键 ALTER 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、 NAS 系统 表1. NAS授权交易表 CREATE TABLE DAT_NAS_MONITOR (CARD_NO CHAR(16) NOT NULL DISABLE, 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 TABLESPACE SOURCE STORAGE (INITIAL 200K NEXT 200K PCTINCREASE 1 MINEXTENTS 1 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 TABLE 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、生成表 表1.每天变动帐户余额表 CREATE TABLE CHG_CARD_FINA (ACCT_NO CHAR(12), ACCT_STATUS CHAR(1), STATUS_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, 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 PCTUSED 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_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_OVER (ACCT_NO CHAR(12), OVERDRAFT_DATE DATE, 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 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_OVER_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), OVERDRAFT_DATE DATE, 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) REFERENCES 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 (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); 索引 CREATE INDEX 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) 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 KEY (ACCT_NO) REFERENCES DAT_CARD_FINA (ACCT_NO); ALTER TABLE CHG_CARD_INFO ADD CONSTRAINT CARD_STATUS_FK FOREIGN KEY (CARD_