银行ATM数据库设计

ATM取款机系统数据库设计问题描述某银行拟开发一套ATM取款机系统，实现如下功能：1、开户（到银行填写开户申请单，卡号自动生成）2、取钱3、存钱4、查询余额5、转账（如使用一卡通代缴手机话费、个人股票交易等）现要求对“ATM柜员机系统”进行数据库的设计并实现，数据库保存在D:\bank目录下，文件增长率为15%。问题分析-1字段名称说明customerID顾客编号自动编号（标识列），从1开始，主键customerName开户名必填PID身份证号必填，只能是18位或15位，身份证号唯一约束telephone联系电话必填，格式为xxxx-xxxxxxxx或手机号13位address居住地址可选输入用户信息表：userInfo：问题分析-2银行卡信息表：cardInfo字段名称说明cardID卡号必填，主健，银行的卡号规则和电话号码一样，一般前8位代表特殊含义，如某总行某支行等。假定该行要求其营业厅的卡号格式为：10103576xxxxxxx开始,每4位号码后有空格，卡号一般是随机产生。curType货币种类必填，默认为RMBsavingType存款类型活期/定活两便/定期openDate开户日期必填，默认为系统当前日期openMoney开户金额必填，不低于1元balance余额必填，不低于1元,否则将销户pass密码必填，6位数字，开户时默认为6个“8”IsReportLoss是否挂失必填，是/否值，默认为”否”customerID顾客编号外键，必填，表示该卡对应的顾客编号，一位顾客允许办理多张卡号问题分析-3字段名称说明transDate交易日期必填，默认为系统当前日期cardID卡号必填，外健，可重复索引transType交易类型必填，只能是存入/支取transMoney交易金额必填，大于0remark备注可选输入，其他说明交易信息表：transInfo难点分析-1设计ER图、建库、建表、加约束、建关系部分建库语句：CREATEDATABASEbankDBON(NAME=‘…',FILENAME=…',SIZE=…,FILEGROWTH=…)LOGON(….)建表语句：CREATETABLE表名(customerIDINTIDENTITY(1,1),customerNameCHAR(8)NOTNULL,…..)文件增长率数据文件日志文件自动编号，从1开始非空/必填难点分析-2设计ER图、建库、建表、加约束、建关系部分建约束语句：ALTERTABLEcardInfoADDCONSTRAINTPK_cardIDPRIMARYKEY(cardID),CONSTRAINTCK_cardIDCHECK(cardIDLIKE'10103576[0-9]…'),CONSTRAINTDF_curTypeDEFAULT('RMB')FORcurTypeCONSTRAINTFK_customerIDFOREIGNKEY(customerID)REFERENCESuserInfo(customerID),CONSTRAINTUQ_PIDUNIQUE(PID),…..主键约束检查约束外键约束（建关系）唯一约束默认约束难点分析-3字段名称说明customerID顾客编号自动编号（标识列），从1开始，主键customerName开户名必填PID身份证号必填，只能是18位或15位，身份证号唯一约束telephone联系电话必填，格式为xxxx-xxxxxxxx或手机号13位address居住地址可选输入建表时:IDENTITY(1,1)check约束：len()函数建表时:NOTNULLcheck约束：like‘[0-9][0-9]…’设计ER图、建库、建表、加约束、建关系部分子查询：SELECT....FROM…WHEREtransMoney=(SELECT…FROM…)内部连接：SELECT…FROMuserInfoINNERJOINcardInfoON…..SQL编程：DECLARE@inMoneymoneySELECT@inMoney=sum(transMoney)from..where(transType='存入')视图：CREATEVIEWview_userInfoAS…--SQL语句GO难点分析-4声明变量插入测试数据、常规业务模拟、创建索引视图部分给变量赋值的两种方法：SELECT或SET测试视图：SELECT..FROMview_userInfo…创建存储过程：CREATEPROCEDUREproc_takeMoney@cardchar(19)，@typechar(4),@inputPasschar(6)=''AS…..—SQL语句GO调用存储过程：EXECproc_openAccount'李四','321245678912345678','0478-44443333',1,'定期'难点分析-5存储过程部分1：存储过程的参数有默认值的参数，放在最后创建存储过程：CREATEPROCEDUREproc_randCardID@randCardIDchar(19)OUTPUT…SELECT@r=RAND(随机种子)例如：0.082332623215…..set@randCardID=….SUBSTRING(@tempStr,3,4)…..GO调用存储过程：DECLARE@mycardIDchar(19)EXECUTEproc_randCardID@mycardIDOUTPUT难点分析-6存储过程部分2：OUTPUT表示传出的参数产生0-1的随机数截取小数点后8位作为卡号的后八为数卡号(4位一组，用空格隔开)：1010357608233262调用带output输出参数的存储过程字符串截取函数第一阶段结果演示1第一阶段操作的结果：第二阶段标准代码演示-1建库IFexists(SELECT*FROMsysdatabasesWHEREname='bankDB')DROPDATABASEbankDBGOCREATEDATABASEbankDBON(NAME='bankDB_data',FILENAME='d:\bank\bankDB_data.mdf',SIZE=3mb,FILEGROWTH=15%)LOGON(…..检验数据库是否存在，如果为真，删除此数据库创建建库bankDB第二阶段标准代码演示-2建表：USEbankDBGOCREATETABLEuserInfo－－用户信息表(customerIDINTIDENTITY(1,1),customerNameCHAR(8)NOTNULL,PIDCHAR(18)NOTNULL,telephoneCHAR(13)NOTNULL,addressVARCHAR(50))GOCREATETABLEcardInfo--银行卡信息表(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第三阶段标准代码演示-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='1010357612121134'GO第三阶段标准代码演示-3修改密码和挂失账号/*---------修改密码-----*/--1.张三（卡号为1010357612345678）修改银行卡密码为123456--2.李四（卡号为1010357612121134）修改银行卡密码为123123updatecardInfosetpass='123456'WHEREcardID='1010357612345678'updatecardInfosetpass='123123'WHEREcardID='1010357612121134'SELECT*FROMcardInfo/*---------李四的卡号挂失---------*/updatecardInfosetIsReportLoss=1WHEREcardID='1010357612121134'SELECT*FROMcardInfoGO第三阶段标准代码演示-4统计银行的资金流通余额和盈利结算DECLARE@inMoneymoneyDECLARE@outMoneymoneyDECLARE@profitmoneySELECT@inMoney=sum(transMoney)FROMtransInfoWHERE(transType='存入')SELECT@outMoney=sum(transMoney)FROMtransInfoWHERE(transType='支取')print'银行流通余额总计为:'+convert(varchar(20),@inMoney-@outMoney)+'RMB'set@profit=@outMoney*0.008-@inMoney*0.003print'盈利结算为:'+convert(varchar(20),@profit)+'RMB'GO第三阶段标准代码演示-5其他操作/*--------查询本周开户的卡号,显示该卡相关信息------