ATM取款机系统数据库设计.ppt

ATM取款机系统数据库设计,问题描述,某银行拟开发一套ATM取款机系统，实现如下功能：1、开户（到银行填写开户申请单，卡号自动生成）2、取钱3、存钱4、查询余额5、转账（如使用一卡通代缴手机话费、个人股票交易等）现要求对“ATM柜员机系统”进行数据库的设计并实现，数据库保存在D:bank目录下，文件增长率为15%。,问题分析-1,用户信息表：userInfo：,问题分析-2,银行卡信息表：cardInfo,问题分析-3,交易信息表：transInfo,难点分析-1,设计ER图、建库、建表、加约束、建关系部分,建库语句：CREATEDATABASEbankDBON(NAME=,FILENAME=,SIZE=,FILEGROWTH=)LOGON(.),建表语句：CREATETABLE表名(customerIDINTIDENTITY(1,1),customerNameCHAR(8)NOTNULL,.),文件增长率,数据文件,日志文件,自动编号，从1开始,非空/必填,难点分析-2,设计ER图、建库、建表、加约束、建关系部分,建约束语句：ALTERTABLEcardInfoADDCONSTRAINTPK_cardIDPRIMARYKEY(cardID),CONSTRAINTCK_cardIDCHECK(cardIDLIKE101035760-9),CONSTRAINTDF_curTypeDEFAULT(RMB)FORcurTypeCONSTRAINTFK_customerIDFOREIGNKEY(customerID)REFERENCESuserInfo(customerID),CONSTRAINTUQ_PIDUNIQUE(PID),.,主键约束,检查约束,外键约束（建关系）,唯一约束,默认约束,难点分析-3,建表时:IDENTITY(1,1),check约束：len()函数,建表时:NOTNULL,check约束：like0-90-9,设计ER图、建库、建表、加约束、建关系部分,子查询：SELECT.FROMWHEREtransMoney=(SELECTFROM)内部连接：SELECTFROMuserInfoINNERJOINcardInfoON.SQL编程：DECLAREinMoneymoneySELECTinMoney=sum(transMoney)from.where(transType=存入)视图：CREATEVIEWview_userInfoAS-SQL语句GO,难点分析-4,声明变量,插入测试数据、常规业务模拟、创建索引视图部分,给变量赋值的两种方法：SELECT或SET,测试视图：SELECT.FROMview_userInfo,创建存储过程：CREATEPROCEDUREproc_takeMoneycardchar(19)，typechar(4),inputPasschar(6)=AS.SQL语句GO调用存储过程：EXECproc_openAccount李四,321245678912345678,0478-44443333,1,定期,难点分析-5,存储过程部分1：,存储过程的参数,有默认值的参数，放在最后,创建存储过程：CREATEPROCEDUREproc_randCardIDrandCardIDchar(19)OUTPUTSELECTr=RAND(随机种子)例如：0.082332623215.setrandCardID=.SUBSTRING(tempStr,3,4).GO调用存储过程：DECLAREmycardIDchar(19)EXECUTEproc_randCardIDmycardIDOUTPUT,难点分析-6,存储过程部分2：,OUTPUT表示传出的参数,产生0-1的随机数,截取小数点后8位作为卡号的后八为数卡号(4位一组，用空格隔开)：1010357608233262,调用带output输出参数的存储过程,字符串截取函数,阶段划分,第一阶段（30分钟）利用Visio设计数据库的E-R图第二阶段（50分钟）利用SQL语句实现建库、建表、加约束、建关系第三阶段（30分钟）利用SQL语句插入测试数据、模拟常规业务操作第四阶段（30分钟）利用SQL语句创建索引和视图第五阶段（60分钟）利用SQL语句创建3个存储过程并测试第六阶段（30分钟）利用SQL语句创建转账事务并测试第七阶段（20分钟）利用SQL语句创建系统维护帐号并授权,第一阶段,第一阶段（30分钟）利用Visio设计数据库的E-R图要求学员自己动手操作，教员巡视，解答学员提出的问题,阶段检查,针对第一阶段抽查学员的操作结果教员给出点评或集中演示难点部分,第一阶段结果演示1,第一阶段操作的结果：,第二阶段,第二阶段（50分钟）利用SQL语句实现建库、建表、加约束、建关系要求学员自己动手编写SQL语句，教员巡视，解答学员提出的问题,阶段检查,针对第二阶段抽查学员的编码结果教员给出点评或集中演示难点部分,第二阶段标准代码演示-1,建库,IFexists(SELECT*FROMsysdatabasesWHEREname=bankDB)DROPDATABASEbankDBGOCREATEDATABASEbankDBON(NAME=bankDB_data,FILENAME=d:bankbankDB_data.mdf,SIZE=3mb,FILEGROWTH=15%)LOGON(.,检验数据库是否存在，如果为真，删除此数据库,创建建库bankDB,第二阶段标准代码演示-2,建表：,USEbankDBGOCREATETABLEuserInfo用户信息表(customerIDINTIDENTITY(1,1),customerNameCHAR(8)NOTNULL,PIDCHAR(18)NOTNULL,telephoneCHAR(13)NOTNULL,addressVARCHAR(50)GO,CREATETABLEcardInfo-银行卡信息表(cardIDCHAR(19)NOTNULL,curTypeCHAR(5)NOTNULL,savingTypeCHAR(8)NOTNULL,openDateDATETIMENOTNULL,openMoneyMONEYNOTNULL,balanceMONEYNOTNULL,passCHAR(6)NOTNULL,IsReportLossBITNOTNULL,customerIDINTNOTNULL),CREATETABLEtransInfo-交易信息表(transDateDATETIMENOTNULL,transTypeCHAR(4)NOTNULL,cardIDCHAR(19)NOTNULL,transMoneyMONEYNOTNULL,remarkTEXT)GO,第三阶段,第三阶段（30分钟）：利用SQL语句实现插入测试数据、常规业务操作要求学员自己动手操作，教员巡视，解答学员提出的问题,阶段检查,针对第三阶段抽查学员的编码结果抽查学员编写的完整代码，要求学员上台讲解，并演示运行结果教员给出点评,第三阶段标准代码演示-1,张三和李四开户：,SETNOCOUNTON-不显示受影响的条数信息INSERTINTOuserInfo(customerName,PID,telephone,address)VALUES(张三,123456789012345,010-67898978,北京海淀)INSERTINTOcardInfo(cardID,savingType,openMoney,balance,customerID)VALUES(1010357612345678,活期,1000,1000,1)INSERTINTOuserInfo(customerName,PID,telephone)VALUES(李四,321245678912345678,0478-44443333)INSERTINTOcardInfo(cardID,savingType,openMoney,balance,customerID)VALUES(1010357612121134,定期,1,1,2)SELECT*FROMuserInfoSELECT*FROMcardInfoGO,第三阶段标准代码演示-2,张三的卡号取款900元，李四的卡号存款5000元,/*-交易信息表插入交易记录-*/INSERTINTOtransInfo(transType,cardID,transMoney)VALUES(支取,1010357612345678,900)/*-更新银行卡信息表中的现有余额-*/UPDATEcardInfoSETbalance=balance-900WHEREcardID=1010357612345678/*-交易信息表插入交易记录-*/INSERTINTOtransInfo(transType,cardID,transMoney)VALUES(存入,1010357612121134,5000)/*-更新银行卡信息表中的现有余额-*/UPDATEcardInfoSETbalance=balance+5000WHEREcardID=1010357612121134GO,第三阶段标准代码演示-3,修改密码和挂失账号,/*-修改密码-*/-1.张三（卡号为1010357612345678）修改银行卡密码为123456-2.李四（卡号为1010357612121134）修改银行卡密码为123123updatecardInfosetpass=123456WHEREcardID=1010357612345678updatecardInfosetpass=123123WHEREcardID=1010357612121134SELECT*FROMcardInfo/*-李四的卡号挂失-*/updatecardInfosetIsReportLoss=1WHEREcardID=1010357612121134SELECT*FROMcardInfoGO,第三阶段标准代码演示-4,统计银行的资金流通余额和盈利结算,DECLAREinMoneymoneyDECLAREoutMoneymoneyDECLAREprofitmoneySELECTinMoney=sum(transMoney)FROMtransInfoWHERE(transType=存入)SELECToutMoney=sum(transMoney)FROMtransInfoWHERE(transType=支取)print银行流通余额总计为:+convert(varchar(20),inMoney-outMoney)+RMBsetprofit=outMoney*0.008-inMoney*0.003print盈利结算为:+convert(varchar(20),profit)+RMBGO,第三阶段标准代码演示-5,其他操作,/*-查询本周开户的卡号,显示该卡相关信息-*/SELECT*FROMcardInfoWHERE(DATEDIFF(Day,getDate(),openDate)0)beginprint转账失败！rollbacktranendelsebeginprint转账成功！committranendGO,-调用上述事务过程转账declarecard1char(19),card2char(19)selectcard1=cardIDfromcardInfoInnerJoinuserInfoONcardInfo.customerID=userInfo.customerIDwherecustomerName=李四selectcard2=cardIDfromcardInfoInnerJoinuserInfoONcardInfo.customerID=userInfo.customerIDwherecustomerName=张三EXECproc_transfercard1,card2,2000GO,第七阶段,第七阶段（20分钟）：利用SQL语句创建系统维护帐号并授权要求学员自己动手操作，教员巡视，解答学员提出的问题,阶段检查,针对第三阶段抽查学员的编码结果抽查学员编写的完整代码，要求学员上台讲解，并演示运行结果教员给出点评,第七阶段标准代码演示-1,添加系统维护帐号sysAdmin，并授权,-1.添加SQL登录帐号Ifnotexists(SELECT*FROMmaster.dbo.sysloginsWHEREloginname=sysAdmin)beginEXECsp_addloginsysAdmin,1234-添加SQL登录帐号EXECsp_defaultdbsysAdmin,bankDB-修改登录的默认数据库为bankDBendgo-2.创建数据库用户EXECsp_grantdbaccesssysAdmin,sysAdminDBUserGO,-3.-给数据库用户授权-为sysAdminDBUser分配对象权限(增删改查的权限)GRANTSELECT,insert,update,delete,selectONtransInfoTOsysAdminDBUserGRANTSELECT,insert,update,delete,selectONuserInfoTOsysAdminDBUserGRANTSELECT,insert,update,delete,selectONcardInfoTOsysAdminDBUserGO,总结,巩固的知识点：SQL语句：建库、建表、加约束、建关系常用的约束类型：主键、外键、非空、默认值、检查约束高级查询：内部连接、子查询、索引、视图存储过程：带参数的存储过程、带返回值的存储过程事务：显示事务的应用安全帐号：创建登录帐号、数据库用户、授权,