（本科）第14章 课程项目-ATM数据库设计ppt课件.pptx

《（本科）第14章 课程项目-ATM数据库设计ppt课件.pptx》由会员分享，可在线阅读，更多相关《（本科）第14章 课程项目-ATM数据库设计ppt课件.pptx（56页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、课程主讲人：第14章 课程项目-ATM数据库设计数据库设计与应用主讲：张成叔微信（QQ）：7153265 微信公众号：张成叔课程项目ATM数据库设计与实现第1章 创建和管理数据库项目分析项目需求及实现1 1目 录2 2任务描述n系统功能概述系统概述系统概述开户开户填写开户申请填写开户申请单自动开通账单自动开通账户户存取款存取款查询余额查询余额转账转账分析案例分析案例：银行：银行ATM存取款机系统存取款机系统讲解讲解系统开发步骤系统开发步骤明确需求明确需求数据库设计数据库设计编码实现功编码实现功能能测试测试绘制绘制E-RE-R图图绘制数据库模型绘制数据库模型图图使用三大范式规使用三大范式规范数据

2、库结构范数据库结构问题分析：整体开发思路讲解讲解问题分析：整体开发思路n银行业务处理u每个用户可以开设多个银行卡账户u每个银行卡账户可以有多笔交易n设计符合第三范式的数据库结构u根据银行业务绘制E-R图u绘制数据库模型图u使用三大范式规范数据库结构n实现“ATM取款机系统”的相关业务讲解讲解难点分析1：数据库设计n数据库设计步骤nE-R图要素u实体u关系 一对一关系 一对多关系 多对多关系讲解讲解难点分析1：数据库设计n数据库设计的三大规范u第一范式u第二范式u第三范式讲解讲解难点分析2：理解业务nATM取款业务u卡号构成 假设某行由16位数字构成，每4位数字一组，中间用空格隔开 如：1010

3、 3576 1001 1202 前8位数字固定的，表示发卡银行识别号（BIN） 前8位是1010 3576 后8位中前7位是开户顺序号，后1位是校验位 开户顺序号从1开始，每开一户，该值加一 校验位算法 以8位随机数替代卡号的后8位数字u客户用身份证开设账户 开户金额不得小于1元 存款余额不得小于1元讲解讲解难点分析3：理解业务nATM取款业务u存款 记录交易发生额，增加账户余额u取款 记录交易发生额，减少账户余额u转账 关系到二个账户，总账平衡 转出账户：记录交易转入金额，减少该账户的余额 转入账户：记录交易转出金额（同转入金额），增加该账户余额讲解讲解难点分析4：来理解业务nATM取款业务

4、u修改密码 变更客户密码u挂失 客户填写特殊业务申请单（挂失申请书），停止该账户发生存款支取交易 在客户信息中设置列记录客户当前账户状态讲解讲解功能需求分析n功能需求1：数据库设计 n功能需求1：建库、建表、建约束 n功能需求3：插入测试数据 n功能需求4：模拟常规业务 n功能需求5：利用视图实现较复杂的数据查询n功能需求6：使用存储过程实现业务处理n功能需求7：利用事务实现较复杂的数据更新讲解讲解一起努力 共同提高第1章 创建和管理数据库目 录1 12 2项目分析项目需求及实现功能需求1：数据库设计n需求说明u根据银行ATM取款机业务处理流程设计数据库 绘制E-R图 绘制数据库模型图 使用第

5、三范式规范数据库表结构n思路分析u按照数据库设计步骤确定实体及实体关系u使用Visio绘制E-R图u使用Visio绘制数据库模型图u使用第三范式规范数据库设计讲解讲解功能需求1：数据库设计n难点提示u寻找、确定ATM取款机系统的实体及实体间关系 根据业务确定实体 确定实体之间关系u使用三大范式规范数据库设计 表内的每一个值都只能被表达一次 表内的每一行都应该被唯一的标识（主键） 表内不应该存储依赖于其他键的非键信息讲解讲解功能需求1：数据库设计n提交设计文档u小组讨论确定实体及实体关系u绘制E-R图u绘制数据库模型图u使用第三范式规范数据库设计n要求u互相验证完成的程序功能u小组长检查本组完成

