数据库课程设计--银行存取款管理

存款单取款单金额存款方式取款单号金额取款方式存款取款存款日期取款日期账号账号金额银行储蓄业务数据库分析一、系统功能简介本系统是银行用户的存取款系统。主要功能是管理各个用户存取款的相关数据。二、本系统由三张表组成，具体如下：表名属性(字段)名储户表账号、身份证号、姓名、性别、身高、地址、存款余额存款单表存款单号、金额、存款方式、账号、存款日期取款单表取款单号、金额、取款方式、账号、取款日期系统表基本信息三、表间关系如下：一个存款单或取款单属于一个用户，一个用户可以拥有多次存款单或取款单。用户通过存款操作与存款单建立联系，通过取款操作与取款单建立联系。ER模型如下图：ER图，如下：储户账号身份证号姓名地址地址存款余额存款单号性别身高四、数据准备前期准备工作：以system账户登录，创建新账户createuserdyidentifiedbylyjn;grantdbatody;connectdy/dy@orcl;各个表的具体信息和创建表的代码如下：储户表(luser)字段(属性)含义字段(属性)名字段(属性)类型账号acnumVARCHAR(5)身份证号idNUMBER姓名nameVARCHAR2(20)性别sexVARCHAR2(2)身高heightNUMBER(3,2)地址addressVARCHAR2(20)存款余额(亿)Balance(billion)INT创建储户表的代码如下：CREATETABLEluser(acnumVARCHAR2(5)NOTNULL,idNUMBERNOTNULL,nameVARCHAR2(20)NOTNULL,sexVARCHAR2(2)CHECK(sexIN('男','女')),heightNUMBER(3,2)NOTNULL,addressVARCHAR2(20)NOTNULL,balanceINTNOTNULL);ALTERTABLEluser添加主键约束ADDCONSTRAINTXPKluserPRIMARYKEY(acnum);存款单表(depositslip)字段(属性)含义字段(属性)名字段(属性)类型存款单号dps_numNUMBER账号acnumVARCHAR2(5)存款时间dps_timeTIMESTAMP金额(亿)moneyINT存款方式dps_wayVARCHAR2(10)创建存款单表的代码如下：CREATETABLEdepositslip(dps_numNUMBERNOTNULL,moneyINTCHECK(money=0),dps_wayVARCHAR2(10)NOTNULL,acnumVARCHAR2(5)NOTNULL,dps_timeTIMESTAMPNULL);ALTERTABLEdepositslip添加主键约束ADDCONSTRAINTXPKdepositslipPRIMARYKEY(dps_num,acnum);ALTERTABLEdepositslip在depositslip表中的acnum字段中添加外键约束ADD(CONSTRAINTR_5FOREIGNKEY(acnum)REFERENCESluser(acnum));取款单表(drawslip)字段(属性)含义字段(属性)名字段(属性)类型取款单号dw_numNUMBER账号acnumVARCHAR2(5)取款时间dw_timeTIMESTAMP金额(亿)MoneyINT取款方式dw_wayVARCHAR2(10)创建取款单表的代码如下：CREATETABLEdrawslip(dw_numNUMBERNOTNULL,moneyINTCHECK(money=0),dw_wayVARCHAR2(10)NOTNULL,acnumVARCHAR2(5)NOTNULL,dw_timeTIMESTAMPNULL);ALTERTABLEdrawslip添加主键约束ADDCONSTRAINTXPKdrawslipPRIMARYKEY(dw_num,acnum);ALTERTABLEdrawslip在drawslip表中的acnum字段中添加外键约束ADD(CONSTRAINTR_6FOREIGNKEY(acnum)REFERENCESluser(acnum));五、插入(insert)数据1、插入储户表(luser)数据insertintoluser(ACNUM,ID,NAME,SEX,HEIGHT,ADDRESS,BALANCE)values('00001',3422101,'刘忠田','男',1.72,'北京市朝阳区',345);insertintoluser(ACNUM,ID,NAME,SEX,HEIGHT,ADDRESS,BALANCE)values('00002',3422102,'周正义','男',1.78,'天津市宝坻区',123);insertintoluser(ACNUM,ID,NAME,SEX,HEIGHT,ADDRESS,BALANCE)values('00003',3422103,'陈光标','男',1.80,'南京市新街口',333);insertintoluser(ACNUM,ID,NAME,SEX,HEIGHT,ADDRESS,BALANCE)values('00004',3422104,'张茵','女',1.65,'上海市黄浦区',234);insertintoluser(ACNUM,ID,NAME,SEX,HEIGHT,ADDRESS,BALANCE)values('00005',3422105,'陈丽华','女',1.70,'香港市旺角区',111);2、插入存款单表(depositslip)数据insertintodepositslip(DPS_NUM,ACNUM,MONEY,DPS_TIME,DPS_WAY)values(98001,'00001',120,to_date('08-08-2009','dd-mm-yyyy'),'整存整取');insertintodepositslip(DPS_NUM,ACNUM,MONEY,DPS_TIME,DPS_WAY)values(98002,'00001',300,to_date('10-08-2009','dd-mm-yyyy'),'整存整取');insertintodepositslip(DPS_NUM,ACNUM,MONEY,DPS_TIME,DPS_WAY)values(98003,'00002',100,to_date('12-08-2009','dd-mm-yyyy'),'整存零取');insertintodepositslip(DPS_NUM,ACNUM,MONEY,DPS_TIME,DPS_WAY)values(98004,'00002',150,to_date('12-09-2009','dd-mm-yyyy'),'整存零取');insertintodepositslip(DPS_NUM,ACNUM,MONEY,DPS_TIME,DPS_WAY)values(98005,'00003',200,to_date('15-08-2009','dd-mm-yyyy'),'零存整取');insertintodepositslip(DPS_NUM,ACNUM,MONEY,DPS_TIME,DPS_WAY)values(98006,'00003',100,to_date('14-09-2009','dd-mm-yyyy'),'零存整取');insertintodepositslip(DPS_NUM,ACNUM,MONEY,DPS_TIME,DPS_WAY)values(98007,'00003',80,to_date('23-09-2009','dd-mm-yyyy'),'零存整取');insertintodepositslip(DPS_NUM,ACNUM,MONEY,DPS_TIME,DPS_WAY)values(98008,'00004',80,to_date('23-08-2009','dd-mm-yyyy'),'零存零取');insertintodepositslip(DPS_NUM,ACNUM,MONEY,DPS_TIME,DPS_WAY)values(98009,'00004',180,to_date('24-09-2009','dd-mm-yyyy'),'零存零取');insertintodepositslip(DPS_NUM,ACNUM,MONEY,DPS_TIME,DPS_WAY)values(98010,'00005',190,to_date('24-08-2009','dd-mm-yyyy'),'通知存款');insertintodepositslip(DPS_NUM,ACNUM,MONEY,DPS_TIME,DPS_WAY)values(98011,'00005',210,to_date('30-09-2009','dd-mm-yyyy'),'通知存款');3、插入取款单表(drawslip)数据insertintodrawslip(DW_NUM,ACNUM,MONEY,DW_TIME,DW_WAY)values(52001,'00001',75,to_date('08-08-2010','dd-mm-yyyy'),'柜台');insertintodrawslip(DW_NUM,ACNUM,MONEY,DW_TIME,DW_WAY)values(52002,'00002',27,to_date('08-09-2010','dd-mm-yyyy'),'取款机');insertintodrawslip(DW_NUM,ACNUM,MONEY,DW_TIME,DW_WAY)values(52003,'00002',100,to_date('12-10-2010','dd-mm-yyyy'),'柜台');insertintodrawslip(DW_NUM,ACNUM,MONEY,DW_TIME,DW_WAY)values(52004,'00003',47,to_date('15-08-2010','dd-mm-yyyy'),'取款机');insertintodrawslip(DW_NUM,ACNUM,MONEY,DW_TIME,DW_WAY)values(52005,'00004',26,to_date('12-09-2010','dd-mm-yyyy'),'取款机');insertintodrawslip(DW_NUM,ACNUM,MONEY,DW_TIME,DW_WAY)values(52006,'00005',100,to_date('23-09-2010','dd-mm-yyyy'),'柜台');insertintodrawslip(DW_NUM,ACNUM,MONEY,DW_TIME,DW_WAY)values(52007,'00005',150,to_date('10-10-2010','dd-mm-yyyy'),'柜台');insertintodrawslip(DW_NUM,ACNUM,MONEY,DW_TIME,DW_WAY)values(52008,'00005',39,to_date('31-10-2010','dd-mm-yyyy'),'取款机');七、索引建立索引遵循的规律：1.建立在where子句经常引用的列上，2.经常需要排序的列上，3.连接属性列上等在存款单表的存款单号字段和账号字段建立组合索引CREATEUNIQUEINDEXXPKdepositslipONdepositslip(dps_numASC,acnumASC);在存款单表的账号字段建立索引CREATEINDEXXIF1depositslipONdepositslip(acnumASC);在取款单表的取款单号字段和账号字段建立组合索引CREATEUNIQUEINDEXXPKdrawslipONdrawsl