取款机系统数据库设计.ppt

《取款机系统数据库设计.ppt》由会员分享，可在线阅读，更多相关《取款机系统数据库设计.ppt（43页珍藏版）》请在三一文库上搜索。

1、ATM取款机系统 数据库设计,2,问题描述,某银行拟开发一套ATM取款机系统，实现如下功能： 1、开户（到银行填写开户申请单，卡号自动生成） 2、取钱 3、存钱 4、查询余额 5、转账（如使用一卡通代缴手机话费、个人股票交易等） 现要求对“ATM柜员机系统”进行数据库的设计并实现，数据库保存在D:bank目录下，文件增长率为15% 。,3,问题分析-1,用户信息表：userInfo ：,4,问题分析-2,银行卡信息表：cardInfo,5,问题分析-3,交易信息表：transInfo,6,难点分析-1,设计ER图、建库、建表、加约束、建关系部分,建库语句： CREATE DATABASE ba

2、nkDB ON ( NAME=, FILENAME=, SIZE=, FILEGROWTH= ) LOG ON ( . ),建表语句： CREATE TABLE 表名 ( customerID INT IDENTITY(1,1), customerName CHAR(8) NOT NULL, ),文件增长率,数据文件,日志文件,自动编号，从1开始,非空/必填,7,难点分析-2,设计ER图、建库、建表、加约束、建关系部分,建约束语句： ALTER TABLE cardInfo ADD CONSTRAINT PK_cardID PRIMARY KEY(cardID), CONSTRAINT CK_

3、cardID CHECK(cardID LIKE 1010 3576 0-9), CONSTRAINT DF_curType DEFAULT(RMB) FOR curType CONSTRAINT FK_customerID FOREIGN KEY(customerID) REFERENCES userInfo(customerID), CONSTRAINT UQ_PID UNIQUE(PID), ,主键约束,检查约束,外键约束 （建关系）,唯一约束,默认约束,8,难点分析-3,建表时:IDENTITY(1,1),check约束：len( )函数,建表时:NOT NULL,check约束： l

4、ike 0-90-9,设计ER图、建库、建表、加约束、建关系部分,9,子查询： SELECT FROM WHERE transMoney=(SELECT FROM ) 内部连接： SELECT FROM userInfo INNER JOIN cardInfo ON SQL编程： DECLARE inMoney money SELECT inMoney=sum(transMoney) from where (transType=存入) 视图： CREATE VIEW view_userInfo AS -SQL语句 GO,难点分析-4,声明变量,插入测试数据、常规业务模拟、创建索引视图部分,给变

5、量赋值的两种方法：SELECT或SET,测试视图： SELECT FROM view_userInfo ,10,CREATE TRIGGER trig_trans ON transInfo FOR INSERT AS select myTransType=transType,outMoney=transMoney , myCardID=cardID from inserted if () begin raiserror (交易失败！余额不足！,16,1) rollback tran -取消交易 end GO,难点分析-5,插入的数据临时表：inserted,创建触发器部分,错误提示： rais

6、error( )函数,取消交易，撤销事务：rollback trans,插入触发器,11,创建存储过程： CREATE PROCEDURE proc_takeMoney card char(19)， type char(4) , inputPass char(6)= AS SQL语句 GO 调用存储过程： EXEC proc_openAccount 李四,321245678912345678, 0478-44443333,1,定期,难点分析-6,存储过程部分1：,存储过程的参数,有默认值的参数，放在最后,12,创建存储过程： CREATE PROCEDURE proc_randCardID r

7、andCardID 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,难点分析-7,存储过程部分2：,OUTPUT表示传出的参数,产生0-1的随机数,字符串截取函数,截取小数点后8位作为卡号的后八为数 卡号(4位一组，用空格隔开)：1010 3576 0823 3262,调用带output输出参数的

8、存储过程,13,阶段划分,第一阶段（30分钟） 利用PowerDesigner设计E-R图 第二阶段（ 50分钟） 利用SQL语句实现建库、建表、加约束、建关系 第三阶段（30分钟） 利用SQL语句插入测试数据、模拟常规业务操作 第四阶段（40分钟） 利用SQL语句创建索引和视图、创建触发器 第五阶段（60分钟） 利用SQL语句创建3个存储过程并测试 第六阶段（20分钟） 利用SQL语句创建转帐事务并测试 第七阶段（20分钟） 利用SQL语句创建系统维护帐号并授权,14,第一阶段,第一阶段（50分钟） 利用PowerDesigner设计E-R图 要求学员自己动手操作，教员巡视，解答学员提出的问