6、情况讲解讲解n客户信息表结构（UserInfo） 列名称列名称数据数据类型类型说明customerIDcustomerIDintint客户编号客户编号自动编号（标识列），从自动编号（标识列），从1 1开始，开始，主键主键customerNamecustomerNamevarcharvarchar开户名开户名必填必填PIDPIDvarcharvarchar身份证号身份证号必填，只能是必填，只能是1818位或位或1515位，身份证位，身份证号唯一约束号唯一约束telephonetelephonevarcharvarchar联系电话联系电话必填，格式为必填，格式为xxxx-xxxxxxxxxxxx-

7、xxxxxxxx或或xxx-xxxxxxxxxxx-xxxxxxxx或手机号或手机号1111位位addressaddressvarcharvarchar居住地址居住地址可选输入可选输入功能需求1：数据库设计讲解讲解列名称列名称数据数据类型类型说明说明cardIDcardIDcharchar卡号卡号必填，主键，银行的卡号规则和电话号码一样，一般前必填，主键，银行的卡号规则和电话号码一样，一般前8 8位代表位代表特殊含义，如某总行某支行等。假定该行要求其营业厅的卡号格特殊含义，如某总行某支行等。假定该行要求其营业厅的卡号格式为：式为：1010 3576 1010 3576 xxxxxxxx xxx

8、xxxxx。每。每4 4位号码后有空格，卡号一般位号码后有空格，卡号一般是随机产生是随机产生curIDcurIDvarcharvarchar货币种类货币种类外键，必填，默认为外键，必填，默认为RMBRMBsavingIDsavingIDvarcharvarchar存款类型存款类型外键，必填外键，必填openDateopenDatedatetimedatetime开户日期开户日期必填，默认为系统当前日期必填，默认为系统当前日期openMoneyopenMoneydecimaldecimal开户金额开户金额必填，不低于必填，不低于1 1元元balancebalancedecimaldecimal余

9、额余额必填，不低于必填，不低于1 1元元passwordpasswordvarcharvarchar密码密码必填，必填，6 6位数字，开户时默认为位数字，开户时默认为6 6个个“8”8”IsReportLossIsReportLossbitbit是否挂失是否挂失必填，是必填，是/ /否值，默认为否值，默认为“否否”customerIDcustomerIDintint客户编号客户编号外键，必填外键，必填n银行卡信息表结构（CardInfo） 功能需求1：数据库设计讲解讲解n交易信息表结构 （TradeInfo）功能需求1：数据库设计字段名称字段名称数据类型数据类型说明transDatetrans

10、Datedatetimedatetime交易日期交易日期必填，默认为系统当前日期必填，默认为系统当前日期cardIDcardIDvarcharvarchar卡号卡号外键，必填外键，必填transTypetransTypeintint交易类型交易类型必填，只能是存入必填，只能是存入/ /支取支取transMoneytransMoneydecimaldecimal交易金额交易金额必填，大于必填，大于0 0remarkremarkvarcharvarchar备注备注可选输入，其他说明可选输入，其他说明讲解讲解n存款类型表结构（Deposit） 功能需求1：数据库设计字段名称字段名称数据数据类型类型说

11、明savingIDint存款类型号存款类型号自动编号（标识列）从自动编号（标识列）从1 1开始，开始，主键主键savingNamevarchar存款类型名称存款类型名称必填必填descripvarchar描述描述可空可空讲解讲解功能需求1：数据库设计字段名称字段名称数据数据类型类型说明curIDvarchar货币货币简称简称必填必填curNamevarchar货币种类名称货币种类名称必填必填n币种表结构（Currency） 讲解讲解一起努力 共同提高功能需求2：建库、建表、建约束n需求说明u使用SQL语言创建数据库BankDB 数据库保存在D:bank目录下，文件增长率为15%u使用SQL语言

