搜索
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输出参数的存储过程字符串截取函数阶段划分第一阶段（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:\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第三阶段第三阶段（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='1010357612121134'GO第三阶段标准代码演示-3修改密码和挂失账号/*---------修改密码-----*/--1.张三（卡号为1010357612345678）修改银行卡密码为123456--2.李四（卡号为1010357612121134）修改银行卡密码为123123updatecardInfosetpass='123456'WHEREcardID='1010357612345678'updatecardInfosetpass='123123'WHEREcardID='1010357612121134'SELECT*FROMcardInfo/*---------李四的卡号挂失--------