[教育]銀行atm數(shù)據(jù)庫設(shè)計

1、ATM取款機系統(tǒng)數(shù)據(jù)庫設(shè)計,問題描述,某銀行擬開發(fā)一套ATM取款機系統(tǒng)，實現(xiàn)如下功能：1、開戶（到銀行填寫開戶申請單，卡號自動生成）2、取錢3、存錢4、查詢余額5、轉(zhuǎn)賬（如使用一卡通代繳手機話費、個人股票交易等）現(xiàn)要求對“ATM柜員機系統(tǒng)”進行數(shù)據(jù)庫的設(shè)計并實現(xiàn)，數(shù)據(jù)庫保存在D:\bank目錄下，文件增長率為15% 。,問題分析-1,,用戶信息表：userInfo ：,問題分析-2,銀行卡信息表：cardInfo,問題分析-

2、3,,交易信息表：transInfo,難點分析-1,設(shè)計ER圖、建庫、建表、加約束、建關(guān)系部分,建庫語句：CREATE DATABASE bankDB ON ( NAME=‘…', FILENAME=…', SIZE=…, FILEGROWTH=…) LOG ON ( …. ),建表語句：CREATE TABLE 表名 ( customerID INT IDENTITY(1

3、,1), customerName CHAR(8) NOT NULL, …..),文件增長率,,數(shù)據(jù)文件,日志文件,,自動編號，從1開始,,非空/必填,難點分析-2,設(shè)計ER圖、建庫、建表、加約束、建關(guān)系部分,建約束語句：ALTER TABLE cardInfo ADD CONSTRAINT PK_cardID PRIMARY KEY(cardID), CONSTRAINT CK_cardID

4、 CHECK(cardID LIKE '1010 3576 [0-9]…'), CONSTRAINT DF_curType DEFAULT('RMB') FOR curType CONSTRAINT FK_customerID FOREIGN KEY(customerID)

5、 REFERENCES userInfo(customerID), CONSTRAINT UQ_PID UNIQUE(PID), …..,,主鍵約束,,檢查約束,,,外鍵約束（建關(guān)系）,,唯一約束,默認約束,難點分析-3,,建表時:IDENTITY(1,1),,check約束：len( )函數(shù),,建表時:NOT NULL,,check約束： like ‘[0-9][0-9]…’,設(shè)計ER圖、建庫、建

6、表、加約束、建關(guān)系部分,子查詢：SELECT ....FROM … WHERE transMoney=(SELECT … FROM …)內(nèi)部連接：SELECT … FROM userInfo INNER JOIN cardInfo ON….. SQL編程：DECLARE @inMoney moneySELECT @inMoney=sum(transMoney) from ..where (transType='

7、存入')視圖：CREATE VIEW view_userInfo AS …--SQL語句GO,難點分析-4,,聲明變量,,插入測試數(shù)據(jù)、常規(guī)業(yè)務(wù)模擬、創(chuàng)建索引視圖部分,給變量賦值的兩種方法：SELECT或SET,,測試視圖：SELECT .. FROM view_userInfo …,創(chuàng)建存儲過程： CREATE PROCEDURE proc_takeMoney @card char(1

8、9)， @type char(4) , @inputPass char(6)=' ' AS …..—SQL語句GO調(diào)用存儲過程：EXEC proc_openAccount '李四','321245678912345678', '0478-44443333',1,'定期',難點分析-5,存儲過程部分1：,存儲過

9、程的參數(shù),,有默認值的參數(shù)，放在最后,,創(chuàng)建存儲過程： CREATE PROCEDURE proc_randCardID @randCardID char(19) OUTPUT …SELECT @r=RAND (隨機種子 ) 例如：0. 08233262 3215 ….. set @randCardID =….SUBSTRING(@tempStr,3,4)…..GO 調(diào)用存儲過程：

10、DECLARE @mycardID char(19)EXECUTE proc_randCardID @mycardID OUTPUT,難點分析-6,存儲過程部分2：,OUTPUT表示傳出的參數(shù),,產(chǎn)生0-1的隨機數(shù),,,截取小數(shù)點后8位作為卡號的后八為數(shù)卡號(4位一組，用空格隔開)：1010 3576 0823 3262,,,調(diào)用帶output輸出參數(shù)的存儲過程,字符串截取函數(shù),第一階段結(jié)果演示1,第一階段操作的結(jié)果：,第二