12、创建表 存款类型表 客户信息表 银行卡信息表 交易信息表u使用SQL语言在每个表上添加约束 主键约束、外键约束、CHECK约束、默认约束、非空约束讲解讲解功能需求2：建库、建表、建约束n建库与建表-建库语句：建库语句：CREATE DATABASE bankDB ON ( NAME=, FILENAME=, SIZE=, FILEGROWTH= ) LOG ON ( )-建表语句：建表语句：CREATE TABLE 表名表名 ( customerID INT IDENTITY(1,1), customerName CHAR(8) NOT NULL, )文件增长率文件增长率数据文件数据文件日志文

13、件日志文件自动编号，从自动编号，从1开始开始非空非空/必填必填讲解讲解功能需求2：建库、建表、建约束n加约束、建关系部分 -建约束语句：建约束语句：ALTER TABLE cardInfoALTER TABLE cardInfo ADD CONSTRAINT PK_cardID PRIMARY KEY(cardID), ADD CONSTRAINT PK_cardID PRIMARY KEY(cardID), CONSTRAINT CK_cardID CHECK(cardID LIKE 1010 3576 0-9), CONSTRAINT CK_cardID CHECK(cardID LIKE

14、 1010 3576 0-9), CONSTRAINT DF_curType DEFAULT(RMB) FOR curTypeCONSTRAINT DF_curType DEFAULT(RMB) FOR curType CONSTRAINT FK_customerID FOREIGN KEY(customerID) CONSTRAINT FK_customerID FOREIGN KEY(customerID) REFERENCES userInfo(customerID),REFERENCES userInfo(customerID), CONSTRAINT UQ_PID UNIQUE(PI

15、D), CONSTRAINT UQ_PID UNIQUE(PID), . .主键约束主键约束检查约束检查约束外键约束（建关系）外键约束（建关系）唯一约束唯一约束默认约束默认约束讲解讲解功能需求2：建库、建表、建约束n代码审查u审查创建数据库、表的SQL代码u审查添加约束的SQL代码n要求u互相验证完成的程序功能u小组长检查本组完成情况讲解讲解一起努力 共同提高功能需求3：插入测试数据 n需求说明u使用SQL语言向每个表中插入至少5条记录n思路分析u先增加主表的记录，后增加子表的记录n难点提示u插入数据表的先后顺序u插入客户记录时，先要查询讲解讲解功能需求3：插入测试数据n张三和李四开户：SET

16、 NOCOUNT ON SET NOCOUNT ON -不显示受影响的条数信息INSERT INTO userInfo(customerName,PID,telephone,address )INSERT INTO userInfo(customerName,PID,telephone,address )INSERT INTO cardInfo(cardID,savingType,openMoneyINSERT INTO cardInfo(cardID,savingType,openMoney ,balance,customerID) VALUES(1010 3576 1234 5678,ba

17、lance,customerID) VALUES(1010 3576 1234 5678, 活期活期,1000,1000,1),1000,1000,1)INSERT INTO userInfo(customerName,PID,telephone)INSERT INTO userInfo(customerName,PID,telephone)INSERT INTO cardInfo(cardID,savingType,openMoney,balance,INSERT INTO cardInfo(cardID,savingType,openMoney,balance, customerID) V

18、ALUES(1010 3576 1212 1134,customerID) VALUES(1010 3576 1212 1134,定期定期,1,1,2),1,1,2)SELECT SELECT * * FROM userInfo FROM userInfoSELECT SELECT * * FROM cardInfo FROM cardInfoGOGO讲解讲解功能需求3：插入测试数据n张三的卡号取款900元，李四的卡号存款5000元/*-交易信息表插入交易记录-*/INSERT INTO transInfo(transType,cardID,transMoney) INSERT INTO tr

19、ansInfo(transType,cardID,transMoney) VALUES( VALUES(支取支取,1010 3576 1234 5678,900) ,1010 3576 1234 5678,900) /*-更新银行卡信息表中的现有余额-*/UPDATE cardInfo SET balance=balance-900 WHERE cardID=1010 3576 1234 5678UPDATE cardInfo SET balance=balance-900 WHERE cardID=1010 3576 1234 5678/*-交易信息表插入交易记录-*/INSERT INTO

