SQL SERVER 应用开发 银行ATM(取款机）系统 数据库设计
作者: j2eedev 发表日期: 2007-04-22 16:07 文章属性: 原创 复制链接
我在学校的 SQL项目,模拟 银行ATM(取款机）系统 数据库设计 2007-03-30 13:42 类别：默认 /*◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
◆◆ ◆◆
◆◆ SQL SERVER 应用开发 阶段项目 ◆◆
◆◆ ================================ ◆◆
◆◆ 银行ATM(取款机）系统 数据库设计 ◆◆
◆◆ ◆◆
◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆*/
use master
GO
exec xp_cmdshell 'mkdir d:ank',no_output
GO
if exists (select * from sysdatabases where name='bankDB')
drop database bankDB
GO
create database bankDB
on
(
name='bank_data',
filename='d:ankank_data.mdf',
size=10mb,
maxsize=100mb,
filegrowth=15%
)
GO
USE bankDB
GO
--------------------------------------------------------用户信息表userInfo----------------------------------------
if exists(select * from SysObjects where name='userInfo')
drop table userInfo
GO
create table userInfo
(
customerID int not null identity(1,1)---顾客编号
constraint PK_USERINFO_CUSTOMERID
primary key,
customerName char(8) not null,--开户名
PID varchar(18) not null
constraint UN_USERINFO_PID--身份证号
unique
constraint CH_USERINFO_PID
check(len(PID)=15 OR LEN(PID)=18),
telephone varchar(13) not null--电话
constraint CH_USERINFO_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]'or telephone
like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
address varchar(20)--地址
)
GO
----------------------------------------------银行卡信息表,cardInfo--------------------------------------------
if exists(select * from sysobjects where name='cardInfo')
drop table cardInfo
GO
create table cardInfo
(
cardID char(19) not null--银行卡卡号
constraint PK_cardInfo_cardID
Primary key
constraint CH_cardInfo_cardID
check(cardID like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'),
curType varchar(10) not null --货币种类
constraint DF_cardInfo_curType
default 'RMB',--缺省为RMB
savingType varchar(10) ----存款类型
constraint CH_cardInfo_savingType
check (savingType in('活期','定活两便','定期')),
openDate datetime not null -----开户日期
constraint DF_cardInfo_openDate
default (getdate()),
openMoney money not null--------开户金额
constraint CH_cardInfo_openMoney
check(openMoney>=1),---不低于1元
balance money not null----------余额
constraint CH_cardInfo_balance
check(balance>=1),---不低于1元
pass char(6) not null----------密码
constraint CH_cardInfo_pass
check(len(pass)=6)
constraint DF_cardInfo_pass
default ('888888'),----密码默认6个8
IsReportLoss bit not null-----------是否挂失,1为挂失，0为没有挂失
constraint DF_cardInfo_IsReportLoss
default 0,
customerID int not null--顾客编号，引用userInfo的customerID
constraint FK_cardInfo_customerID
foreign key references userInfo(customerID)
)
GO
-----------------------------------交易信息表------------------------------------------------------
if exists (select * from sysobjects where name='transInfo')
drop table transInfo
GO
create table transInfo
(
transDate datetime not null-----------------交易日期
constraint DF_transInfo_transDate
default getdate(),
cardID char(19) not null--------------------银行卡号
constraint FK_transInfo_cardID
foreign key references cardInfo(cardID),
transType char(4) not null------------------交易类型
constraint CH_transInfo_trasnType
check(transType in('存入','支取')),
transMoney money not null-------------------交易金额
constraint CH_transInfo_transMoney
check(transMoney>0),
remark text---------------------------------备 注
)
------------------------------------------------------------------------------------------
GO
--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
--插入测试数据
insert into userInfo(customerName,PID,telephone,address)
values('张三','123456789012345','0100-67898978','北京海淀')
insert into userInfo(customerName,PID,telephone,address)
values('李四','321245678912345678','0478-44443333',NULL)
select * from userInfo
insert into cardInfo(cardID,curType,savingType,openMoney,balance,pass,IsReportLoss,customerID)
values('1010 3576 1212 1134','RMB','定期',1,1,'888888',0,2)
insert into cardInfo(cardID,curType,savingType,openMoney,balance,pass,IsReportLoss,customerID)
values('1010 3576 1234 5678','RMB','活期',1000,1000,'888888',0,1)
select * from cardInfo
insert into transInfo(transType,cardID,transMoney,remark)
values('支取','1010 3576 1234 5678',900,null)
update cardInfo set balance=balance-900 where cardID='1010 3576 1234 5678'
insert into transInfo(transType,cardID,transMoney,remark)
values('存入','1010 3576 1212 1134',5000,null)
update cardInfo set balance=balance+5000 where cardID='1010 3576 1212 1134'
GO
select * from cardInfo
select * from transInfo
GO
--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
---更改密码
update cardInfo set pass='123456' where cardID='1010 3576 1234 5678'
update cardInfo set pass='123123' where cardID='1010 3576 1212 1134'
GO
--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
-->>挂失帐号
--挂失李四的帐号
update cardInfo set IsReportLoss=1 where cardID='1010 3576 1212 1134'
GO
--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
-->>统计银行的资金流通余额和盈利结算
/*-----------------------------------
统计说明：
存入代表资金流入，支取代表资金流出，则有：
资金流通余额=总存入量-总支出量
假定存款利率总计为千分之三，货款利率为千分之八，则有：
盈利结算=总支取量*0.008-总存入量*0.003
-----------------------------------*/
declare @inMoney money ----总存入
declare @outMoney money ----总支取
declare @sumBalance money ---流通余额
declare @sumProfit money ---盈利结算总和
select @inMoney=sum(transMoney) from transInfo where transType='存入'
select @outMoney=sum(transMoney) from transInfo where transType='支取'
set @sumBalance=@inMoney-@outMoney
print '银行流通余额总计为: '+convert(varchar(30),@sumBalance)+' RMB'
set @sumProfit=@outMoney*0.008-@inMoney*0.003--计算公式
print '盈利结算为: '+convert(varchar(30),@sumProfit)+' RMB'
GO
--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
--->>>查询本周开户的卡号,显示该卡的信息
select * from cardInfo where DATEDIFF ( wk ,openDate ,getdate())=0
--<<查询本月交易额最高的卡号
select distinct 交易额最高的卡号=cardID from transInfo where transMoney=(select max(transMoney) from transInfo) and DATEDIFF ( mm ,transDate ,getdate())=0
GO
--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
--==>>查询挂失帐号的客户信息
select 客户姓名=u.customerName,身份证号=u.PID,地址=u.address,联系电话=u.telephone,帐上余额=c.balance from userInfo as u inner join cardInfo as c on u.customerID in(
select c.customerID from cardInfo where c.IsReportLoss=1)
GO
--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
--月末自动催款
select * from userInfo where customerID=(select customerID from cardInfo where balance<200)
--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
-->>>创建索引和视图
----给交易表的卡cardID字段创建重复索引,以便加速查询
if exists (select * from sysindexes where name='index_transInfo_cardID')
drop index transInfo.index_transInfo_cardID
GO
create nonclustered index index_transInfo_cardID
on transInfo(cardID)
with fillfactor=70
GO
--->>按指定索引查询张三的交易记录
select * from transInfo(index=index_tra
