《ATM取款机系统数据库设计.ppt》由会员分享，可在线阅读，更多相关《ATM取款机系统数据库设计.ppt（42页珍藏版）》请在金锄头文库上搜索。
1、ATMATM取款机系统数取款机系统数据库设计据库设计 问题描述某银行拟开发一套某银行拟开发一套ATM取款机系统，实现如下功能：取款机系统，实现如下功能：1、开户（到银行填写开户申请单，卡号自动生成）、开户（到银行填写开户申请单，卡号自动生成）2、取钱、取钱3、存钱、存钱4、查询余额、查询余额5、转账（如使用一卡通代缴手机话费、个人股票交易等）、转账（如使用一卡通代缴手机话费、个人股票交易等）现要求对现要求对“ATM柜员机系统柜员机系统”进行数据库的设计并实现，数进行数据库的设计并实现，数据库保存在据库保存在D:bank目录下，文件增长率为目录下，文件增长率为15% 。问题分析-1字段名称说 明
2、customerID顾客编号自动编号（标识列），从1开始，主键customerName开户名必填PID身份证号必填，只能是18位或15位，身份证号唯一约束telephone联系电话必填，格式为xxxx-xxxxxxxx或手机号13位address居住地址可选输入用户信息表：用户信息表：userInfo ：问题分析-2银行卡信息表：银行卡信息表：cardInfo字段名称说 明cardID卡号必填，主健，银行的卡号规则和电话号码一样，一般前8位代表特殊含义，如某总行某支行等。假定该行要求其营业厅的卡号格式为：1010 3576 xxxx xxx开始,每4位号码后有空格，卡号一般是随机产生。curT
3、ype货币种类必填，默认为RMBsavingType存款类型活期/定活两便/定期openDate开户日期必填，默认为系统当前日期openMoney开户金额必填，不低于1元balance余额必填，不低于1元,否则将销户pass密码必填，6位数字，开户时默认为6个“8”IsReportLoss是否挂失必填，是/否值，默认为”否”customerID顾客编号外键，必填，表示该卡对应的顾客编号，一位顾客允许办理多张卡号问题分析-3字段名称说 明transDate 交易日期必填，默认为系统当前日期cardID卡号必填，外健，可重复索引transType 交易类型必填，只能是存入/支取transMoney
4、交易金额必填，大于0remark备注可选输入，其他说明交易信息表：交易信息表：transInfo 难点分析-1设计ER图、建库、建表、加约束、建关系部分 建库语句：建库语句：CREATE DATABASE bankDB ON ( NAME=, FILENAME=, SIZE=, FILEGROWTH=) LOG ON ( . )建表语句：建表语句：CREATE TABLE 表名表名 ( customerID INT IDENTITY(1,1), customerName CHAR(8) NOT NULL, .)文件增长率文件增长率数据文件数据文件日志文件日志文件自动编号，自动编号，从从1开始开
5、始非空非空/必填必填难点分析-2设计ER图、建库、建表、加约束、建关系部分 建约束语句：建约束语句：ALTER TABLE cardInfo ADD CONSTRAINT PK_cardID PRIMARY KEY(cardID), CONSTRAINT CK_cardID CHECK(cardID LIKE 1010 3576 0-9), CONSTRAINT DF_curType DEFAULT(RMB) FOR curType CONSTRAINT FK_customerID FOREIGN KEY(customerID) REFERENCES userInfo(customerID),
6、 CONSTRAINT UQ_PID UNIQUE(PID), .主键约束主键约束检查约束检查约束外键约束（建关系）外键约束（建关系）唯一约束唯一约束默认约束默认约束难点分析-3字段名称说 明customerID顾客编号自动编号（标识列），从1开始，主键customerName开户名必填PID身份证号必填，只能是18位或15位，身份证号唯一约束telephone联系电话必填，格式为xxxx-xxxxxxxx或手机号13位address居住地址可选输入建表时建表时:IDENTITY(1,1)check约束：约束：len( )函数函数建表时建表时:NOT NULLcheck约束：约束： like
7、0-90-9设计ER图、建库、建表、加约束、建关系部分 子查询：子查询：SELECT .FROM WHERE transMoney=(SELECT FROM )内部连接：内部连接：SELECT FROM userInfo INNER JOIN cardInfo ON. SQL编程：编程：DECLARE inMoney moneySELECT inMoney=sum(transMoney) from .where (transType=存入存入)视图：视图：CREATE VIEW view_userInfo AS -SQL语句语句GO难点分析-4声明变量声明变量插入测试数据、常规业务模拟、创建索
8、引视图部分 给变量赋值的两种方法：给变量赋值的两种方法：SELECT或或SET测试视图：测试视图：SELECT . FROM view_userInfo 创建存储过程：创建存储过程： CREATE PROCEDURE proc_takeMoney card char(19)， type char(4) , inputPass char(6)= AS .SQL语句语句GO调用存储过程：调用存储过程：EXEC proc_openAccount 李四李四,321245678912345678, 0478-44443333,1,定期定期难点分析-5存储过程部分1： 存储过程的参数存储过程的参数有默认值
9、的参数，放在最后有默认值的参数，放在最后创建存储过程：创建存储过程： CREATE PROCEDURE proc_randCardID randCardID char(19) OUTPUT SELECT r=RAND (随机种子随机种子 ) 例如：例如：0. 08233262 3215 . set randCardID =.SUBSTRING(tempStr,3,4).GO 调用存储过程：调用存储过程：DECLARE mycardID char(19)EXECUTE proc_randCardID mycardID OUTPUT难点分析-6存储过程部分2：OUTPUT表示表示传出的参数传出的参
10、数产生产生0-1的随机数的随机数截取小数点后截取小数点后8位作为卡号的后八为数位作为卡号的后八为数卡号卡号(4位一组，用空格隔开位一组，用空格隔开)：1010 3576 0823 3262调用带调用带output输出参数的存储过程输出参数的存储过程字符串截取函数字符串截取函数阶段划分第一阶段（30分钟） 利用Visio设计数据库的E-R图第二阶段（ 50分钟） 利用SQL语句实现建库、建表、加约束、建关系第三阶段（30分钟） 利用SQL语句插入测试数据、模拟常规业务操作 第四阶段（30分钟） 利用SQL语句创建索引和视图第五阶段（60分钟） 利用SQL语句创建3个存储过程并测试 第六阶段（30
11、分钟） 利用SQL语句创建转账事务并测试 第七阶段（20分钟） 利用SQL语句创建系统维护帐号并授权 第一阶段第一阶段（30分钟） 利用Visio设计数据库的E-R图要求学员自己动手操作，教员巡视，解答学员提出的问题阶段检查针对第一阶段抽查学员的操作结果教员给出点评或集中演示难点部分第一阶段结果演示1第一阶段操作的结果：第二阶段第二阶段（50分钟） 利用SQL语句实现建库、建表、加约束、建关系要求学员自己动手编写SQL语句，教员巡视，解答学员提出的问题阶段检查针对第二阶段抽查学员的编码结果教员给出点评或集中演示难点部分第二阶段标准代码演示-1建库IF exists(SELECT * FROM
12、sysdatabases WHERE name=bankDB) DROP DATABASE bankDBGOCREATE DATABASE bankDB ON ( NAME=bankDB_data, FILENAME=d:bankbankDB_data.mdf, SIZE=3mb, FILEGROWTH=15% ) LOG ON (.检验数据库是否存在，如果为检验数据库是否存在，如果为真，删除此数据库真，删除此数据库 创建建库创建建库bankDB第二阶段标准代码演示-2建表：USE bankDBGOCREATE TABLE userInfo 用户信息表用户信息表 ( customerID IN
13、T IDENTITY(1,1), customerName CHAR(8) NOT NULL, PID CHAR(18) NOT NULL, telephone CHAR(13) NOT NULL, address VARCHAR(50)GOCREATE TABLE cardInfo -银行卡信息表银行卡信息表( cardID CHAR(19) NOT NULL, curType CHAR(5) NOT NULL, savingType CHAR(8) NOT NULL, openDate DATETIME NOT NULL, openMoney MONEY NOT NULL, balance
14、 MONEY NOT NULL, pass CHAR(6) NOT NULL, IsReportLoss BIT NOT NULL, customerID INT NOT NULL)CREATE TABLE transInfo -交易信息表交易信息表( transDate DATETIME NOT NULL, transType CHAR(4) NOT NULL, cardID CHAR(19) NOT NULL, transMoney MONEY NOT NULL, remark TEXT )GO第三阶段第三阶段（30分钟）： 利用SQL语句实现插入测试数据、常规业务操作 要求学员自己动手操
15、作，教员巡视，解答学员提出的问题阶段检查针对第三阶段抽查学员的编码结果抽查学员编写的完整代码，要求学员上台讲解，并演示运行结果教员给出点评第三阶段标准代码演示-1张三和李四开户：SET NOCOUNT ON -不显示受影响的条数信息不显示受影响的条数信息INSERT INTO userInfo(customerName,PID,telephone,address ) VALUES(张三张三,123456789012345,010-67898978,北京海淀北京海淀)INSERT INTO cardInfo(cardID,savingType,openMoney ,balance,custome
16、rID) VALUES(1010 3576 1234 5678, 活期活期,1000,1000,1)INSERT INTO userInfo(customerName,PID,telephone) VALUES(李四李四,321245678912345678,0478-44443333)INSERT INTO cardInfo(cardID,savingType,openMoney,balance, customerID) VALUES(1010 3576 1212 1134,定期定期,1,1,2)SELECT * FROM userInfoSELECT * FROM cardInfoGO第三
17、阶段标准代码演示-2张三的卡号取款900元，李四的卡号存款5000元/*-交易信息表插入交易记录交易信息表插入交易记录-*/INSERT INTO transInfo(transType,cardID,transMoney) VALUES(支取支取,1010 3576 1234 5678,900) /*-更新银行卡信息表中的现有余额更新银行卡信息表中的现有余额-*/UPDATE cardInfo SET balance=balance-900 WHERE cardID=1010 3576 1234 5678/*-交易信息表插入交易记录交易信息表插入交易记录-*/INSERT INTO tran
18、sInfo(transType,cardID,transMoney) VALUES(存入存入,1010 3576 1212 1134,5000) /*-更新银行卡信息表中的现有余额更新银行卡信息表中的现有余额-*/UPDATE cardInfo SET balance=balance+5000 WHERE cardID=1010 3576 1212 1134GO第三阶段标准代码演示-3修改密码和挂失账号/*-修改密码修改密码-*/-1.张三（卡号为张三（卡号为1010 3576 1234 5678）修改银行卡密码为）修改银行卡密码为123456-2.李四（卡号为李四（卡号为1010 3576
19、1212 1134）修改银行卡密码为）修改银行卡密码为123123update cardInfo set pass=123456 WHERE cardID=1010 3576 1234 5678 update cardInfo set pass=123123 WHERE cardID=1010 3576 1212 1134 SELECT * FROM cardInfo/*- 李四李四的卡号挂失的卡号挂失 -*/update cardInfo set IsReportLoss=1 WHERE cardID=1010 3576 1212 1134 SELECT * FROM cardInfoGO第
20、三阶段标准代码演示-4统计银行的资金流通余额和盈利结算DECLARE inMoney moneyDECLARE outMoney moneyDECLARE profit moneySELECT inMoney=sum(transMoney) FROM transInfo WHERE (transType=存入存入)SELECT outMoney=sum(transMoney) FROM transInfo WHERE (transType=支取支取)print 银行流通余额总计为银行流通余额总计为:+ convert(varchar(20), inMoney-outMoney)+RMBset
21、profit=outMoney*0.008-inMoney*0.003print 盈利结算为盈利结算为:+ convert(varchar(20),profit)+RMBGO第三阶段标准代码演示-5其他操作其他操作/*-查询本周开户的卡号查询本周开户的卡号,显示该卡相关信息显示该卡相关信息-*/SELECT * FROM cardInfo WHERE (DATEDIFF(Day,getDate(),openDate)DATEPART(weekday,openDate)/*-查询本月交易金额最高的卡号查询本月交易金额最高的卡号-*/SELECT DISTINCT cardID FROM tran
22、sInfo WHERE transMoney=(SELECT Max(transMoney) FROM transInfo)/*-查询挂失查询挂失账账号的客户信息号的客户信息-*/SELECT customerName as 客户姓名客户姓名,telephone as 联系电话联系电话 FROM userInfo WHERE customerID IN (SELECT customerID FROM cardInfo WHERE IsReportLoss=1)/*-催款提醒催款提醒： 如果发现用户如果发现用户账账上余额少于上余额少于200元，将致电催款。元，将致电催款。-*/SELECT,.,
23、 FROM userInfo INNER JOIN cardInfo ON userInfo.customerID =cardInfo.customerID WHERE balance200第四阶段第四阶段（30分钟）： 利用SQL语句创建索引和视图 要求学员自己动手操作，教员巡视，解答学员提出的问题阶段检查针对第三阶段抽查学员的编码结果抽查学员编写的完整代码，要求学员上台讲解，并演示运行结果教员给出点评第四阶段标准代码演示-1创建索引和视图：-1.创建索引：给交易表的卡号创建索引：给交易表的卡号cardID字段创建重复索引字段创建重复索引create NONCLUSTERED INDEX i
24、ndex_cardID ON transInfo(cardID)WITH FILLFACTOR=70GO-2.按指定索引查询按指定索引查询 张三（卡号为张三（卡号为1010 3576 1212 1134）的交易记录）的交易记录SELECT * FROM transInfo (INDEX=index_cardID) WHERE cardID=1010 3576 1234 5678GO-3.创建视图：查询各表要求字段全为中文字段名。创建视图：查询各表要求字段全为中文字段名。create VIEW view_userInfo -银行卡信息表视图银行卡信息表视图（其他表同理）其他表同理） AS sel
25、ect customerID as 客户编号客户编号,customerName as 开户名开户名, PID as 身份证身份证号号, telephone as 电话号码电话号码,address as 居住地址居住地址 from userInfoGO第五阶段第五阶段（60分钟）： 利用SQL语句创建3个存储过程并测试要求学员自己动手操作，教员巡视，解答学员提出的问题阶段检查针对第三阶段抽查学员的编码结果抽查学员编写的完整代码，要求学员上台讲解，并演示运行结果教员给出点评第五阶段标准代码演示-1取钱或存钱的存储过程create procedure proc_takeMoney card char
26、(19),m money,type char(4),inputPass char(6)= AS print 交易正进行交易正进行,请稍后请稍后. if (type=支取支取) if (SELECT pass FROM cardInfo WHERE cardID=card)inputPass ) begin raiserror (密码错误密码错误!,16,1) return endDECLARE myTransType char(4),outMoney MONEY,myCardID char(19) SELECT myTransType=transType,outMoney=transMoney
27、 ,myCardID=cardID FROM transInfo where cardID=card DECLARE mybalance money SELECT mybalance=balance FROM cardInfo WHERE cardID=card （未完待续）未完待续）-2.调用存储过程取钱或存钱调用存储过程取钱或存钱 张三取张三取300，李四存，李四存500 现实中的现实中的ATM依靠读卡器读出张三的卡号依靠读卡器读出张三的卡号,这里根据张三的名字查出考号模拟这里根据张三的名字查出考号模拟declare card char(19)select card=cardID from
28、 cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName=张三张三EXEC proc_takeMoney card,300 ,支取支取,123456 GO 李四同理李四同理declare card char(19)select card=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName=李四李四EXEC proc_t
29、akeMoney card,500 ,存入存入select * from view_cardInfoselect * from view_transInfoif (type=支取支取) if (mybalance=m+1) update cardInfo set balance=balance-m WHERE cardID=myCardID else begin raiserror (交易失败！余额不足！交易失败！余额不足！,16,1) print 卡号卡号+card+ 余额：余额：+convert(varchar(20),mybalance) return end else update c
30、ardInfo set balance=balance+m WHERE cardID=card print 交易成功！交易金额：交易成功！交易金额：+convert(varchar(20),m) SELECT mybalance=balance FROM cardInfo WHERE cardID=card print 卡号卡号+card+ 余额：余额：+convert(varchar(20),mybalance) INSERT INTO transInfo(transType,cardID,transMoney) VALUES(type,card,m) GO第五阶段标准代码演示-2产生随机卡
31、号的存储过程create procedure proc_randCardID randCardID char(19) OUTPUT AS DECLARE r numeric(15,8) DECLARE tempStr char(10) SELECT r=RAND(DATEPART(mm, GETDATE() * 100000 ) + (DATEPART(ss, GETDATE() * 1000 ) + DATEPART(ms, GETDATE() ) set tempStr=convert(char(10),r) set randCardID=1010 3576 +SUBSTRING(temp
32、Str,3,4)+ +SUBSTRING(tempStr,7,4) GO-测试产生随机卡号测试产生随机卡号DECLARE mycardID char(19) EXECUTE proc_randCardID mycardID OUTPUTprint 产生的随机卡号为：产生的随机卡号为：+mycardIDGO测试：产生随机卡号测试：产生随机卡号代码：代码：DECLARE mycardID char(19) EXECUTE proc_randCardID mycardID OUTPUTprint 产生的随机卡号为：产生的随机卡号为：+mycardIDGO 第五阶段标准代码演示-3开户的存储过程cre
33、ate procedure proc_openAccount customerName char(8),PID char(18),telephone char(13) ,openMoney money,savingType char(8),address varchar(50)= AS DECLARE mycardID char(19),cur_customerID int -调用产生随机卡号的存储过程获得随机卡号调用产生随机卡号的存储过程获得随机卡号 EXECUTE proc_randCardID mycardID OUTPUT while exists(SELECT * FROM card
34、Info WHERE cardID=mycardID) EXECUTE proc_randCardID mycardID OUTPUT print 尊敬的客户尊敬的客户,开户成功开户成功!系统为您产生的随机卡号为系统为您产生的随机卡号为:+mycardID print 开户日期开户日期+convert(char(10),getdate(),111)+ 开户金额开户金额:+convert(varchar(20),openMoney)(未完待续未完待续)IF not exists(select * from userInfo where PID=PID) INSERT INTO userInfo(
35、customerName,PID,telephone,address ) VALUES(customerName,PID,telephone,address) select cur_customerID=customerID from userInfo where PID=PID INSERT INTO cardInfo(cardID,savingType,openMoney,balance,customerID)VALUES(mycardID,savingType,openMoney,openMoney,cur_customerID)GO-调用存储过程开户调用存储过程开户EXEC proc_
36、openAccount 王五王五,334456889012678,2222-63598978,1000,活活期期,河南新乡河南新乡 EXEC proc_openAccount 李四李四,213445678912342222,0760-44446666,1,定定期期 第六阶段第六阶段（30分钟）： 利用SQL语句创建转账事务的存储过程并测试要求学员自己动手操作，教员巡视，解答学员提出的问题阶段检查针对第三阶段抽查学员的编码结果抽查学员编写的完整代码，要求学员上台讲解，并演示运行结果教员给出点评第六阶段标准代码演示-1转账事务的存储过程create procedure proc_transfer
37、card1char(19),card2char(19),outmoney money AS begin tran print 开始转开始转账账,请稍后请稍后. DECLARE errors int set errors=0 EXEC proc_takeMoney card1,outmoney ,支取支取,123123 set errors=errors+error EXEC proc_takeMoney card2,outmoney ,存入存入 set errors=errors+error if (errors0) begin print 转转账账失败失败！ rollback tran en
38、d else begin print 转转账账成功成功！ commit tran endGO-调用上述事务过程转调用上述事务过程转账账declare card1 char(19),card2 char(19)select card1=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName=李四李四select card2=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID
39、=userInfo.customerID where customerName=张三张三EXEC proc_transfer card1,card2,2000GO第七阶段第七阶段（20分钟）： 利用SQL语句创建系统维护帐号并授权要求学员自己动手操作，教员巡视，解答学员提出的问题阶段检查针对第三阶段抽查学员的编码结果抽查学员编写的完整代码，要求学员上台讲解，并演示运行结果教员给出点评第七阶段标准代码演示-1添加系统维护帐号sysAdmin，并授权-1.添加添加SQL登录帐号登录帐号If not exists(SELECT * FROM master.dbo.syslogins WHERE lo
40、ginname=sysAdmin) begin EXEC sp_addlogin sysAdmin, 1234 -添加添加SQL登录帐号登录帐号 EXEC sp_defaultdb sysAdmin , bankDB -修改登录的默认数据库为修改登录的默认数据库为bankDB end go-2.创建数据库用户创建数据库用户 EXEC sp_grantdbaccess sysAdmin, sysAdminDBUser GO-3.-给数据库用户授权给数据库用户授权 -为为sysAdminDBUser分配对象权限分配对象权限(增删改查的权限增删改查的权限) GRANT SELECT,insert,u
41、pdate,delete,select ON transInfo TO sysAdminDBUser GRANT SELECT,insert,update,delete,select ON userInfo TO sysAdminDBUser GRANT SELECT,insert,update,delete,select ON cardInfo TO sysAdminDBUser GO总结巩固的知识点：SQL语句：建库、建表、加约束、建关系常用的约束类型：主键、外键、非空、默认值、检查约束高级查询：内部连接、子查询、索引、视图存储过程：带参数的存储过程、带返回值的存储过程事务：显示事务的应用安全帐号：创建登录帐号、数据库用户、授权