11、階段標準代碼演示-1,建庫,IF exists(SELECT * FROM sysdatabases WHERE name='bankDB') DROP DATABASE bankDBGOCREATE DATABASE bankDB ON ( NAME='bankDB_data', FILENAME='d:\bank\bankDB_data.mdf', SIZE=3

12、mb, FILEGROWTH=15% ) LOG ON (…..,檢驗數(shù)據(jù)庫是否存在，如果為真，刪除此數(shù)據(jù)庫,創(chuàng)建建庫bankDB,第二階段標準代碼演示-2,建表：,,USE bankDBGOCREATE TABLE userInfo －－用戶信息表 ( customerID INT IDENTITY(1,1), customerName CHAR(8) NOT NULL, PID CHAR(18)

13、NOT NULL, telephone CHAR(13) NOT 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 NUL

14、L, 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

15、 CHAR(4) NOT NULL, cardID CHAR(19) NOT NULL, transMoney MONEY NOT NULL, remark TEXT )GO,第三階段標準代碼演示-1,張三和李四開戶：,SET NOCOUNT ON --不顯示受影響的條數(shù)信息INSERT INTO userInfo(customerName,PID,telephone,address ) VALU

16、ES('張三','123456789012345','010-67898978','北京海淀')INSERT INTO cardInfo(cardID,savingType,openMoney ,balance,customerID) VALUES('1010 3576 1234 5678', '活期',1000,1000,1)

17、INSERT INTO userInfo(customerName,PID,telephone) VALUES('李四','321245678912345678','0478-44443333')INSERT INTO cardInfo(cardID,savingType,openMoney,balance, customerID) VALUES('1010

18、3576 1212 1134','定期',1,1,2)SELECT * FROM userInfoSELECT * FROM cardInfoGO,第三階段標準代碼演示-2,張三的卡號取款900元，李四的卡號存款5000元,/*--------------交易信息表插入交易記錄--------------------------*/INSERT INTO transInfo(transType,cardI

19、D,transMoney) VALUES('支取','1010 3576 1234 5678',900) /*-------------更新銀行卡信息表中的現(xiàn)有余額-------------------*/UPDATE cardInfo SET balance=balance-900 WHERE cardID='1010 3576 1234 5678'/*------

20、--------交易信息表插入交易記錄--------------------------*/INSERT INTO transInfo(transType,cardID,transMoney) VALUES('存入','1010 3576 1212 1134',5000) /*-------------更新銀行卡信息表中的現(xiàn)有余額-------------------*/UPDA

21、TE cardInfo SET balance=balance+5000 WHERE cardID='1010 3576 1212 1134'GO,第三階段標準代碼演示-3,修改密碼和掛失賬號,/*---------修改密碼-----*/--1.張三（卡號為1010 3576 1234 5678）修改銀行卡密碼為123456--2.李四（卡號為1010 3576 1212 1134）修改銀行卡密碼為123123u

22、pdate cardInfo set pass='123456' WHERE cardID='1010 3576 1234 5678' update cardInfo set pass='123123' WHERE cardID='1010 3576 1212 1134' SELECT * FROM cardInfo/*--------- 李四的卡號掛失 ------

23、---*/update cardInfo set IsReportLoss=1 WHERE cardID='1010 3576 1212 1134' SELECT * FROM cardInfoGO,第三階段標準代碼演示-4,統(tǒng)計銀行的資金流通余額和盈利結(jié)算,DECLARE @inMoney moneyDECLARE @outMoney moneyDECLARE @profit moneySELECT @in

24、Money=sum(transMoney) FROM transInfo WHERE (transType='存入')SELECT @outMoney=sum(transMoney) FROM transInfo WHERE (transType='支取')print '銀行流通余額總計為:'+ convert(varchar(20), @inMon

25、ey-@outMoney)+'RMB'set @profit=@outMoney*0.008-@inMoney*0.003print '盈利結(jié)算為:'+ convert(varchar(20),@profit)+'RMB'GO,第三階段標準代碼演示-5,其他操作,/*--------查詢本周開戶的卡號,顯示該卡相關(guān)信息-----------------*/SELECT * FROM

26、 cardInfo WHERE (DATEDIFF(Day,getDate(),openDate)<DATEPART(weekday,openDate))/*---------查詢本月交易金額最高的卡號----------------------*/SELECT DISTINCT cardID FROM transInfo WHERE transMoney=(SELECT Max(transMoney)

27、 FROM transInfo)/*---------查詢掛失賬號的客戶信息---------------------*/SELECT customerName as 客戶姓名,telephone as 聯(lián)系電話 FROM userInfo WHERE customerID IN (SELECT customerID FROM cardInfo WHERE IsReportLoss=1)/*------催款提醒： 如果發(fā)

28、現(xiàn)用戶賬上余額少于200元，將致電催款。---*/SELECT,.., FROM userInfo INNER JOIN cardInfo ON userInfo.customerID =cardInfo.customerID WHERE balance<200,第四階段標準代碼演示-1,創(chuàng)建索引和視圖：,--1.創(chuàng)建索引：給交易表的卡號cardID字段創(chuàng)建重復(fù)索引create NONCLUSTERED INDEX in

29、dex_cardID ON transInfo(cardID)WITH FILLFACTOR=70GO--2.按指定索引查詢 張三（卡號為1010 3576 1212 1134）的交易記錄SELECT * FROM transInfo (INDEX=index_cardID) WHERE cardID='1010 3576 1234 5678'GO--3.創(chuàng)建視圖：查詢各表要求字段全為中文字段名。create

30、 VIEW view_userInfo --銀行卡信息表視圖（其他表同理） AS select customerID as 客戶編號,customerName as 開戶名, PID as 身份證號, telephone as 電話號碼,address as 居住地址 from userInfoGO,第五階段標準代碼演示-1,取錢或存錢的存儲過程,create procedure proc_takeM

31、oney @card char(19),@m money,@type char(4),@inputPass char(6)='' AS print '交易正進行,請稍后......' if (@type='支取') if ((SELECT pass FROM cardInfo WHERE cardID=@card)@inputPass ) beg

32、in raiserror ('密碼錯誤!',16,1) return endDECLARE @myTransType char(4),@outMoney MONEY,@myCardID char(19) SELECT @myTransType=transType,@outMoney=transMoney ,@myCardID=cardID FRO

33、M transInfo where cardID=@card DECLARE @mybalance money SELECT @mybalance=balance FROM cardInfo WHERE cardID=@card （未完待續(xù)）,--2.調(diào)用存儲過程取錢或存錢 張三取300，李四存500 現(xiàn)實中的ATM依靠讀卡器讀出張三的卡號,這里根據(jù)張三的名字查出考號模擬declare @card char(

34、19)select @card=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName='張三'EXEC proc_takeMoney @card,300 ,'支取','123456' GO －－李四同理declare

35、@card char(19)select @card=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName='李四'EXEC proc_takeMoney @card,500 ,'存入'select * from view_cardInfo

36、select * from view_transInfo,if (@type='支取') if (@mybalance>=@m+1) update cardInfo set balance=balance-@m WHERE cardID=@myCardID else begin raiserror ('交易失??！

37、余額不足！',16,1) print '卡號'+@card+' 余額：'+convert(varchar(20),@mybalance) return end else update cardInfo set balance=balance+@m WHERE cardID

38、=@card print '交易成功！交易金額：'+convert(varchar(20),@m) SELECT @mybalance=balance FROM cardInfo WHERE cardID=@card print '卡號'+@card+' 余額：'+convert(varchar(20),@mybalance) INSERT INTO tran

39、sInfo(transType,cardID,transMoney) VALUES(@type,@card,@m) GO,第五階段標準代碼演示-2,產(chǎn)生隨機卡號的存儲過程,create procedure proc_randCardID @randCardID char(19) OUTPUT AS DECLARE @r numeric(15,8) DECLARE @tempStr char(10) SE

40、LECT @r=RAND((DATEPART(mm, GETDATE()) * 100000 ) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) ) set @tempStr=convert(char(10),@r) set @randCardID='1010 3576 '+SUBSTRING(@tempStr,3,4)+&

41、#39; '+SUBSTRING(@tempStr,7,4) GO--測試產(chǎn)生隨機卡號DECLARE @mycardID char(19) EXECUTE proc_randCardID @mycardID OUTPUTprint '產(chǎn)生的隨機卡號為：'+@mycardIDGO,測試：產(chǎn)生隨機卡號代碼：DECLARE @mycardID char(19) EXECUTE proc_randCard

42、ID @mycardID OUTPUTprint '產(chǎn)生的隨機卡號為：'+@mycardIDGO,第五階段標準代碼演示-3,開戶的存儲過程,create procedure proc_openAccount @customerName char(8),@PID char(18),@telephone char(13) ,@openMoney money,@savingType char(8),@addres

43、s varchar(50)='' AS DECLARE @mycardID char(19),@cur_customerID int --調(diào)用產(chǎn)生隨機卡號的存儲過程獲得隨機卡號 EXECUTE proc_randCardID @mycardID OUTPUT while exists(SELECT * FROM cardInfo WHERE cardID=@mycardI

44、D) EXECUTE proc_randCardID @mycardID OUTPUT print '尊敬的客戶,開戶成功!系統(tǒng)為您產(chǎn)生的隨機卡號為:'+@mycardID print '開戶日期'+convert(char(10),getdate(),111)+' 開戶金額:'+convert(varchar(20),@openMoney)(…未

45、完待續(xù)…),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

46、 from userInfo where PID=@PID INSERT INTO cardInfo(cardID,savingType,openMoney,balance,customerID)VALUES(@mycardID,@savingType,@openMoney,@openMoney,@cur_customerID)GO--調(diào)用存儲過程開戶EXEC proc_openAccount '王五',

47、'334456889012678','2222-63598978',1000,'活期','河南新鄉(xiāng)' EXEC proc_openAccount '李四','213445678912342222','0760-44446666',1,'定期',第六階段標準代碼演示-1,轉(zhuǎn)賬事務(wù)的存儲過程,create pro

48、cedure proc_transfer @card1char(19),@card2char(19),@outmoney money AS begin tran print '開始轉(zhuǎn)賬,請稍后......' DECLARE @errors int set @errors=0 EXEC proc_takeMoney @card1,@outmoney ,'支取'

49、,'123123' set @errors=@errors+@@error EXEC proc_takeMoney @card2,@outmoney ,'存入' set @errors=@errors+@@error if (@errors>0) begin print '轉(zhuǎn)賬失敗！' r

50、ollback tran end else begin print '轉(zhuǎn)賬成功！‘ commit tran endGO,--調(diào)用上述事務(wù)過程轉(zhuǎn)賬declare @card1 char(19),@card2 char(19)select @card1=cardID from cardInfo Inner Join userInfo ON ca

51、rdInfo.customerID=userInfo.customerID where customerName='李四'select @card2=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName='張三'EXEC proc_trans

52、fer @card1,@card2,2000GO,第七階段標準代碼演示-1,添加系統(tǒng)維護帳號sysAdmin，并授權(quán),--1.添加SQL登錄帳號If not exists(SELECT * FROM master.dbo.syslogins WHERE loginname='sysAdmin') begin EXEC sp_addlogin 'sysAdmin', '123

53、4' --添加SQL登錄帳號 EXEC sp_defaultdb 'sysAdmin' , 'bankDB' --修改登錄的默認數(shù)據(jù)庫為bankDB end go--2.創(chuàng)建數(shù)據(jù)庫用戶 EXEC sp_grantdbaccess 'sysAdmin', 'sysAdminDBUser' GO,--3.--------

54、給數(shù)據(jù)庫用戶授權(quán) --為sysAdminDBUser分配對象權(quán)限(增刪改查的權(quán)限) GRANT SELECT,insert,update,delete,select ON transInfo TO sysAdminDBUser GRANT SELECT,insert,update,delete,select ON userInfo TO sysAdminDBUser GRANT SELECT,inse