ATM取款机数据库设计

/*ATM取款机系统数据库设计*//*某银行拟开发一套ATM取款机系统，实现如下功能：1、开户（到银行填写开户申请单，卡号自动生成）2、取钱3、存钱4、查询余额5、转账（如使用一卡通代缴手机话费、个人股票交易等）现要求对“ATM柜员机系统”进行数据库的设计并实现，数据库保存在D:\bank目录下，文件增长率为15% 。*/--创建数据库if exists(select * from sysdatabases where name='BankDB')drop database BankDBcreate database BankDBon(name='BankDBmdf',filename='d:\SQL2008Workspace\BankDB.mdf',size=5mb,maxsize=15mb,filegrowth=15%)log on(name='BankDBldf',filename='d:\SQL2008Workspace\BankDB.ldf',size=5mb,maxsize=15mb,filegrowth=15%)go--创建用户信息表use BankDBif exists(select * from sysobjects where name ='userInfo')drop table userInfocreate table userInfo(customerID int not null  identity(1,1),--客户编号customerName nvarchar(8) not null,--开户名pID varchar(20) not null, --身份证号telephone varchar(13) not null,--电话号码uaddress nvarchar(50)--家庭住址)go--添加约束alter table userInfo add constraint PK_customer primary key (customerID),--主键约束    constraint UQ_pID unique (pID),--唯一约束    constraint CK_piD check (len(pID)=15 or len(pID)=18),--检查约束    constraint CK_telephone check (telephone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or telephone like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')--检查约束go--创建银行卡信息表use BankDBif exists(select * from sysobjects where name='cardInfo')drop table cardInfocreate table cardInfo    (cardID varchar(18) not null,--银行卡号curType varchar(3) not null,--货币种类savingType nvarchar(4) not null,--存款类型openDate datetime not null,--开户日期openMoney money not null,--开户金额balance money not null,--余额pass char(6) not null,--密码isReportPass nvarchar(2) not null,--是否挂失customerID int not null--顾客编号)go--添加约束alter table cardInfoadd constraint PK_cardID primary key (cardID),--主键约束constraint CK_cardID check (cardID like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9]'),--检查约束constraint DF_curType default ('RMB') for curType,--默认约束constraint CK_savingType check (savingType = '活期' or savingType = '定期' or savingType = '定活两便'),--检查约束constraint DF_opendate default (getdate()) for opendate,--默认约束constraint CK_openMoney check (openMoney>=1),--检查约束constraint CK_balance check (balance>=1),--检查约束constraint CK_pass check (pass like '[0-9][0-9][0-9][0-9][0-9][0-9]'),--检查约束constraint DF_pass default ('888888') for pass,--默认约束constraint CK_isReportPass check (isReportPass = '是' or isReportPass = '否'),constraint DF_isReportPass default ('否') for isReportPass,constraint FK_customerID foreign key (customerID) references userInfo(customerID)--外键约束go--创建交易信息表use BankDBif exists(select * from sysobjects where name='transInfo')drop table transInfocreate table transInfo(transDate datetime not null,--交易日期cardID varchar(18) not null,--卡号transType nvarchar(4) not null,--交易类型transMoney money not null,--交易金额remark nvarchar(50)--备注) go--添加约束alter table transinfoadd constraint DF_transDate default (getdate()) for transDate,--默认约束    constraint FK_cardID foreign key (cardID) references cardInfo(cardID),--外键约束    constraint CK_transType check (transType = '存入' or transType = '支取'),--检查约束    constraint CK_transMoney check (transMoney>0 )go--给卫庄和盖聂开户insert into userInfo(customerName,pID,telephone,uaddress) values('卫庄','123456789123456789','13921210101','韩国新郑')insert into userInfo(customerName,pID,telephone,uaddress) values('盖聂','123456780123456780','0712-81210101','秦国咸阳')insert into cardInfo (cardID,savingType,openMoney,balance,pass,customerID) values ('1010 3576 6221 543','活期',1000,1000,'123456',1)insert into cardInfo (cardID,savingType,openMoney,balance,customerID) values ('1010 3576 3890 871','定期',999,999,2)select * from userInfoselect * from cardInfogo--卫庄取钱500,盖聂存钱1001insert into transInfo (cardID,transType,transMoney) values ('1010 3576 6221 543','支取',500)update cardInfo set balance=balance-500 where cardID='1010 3576 6221 543'insert into transInfo (cardID,transType,transMoney) values ('1010 3576 3890 871','存入',1001)update cardInfo set balance=balance+1001 where cardID='1010 3576 3890 871'select * from transInfo select * from cardInfo go--盖聂修改密码和挂失账号update cardInfo set pass=654321 where cardID='1010 3576 3890 871'update cardInfo set isReportPass = '是' where cardID = '1010 3576 3890 871'select * from cardInfogo--给cardID创建非聚集索引create nonclustered index index_cardID on transInfo(cardID) with fillfactor=60go--根据索引进行查询select * from transInfo with (index=index_cardID) where cardID = '1010 3576 3890 871'go--创建视图:查询各表要求字段为中文字段名create view view_userasselect  customerID '客户编号',customerName as '开户名','身份证号'=pID,telephone '电话号码',uaddress '居住地址'from userInfogoselect * from view_user/*---创建存储过程(获取卡号)---*/if exists (select * from sysobjects where name='pro_getcID')drop procedure pro_getcIDcreate procedure pro_getcID @randID char(18) outputasdeclare @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)--substring是返回指定位置的字符串,从@tempStr字符串第4个字符开始,返回长度为4的字符set @randID='1010 3576'+' '+substring(@tempStr,4,4)+' '+substring(@tempStr,8,3)go--测试产生随机卡号declare @cardID varchar(18)exec pro_getcID @cardID outputprint '产生的随机卡号为:'+@cardIDgo/*---创建存储过程(开户)---*/if exists (select * from sysobjects where name = 'pro_openAccount')drop procedure pro_openAccountcreate procedure Pro_openAccount@customerName nvarchar(8),@pID varchar(18),@telephone varchar(13),@openMoney money,@savingType varchar(4),@uaddress nvarchar(50)=' 'asdeclare @cardID char(18)declare @customerID intexec pro_getcID @cardID outputwhile exists(select * from cardInfo where cardID =@cardID)beginexec pro_getcID @cardID outputendprint '尊敬的客户,开户成功!系统为您产生的随机卡号为:'+@cardIDprint '开户日期'+convert(char(10),getdate(),111)+'开户金额'+convert(varchar(20),@openMoney)if exists(select * from userInfo where @pID=pID)beginupdate userInfo set telephone=@telephone,uaddress=@uaddressselect @customerID=customerID from userInfo where @pID=pIDinsert into cardInfo (cardID,savingType,openMoney,balance,customerID) values (@cardID,@savingType,@openMoney,@openMoney,@customerID)endelsebegininsert into userInfo (customerName,pID,telephone,uaddress) values (@customerName,@pID,@telephone,@uaddress)select @customerID=customerID from userInfo where @pID=pIDinsert into cardInfo (cardID,savingType,openMoney,balance,customerID) values (@cardID,@savingType,@openMoney,@openMoney,@customerID)endgo--调用存储过程开户set nocount on--不显示"受影响的行数信息"set nocount off--显示"受影响的行数信息"EXEC pro_openAccount '王五','334456889012678','2222-63598978',1000,'活期','河南新乡' EXEC pro_openAccount '李四','213445678912342222','0760-44446666',1,'定期',go/*---取钱或存钱的存储过程---*/if exists (select * from sysobjects where name = 'pro_playMoney')drop procedure pro_playMoneycreate procedure pro_playMoney--输入参数@cardID varchar(18),@transType nvarchar(4),@transMoney money,@pass char(6)=''asprint '交易正在进行中......'declare @balance moneyselect @balance=balance from  cardInfo where cardID=@cardIDif (@transType='支取')beginif (@pass=(select pass from cardInfo where cardID=@cardID))beginif(@balance>=@transmoney+1)beginupdate cardInfo set balance=balance-@transmoney where cardID=@cardIDinsert into transInfo (cardID,transType,transMoney) values (@cardID,@transType,@transMoney)print '交易成功! 交易金额:'+convert(varchar(10),@transMoney)select @balance=balance from  cardInfo where cardID=@cardIDprint '卡号:'+@cardID+'余额:'+convert(varchar(10),@balance)endelsebeginraiserror('交易失败,余额不足!',16,1)print '卡号:'+@cardID+'余额:'+convert(varchar(10),@balance)returnendendelsebeginraiserror('密码错误',16,1)returnendendif(@transType='存入')beginupdate cardInfo set balance=balance+@transmoney where cardID=@cardIDinsert into transInfo (cardID,transType,transMoney) values (@cardID,@transType,@transMoney)print '交易成功! 交易金额:'+convert(varchar(10),@transMoney)select @balance=balance from  cardInfo where cardID=@cardIDprint '卡号:'+@cardID+'余额:'+convert(varchar(10),@balance)endgo--卫庄存1000,盖聂取600exec pro_playMoney '1010 3576 6221 543','存入',1000exec pro_playMoney '1010 3576 3890 871','支取',600,'654321'go--统计银行的流通余额和盈利结算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)+'RMB'set @profit=@inMoney*0.008-@outMoney*0.003print '盈利结算为:'+convert(varchar(20),@profit)+'RMB'go--查询本周开户的卡号,显示该卡的相关信息select * from cardInfo where datediff(day,getdate(),openDate)<datepart(weekday,getdate())--查询本月交易金额最高的卡号select cardID from transinfo where transMoney=(select max(transMoney) from transInfo)--查询挂失账号的客户信息select * from userInfo where customerID in (select customerID from cardInfo where isReportPass='是')--提醒信息:当发现客户账户余额少于200,致电提醒select customerName,cardID,balance,telephone from userInfo u,cardInfo c where u.customerID=c.customerIDand balance<200/*---转账的存储过程---*/if exists(select * from sysobjects where name='pro_exMoney')drop procedure pro_exMoneycreate procedure pro_exMoney@cardID1 char(18),@cardID2 char(18),@transMoney moneyasprint '开始转账,请稍后......'begin transactiondeclare @error intset @error=0exec pro_playMoney @cardID1,'支取',@transMoney,123456set @error=@error+@@ERRORexec pro_playMoney @cardID2,'存入',@transMoneyset @error=@error+@@ERROR if(@error<>0)beginprint '转账失败!'rollback transactionendelsebeginprint '转账成功!'commit transactionendgo--卫庄给盖聂转账2000exec pro_exMoney '1010 3576 6221 543','1010 3576 3890 871',2000--账户安全--1.添加SQL登录账号if exists(select * from syslogins where loginname='haha')exec sp_droplogin 'haha'--删除SQL登录账号beginexec sp_addlogin 'haha','1234'--添加SQL登录账号exec sp_defaultdb 'haha','BankDB'--修改登录的默认数据库时BankDBendgo--2.创建数据库用户exec sp_grantdbaccess 'haha','dbuser'go--3.授予数据库使用权限,为dbuser分配对象权限(增删改查的权限)grant insert,delete,update,select on userInfo to dbusergrant insert,delete,update,select on cardInfo to dbusergrant insert,delete,update,select on transInfo to dbusergo