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

目 录 一 概述 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 STATUS REFERENCES COD CARD STATUS CARD STATUS 索引 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 MERCHANT STATUS CHAR 1 STATUS DATE DATE EXP DATE DATE OWNERSHIP CHAR 1 AUTH MODE CHAR 1 DEBIT NUMBER 9 2 CREDIT NUMBER 9 2 TRAN COUNT NUMBER 5 0 POS COUNT NUMBER 3 0 DISC RATE OPTION CHAR 1 BUILD DATE DATE PCTFREE 15 PCTUSED 60 TABLESPACE CHANGE STORAGE INITIAL 500K NEXT 500K PCTINCREASE 1 MINEXTENTS 1 MAXEXTENTS 200 外键 ALTER TABLE CHG MERCHANT INFO ADD CONSTRAINT CHANGE MERCHANT FK FOREIGN KEY MERCHANT NO REFERENCES DAT MERCHANT INFO MERCHANT NO ALTER TABLE CHG MERCHANT INFO ADD CONSTRAINT CHANGE MERCHANT STATUS FK FOREIGN KEY MERCHANT STATUS REFERENCES COD MERCHANT STATUS MERCHANT STATUS 索引 CREATE INDEX MERCHANT INFO BUILD DATE ON CHG MERCHANT INFO BUILD DATE TABLESPACE INDX 表6 每天变动POS信息表 CREATE TABLE CHG POS INFO MERCHANT NO CHAR 10 NOT NULL ENABLE POS NO VARCHAR2 8 NOT NULL ENABLE POS STATUS CHAR 1 STATUS DATE DATE DIAL TYPE CHAR 1 DEBIT NUMBER 9 2 CREDIT NUMBER 9 2 TRAN COUNT NUMBER 5 0 BUILD DATE DATE PCTFREE 15 PCTUSED 60 TABLESPACE CHANGE STORAGE INITIAL 300K NEXT 300K PCTINCREASE 1 MINEXTENTS 1 MAXEXTENTS 200 外键 ALTER TABLE CHG POS INFO ADD CONSTRAINT CHANGE POS FK FOREIGN KEY POS NO REFERENCES DAT POS INFO POS NO ALTER TABLE CHG POS INFO ADD CONSTRAINT CHANGE POS MERCHANT FK FOREIGN KEY MERCHANT NO REFERENCES DAT MERCHANT INFO MERCHANT NO 索引 CREATE INDEX POS INFO BUILD DATE ON CHG POS INFO BUILD DATE TABLESPACE INDX 5 码表 表1 余额分组码表 CREATE TABLE COD BAL GROUP BAL GROUP CHAR 14 BAL MAX NUMBER 6 0 BAL MIN NUMBER 6 0 PCTFREE 15 PCTUSED 60 TABLESPACE USR 表2 北京分行支行码表 CREATE TABLE COD BOCBJ BRANCH ISSUE CODE CHAR 2 ISSUE NAME VARCHAR2 8 PCTFREE 15 PCTUSED 60 TABLESPACE USR 主键 ALTER TABLE COD BOCBJ BRANCH ADD CONSTRAINT ISSUE PK PRIMARY KEY ISSUE CODE USING INDEX STORAGE INITIAL 1K NEXT 1K PCTINCREASE 0 TABLESPACE INDX 表3 卡类型码表 CREATE TABLE COD CARD TYPE CARD TYPE CHAR 2 CARD NAME VARCHAR2 20 PCTFREE 15 PCTUSED 60 TABLESPACE USR 主键 ALTER TABLE COD CARD TYPE ADD CONSTRAINT CARD TYPE PK PRIMARY KEY CARD TYPE USING INDEX STORAGE INITIAL 1K NEXT 1K PCTINCREASE 0 TABLESPACE INDX 表4 帐户状态码表 CREATE TABLE COD ACCT STATUS ACCT STATUS CHAR 2 STATUS NAME VARCHAR2 20 PCTFREE 15 PCTUSED 60 TABLESPACE USR 主键 ALTER TABLE COD ACCT STATUS ADD CONSTRAINT ACCT STATUS PK PRIMARY KEY ACCT STATUS USING INDEX STORAGE INITIAL 1K NEXT 1K PCTINCREASE 0 TABLESPACE INDX 表5 卡状态码表 CREATE TABLE COD CARD STATUS CARD STATUS CHAR 2 STATUS NAME VARCHAR2 20 PCTFREE 15 PCTUSED 60 TABLESPACE USR 主键 ALTER TABLE COD CARD STATUS ADD CONSTRAINT CARD STATUS PK PRIMARY KEY CARD STATUS USING INDEX STORAGE INITIAL 1K NEXT 1K PCTINCREASE 0 TABLESPACE INDX 表6 单位所有制码表 CREATE TABLE COD OWNERSHIP OWNERSHIP CHAR 1 OWNERSHIP NAME VARCHAR2 10 PCTFREE 15 PCTUSED 60 TABLESPACE USR 主键 ALTER TABLE COD OWNERSHIP ADD CONSTRAINT OWNERSHIP PK PRIMARY KEY OWNERSHIP USING INDEX STORAGE INITIAL 1K NEXT 1K PCTINCREASE 0 TABLESPACE INDX 表7 持卡人职务码表 CREATE TABLE COD TITLE CODE TITLE CODE CHAR 2 TITLE NAME VARCHAR2 10 PCTFREE 15 PCTUSED 60 TABLESPACE USR 主键 ALTER TABLE COD TITLE CODE ADD CONSTRAINT TITLE PK PRIMARY KEY TITLE CODE USING INDEX STORAGE INITIAL 1K NEXT 1K PCTINCREASE 0 TABLESPACE INDX 表8 扣卡代码码表 CREATE TABLE COD PROCESS CODE PROCESS CODE CHAR 1 PROCESS NAME VARCHAR2 20 PCTFREE 15 PCTUSED 60 TABLESPACE USR 主键 ALTER TABLE COD PROCESS CODE ADD CONSTRAINT PROCESS PK PRIMARY KEY PROCESS CODE USING INDEX STORAGE INITIAL 1K NEXT 1K PCTINCREASE 0 TABLESPACE INDX 表9 授权方式码表 CREATE TABLE COD AUTH MODE AUTH MODE CHAR 1 AUTH NAME VARCHAR2 20 PCTFREE 15 PCTUSED 60 TABLESPACE USR 主键 ALTER TABLE COD AUTH MODE ADD CONSTRAINT AUTH MODE PK PRIMARY KEY AUTH MODE USING INDEX STORAGE INITIAL 1K NEXT 1K PCTINCREASE 0 TABLESPACE INDX 表10 日期分组码表 CREATE TABLE COD DAY GROUP DAY GROUP CHAR 14 DAY MAX NUMBER 4 0 DAY MIN NUMBER 4 0 PCTFREE 15 PCTUSED 60 TABLESPACE USR 表11 商户类型码表 CREATE TABLE COD MERCHANT TYPE MERCHANT TYPE CHAR 1 MERCHANT NAME VARCHAR2 20 PCTFREE 15 PCTUSED 60 TABLESPACE USR 主键 ALTER TABLE COD MERCHANT TYPE ADD CONSTRAINT MERCHANT TYPE PK PRIMARY KEY MERCHANT TYPE USING INDEX STORAGE INITIAL 1K NEXT 1K PCTINCREASE 0 TABLESPACE INDX 表12 BCS交易类型码表 CREATE TABLE COD BCS TRAN TYPE BCS TRAN TYPE CHAR 2 BCS TRAN NAME VARCHAR2 20 PCTFREE 15 PCTUSED 60 TABLESPACE USR 主键 ALTER TABLE COD BCS TRAN TYPE ADD CONSTRAINT BCS TRAN TYPE PK PRIMARY KEY BCS TRAN TYPE USING INDEX STORAGE INITIAL 1K NEXT 1K PCTINCREASE 0 TABLESPACE INDX 表13 EDC交易类型码表 CREATE TABLE COD EDC TRAN TYPE EDC TRAN TYPE CHAR 2 EDC TRAN NAME VARCHAR2 20 EDC TRAN NAME ENG VARCHAR2 30 PCTFREE 15 PCTUSED 60 TABLESPACE USR 主键 ALTER TABLE COD EDC TRAN TYPE ADD CONSTRAINT EDC TRAN TYPE PK PRIMARY KEY EDC TRAN TYPE USING INDEX STORAGE INITIAL 1K NEXT 1K PCTINCREASE 0 TABLESPACE INDX 表14 NAS交易类型码表 CREATE TABLE COD NAS TRAN TYPE NAS TRAN TYPE CHAR 2 NAS TRAN NAME VARCHAR2 20 PCTFREE 15 PCTUSED 60 TABLESPACE USR 主键 ALTER TABLE COD NAS TRAN TYPE ADD CONSTRAINT NAS TRAN TYPE PK PRIMARY KEY NAS TRAN TYPE USING INDEX STORAGE INITIAL 1K NEXT 1K PCTINCREASE 0 TABLESPACE INDX 表15 中国银行分行码表 CREATE TABLE COD BOC BRANCH BRANCH ID CHAR 4 IN OUT ID CHAR 1 BRANCH NAME CHAR 40 PCTFREE 15 PCTUSED 60 TABLESPACE USR 主键 ALTER TABLE COD BOC BRANCH ADD CONSTRAINT BOC BRANCH PK PRIMARY KEY BRANCH CODE USING INDEX STORAGE INITIAL 1K NEXT 1K PCTINCREASE 0 TABLESPACE INDX 表16 帐号大客户对应码表 CREATE TABLE COD ACCT EMP ACCT NO CHAR 12 EMPLOYER NAME VARCHAR2 30 PCTFREE 15 PCTUSED 60 TABLESPACE USR ALTER TABLE COD ACCT EMP ADD CONSTRAINT ACCT EMP ACCT FK FOREIGN KEY ACCT NO REFERENCES DAT CARD FINA ACCT NO 表17 卡属性码表 CREATE TABLE COD CARD ATTRIBUTE CARD ATTRIBUTE CHAR 1 ATTRIBUTE NAME VARCHAR2 20 PCTFREE 15 PCTUSED 60 TABLESPACE USR 主键 ALTER TABLE COD CARD ATTRIBUTE ADD CONSTRAINT ATTRIBUTE PK PR