9、题,15,阶段检查,针对第一阶段抽查学员的操作结果 教员给出点评或集中演示难点部分,16,第一阶段结果演示1,第一阶段操作的结果： ？加吗？,17,第二阶段,第二阶段（50分钟） 利用SQL语句实现建库、建表、加约束、建关系 要求学员自己动手编写SQL语句，教员巡视，解答学员提出的问题,18,阶段检查,针对第二阶段抽查学员的编码结果 教员给出点评或集中演示难点部分,19,第二阶段标准代码演示-1,建库,IF exists(SELECT * FROM sysdatabases WHERE name=bankDB) DROP DATABASE bankDB GO CREATE DATABASE b

10、ankDB ON ( NAME=bankDB_data, FILENAME=d:bankbankDB_data.mdf, SIZE=1mb, FILEGROWTH=15% ) LOG ON ( ,检验数据库是否存在，如果为真，删除此数据库,创建建库bankDB,20,第二阶段标准代码演示-2,建表：,USE bankDB GO CREATE TABLE userInfo 用户信息表 ( customerID INT IDENTITY(1,1), customerName CHAR(8) NOT NULL, PID CHAR(18) NOT NULL, telephone CHAR(13) NO

11、T NULL, address VARCHAR(50) ) GO,CREATE 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 MONEY NOT NULL, pass CHAR(6) NOT NULL, IsReportLoss BIT NOT NULL, customerID INT NOT NULL ),

12、CREATE TABLE transInfo -交易信息表 ( transDate DATETIME NOT NULL, transType CHAR(4) NOT NULL, cardID CHAR(19) NOT NULL, transMoney MONEY NOT NULL, remark TEXT ) GO,21,第三阶段,第三阶段（60分钟）： 利用SQL语句实现插入测试数据、常规业务操作 要求学员自己动手操作，教员巡视，解答学员提出的问题,22,阶段检查,针对第三阶段抽查学员的编码结果 抽查学员编写的完整代码，要求学员上台讲解，并演示运行结果 教员给出点评,23,第三阶段标准代码演

