Go
4.creat table gys (gysbh char(8) not null constraint PK_dno primary key, gysmc char(8) not null, dz varchar(20) not null, yzbm char(8) not null, dhhm varchar(15) not null, sh varchar(3) not null, yhzh varchar(20) not null, khyh char(8) not null, lxr char(8) not null, beizhu text null ) Go
5.creat table hy (hybh char(8) not null constraint PK_eno primary key, hyname char(6) not null,
sex char(2) check CK_hy sex in (‘男’，‘女’) not null, sfzh varchar(20) not null, xfzje money not null, jf int not null )
6. creat table yg (ygbh char(8) not null constraint PK_fno primary key, ygname char(8) not null,
sex char(2) check CK_yg sex in (‘男’,‘女’) not null, zw char(8) not null, kl varchar(20) not null, qxjb char(4) not null,
10
sfzh varchar(18) not null, ssbmbh char(8) not null,
constraint FK_ano foreign key reference bm(bmbh) ) Go
7.creat table rk (rkbh char(8) not null
constraint PK_gno primary key, xsjg money not null, rkrq datetime not null, spbh char(8) not null
constraint FK_bno foreign key references sp(spbh), ywybh char(8) not null
constraint FK_cno foreign key references yg(ygbh), jldw char(2) not null, rkjg money not null, gysbh char(8) not null
constraint FK_dno foreign key references gsy(gysbh), zje money not null, sl int not null) go
8.creat table zhuxx (xsrp datetime not null, zje money not null,
sfxj char(2) check CK_zhuxx_sfxj in(‘是’,‘否’) not null, sfhy char(2) check CK_zhuxx_sfhy in (‘是’,‘否’) hybh char(8) not null
constraint FK_eno foreign key references hy(hybh), syybh char(8) not null
constraint FK_fno foreign key references yg(ygbh)) go
11
9. creat table zixx (spbh char(8) not null
constraint FK_gno foreign key references sp(spbh), sl int not null, dj money not null, zkbl char(10) not null, je money not null) go
10. creat table kc (kcxxbh char(8) )not null constraint PK_pno primary key, spbh char(8) not null
constraint FK_ino foreign key references sp(spbh), kcl int not null) go
4.2 数据库扩展功能实现
1.CREAT TRIGGER triger_s1 ON rk after insert AS update kc
set kcl =kcl +(select sl from inserted) where spbh=(select spbh from inserted)
2.CREAT TRIGGER triger_kc ON zixx after delete AS update kc
set kcl =kcl -(select sl from deleted) where spbh=(select spbh from deleted)
12