20、 transInfo(transType,cardID,transMoney) INSERT INTO transInfo(transType,cardID,transMoney) VALUES( VALUES(存入存入,1010 3576 1212 1134,5000) ,1010 3576 1212 1134,5000) /*-更新银行卡信息表中的现有余额-*/UPDATE cardInfo SET balance=balance+5000 WHERE cardID=1010 3576 1212 1134UPDATE cardInfo SET balance=balance+5000 WH

21、ERE cardID=1010 3576 1212 1134GOGO讲解讲解功能需求3：插入测试数据n功能测试u使用查询语句测试插入的数据是否正确u代码审查u审查插入测试数据的SQL代码n要求u互相验证完成的程序功能u小组长检查本组完成情况讲解讲解一起努力 共同提高功能需求4：模拟常规业务n需求说明u修改客户密码u办理银行卡挂失u统计银行资金流通余额和盈利结算 银行资金流通余额=总存入金额-总支取金额 盈利结算=总支取金额 * 0.008 总存入金额 * 0.003u查询本周开户的卡号，显示该卡相关信息u查询本月交易金额最高的卡号u查询挂失账号的客户信息u催款提醒业务讲解讲解功能需求4：模拟常

22、规业务n思路分析u修改客户密码u办理银行卡挂失 使用UPDATE语句实现密码变更和卡挂失u查询本周开户的卡号，显示该卡相关信息 日期函数DATEDIFF()和DATEPART()u统计银行资金流通余额和盈利结算 使用聚合函数SUM()和数据类型转换函数CONVERT()UPDATEUPDATE WHERE SELECT inMoney=SUM()SELECT inMoney=SUM() FROM FROM WHERE 讲解讲解功能需求4：模拟常规业务n思路分析u查询本月交易金额最高的卡号 使用子查询和DISTINCT关键字去掉重复的卡号u查询挂失账号的客户信息 使用子查询IN 或内联接查询IN

23、NER JOINu催款提醒业务 使用子查询IN 或内联接查询INNER JOINSELECT FROM transInfo WHERE transMoney = ( SELECT FROM transInfo WHERE transMoney = ( SELECT FROM ) )SELECT FROM userInfo WHERE customerID IN ( SELECT FROM userInfo WHERE customerID IN ( SELECT FROM ) )SELECT FROM userInfo SELECT FROM userInfo INNER JOIN 讲解讲解功

24、能需求4：模拟常规业务n功能测试u检查点 正确修改客户密码 正确办理银行卡挂失，查询获得挂失账号的客户信息 正确查询本月交易金额最高的卡号 正确统计银行资金流通余额和盈利结算 正确查询本周开户的卡号，显示该卡相关信息 正确催款提醒业务n要求u互相验证完成的程序功能u小组长检查本组完成情况讲解讲解一起努力 共同提高功能需求5：利用视图实现数据查询n需求说明u为客户提供以下3个视图供其查询该客户数据 客户基本信息：vw_userInfo 银行卡信息：vw_cardInfo 银行卡交易信息：vw_tradeInfou提供友好界面，要求各列名称为中文描述u调用创建的视图获得查询结果讲解讲解功能需求5：

25、利用视图实现数据查询n思路分析u创建视图u调用视图CREATE VIEW CREATE VIEW AS AS GOGOSELECT FROM SELECT FROM 讲解讲解功能需求5：利用视图实现数据查询n功能测试u检查点 正确创建视图 正确使用视图获得查询数据n要求u互相验证完成的程序功能u小组长检查本组完成情况讲解讲解一起努力 共同提高功能需求6：存储过程实现业务处理n需求说明u产生随机卡号u完成开户业务u完成取款或存款业务u根据卡号打印对账单u根据指定显示的页数和每页的记录数分页显示数据u查询、统计指定时间段内没有发生交易的账户信息u统计指定时间段内某地区客户在银行卡交易量和交易额。如

