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

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

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

3、,1), customerName CHAR(8) NOT NULL, …..),文件增長(zhǎng)率,,數(shù)據(jù)文件,日志文件,,自動(dòng)編號(hào)，從1開(kāi)始,,非空/必填,難點(diǎn)分析-2,設(shè)計(jì)ER圖、建庫(kù)、建表、加約束、建關(guān)系部分,建約束語(yǔ)句：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)系）,,唯一約束,默認(rèn)約束,難點(diǎn)分析-3,,建表時(shí):IDENTITY(1,1),,check約束：len( )函數(shù),,建表時(shí):NOT NULL,,check約束： like ‘[0-9][0-9]…’,設(shè)計(jì)ER圖、建庫(kù)、建

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

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

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

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

11、階段標(biāo)準(zhǔn)代碼演示-1,建庫(kù),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 (…..,檢驗(yàn)數(shù)據(jù)庫(kù)是否存在，如果為真，刪除此數(shù)據(jù)庫(kù),創(chuàng)建建庫(kù)bankDB,第二階段標(biāo)準(zhǔn)代碼演示-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,第三階段標(biāo)準(zhǔn)代碼演示-1,張三和李四開(kāi)戶：,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,第三階段標(biāo)準(zhǔn)代碼演示-2,張三的卡號(hào)取款900元，李四的卡號(hào)存款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,第三階段標(biāo)準(zhǔn)代碼演示-3,修改密碼和掛失賬號(hào),/*---------修改密碼-----*/--1.張三（卡號(hào)為1010 3576 1234 5678）修改銀行卡密碼為123456--2.李四（卡號(hào)為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/*--------- 李四的卡號(hào)掛失 ------

23、---*/update cardInfo set IsReportLoss=1 WHERE cardID='1010 3576 1212 1134' SELECT * FROM cardInfoGO,第三階段標(biāo)準(zhǔn)代碼演示-4,統(tǒng)計(jì)銀行的資金流通余額和盈利結(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 '銀行流通余額總計(jì)為:'+ convert(varchar(20), @inMon

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

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

27、 FROM transInfo)/*---------查詢掛失賬號(hào)的客戶信息---------------------*/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,第四階段標(biāo)準(zhǔn)代碼演示-1,創(chuàng)建索引和視圖：,--1.創(chuàng)建索引：給交易表的卡號(hào)cardID字段創(chuàng)建重復(fù)索引create NONCLUSTERED INDEX in

29、dex_cardID ON transInfo(cardID)WITH FILLFACTOR=70GO--2.按指定索引查詢 張三（卡號(hào)為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 客戶編號(hào),customerName as 開(kāi)戶名, PID as 身份證號(hào), telephone as 電話號(hào)碼,address as 居住地址 from userInfoGO,第五階段標(biāo)準(zhǔn)代碼演示-1,取錢(qián)或存錢(qián)的存儲(chǔ)過(guò)程,create procedure proc_takeM

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

32、in raiserror ('密碼錯(cuò)誤!',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)用存儲(chǔ)過(guò)程取錢(qián)或存錢(qián) 張三取300，李四存500 現(xiàn)實(shí)中的ATM依靠讀卡器讀出張三的卡號(hào),這里根據(jù)張三的名字查出考號(hào)模擬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 '卡號(hào)'+@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 '卡號(hào)'+@card+' 余額：'+convert(varchar(20),@mybalance) INSERT INTO tran

39、sInfo(transType,cardID,transMoney) VALUES(@type,@card,@m) GO,第五階段標(biāo)準(zhǔn)代碼演示-2,產(chǎn)生隨機(jī)卡號(hào)的存儲(chǔ)過(guò)程,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--測(cè)試產(chǎn)生隨機(jī)卡號(hào)DECLARE @mycardID char(19) EXECUTE proc_randCardID @mycardID OUTPUTprint '產(chǎn)生的隨機(jī)卡號(hào)為：'+@mycardIDGO,測(cè)試：產(chǎn)生隨機(jī)卡號(hào)代碼：DECLARE @mycardID char(19) EXECUTE proc_randCard

42、ID @mycardID OUTPUTprint '產(chǎn)生的隨機(jī)卡號(hào)為：'+@mycardIDGO,第五階段標(biāo)準(zhǔn)代碼演示-3,開(kāi)戶的存儲(chǔ)過(guò)程,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)生隨機(jī)卡號(hào)的存儲(chǔ)過(guò)程獲得隨機(jī)卡號(hào) EXECUTE proc_randCardID @mycardID OUTPUT while exists(SELECT * FROM cardInfo WHERE cardID=@mycardI

44、D) EXECUTE proc_randCardID @mycardID OUTPUT print '尊敬的客戶,開(kāi)戶成功!系統(tǒng)為您產(chǎn)生的隨機(jī)卡號(hào)為:'+@mycardID print '開(kāi)戶日期'+convert(char(10),getdate(),111)+' 開(kāi)戶金額:'+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)用存儲(chǔ)過(guò)程開(kāi)戶EXEC proc_openAccount '王五',

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

48、cedure proc_transfer @card1char(19),@card2char(19),@outmoney money AS begin tran print '開(kāi)始轉(zhuǎn)賬,請(qǐng)稍后......' 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ù)過(guò)程轉(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,第七階段標(biāo)準(zhǔn)代碼演示-1,添加系統(tǒng)維護(hù)帳號(hào)sysAdmin，并授權(quán),--1.添加SQL登錄帳號(hào)If not exists(SELECT * FROM master.dbo.syslogins WHERE loginname='sysAdmin') begin EXEC sp_addlogin 'sysAdmin', '123

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

54、給數(shù)據(jù)庫(kù)用戶授權(quán) --為sysAdminDBUser分配對(duì)象權(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