SQL小型数据库系统设计

--------------创建数据库--------------------------

create database bankDB

on primary

(

name = 'bankDB',

filename = 'e:\bank\bankDB.mdf',

size = 3MB,

maxsize = 100MB,

filegrowth = 15%

)

log on

(

name = 'bankDB_log',

filename = 'e:\bank\bankDB_log.ldf',

size = 2MB,

filegrowth = 1MB

)

go

--------------------创建数据表---------------------

use bankDB

gocreate table userInfo

(

customerID int identity(1,1) not null,

customerName varchar(10) not null,

PID char(18) not null,

telphone char(13) not null,

adress varchar(100)

)alter table userInfo

add constraint PK_customerID primary key(customerID)

alter table userInfo

add constraint CK_PID check(PID like '[1-9][0-9][0-9][0-9][0-9][0-9][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

PID like '[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]X' or

PID like '[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

alter table userInfo

add constraint CK_telphone check(telphone like '13[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or telphone 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]')

alter table userInfo

add constraint DF_telphone default('地址不详') for adress

alter table userInfo

add constraint UQ_PID unique(PID)create table cardInfo

(

cardID char(19) not null,

curType varchar(5) not null,

savingType varchar(6) not null,

openDate datetime not null,

openMoney money not null,

balance money not null,

pass char(6) not null,

IsReportLoss char(2) not null,

customerID int not null

)alter table cardInfo

add constraint FK_customerID foreign key(customerID) references userInfo(customerID)

alter table cardInfo

add constraint PK_cardID primary key(cardID)

alter table cardInfo

add constraint DF_openDate default(getdate()) for openDate

alter table cardInfo

add constraint DF_pass default('888888') for pass

alter table cardInfo

add constraint DF_curType default('RMB') for curType

alter table cardInfo

add constraint CK_openMoney check(openMoney >1)

alter table cardInfo

add constraint CK_balance check(balance >1)

alter table cardInfo

add constraint CK_pass check(pass like '[0-9][0-9][0-9][0-9][0-9][0-9]')

alter table cardInfo

add constraint DF_cardID default('1010 3576 ' + convert(varchar(4),convert(int,rand()*10000)) + ' ' +convert(varchar(4),convert(int,rand()*10000))) for cardID

alter table cardInfo

add constraint DF_IsReportLoss default('否') for IsReportLoss

alter table cardInfo

add constraint CK_IsReportLoss check(IsReportLoss like '是' or IsReportLoss like '否')

alter table cardInfo

add constraint CK_savingType check(savingType like '定期' or savingType like '活期' or savingType like '定活期')create table transInfo

(

transDate datetime not null,

cardID char(19) not null,

transType char(4) not null,

transMoney money not null,

remark varchar(100)

)alter table transInfo

add constraint DF_transDate default(getdate()) for transDate

alter table transInfo

add constraint FK_cardID foreign key(cardID) references cardInfo(cardID)

alter table transInfo

add constraint CK_stransType check(transType like '存入' or transType like '支取')

alter table transInfo

add constraint CK_transMoney check(transMoney >0)go

--------------------创建开户用户卡信息视图---------

create view v_cus

as

select customerName, cardID,curType,savingType,telphone,adress

from cardInfo inner join userInfo on cardInfo.customerID = userInfo.customerID

where datediff(wk,openDate,getdate()) = 0-----------------------修改密码--------------------

create proc changePWD

@cardID char(19),

@newpwd char(6),

@oldpwd char(6) = '888888'

as

declare @temppwd int

if((select count(*) from cardInfo where cardID = @cardID) = 0)

begin

print '您输入的卡号不存在！'

return

end

select @temppwd = pass from cardInfo where cardID = @cardID

if(@temppwd = @oldpwd)

begin

update cardInfo set pass = @newpwd where cardID = @cardID

if(@@error = 0)

print '密码修改成功！'

else

print '密码修改失败！'

end

else

print '您输入的原始密码不对，不能修改密码！'----------------------yhk挂失-------------------

create proc ReportLoss

@cardID char(19),

@PID char(18)

as

if((select count(*) from cardInfo where cardID = @cardID) = 0)

begin

print '您输入的卡号不存在！'

return

end

if((select IsReportLoss from cardInfo where cardID = @cardID) = '是')

begin

print '该卡已挂失！'

return

end

if((select PID from userInfo where customerID = (select customerID from cardInfo where cardID = @cardID)) = @PID)

begin

update cardInfo set IsReportLoss = '是' where cardID = @cardID

if(@@error = 0)

print '挂失成功！'

else

print '挂失失败！'

end

else

begin

print '您输入的身份z错误！不能挂失！'

return

end----------------------取消yhk挂失-------------------

create proc CancelReportLoss

@cardID char(19),

@PID char(18)

as

if((select count(*) from cardInfo where cardID = @cardID) = 0)

begin

print '您输入的卡号不存在！'

return

end

if((select IsReportLoss from cardInfo where cardID = @cardID) = '否')

begin

print '该卡没有挂失！不能进行此项 *** 作！'

return

end

if((select PID from userInfo where customerID = (select customerID from cardInfo where cardID = @cardID)) = @PID)

begin

update cardInfo set IsReportLoss = '否' where cardID = @cardID

if(@@error = 0)

print '取消挂失成功！'

else

print '取消挂失失败！'

end

else

begin

print '您输入的身份z错误！不能取消挂失！'

return

end----------------------统计银行的资金流通余额和盈利结算--------------------create proc StatMoney

as

select ((select sum(transMoney) as 资金流通余额 from transInfo where transType like '存入') - (select sum(transMoney) as 资金流通余额 from transInfo where transType like '支取'))

as 资金流通余额 select ((select sum(transMoney) as 资金流通余额 from transInfo where transType like '存入')*0.008

- (select sum(transMoney) as 资金流通余额 from transInfo where transType like '支取')*0.003)

as 盈利结算 ---------------------查询本周开户的卡号，显示该卡的信息-------------------

create proc SearchNewCus

as

print '本周开户卡号信息'

select * from v_cus -----------------------查询本月交易金额最高的卡号-------------------------

create proc SearchHigh

as

select top 1 cardID, sum(transMoney) as 交易金额 from transInfo

group by cardID------------------------------查询挂失帐号的客户信息----------------------

create proc SearchLossCus

as

print '挂失帐号客户信息'

select * from userInfo where customerID = (select customerID from cardInfo where IsReportLoss = '是')--------------------------催款提醒业务------------------------------------

create proc Awoke

as

select telphone, balance, customerName, cardID

from cardInfo inner join userInfo on cardInfo.customerID = userInfo.customerID

where balance <200-------------------------银行开户-----------------------------------------

create proc OpenUser

@curType varchar(5),

@savingType varchar(6),

@openMoney money,

@PID char(18),

@telphone char(13),

@customerName varchar(10)

as

begin tran OpenU

declare @tag int

declare @tempID varchar(10)

set @tag = 0 insert into userInfo (customerName,PID,telphone) values (@customerName, @PID, @telphone)

set @tag = @tag + @@error

if(@tag = 0)

set @tempID = (select customerID from userInfo where PID = @PID)

insert into cardInfo (curType, savingType, openMoney, balance, customerID) values (@curType, @savingType, @openMoney, @openMoney, @tempID)

set @tag = @tag + @@error

if(@tag = 0)

begin

print '开户成功！'

commit tran

end

else

begin

print '开户失败！'

rollback tran

end-----------------------------客户存款-------------------------------------

create proc SaveMoney

@cardID char(19),

@transMoney money,

@transType char(4) = '存入'

as

if((select IsReportLoss from cardInfo where cardID = @cardID) = '是')

begin

print '该卡已挂失!无法对其进行 *** 作!'

return

end

begin tran Saving

declare @tag int

set @tag = 0 insert into transInfo (cardID, transType, transMoney) values (@cardID, @transType, @transMoney)

set @tag = @tag + @@error

update cardInfo set balance = balance + @transMoney where cardID = @cardID

set @tag = @tag + @@error

if(@tag = 0)

begin

print '存款成功！'

commit tran

end

else

begin

print '存款失败!'

rollback tran

end ------------------------------客户取款------------------------------------

create proc DrawMoney

@cardID char(19),

@transMoney money,

@transType char(4) = '支取'

as

if((select IsReportLoss from cardInfo where cardID = @cardID) = '是')

begin

print '该卡已挂失!无法对其进行 *** 作!'

return

end

begin tran Drawing

declare @tag int

set @tag = 0 insert into transInfo (cardID, transType, transMoney) values (@cardID, @transType, @transMoney)

set @tag = @tag + @@error

update cardInfo set balance = balance - @transMoney where cardID = @cardID

set @tag = @tag + @@error

if(@tag = 0)

begin

print '取款成功！'

commit tran

end

else

begin

print '取款失败!'

rollback tran

end----------------------------转帐------------------------------------------

create proc Transfer

@cardID_out char(19),

@cardID_in char(19),

@tranMoney money

as

if((select count(*) from cardInfo where cardID = @cardID_out or cardID = @cardID_in) <>2)

begin

print '请确认两张卡是否都存在！'

return

end

if((select IsReportLoss from cardInfo where cardID = @cardID_out) = '是')

begin

print @cardID_out + '该卡已挂失!无法对其进行 *** 作!'

return

end

if((select IsReportLoss from cardInfo where cardID = @cardID_in) = '是')

begin

print @cardID_in + '该卡已挂失!无法对其进行 *** 作!'

return

end

begin tran TransMoney

declare @tag int

set @tag = 0 update cardInfo set balance = balance - @tranMoney where cardID = @cardID_out

set @tag = @tag + @@error

update cardInfo set balance = balance + @tranMoney where cardID = @cardID_in

set @tag = @tag + @@error

if(@tag = 0)

begin

print '转帐成功！'

commit tran

end

else

begin

print '转帐失败!'

rollback tran

end

微型计算机中使用的数据库管理系统，属于计算机应用中的（ C、信息管理）。

微型计算机系统从全局到局部存在三个层次：微型计算机系统、微型计算机、微处理器（CPU）。单纯的微处理器和单纯的微型计算机都不能独立工作，只有微型计算机系统才是完整的信息处理系统，才具有实用意义。

一个完整的微型计算机系统包括硬件系统和软件系统两大部分。硬件系统由运算器、控制器、存储器（ 含内存、外存和缓存）、各种输入输出设备组成，采用“ 指令驱动”方式工作。

扩展资料：

微型计算机的特点是体积小、灵活性大、价格便宜、使用方便。自1981年美国IBM公司推出第一代微型计算机IBM-PC以来，微型机以其执行结果精确、处理速度快捷、性价比高、轻便小巧等特点迅速进入社会各个领域，且技术不断更新、产品快速换代。

从单纯的计算工具发展成为能够处理数字、符号、文字、语言、图形、图像、音频、视频等多种信息的强大多媒体工具。如今的微型机产品无论从运算速度、多媒体功能、软硬件支持还是易用性等方面都比早期产品有了很大飞跃。

微型计算机中使用的数据库属于“数据处理方面的计算机应用”。

数据处理，数据处理是系统工程和自动控制的基本环节。数据处理贯穿于社会生产和社会生活的各个领域。数据处理技术的发展及其应用的广度和深度，极大地影响了人类社会发展的进程。

数据处理的基本目的是从大量的、可能是杂乱无章的、难以理解的数据中抽取并推导出对于某些特定的人们来说是有价值、有意义的数据。

数据处理离不开软件的支持，数据处理软件包括：用以书写处理程序的各种程序设计语言及其编译程序，管理数据的文件系统和数据库系统，以及各种数据处理方法的应用软件包。为了保证数据安全可靠，还有一整套数据安全保密的技术。

---