26、果不指定地区，则查询所有客户的交易量和交易额讲解讲解n思路分析u完成取款或存款业务 创建存储过程CREATE PROCEDURE proc_takeMoneyCREATE PROCEDURE proc_takeMoney card char(19) card char(19)， type char(4) ,type char(4) , inputPass char(6)= inputPass char(6)= AS AS -SQL -SQL语句语句 IF() IF() GOGO存储过程的参数存储过程的参数有默认值的参数，放在最后有默认值的参数，放在最后EXEC proc_takeMoney 1

27、010 3576 0823 3262, EXEC proc_takeMoney 1010 3576 0823 3262, 支取支取,123456,123456检测支取金额是否大于当前存款金额加检测支取金额是否大于当前存款金额加1功能需求6：存储过程实现业务处理 调用存储过程讲解讲解u产生随机卡号CREATE PROCEDURE proc_randCardID CREATE PROCEDURE proc_randCardID randCardID char(19) OUTPUT randCardID char(19) OUTPUT SELECT r = RAND ( SELECT r = RAN

28、D (随机种子随机种子 ) ) SET randCardID =SUBSTRING(tempStr,3,4) SET randCardID =SUBSTRING(tempStr,3,4) . .GO GO DECLARE mycardID char(19)DECLARE mycardID char(19)EXECUTE proc_randCardID mycardID OUTPUTEXECUTE proc_randCardID mycardID OUTPUTOUTPUT表示传出的参数表示传出的参数产生产生0-1的随机数的随机数字符串截取函数字符串截取函数调用带调用带output输出参数的存储过

29、程输出参数的存储过程0.xxxxxxxx 调用存储过程功能需求6：存储过程实现业务处理讲解讲解n思路分析u完成开户业务CREATE PROCEDURE proc_openAccount CREATE PROCEDURE proc_openAccount 存储过程参数存储过程参数AS AS -SQL -SQL语句语句 WHILE WHILE EXISTSEXISTS( ( ) ) EXEC PROCEDURE proc_randCardID EXEC PROCEDURE proc_randCardID GOGOEXEC proc_openAccount EXEC proc_openAccount

30、 李四李四 0478-44443333,1, 0478-44443333,1,定期定期 调用产生随机卡号存储过程，获得唯一的卡号调用产生随机卡号存储过程，获得唯一的卡号包括：开户名、身份证号、包括：开户名、身份证号、电话号码、开户金额、存款类型和地址电话号码、开户金额、存款类型和地址功能需求6：存储过程实现业务处理u调用存储过程讲解讲解n思路分析u根据卡号打印对账单CREATE PROCEDURE CREATE PROCEDURE proc_CheckSheetproc_CheckSheet cardID varchar(19) cardID varchar(19)AS AS SELECT F

31、ROM WHERE cardID = cardID SELECT FROM WHERE cardID = cardID PRINT PRINT GOGOEXEC EXEC proc_CheckSheet 1010 3576 1212 1134proc_CheckSheet 1010 3576 1212 1134根据输入的卡号查找相关交易记录根据输入的卡号查找相关交易记录u调用存储过程功能需求6：存储过程实现业务处理讲解讲解n功能测试u检查点1 正确创建存储过程u检查点2 正确调用存储过程完成相关的数据操作n要求u互相验证完成的程序功能u小组长检查本组完成情况功能需求6：存储过程实现业务处理讲解

32、讲解一起努力 共同提高功能需求7：实现较复杂的数据更新n需求说明u使用事务和存储过程实现转账业务n思路分析u指定的客户取款u指定的客户存钱u打印对账单讲解讲解功能需求7：实现较复杂的数据更新讲解讲解功能需求7：实现较复杂的数据更新n功能测试u检查点1 正确创建、调用存储过程实现数据库数据的更新操作u检查点2 正确使用事务以保证数据的完整性n要求u互相验证完成的程序功能u小组长检查本组完成情况讲解讲解n讲解要点u完成情况、技能总结、经验分享、项目收获n表达要求u清晰流畅、有条理、重点突出总结 作品展示作品展示n巩固的知识点uSQL语句：建库、建表、加约束、建关系u常用的约束类型：主键、外键、非空、默认值、检查约束u高级查询：内部连接、子查询、索引、视图u存储过程：带参数的存储过程、带返回值的存储过程u事务：显示事务的应用总结一起努力 共同提高