《数据库技术与开发》工程实训指导书(7)

《数据库技术与开发》工程实训指导书

银行卡业务编号客户编号银行卡号密码货币类型开户日期开户金额是否挂失卡内余额...intintchar(19)char(6)char(5)datetimemoneychar(10)moneyFK_BANKCARD_BBT-BC_BANKBUSI业务类型业务编号int业务名称char(20)业务描述varchar(100)FK_BANKCARD_BC-BC_BANKCUSTFK_BANKDEAL_BC-BD_BANKCARD业务编号客户编号银行卡号交易编号交易日期交易金额交易类型交易备注...银行交易信息intintchar(19)intdatetimemoneychar(10)varchar(100)客户信息客户编号客户姓名身份证号客户电话客户住址...intchar(20)char(18)char(20)varchar(100) 图13．银行存取款系统数据物理模型PDM

3、实训三：创建数据库

使用T-SQL语句完成数据库、数据表和各种约束及触发器的创建，并保存为item2.sql文件。

按下述推荐步骤，在4学时内完成下述实训内容： (1) 创建数据库（1学时）

使用Create DataBase语句创建“ATM存取款机系统”数据库BankDB，数据文件和日志文件保存在指定目录下（建议建立一个文件夹，用于存放该实训项目的所有相关T-SQL源文件），文件增长率为15%。

参考代码如下所示： --创建BankDB数据库，数据库文件和日志文件均保存在 --文件夹G:\\2014年工程案例项目\\银行ATM存取款机系统下 --文件增长率均为%，数据文件起始大小为MB，日志文件起始大小为MB create database BankDB on primary ( name=N'BankDB', filename=N'G:\\2014年工程案例项目\\银行ATM存取款机系统\\BankDB.mdf', size=5MB, filegrowth=15% ) log on ( name=N'BankDB_log', filename=N'G:\\2014年工程案例项目\\银行ATM存取款机系统\\BankDB_log.ldf', size=2MB, filegrowth=15%

31

《数据库技术与开发》工程实训指导书

) (2) 创建各个数据表及相关的约束（2学时）

根据实训1设计出的“银行ATM存取款机系统”PDM模型的数据表结构，使用Create Table语句创建表结构。

根据银行业务，分析表中每个列相应的约束要求，为每个表添加各种约束。 要求创建表时要求检测是否存在表结构，如果存在，则先删除再创建。 建议选择2张表的T-SQL语句进行修改，先创建表结构里的各个字段，再用Alter Table语句为每个表添加各种约束。

