ATM取款机数据库设计

原创 2016年05月31日 20:23:39
/*ATM取款机系统数据库设计*/


/*
某银行拟开发一套ATM取款机系统,实现如下功能:
1、开户(到银行填写开户申请单,卡号自动生成)
2、取钱
3、存钱
4、查询余额
5、转账(如使用一卡通代缴手机话费、个人股票交易等)
现要求对“ATM柜员机系统”进行数据库的设计并实现,数据库保存在D:\bank目录下,文件增长率为15% 。

*/


--创建数据库
if exists(select * from sysdatabases where name='BankDB')
drop database BankDB
create database BankDB
on(
	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 BankDB
if exists(select * from sysobjects where name ='userInfo')
drop table userInfo
create 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 BankDB
if exists(select * from sysobjects where name='cardInfo')
drop table cardInfo
create 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 cardInfo
add 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 BankDB
if exists(select * from sysobjects where name='transInfo')
drop table transInfo
create 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 transinfo
add 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 userInfo
select * from cardInfo
go
--卫庄取钱500,盖聂存钱1001
insert 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 cardInfo
go

--给cardID创建非聚集索引
create nonclustered index index_cardID on transInfo(cardID) with fillfactor=60
go
--根据索引进行查询
select * from transInfo with (index=index_cardID) where cardID = '1010 3576 3890 871'
go
--创建视图:查询各表要求字段为中文字段名
create view view_user
as
	select  customerID '客户编号',customerName as '开户名','身份证号'=pID,
	telephone '电话号码',uaddress '居住地址'
	from userInfo
go
select * from view_user


/*---创建存储过程(获取卡号)---*/
if exists (select * from sysobjects where name='pro_getcID')
drop procedure pro_getcID

create procedure pro_getcID @randID char(18) output
as
	declare @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 output
print '产生的随机卡号为:'+@cardID
go

	



/*---创建存储过程(开户)---*/
if exists (select * from sysobjects where name = 'pro_openAccount')
drop procedure pro_openAccount

create procedure Pro_openAccount
@customerName nvarchar(8),@pID varchar(18),@telephone varchar(13),
@openMoney money,@savingType varchar(4),@uaddress nvarchar(50)=' '
as
	declare @cardID char(18)
	declare @customerID int
	exec pro_getcID @cardID output
	while exists(select * from cardInfo where cardID =@cardID)
		begin
			exec pro_getcID @cardID output
		end
	print '尊敬的客户,开户成功!系统为您产生的随机卡号为:'+@cardID
	print '开户日期'+convert(char(10),getdate(),111)+'开户金额'+convert(varchar(20),@openMoney)	
	if exists(select * from userInfo where @pID=pID)
		begin
			update userInfo set telephone=@telephone,uaddress=@uaddress
			select @customerID=customerID from userInfo where @pID=pID
			insert into cardInfo (cardID,savingType,openMoney,balance,customerID) values (@cardID,@savingType,@openMoney,@openMoney,@customerID)
		end
	else
		begin
			insert into userInfo (customerName,pID,telephone,uaddress) values (@customerName,@pID,@telephone,@uaddress)
			select @customerID=customerID from userInfo where @pID=pID
			insert into cardInfo (cardID,savingType,openMoney,balance,customerID) values (@cardID,@savingType,@openMoney,@openMoney,@customerID)
		end
go
--调用存储过程开户
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_playMoney

create procedure pro_playMoney
--输入参数
@cardID varchar(18),@transType nvarchar(4),@transMoney money,@pass char(6)=''
as
	print '交易正在进行中......'
	declare @balance money
	select @balance=balance from  cardInfo where cardID=@cardID
	if (@transType='支取')
		begin
			if (@pass=(select pass from cardInfo where cardID=@cardID))
				begin
					if(@balance>=@transmoney+1)
						begin
							update cardInfo set balance=balance-@transmoney where cardID=@cardID
							insert into transInfo (cardID,transType,transMoney) values (@cardID,@transType,@transMoney)
							print '交易成功! 交易金额:'+convert(varchar(10),@transMoney)
							select @balance=balance from  cardInfo where cardID=@cardID
							print '卡号:'+@cardID+'余额:'+convert(varchar(10),@balance)
						end
					else
						begin
							raiserror('交易失败,余额不足!',16,1)
							print '卡号:'+@cardID+'余额:'+convert(varchar(10),@balance)
							return
						end
				end
			else
				begin	
					raiserror('密码错误',16,1)
					return
				end
		end
	if(@transType='存入')
		begin
			update cardInfo set balance=balance+@transmoney where cardID=@cardID
			insert into transInfo (cardID,transType,transMoney) values (@cardID,@transType,@transMoney)
			print '交易成功! 交易金额:'+convert(varchar(10),@transMoney)
			select @balance=balance from  cardInfo where cardID=@cardID
			print '卡号:'+@cardID+'余额:'+convert(varchar(10),@balance)
		end
go
	
--卫庄存1000,盖聂取600
exec pro_playMoney '1010 3576 6221 543','存入',1000
exec pro_playMoney '1010 3576 3890 871','支取',600,'654321'
go


--统计银行的流通余额和盈利结算
declare @inMoney money
declare @outMoney money
declare @profit money
select @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.003
print '盈利结算为:'+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.customerID
and balance<200






/*---转账的存储过程---*/
if exists(select * from sysobjects where name='pro_exMoney')
drop procedure pro_exMoney

create procedure pro_exMoney
@cardID1 char(18),@cardID2 char(18),@transMoney money
as
	print '开始转账,请稍后......'
	begin transaction
	declare @error int
	set @error=0
	exec pro_playMoney @cardID1,'支取',@transMoney,123456
	set @error=@error+@@ERROR
	exec pro_playMoney @cardID2,'存入',@transMoney
	set @error=@error+@@ERROR 
	if(@error<>0)
		begin
			print '转账失败!'
			rollback transaction
		end
	else
		begin
			print '转账成功!'
			commit transaction
		end
go
--卫庄给盖聂转账2000
exec 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登录账号
begin
	exec sp_addlogin 'haha','1234'--添加SQL登录账号
	exec sp_defaultdb 'haha','BankDB'--修改登录的默认数据库时BankDB
end
go	
--2.创建数据库用户
exec sp_grantdbaccess 'haha','dbuser'
go
--3.授予数据库使用权限,为dbuser分配对象权限(增删改查的权限)
grant insert,delete,update,select on userInfo to dbuser
grant insert,delete,update,select on cardInfo to dbuser
grant insert,delete,update,select on transInfo to dbuser
go

相关文章推荐

Web Service开发

Web Service开发   前言 这一章咱们一起学习一下Web Service开发,首先Web Service是Web服务器上的一些组建,客户端应用程序可通过Web发...

uva 11039

题意:给出n层去盖楼,下层高度一定比上层高度大,且相邻两层颜色不同,然后给出了n个数,正数和负数区分颜色,绝对值代表高度,问最高有几层。 题解:直接按数的绝对值排序,然后按顺序取正负不同的数,最多能...

ATM自动取款机后台数据库设计1.ppt

  • 2009年09月16日 18:39
  • 1.17MB
  • 下载

模拟atm取款机数据库设计

  • 2008年03月29日 17:17
  • 201KB
  • 下载

黑马程序员之项目开发学习笔记:ATM取款机系统数据库设计

某银行拟开发一套ATM取款机系统,实现如下功能: 1. 开户(到银行填写开户申请单, 卡号自动生成); 2. 取钱 3. 存钱 4. 查询余额 5. 转账(如使用一卡通代缴手机话费,个人股票交易等) ...

C#与.NET ATM取款机系统数据库设计

  • 2008年11月24日 21:51
  • 1.47MB
  • 下载

ATM取款机系统数据库设计

  • 2009年05月09日 15:50
  • 1.47MB
  • 下载

ATM 取款机

#include #include #include #include #include #include "ConsoleOut.h"#pragma comment(lib,"ConsoleOut....

ATM取款机系统数据库设计

  • 2009年09月25日 16:21
  • 1.47MB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ATM取款机数据库设计
举报原因:
原因补充:

(最多只允许输入30个字)