SQL项目实战银行ATM系统.doc

项目实战银行ATM（取款机）系统第一部分 问题描述某银行拟开发一套ATM取款机系统，实现如下功能： 开户（到银行填写开户申请单，卡号自动生成） 取钱 存钱 查询余额 转账（如使用一卡通代缴手机话费，个人股票交易等）现要求对“ATM柜员机系统”进行数据库的设计并实现，数据库保存在F:bank目录下，文件增长率为15%第二部分 环境要求开发工具：SQL Server2000企业版、PowerDesigner第三部分 问题分析用户信息表： userInfo 见表1.1表1.1 用户信息表字 段 名 称说 明customerID顾客编号自动编号（标识列），从1开始，主键customerName开户名必填PID身份证号必填，只能是18位或15位，身份证号唯一约束telephone联系电话必填，格式为xxxx-xxxxxxxx或手机号11位Address居住地址可选输入银行卡信息表： cardInfo 见表1.2表1.2银行卡信息表字 段 名 称说 明cardID卡号必填，主键，银行的卡号规则和电话号码一样，一般前8位代表特殊含义，如某总行某支行等。假定该行要求其营业厅的卡号格式为：1010 3576 xxxx xxxx开始，每4位号码后有空格，卡号一般是随机产生curType货币类型必填，默认为RMBsavingType存款类型活期 / 定活两便 / 定期 必填openDate开户日期必填，默认为系统当前日期openMoney开户金额必填，不低于1元balance余额必填，不低于1元，否则将销户pass密码必填，6为数字，开户时默认为6个“8”IsReportLoss是否挂失必填，是/否值，默认为“否”customerID顾客编号外键，必填，表示该卡对应的顾客编号，一般顾客允许办理多张卡交易信息表：transInfo 见表1.3表1.3 交易信息表字 段 名 称说 明transDate交易日期必填，默认为系统当前日期cardID卡号必填，外键，可重复索引transType交易类型必填，只能是存入/支取transMoney交易金额必填，大于0remark备注可选输入，其他说明第四部分 实现步骤1 设计数据库E-R模型图（采用PowerDesigner建模工具）提示：根据第三部分的各个表，绘制概念数据模型图(Conceptual Data Model )。2 建库：要求保存在F:bank文件夹下，取名为bankDB，文件增长率为15%。提示：以下所有步骤的软件环境均为SQL查询分析器 在F盘创建文件夹bank。提示：exec xp_cmdshell mkdir f:bank,NO_OUTPUT 建库是要求检测是否存在bankDB，如果存在先删除再创建。提示：参考上机相关代码 if exists() 3 建表：创建用户信息表userInfo，银行卡信息表cardInfo，交易信息表transInfo提示：参照第三部分各表提出的要求，确定表中各个字段的长度、数据类型，注意一个汉字占两个字节。身份证号字段为了验证方便，一般保存为字符串；是/否挂失字段，可保存为BIT（即1/0）类型。4 加约束 根据第三部分“说明”一栏中的要求，分析每列相应的约束条件例如：(1) 主键，添加主键(Primary Key)约束(2) 只能是18位或者15位，添加检查(check)约束。(利用字符串长度函数len() )(3) 同一个表的多个约束可以连写例如：Alter TABLE cardInfoADD CONSTRAINT PK_cardID PRIMARY KEY(cardID),CONSTRAINT CK_cardID CHECK(cardID,.),CONSTRAINT DF_cruType .5 建关系提示：根据三表的字段，通过外键约束来建立表之间的关系6 插入测试数据： 先假定卡号不自动随机产生，而是人工指定，向相关表中插入如下开户信息张三开户身份证：123456789012345, 电话址：北京海淀，开户金额：1000，活期，卡号：1010 3576 1234 5678李四开户身份证：321245678912345678电话户金额：1，定期，卡号：1010 3576 1212 1134 插入交易信息张三的卡中取款900元李四的卡中存款5000元要求保存交易记录，以便客户查询和银行业务统计。提示：当张三取款900时，会向信息表(transInfo)中添加一条交易记录，同时应自动更新银行卡信息表(cardInfo)中的现有余额（减少900元），先假定手动完成插入更新信息。7 常规业务模拟 修改密码张三(1010 3576 1234 5678)修改银行卡密码为123456李四(1010 3576 1212 1134)修改银行卡密码为123123 挂失帐号李四(1010 3576 1212 1134)因银行卡丢失，申请挂失 统计银行的资金流通余额和盈利结算。统计说明：存入代表资金流入，支取代表资金流出，则有：资金流通余额 = 总存入量 总支取量假定存款利率为千分之3，贷款利率为千分之8，则有：盈利结算 = 总支取量0.008 总存入量0.003提示：定义两个变量存放总存入和总支取量、sum（）函数及转换函数convert（） 查询本周开户的卡号，显示该卡相关信息。提示：求时间差的日期函数DateDiff，求星期几用DatePart函数 查询本月交易金额最高的卡号提示：在交易信息表中，采用子查询和Distinct去掉重复的卡号。 查询挂失帐号的客户信息。提示：利用子查询In的方式或内部连接Inner Join 催款提醒业务例如因某种业务的需要，每个月末如果发现用户帐上余额少于200元，将致电催款。8 创建索引和视图。 给交易表的卡号cardID字段创建重复索引，以便加速查询，填充因子为70% 按指定索引查询张三的交易记录 创建视图。为了向客户显示友好信息，请创建视图，把表中英文字段名改成中文字段名，3个表对应的视图名分别为：view_userInfo、view_cardInfo、view_Info9 创建触发器trig_trans：改进上述的存款或取款语句，当存取或取钱（如张三取款900元）时，会往交易信息表transInfo中插入一条交易记录，同时会自动更新（而不是人工更新）用户信息表userInfo中的余额（如减少900元）。因此，我们应该在交易信息表transInfo中创建插入触发器，自动触发更新余额的动作。提示： 如果交易类型是“支取”，则应判断余额是否足够支取（余额-支取金额=1），否则提示余额不足。如果支取成功，更新余额，减去支取的金额。 如果是“存入”，则更新余额，加上存入的金额。 交易结束后，提示交易信息：是否成功，目前余额是多少。10 测试触发器：张三的卡号支取1000，李四的卡号存入200.提示：这里需要张三和李四的卡号，现实中的取款机依靠读卡器读出卡号，此处可以根据张三和李四的名字查出卡号来模拟（采用内连接或子查询）11 创建存储过程： 取钱或存钱的存储过程proc_takeMoney提示：取钱时要求提供密码，存取不用。取钱时需验证密码是否正确，然后向交易表transInfo中插入一条支取的交易信息即可。因为我们已经在该表上创建了插入触发器，所以插入时将自动触发上述动作，检测余额是否足够支取，然后自动更新余额。存储过程参数：卡号、交易金额、交易类型、密码（默认为空）测试：调用存储过程取钱或存取，张三的卡号支取300（密码123456），李四的卡号存入500，同样，张三、李四的卡号可以根据名字查出来。 产生随机卡号的存储过程proc_randCardID提示：银行卡卡号共19位（4位数一组，中间用空格隔开），对于某个银行来说，前面8个数字是固定的，后面8个数字要求随机的，并且唯一。我们只需要产生8位随机的数字，然后和前8位固定的数字“1010 3576”连接即可。随机函数的用法下：RAND（随机种子）：将产生01的随机数，要求每次的随机种子不一样。为了保证随机种子每次不相同，一般的算法如下：随机种子=当前的月份数100000 + 当前秒数1000 +当前毫秒数产生了01的随机数后，我们取小数点后8位，即：0.xxxxxxxx测试：产生随机号 开户的存储过程proc_openAccount提示：开户的相关信息作为该存储过程的参数，包括开户名、身份证号、电话号码、开户金额、存款类型和地址（默认值为空）。该存储过程调用上述的随机卡号的存储过程生成随机卡号，如果该随机卡号在cardInfo中不存在，则往相关表插入开户信息。否则将调用上述随机卡号的存储过程，重新产生随机卡号（使用while循环）测试：调用此存储过程开户。王五，身份证：334456889012678，电话：2222-63598978，开户金额：1000，活期，地址：河南新乡。赵二，身份证：213445678912342222，电话开户金额：1，定期12 创建事务：同银行的帐户间一般都支持转账功能，因为转账的规则为一方支取，另一方存入相应金额。这两步要求要么同时完成，要么同时失败，所以采用事务处理。如果任何一方失败，可以撤销回滚操作。现模拟从李四转账2000到张三的帐号上，要求显示转账信息、是否转账成功及转账后双方的余额。为了调用方便，采用存储过程实现，需要3个参数：转出方卡号、接收方卡号和转账金额。测试：调用此事务存储过程，模拟从李四转账2000到张三的帐号上。（张三、李四的卡号同前，