参考代码如下所示： use BankDB; go --判断银行业务类型表是否存在,若存在则删除 if exists(select * from sysobjects where id=OBJECT_ID(N'BankBusinessType')) drop table BankBusinessType --创建银行业务类型表，包含银行业务类型编号BBTId，银行业务类型名称BBTName，银行业务描述BBTComment create table BankBusinessType ( BBTId int identity(1,1) primary key, BBTName char(20) not null, BBTComment varchar(100) ); go --判断银行卡客户是否存在,若存在则删除 if exists(select * from sysobjects where id=OBJECT_ID(N'BankCustomer')) drop table BankCustomer --创建银行客户信息表，包含客户编号BCID，客户姓名BCName，客户身份证BCICNo，客户联系电话BCTel、客户居住地址BCAddr create table BankCustomer ( BCId int identity(1,1) primary key, BCName char(20) not null, --定义身份证号前位必须是数字，后位可以是数字或者X。 BCICNo char(18) not null check(left(BCICNo,17) 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][0-9][0-9][0-9][0-9][0-9]' and (right(BCICNo,1) like '[0-9]' or right(BCICNo,1) like 'X')), --定义联系方式，必须是固定电话号码或者手机号，且前位必须是，第位必须是或或 BCTel varchar(20) not null check(BCTel like '[0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or BCTel like '[0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or BCTel like '1[358][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), --定义客户地址 BCAddr varchar(100) );

32

《数据库技术与开发》工程实训指导书

go --判断银行卡是否存在，若存在，则删除银行卡BankCard if exists(select * from sysobjects where id=object_id(N'BankCard')) drop table BankCard --建立银行卡信息 create table BankCard ( --卡号必须符合位数字构成,前位为3576,后位是随机产生且唯一,每位必须有一个空格 BCNo char(19) primary key check(BCNo like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'), --密码,开户默认为 BCPwd char(6) not null default('888888'), --币种,为RMB类型 BCCurrency char(5) not null default('RMB'), --存款类型 BCBBTId int not null, --开户日期,默认当日 BCOpenDate date not null default(getdate()), --开户金额,不得小于元 BCOpenAmount money not null check(BCOpenAmount>=1), --是否挂失,默认为否 BCRegLoss char(2) default('否'), --客户编号 BCBCId int not null, BCExistBalance money not null ); --判断交易信息BankDealInfo是否存在,若存在则删除 if exists(select * from sysobjects where id=OBJECT_ID(N'BankDealInfo')) drop table BankDealInfo --创建交易信息表 create table BankDealInfo ( --交易编号为自动增长列 BDNo int identity(1,1) primary key, --卡号 BDBCNo char(19) not null, --交易日期 BDDealDate Date not null default(getdate()), --交易金额 BDDealAcount money not null, --交易类型，有种存入和支取 BDDealType Char(10) not null check(BDDealType='存入' or BDDealType='支取'), --交易备注 BDDealComment varchar(100) ); (3) 添加外键约束和生成数据库关系图（1学时）

添加子表外键约束及生成数据库关系图 添加外键约束参考代码如下所示： --建立表之间的外键约束关系 alter table bankcard add constraint fk_BC_BBT foreign key(BCBBTId) references bankbusinesstype(bbtid);

33

《数据库技术与开发》工程实训指导书

alter table bankcard add constraint fk_BC_BC foreign key(BCBCId) references BankCustomer(BCId); alter table bankDealInfo add constraint fk_BDI_BC foreign key(BDBCNo) references BankCard(BCNo); 在SQL SERVER里自动生成数据库关系图，如下图所示：

图14．BankDb数据库关系图

4、实训四：创建触发器和插入测试数据

(1) 创建级联触发器（2学时） ? 创建Insert触发器

在交易信息表BankDealInfo中创建一个Insert触发器，当增加一条交易信息时，修改相应银行卡的存款余额。

建议使用游标，实现批量增加的级联更新。 --在交易信息表中插入一个触发器，使用游标当新增一个交易信息，修改银行卡的存款余额 if (object_id('tr_InsertdealInfo','tr') is not null) drop trigger tr_InsertdealInfo go create trigger tr_InsertdealInfo on bankdealinfo for insert as declare @type char(10),@sum money,@BDBCNo char(19); --创建一个游标，指向inserted表 declare cursor_BankDealinfo cursor for select BDDealType,BDDealAcount,BDBCNo from inserted --打开游标 open cursor_BankDealinfo --取游标中各个字段的值复制给各个变量 fetch next from cursor_BankDealinfo into @type,@sum,@BDBCNo while @@fetch_status=0 begin

34

《数据库技术与开发》工程实训指导书

--判断交易记录里是存入还是支取，及时更新银行卡表的存款余额 if(rtrim(ltrim(@type))='存入') update bankcard set BCExistBalance=BCExistBalance+@sum where BCNo=@BDBCNo; if(rtrim(ltrim(@type))='支取') update bankcard set BCExistBalance=BCExistBalance-@sum where BCNo=@BDBCNo; fetch next from cursor_BankDealinfo into @type,@sum,@BDBCNo end close cursor_BankDealinfo deallocate cursor_BankDealinfo go ? 创建Delete触发器

在交易信息表创建一个Delete触发器，当删除一条交易信息时，修改相应银行卡的存款余额。

建议使用游标，实现批量删除的级联更新。 --在交易信息表中插入一个触发器，使用游标,当删除一个交易信息，修改银行卡的存款余额 if (object_id('tr_DeldealInfo','tr') is not null) drop trigger tr_DeldealInfo go create trigger tr_DeldealInfo on bankdealinfo for delete as declare @type char(10),@sum money,@BDBCNo char(19); --创建一个游标，指向deleted表 declare cursor_BankDealinfo cursor for select BDDealType,BDDealAcount,BDBCNo from deleted --打开游标 open cursor_BankDealinfo --取游标中各个字段的值复制给各个变量 fetch next from cursor_BankDealinfo into @type,@sum,@BDBCNo while @@fetch_status=0 begin if(rtrim(ltrim(@type))='存入') update bankcard set BCExistBalance=BCExistBalance-@sum where BCNo=@BDBCNo; if(rtrim(ltrim(@type))='支取') update bankcard set BCExistBalance=BCExistBalance+@sum where BCNo=@BDBCNo; fetch next from cursor_BankDealinfo into @type,@sum,@BDBCNo end close cursor_BankDealinfo deallocate cursor_BankDealinfo go ? 创建Update触发器 参照上述代码，创建一个Update触发器，当更新交易信息表的记录时，更新银行卡表的相应卡号的余额。

35