atm提款机.txt

QL SERVER 应用开发 银行ATM(取款机）系统 数据库设计 我在学校的 SQL项目,模拟 银行ATM(取款机）系统

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_transInfo_cardID) where cardID='1010 3576 1234 5678'

GO