《SQL Server 2008数据库应用技术 教学课件 ppt 作者 张素青 孙杰 第13章》由会员分享，可在线阅读，更多相关《SQL Server 2008数据库应用技术 教学课件 ppt 作者 张素青 孙杰 第13章（46页珍藏版）》请在金锄头文库上搜索。
1、第十三章 项目案例,银行业务系统,训练的技能点,使用T-SQL语句建数据库和表结构 使用T-SQL语句编程实现用户业务 使用事务和存储过程封装业务逻辑 使用视图简化复杂的数据查询,任务描述,系统概述 某银行需要开发业务系统，实现如下功能： 开户（到银行填写开户申请单，卡号自动生成） 取款 存款 查询余额 转账 系统角色 数据库设计员、数据库程序员、数据库维护员,问题分析：整体开发思路,系统开发步骤 明确需求 设计数据库 绘制E-R图 绘制数据库模型图 使用三大范式规范数据库结构设计 编码实现需求 测试,问题分析：数据库设计与创建,银行业务处理 每个用户可以开设多个银行卡账户 每个银行卡账户可以
2、有多笔交易 设计符合第三范式的数据库结构 根据银行业务绘制E-R图 绘制数据库模型图 使用三大范式规范数据库结构 实现“银行业务系统”的相关业务,难点分析,数据库设计步骤 E-R图要素 实体 关系 一对一关系 一对多关系 多对多关系 数据库设计的三大规范 第一范式 第二范式 第三范式,难点分析,银行业务系统 卡号构成 假设某行由16位数字构成，每4位数字一组，中间用空格隔开 如：6227 2666 1001 1202 前8位数字固定的，表示发卡银行识别号（BIN） 如：前8位是6227 2666 后8位随机产生 客户用身份证开设账户 开户金额不得小于1元 存款余额不得小于1元,难点分析, 银行
3、取款业务 存款 记录交易发生额，增加账户余额 取款 记录交易发生额，减少账户余额 转账 关系到二个账户，总账平衡 转出账户：记录交易转入金额，减少该账户的余额 转入账户：记录交易转出金额（同转入金额），增加该账户余额,难点分析, 银行业务系统 修改密码 变更客户密码 挂失 客户填写特殊业务申请单（挂失申请书），停止该账户发生存款支取交易 在客户信息中设置列记录客户当前账户状态,开发计划,用例1：数据库设计 用例2：建库、建表、建约束 用例3：插入测试数据 用例4：模拟常规业务 用例5：利用视图实现较复杂的数据查询 用例6：使用存储过程实现业务处理 用例7：利用事务实现较复杂的数据更新,用例1：
4、数据库设计,需求说明 根据银行ATM取款机业务处理流程设计数据库 绘制E-R图 将E-R图转为关系 使用第三范式规范数据库表结构,用例1：数据库设计,思路分析 按照数据库设计步骤确定实体及实体关系 绘制E-R图 将E-R图转换为表 使用第三范式规范数据库设计 难点提示 寻找、确定银行业务系统的实体及实体间关系 根据业务确定实体 确定实体之间关系 使用三大范式规范数据库设计 表内的每一个值都只能被表达一次 表内的每一行都应该被唯一的标识（主键） 表内不应该存储依赖于其他键的非键信息,绘制E-R图,用例1：数据库设计,客户表（userInfo）结构,用例1：数据库设计,银行卡表（cardInfo）
5、结构,用例1：数据库设计,交易表（tradeInfo）结构,用例1：数据库设计,存款类型表（deposit）结构,用例1：数据库设计,用例2：建库、建表、建约束,需求说明 使用SQL语言创建数据库BankDB 数据库保存在D:bank目录下，文件增长率为15% 使用SQL语言创建表 存款类型表 客户表 银行卡表 交易表 使用SQL语言在每个表上添加约束 主键约束、外键约束、CHECK约束、默认约束、非空约束 难点提示 CREATE语句与ALTER语句,用例2：建库、建表、建约束,参考代码,-建库语句： CREATE DATABASE bankDB ON ( NAME=, FILENAME=,
6、SIZE=, FILEGROWTH= ) LOG ON ( ),-建表语句： CREATE TABLE 表名 ( customerID INT IDENTITY(1,1), customerName CHAR(8) NOT NULL, ),文件增长率,数据文件,日志文件,自动编号，从1开始,非空/必填,加约束、建关系部分,-建约束语句： ALTER TABLE cardInfo ADD CONSTRAINT PK_cardID PRIMARY KEY(cardID), CONSTRAINT CK_cardID CHECK(cardID LIKE 6227 2666 0-9), CONSTRAI
7、NT DF_curType DEFAULT(RMB) FOR curType CONSTRAINT FK_customerID FOREIGN KEY(customerID) REFERENCES userInfo(customerID), CONSTRAINT UQ_PID UNIQUE(PID), ,主键约束,检查约束,外键约束（建关系）,唯一约束,默认约束,用例2：建库、建表、建约束,用例3：插入测试数据,需求说明 使用SQL语言向每个表中插入至少5条记录 思路分析 先增加主表的记录，后增加子表的记录 难点提示 插入数据表的先后顺序 插入客户记录时，先要查询是否已存在这个客户，如果已存在
8、，就不要再插入。,张三和李四开户：,SET NOCOUNT ON -不显示受影响的条数信息 INSERT INTO userInfo(customerName,PID,telephone,address ) VALUES(张三,130101601221321 ,010-12345678 ,北京海淀) INSERT INTO cardInfo(cardID,savingType,openMoney ,balance,customerID) VALUES(6227 2666 1234 5678 , 活期,1000,1000,1) INSERT INTO userInfo(customerName,
9、PID,telephone) VALUES(李四,213456197506133178 ,0478-24322123 ) INSERT INTO cardInfo(cardID,savingType,openMoney,balance, customerID) VALUES(6227 2666 5678 1234 ,定期一年 ,1,1,2) SELECT * FROM userInfo SELECT * FROM cardInfo GO,用例3：插入测试数据,张三的卡号取款900元，李四的卡号存款5000元,/*-交易信息表插入交易记录-*/ INSERT INTO transInfo(tra
10、nsType,cardID,transMoney) VALUES(支取, 6227 2666 1234 5678 ,900) /*-更新银行卡信息表中的现有余额-*/ UPDATE cardInfo SET balance=balance-900 WHERE cardID=6227 2666 1234 5678 /*-交易信息表插入交易记录-*/ INSERT INTO transInfo(transType,cardID,transMoney) VALUES(存入, 6227 2666 5678 1234 ,5000) /*-更新银行卡信息表中的现有余额-*/ UPDATE cardInfo
11、 SET balance=balance+5000 WHERE cardID=6227 2666 5678 1234 GO,用例3：插入测试数据,用例3：插入测试数据,功能测试 使用查询语句测试插入的数据是否正确 代码审查 审查插入测试数据的SQL代码,用例4：模拟常规业务,需求说明 修改客户密码 办理银行卡挂失 统计银行资金流通余额和盈利结算 银行资金流通余额=总存入金额-总支取金额 盈利结算=总支取金额 * 0.008 总存入金额 * 0.003 查询本周开户的卡号，显示该卡相关信息 查询本月交易金额最高的卡号 查询挂失账号的客户信息 催款提醒业务,用例4：模拟常规业务,思路分析 修改客户
12、密码 办理银行卡挂失 使用UPDATE语句实现密码变更和卡挂失,UPDATE WHERE ,统计银行资金流通余额和盈利结算 使用聚合函数SUM()和数据类型转换函数CONVERT(),SELECT inMoney=SUM() FROM WHERE ,查询本周开户的卡号，显示该卡相关信息 日期函数DATEDIFF()和DATEPART(),用例4：模拟常规业务,思路分析 查询本月交易金额最高的卡号 使用子查询和DISTINCT关键字去掉重复的卡号,SELECT FROM transInfo WHERE transMoney = ( SELECT FROM ),查询挂失账号的客户信息 使用子查询I
13、N 或内联接查询INNER JOIN,SELECT FROM userInfo WHERE customerID IN ( SELECT FROM ),催款提醒业务 使用子查询IN 或内联接查询INNER JOIN,SELECT FROM userInfo INNER JOIN ,用例4：模拟常规业务,功能测试 检查点 正确修改客户密码 正确办理银行卡挂失，查询获得挂失账号的客户信息 正确查询本月交易金额最高的卡号 正确统计银行资金流通余额和盈利结算 正确查询本周开户的卡号，显示该卡相关信息 正确催款提醒业务,用例5：利用视图实现数据查询,需求说明 为客户提供以下3个视图供其查询该客户数据 客
14、户基本信息：vw_userInfo 银行卡信息：vw_cardInfo 银行卡交易信息：vw_tradeInfo 提供友好界面，要求各列名称为中文描述 调用创建的视图获得查询结果 思路分析 创建视图 调用视图,CREATE VIEW AS GO,SELECT FROM ,用例5：利用视图实现数据查询,功能测试 检查点 正确创建视图 正确使用视图获得查询数据,用例6：用存储过程实现业务处理,需求说明 产生随机卡号 完成开户业务 完成取款或存款业务 根据卡号打印对账单 查询、统计指定时间段内没有发生交易的账户信息 统计指定时间段内某地区客户在银行卡交易量和交易额。如果不指定地区，则查询所有客户的交
15、易量和交易额,用例6：用存储过程实现业务处理,思路分析 完成取款或存款业务 创建存储过程,CREATE PROCEDURE proc_takeMoney card char(19)， type char(4) , inputPass char(6)= AS -SQL语句 IF() GO,存储过程的参数,有默认值的参数，放在最后,调用存储过程,EXEC proc_takeMoney 6227 2666 5678 1234 , 支取,123456,检测支取金额是否大于当前存款金额加1,用例6：用存储过程实现业务处理,思路分析 产生随机卡号 创建存储过程,CREATE PROCEDURE proc_randCardID randCardID char(19) OUTPUT SELECT
