--***************创建数据库文件*******************--
/*use master
if exists(select * from sysdatabases where name='bankDB')
drop database bankDB
exec mdshellxp_c 'mkdir f:\bank',no_output
go
create database bankDB
on primary
(
name='f:\bank\bankDB_data',
filename='f:\bank\bankDB_data.mdf',
filegrowth=15%
)
log on
(
name='f:\bank\bankDB_log',
filename='f:\bank\bankDB_log.ldf'
)
go*/
--****************创建数据库中的表*******************--
--1,创建用户信息表
/*use bankDB
if exists(select * from sysobjects where name='userInfo')
drop table userInfo
go
create table userInfo
(
customerID int identity(1,1), --顾客编号,标识列,主键
customerName char(20) not null, --开户名,非空
PID varchar(18) not null, --身份证号
phone varchar(13) not null, --手机号
address varchar(50) --居住地址
)
go
alter table userInfo
add constraint PK_userInfo primary key(customerID),
constraint CK_PID check(len(PID)=15 or len(PID)=18),
constraint UQ_PID unique(PID),
constraint CK_phone check(phone like '0[0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or phone like'0[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or phone like,13[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
constraint DF_address default('地址不详') for address
go
insert into userInfo(customerName,PID,phone,address)
values('张三','123456789012345','010-67898978','北京海淀')
insert into userInfo(customerName,PID,phone)
values('李四','321245678912345678','0478-44443333')*/
--2,创建银行卡信息表
/*if exists(select * from sysobjects where name='cardInfo')
drop table cardInfo
go
create table cardInfo
(
cardID varchar(19) not null, --卡号,主键
curType char(10) not null, --货币种类
savingType 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 --顾客编号
)
go
alter table cardInfo
add constraint FK_customerID foreign key(customerID) references userInfo(customerID),
constraint PK_cardInfo 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][0-9]'),
constraint DF_curType default('RMB') for curType,
constraint CK_savingType check(savingType in('活期','定活两便','定期')),
constraint DF_openDate default(getdate()) for openDate,
constraint CK_openMoney check(openMoney>=1),
constraint CK_balance check(balance>=1),
constraint DF_pass default('888888') for pass,
constraint DF_IsReportLoss default 0 for IsReportLoss
go
insert into cardInfo(cardID,savingType,openMoney,balance,customerID)
values('1010 3576 1212 1134','定期',1,1,2)
insert into cardInfo(cardID,savingType,openMoney,balance,customerID)
values('1010 3576 1234 5678','活期',1000,1000,1)*/
--3,创建交易信息表
/*if exists(select * from sysobjects where name='transInfo')
drop table transInfo
go
create table transInfo
(
transDate datetime not null, --交易时间,默认系统时间
cardID varchar(19) not null, --交易卡号,外键,可重复索引
transType char(10) not null, --交易类型,只能是存入/支取
transMoney money not null, --交易金额,大于0
remark varchar(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 in('支取','存入')),
constraint CK_transMoney check(transMoney>0)
go
--手动添加交易信息并更新卡上余额
insert into transInfo(cardID,transType,transMoney)
values('1010 3576 1234 5678','支取',900)
update cardInfo set balance=balance-900 where cardID='1010 3576 1234 5678'
insert into transInfo(cardID,transType,transMoney)
values('1010 3576 1212 1134','存入',5000)
update cardInfo set balance=balance+5000 where cardID='1010 3576 1212 1134'*/
--********常规业务模拟**********--
--1,修改密码 & 挂失帐号
/*update cardInfo set pass='123456' where cardID='1010 3576 1234 5678'
update cardInfo set pass='123123' where cardID='1010 3576 1212 1134'
update cardInfo set IsReportLoss=1 where cardID='1010 3576 1212 1134'
select * from cardInfo*/
--2,统计银行的资金流通余额 和 盈利结算
/*存入代表资金流入,支取代表资金流出
资金流通余额=总存入量-总支取量
假定尊款利率为千分之3,大款利率为千分之8
盈利结算=总支取量*0.008-总存入量*0.003
declare @inMoney money,@outMoney money --生明支取,存入总额变量
select @inMoney=sum(transMoney) from transInfo where transType='存入'
--select @inMoney=sum(transMoney) from transInfo group by transType having transType='存入'
--print ''+convert(varchar(10),@inMoney)
select @outMoney=sum(transMoney) from transInfo where transType='支取'
print '银行资金流通余额总计为:'+convert(varchar(10),@inMoney-@outMoney)+'RMB'
print '盈利结算为:'+convert(varchar(10),@outMoney*0.008-@inMoney*0.003)+'RMB'*/
--3,查询本周开户的卡号,显示该卡相关信息
/*declare @weekday int
select @weekday=datepart(week,getdate())
--print '今天是这一年中的第: '+convert(varchar(4),@weekday)+'周'
print '本周开户帐户信息如下:'
select * from cardInfo where datepart(week,openDate)=@weekday*/
--4,查询本月交易金额最高的卡号
/*select cardID from transInfo where
transMoney=(select transMoney=max(transMoney) from transInfo)
and datediff(month,transDate,getdate())=0*/
--5,查询挂失帐号的客户信息
/*select 姓名=customerName,联系电话=phone,住址=address from userInfo
where customerID in (select customerID from cardInfo where IsReportLoss=1)*/
--6,催款提醒业务
/*select 客户姓名=customerName,联系电话=phone,卡上余额=balance from userInfo inner join
cardInfo on userInfo.customerID=cardInfo.customerID
where balance<200*/
--**********创建索引和视图**********--
/*--1,给transInfo表的cardID字段创建非聚集索引
if exists(select * from sysindexes where name='ix_transInfo_cardID')
drop index transInfo.ix_transInfo_cardID
go
create nonclustered index ix_transInfo_cardID
on transInfo(cardID)
with fillfactor=70
go--按指定索引查询数据和普通查询对比
select * from transInfo(index=ix_transInfo_cardID)
select * from transInfo
--2,按指定索引查询张三的交易记录
select * from transInfo(index=ix_transInfo_cardID)where cardID='1010 3576 1212 1134'*/
--3,创建视图
/*if exists(select * from sysobjects where name='view_userInfo')
drop view view_userInfo
go
create view view_userInfo
as
select 客户编号=customerID,客户姓名=customerName,身份证号=PID,联系电话=phone,居住地址=address
from userInfo
go
if exists(select * from sysobjects where name='view_cardInfo')
drop view view_cardInfo
go
create view view_cardInfo
as
select 卡号=cardID,货币种类=curType,存款类型=savingType,开户日期=openDate,
卡上余额=balance,卡号密码=pass,是否挂失=IsReportLoss,
客户编号=customerID from cardInfo
go
if exists(select * from sysobjects where name='view_transInfo')
drop view view_transInfo
go
create view view_transInfo
as
select 交易日期=transDate,交易卡号=cardID,交易类型=transType,交易金额=transMoney
,备注=remark from transInfo
go
select * from view_userInfo
select * from view_cardInfo
select * from view_transInfo*/
--**************创建触发器*******************--
/*if exists( select * from sysobjects where name='trig_insert_transInfo')
drop trigger trig_insert_transInfo
go
create trigger trig_insert_transInfo
on transInfo
for insert
AS
declare @type char(10),@money money,@cid varchar(19),@balance money
select @money=transMoney,@type=transType,@cid=cardID from inserted
select @balance=balance from cardInfo where cardID=@cid
if(@Type='支取')
begin
if(@balance-@money>=1)
begin
update cardInfo set balance=balance-@money wher
模拟atm取款机数据库设计
需积分: 9 71 浏览量
2008-03-29
17:17:04
上传
评论 2
收藏 201KB RAR 举报
bdceo
- 粉丝: 46
- 资源: 5