13、示-1,张三和李四开户：,SET NOCOUNT ON -不显示受影响的条数信息 INSERT INTO userInfo(customerName,PID,telephone,address ) VALUES(张三,123456789012345,010-67898978,北京海淀) INSERT INTO cardInfo(cardID,savingType,openMoney ,balance,customerID) VALUES(1010 3576 1234 5678, 活期,1000,1000,1) INSERT INTO userInfo(customerName,PID,tele

14、phone) VALUES(李四,321245678912345678,0478-44443333) INSERT INTO cardInfo(cardID,savingType,openMoney,balance, customerID) VALUES(1010 3576 1212 1134,定期,1,1,2) SELECT * FROM userInfo SELECT * FROM cardInfo GO,24,第三阶段标准代码演示-2,张三的卡号取款900元，李四的卡号存款5000元,/*-交易信息表插入交易记录-*/ INSERT INTO transInfo(transType,ca

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

16、balance+5000 WHERE cardID=1010 3576 1212 1134 GO,25,第三阶段标准代码演示-3,修改密码和挂失帐号,/*-修改密码-*/ -1.张三（卡号为1010 3576 1234 5678）修改银行卡密码为123456 -2.李四（卡号为1010 3576 1212 1134）修改银行卡密码为123123 update cardInfo set pass=123456 WHERE cardID=1010 3576 1234 5678 update cardInfo set pass=123123 WHERE cardID=1010 3576 1212 1

17、134 SELECT * FROM cardInfo /*- 李四的卡号挂失 -*/ update cardInfo set IsReportLoss=1 WHERE cardID=1010 3576 1212 1134 SELECT * FROM cardInfo GO,26,第三阶段标准代码演示-4,统计银行的资金流通余额和盈利结算,DECLARE inMoney money DECLARE outMoney money DECLARE profit money SELECT inMoney=sum(transMoney) FROM transInfo WHERE (transType=存

18、入) SELECT outMoney=sum(transMoney) FROM transInfo WHERE (transType=支取) print 银行流通余额总计为:+ convert(varchar(20), inMoney-outMoney)+RMB set profit=outMoney*0.008-inMoney*0.003 print 盈利结算为:+ convert(varchar(20),profit)+RMB GO,27,第三阶段标准代码演示-5,其他操作,/*-查询本周开户的卡号,显示该卡相关信息-*/ SELECT * FROM cardInfo WHERE (DAT

19、EDIFF(Day,getDate(),openDate)DATEPART(weekday,openDate) /*-查询本月交易金额最高的卡号-*/ SELECT DISTINCT cardID FROM transInfo WHERE transMoney=(SELECT Max(transMoney) FROM transInfo) /*-查询挂失帐号的客户信息-*/ SELECT customerName as 客户姓名,telephone as 联系电话 FROM userInfo WHERE customerID IN (SELECT customerID FROM cardInf

20、o WHERE IsReportLoss=1) /*-催款提醒： 如果发现用户帐上余额少于200元，将致电催款。-*/ SELECT, FROM userInfo INNER JOIN cardInfo ON userInfo.customerID =cardInfo.customerID WHERE balance200,28,第四阶段,第四阶段（60分钟）： 利用SQL语句创建索引和视图 要求学员自己动手操作，教员巡视，解答学员提出的问题,29,阶段检查,针对第三阶段抽查学员的编码结果 抽查学员编写的完整代码，要求学员上台讲解，并演示运行结果 教员给出点评,30,第四阶段标准代码演示-1,

21、创建索引和视图：,-1.创建索引：给交易表的卡号cardID字段创建重复索引 create NONCLUSTERED INDEX index_cardID ON transInfo(cardID)WITH FILLFACTOR=70 GO -2.按指定索引查询 张三（卡号为1010 3576 1212 1134）的交易记录 SELECT * FROM transInfo (INDEX=index_cardID) WHERE cardID=1010 3576 1234 5678 GO -3.创建视图：查询各表要求字段全为中文字段名。 create VIEW view_userInfo -银行卡信

22、息表视图（其他表同理） AS select customerID as 客户编号,customerName as 开户名, PID as 身份证号, telephone as 电话号码,address as 居住地址 from userInfo GO,31,第四阶段标准代码演示-2,创建触发器：,CREATE TRIGGER trig_trans ON transInfo FOR INSERT AS DECLARE myTransType char(4),outMoney MONEY,myCardID char(19) SELECT myTransType=transType,outMoney

23、=transMoney ,myCardID=cardID FROM inserted DECLARE mybalance money SELECT mybalance=balance FROM cardInfo WHERE cardID=myCardID if (myTransType=支取) if (mybalance=outMoney+1) update cardInfo set balance=balance-outMoney WHERE cardID=myCardID else （未完待续）,.(提示余额不足，交易失败！代码略) else update cardInfo set bal

24、ance=balance+outMoney WHERE cardID=myCardID print 交易成功！交易金额：+convert(varchar(20),outMoney) SELECT mybalance=balance FROM cardInfo WHERE cardID=myCardID print 卡号+myCardID+ 余额：+convert(varchar(20),mybalance) GO ）,-测试触发器：张三的卡号支取1000，李四的卡号存入200 -现实中的取款机依靠读卡器读出卡号,这里根据张三的名字查出考号来模拟 declare card char(19) se

25、lect card=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName=张三 INSERT INTO transInfo(transType,cardID,transMoney) VALUES(支取,card,1000) GO declare card char(19) select card=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.cu

26、stomerID where customerName=李四 INSERT INTO transInfo(transType,cardID,transMoney) VALUES(存入,card,200) GO,32,第五阶段,第五阶段（60分钟）： 利用SQL语句创建3个存储过程并测试 要求学员自己动手操作，教员巡视，解答学员提出的问题,33,阶段检查,针对第三阶段抽查学员的编码结果 抽查学员编写的完整代码，要求学员上台讲解，并演示运行结果 教员给出点评,34,第五阶段标准代码演示-1,取钱或存钱的存储过程,CREATE PROCEDURE proc_takeMoney card char(1

27、9),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 end INSERT INTO transInfo(transType,cardID,transMoney) VALUES(type,card,m) GO,-2.调用存储过程取钱或存钱 张三取300，李四存500 现实中的ATM依靠读卡器读出张三的卡号,这里

28、根据张三的名字查出考号模拟 declare card char(19) select card=cardID from 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=

29、userInfo.customerID where customerName=李四 EXEC proc_takeMoney card,500 ,存入 select * from view_cardInfo select * from view_transInfo,35,第五阶段标准代码演示-2,产生随机卡号的存储过程,create procedure proc_randCardID randCardID char(19) OUTPUT AS DECLARE r numeric(15,8) DECLARE tempStr char(10) SELECT r=RAND(DATEPART(mm, G

30、ETDATE() * 100000 ) + (DATEPART(ss, GETDATE() * 1000 ) + DATEPART(ms, GETDATE() ) set tempStr=convert(char(10),r) set randCardID=1010 3576 +SUBSTRING(tempStr,3,4)+ +SUBSTRING(tempStr,7,4) GO -测试产生随机卡号 DECLARE mycardID char(19) EXECUTE proc_randCardID mycardID OUTPUT print 产生的随机卡号为：+mycardID GO,测试：产生

31、随机卡号 代码： DECLARE mycardID char(19) EXECUTE proc_randCardID mycardID OUTPUT print 产生的随机卡号为：+mycardID GO,36,第五阶段标准代码演示-3,开户的存储过程,create 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),

32、cur_customerID int -调用产生随机卡号的存储过程获得随机卡号 EXECUTE proc_randCardID mycardID OUTPUT while exists(SELECT * FROM cardInfo WHERE cardID=mycardID) EXECUTE proc_randCardID mycardID OUTPUT print 尊敬的客户,开户成功!系统为您产生的随机卡号为:+mycardID print 开户日期+convert(char(10),getdate(),111)+ 开户金额:+convert(varchar(20),openMoney)

33、(未完待续),IF not exists(select * from userInfo where PID=PID) INSERT INTO userInfo(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(myc

34、ardID,savingType,openMoney,openMoney,cur_customerID) GO -调用存储过程开户 EXEC proc_openAccount 王五,334456889012678,2222-63598978,1000,活期,河南新乡 EXEC proc_openAccount 李四,213445678912342222,0760-44446666,1,定期,37,第五阶段,第六阶段（30分钟）： 利用SQL语句创建转帐事务的存储过程并测试 要求学员自己动手操作，教员巡视，解答学员提出的问题,38,阶段检查,针对第三阶段抽查学员的编码结果 抽查学员编写的完整代码

35、，要求学员上台讲解，并演示运行结果 教员给出点评,39,第六阶段标准代码演示-1,转帐事务的存储过程,create procedure proc_transfer card1 char(19),card2 char(19),outmoney money AS begin tran print 开始转帐,请稍后 DECLARE errors int set errors=0 INSERT INTO transInfo(transType,cardID,transMoney) VALUES(支取,card1,outmoney) set errors=errors+error INSERT INTO

36、 transInfo(transType,cardID,transMoney) VALUES(存入,card2,outmoney) set errors=errors+error （未完待续）,if (errors0) begin print 转帐失败！ rollback tran end else begin print 转帐成功！ commit tran end GO,-调用上述事务过程转帐 declare card1 char(19),card2 char(19) select card1=cardID from cardInfo Inner Join userInfo ON cardI

37、nfo.customerID=userInfo.customerID where customerName=李四 select card2=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName=张三 EXEC proc_transfer card1,card2,2000 GO,40,第七阶段,第七阶段（60分钟）： 利用SQL语句创建系统维护帐号并授权 要求学员自己动手操作，教员巡视，解答学员提出的问题,41,阶段检查,针对第三阶段抽查学员的编

38、码结果 抽查学员编写的完整代码，要求学员上台讲解，并演示运行结果 教员给出点评,42,第七阶段标准代码演示-1,添加系统维护帐号sysAdmin，并授权,-1.添加SQL登录帐号 If not exists(SELECT * FROM master.dbo.syslogins WHERE loginname=sysAdmin) begin EXEC sp_addlogin sysAdmin, 1234 -添加SQL登录帐号 EXEC sp_defaultdb sysAdmin , bankDB -修改登录的默认数据库为bankDB end go -2.创建数据库用户 EXEC sp_grant

39、dbaccess sysAdmin, sysAdminDBUser GO,-3.-给数据库用户授权 -为sysAdminDBUser分配对象权限(增删改查的权限) GRANT SELECT,insert,update,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,43,总结,巩固的知识点： SQL语句：建库、建表、加约束、建关系 常用的约束类型：主键、外键、非空、默认值、检查约束 高级查询：内部连接、子查询、索引、视图 触发器：插入触发器的使用 存储过程：带参数的存储过程、带返回值的存储过程 事务：显示事务的应用 安全帐号：创建登录帐号、数据库用户、授权,