取款机系统数据库设计.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 bankDB ON(NA

2、ME=,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_cardID CHECK(cardID LIKE 10

3、10 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约束：like 0-90-9,设计ER图、建库、建表、加约束、建关系部分,9

4、,子查询：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,声明变量,插入测试数据、常规业务模拟、创建索引视图部分,给变量赋值的两种方法：SELECT或SET,测试视图：SELECT.FROM view_user

5、Info,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,创建触发器部分,错误提示：raiserror()函数,取消交易，撤销事务：rollback trans,插入触发器,11,创建存储过程：CREA

6、TE 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 randCardID char(19)OUTPUT SELECT r=RAND(随机种子)例如：0.08233262 3215.set

7、 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输出参数的存储过程,13,阶段划分,第一阶段（30分钟）利用PowerDesigner设计E-R图第二阶段（50分钟）利用SQL语句实现建库、建表、加约束、建关

8、系第三阶段（30分钟）利用SQL语句插入测试数据、模拟常规业务操作 第四阶段（40分钟）利用SQL语句创建索引和视图、创建触发器 第五阶段（60分钟）利用SQL语句创建3个存储过程并测试 第六阶段（20分钟）利用SQL语句创建转帐事务并测试 第七阶段（20分钟）利用SQL语句创建系统维护帐号并授权,14,第一阶段,第一阶段（50分钟）利用PowerDesigner设计E-R图要求学员自己动手操作，教员巡视，解答学员提出的问题,15,阶段检查,针对第一阶段抽查学员的操作结果教员给出点评或集中演示难点部分,16,第一阶段结果演示1,第一阶段操作的结果：？加吗？,17,第二阶段,第二阶段（50分钟）

9、利用SQL语句实现建库、建表、加约束、建关系要求学员自己动手编写SQL语句，教员巡视，解答学员提出的问题,18,阶段检查,针对第二阶段抽查学员的编码结果教员给出点评或集中演示难点部分,19,第二阶段标准代码演示-1,建库,IF exists(SELECT*FROM sysdatabases WHERE name=bankDB)DROP DATABASE bankDBGOCREATE DATABASE bankDB ON(NAME=bankDB_data,FILENAME=d:bankbankDB_data.mdf,SIZE=1mb,FILEGROWTH=15%)LOG ON(.,检验数据库是否

10、存在，如果为真，删除此数据库,创建建库bankDB,20,第二阶段标准代码演示-2,建表：,USE bankDBGOCREATE TABLE userInfo 用户信息表(customerID INT IDENTITY(1,1),customerName CHAR(8)NOT NULL,PID CHAR(18)NOT NULL,telephone CHAR(13)NOT NULL,address VARCHAR(50)GO,CREATE TABLE cardInfo-银行卡信息表(cardID CHAR(19)NOT NULL,curType CHAR(5)NOT NULL,savingTyp

11、e 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),CREATE TABLE transInfo-交易信息表(transDate DATETIME NOT NULL,transType CHAR(4)NOT NULL,cardID CHAR(19)NOT NULL,transMoney MONEY NOT NULL,re

12、mark TEXT)GO,21,第三阶段,第三阶段（60分钟）：利用SQL语句实现插入测试数据、常规业务操作 要求学员自己动手操作，教员巡视，解答学员提出的问题,22,阶段检查,针对第三阶段抽查学员的编码结果抽查学员编写的完整代码，要求学员上台讲解，并演示运行结果教员给出点评,23,第三阶段标准代码演示-1,张三和李四开户：,SET NOCOUNT ON-不显示受影响的条数信息INSERT INTO userInfo(customerName,PID,telephone,address)VALUES(张三,123456789012345,010-67898978,北京海淀)INSERT INT

13、O cardInfo(cardID,savingType,openMoney,balance,customerID)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)SELEC

14、T*FROM userInfoSELECT*FROM cardInfoGO,24,第三阶段标准代码演示-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

15、 transInfo(transType,cardID,transMoney)VALUES(存入,1010 3576 1212 1134,5000)/*-更新银行卡信息表中的现有余额-*/UPDATE cardInfo SET balance=balance+5000 WHERE cardID=1010 3576 1212 1134GO,25,第三阶段标准代码演示-3,修改密码和挂失帐号,/*-修改密码-*/-1.张三（卡号为1010 3576 1234 5678）修改银行卡密码为123456-2.李四（卡号为1010 3576 1212 1134）修改银行卡密码为123123update c

16、ardInfo 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,26,第三阶段标准代码演示-4,统计银行的资金流通余额和盈利结算,DECLARE inMoney money

17、DECLARE 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 profit=outMoney*0.008-inMoney*0.003print 盈利结算为:+convert(varchar(20)

18、,profit)+RMBGO,27,第三阶段标准代码演示-5,其他操作,/*-查询本周开户的卡号,显示该卡相关信息-*/SELECT*FROM cardInfo WHERE(DATEDIFF(Day,getDate(),openDate)DATEPART(weekday,openDate)/*-查询本月交易金额最高的卡号-*/SELECT DISTINCT cardID FROM transInfo WHERE transMoney=(SELECT Max(transMoney)FROM transInfo)/*-查询挂失帐号的客户信息-*/SELECT customerName as 客户姓

19、名,telephone as 联系电话 FROM userInfo WHERE customerID IN(SELECT customerID FROM cardInfo WHERE IsReportLoss=1)/*-催款提醒：如果发现用户帐上余额少于200元，将致电催款。-*/SELECT,.,FROM userInfo INNER JOIN cardInfo ON userInfo.customerID=cardInfo.customerID WHERE balance200,28,第四阶段,第四阶段（60分钟）：利用SQL语句创建索引和视图 要求学员自己动手操作，教员巡视，解答学员提出

20、的问题,29,阶段检查,针对第三阶段抽查学员的编码结果抽查学员编写的完整代码，要求学员上台讲解，并演示运行结果教员给出点评,30,第四阶段标准代码演示-1,创建索引和视图：,-1.创建索引：给交易表的卡号cardID字段创建重复索引create NONCLUSTERED INDEX index_cardID ON transInfo(cardID)WITH FILLFACTOR=70GO-2.按指定索引查询 张三（卡号为1010 3576 1212 1134）的交易记录SELECT*FROM transInfo(INDEX=index_cardID)WHERE cardID=1010 3576

21、 1234 5678GO-3.创建视图：查询各表要求字段全为中文字段名。create VIEW view_userInfo-银行卡信息表视图（其他表同理）AS select customerID as 客户编号,customerName as 开户名,PID as 身份证号,telephone as 电话号码,address as 居住地址 from userInfoGO,31,第四阶段标准代码演示-2,创建触发器：,CREATE TRIGGER trig_trans ON transInfo FOR INSERT AS DECLARE myTransType char(4),outMoney

22、 MONEY,myCardID char(19)SELECT myTransType=transType,outMoney=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 els

23、e（未完待续）,.(提示余额不足，交易失败！代码略)else update cardInfo set balance=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-现实中的取款机依靠

24、读卡器读出卡号,这里根据张三的名字查出考号来模拟declare card char(19)select card=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName=张三INSERT INTO transInfo(transType,cardID,transMoney)VALUES(支取,card,1000)GOdeclare card char(19)select card=cardID from cardInfo Inner Join u

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

26、ROCEDURE proc_takeMoney card char(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 end INSERT INTO transInfo(transType,cardID,transMoney)VALUES(type,card,m)GO,-2.调用存储过程取钱或存钱 张三取300，李四存5

27、00 现实中的ATM依靠读卡器读出张三的卡号,这里根据张三的名字查出考号模拟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 c

28、ardInfo.customerID=userInfo.customerID where customerName=李四EXEC proc_takeMoney card,500,存入select*from view_cardInfoselect*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(DATEPA

29、RT(mm,GETDATE()*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 OUTPUTprint 产生的随机卡号为：+mycardIDGO,测试：产生随机卡号代码：DECLARE myc

30、ardID char(19)EXECUTE proc_randCardID mycardID OUTPUTprint 产生的随机卡号为：+mycardIDGO,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),cur_customerID int-调用产生随机

31、卡号的存储过程获得随机卡号 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)(未完待续),IF not exists(select*fro

32、m 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(mycardID,savingType,openMoney,openMoney,c

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

34、的存储过程,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 transInfo(transType,cardID,transMoney)VALUES(存

35、入,card2,outmoney)set errors=errors+error（未完待续）,if(errors0)begin print 转帐失败！rollback tran end 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=李四s

36、elect card2=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName=张三EXEC proc_transfer card1,card2,2000GO,40,第七阶段,第七阶段（60分钟）：利用SQL语句创建系统维护帐号并授权要求学员自己动手操作，教员巡视，解答学员提出的问题,41,阶段检查,针对第三阶段抽查学员的编码结果抽查学员编写的完整代码，要求学员上台讲解，并演示运行结果教员给出点评,42,第七阶段标准代码演示-1,添加系统维护帐号

37、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_grantdbaccess sysAdmin,sysAdminDBUser GO,-3.-给数据库用户授权-为sysAdminDBUser分配对象权限(增删改

38、查的权限)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语句：建库、建表、加约束、建关系常用的约束类型：主键、外键、非空、默认值、检查约束高级查询：内部连接、子查询、索引、视图 触发器：插入触发器的使用存储过程：带参数的存储过程、带返回值的存储过程事务：显示事务的应用安全帐号：创建登录帐号、数据库用户、授权,