附录一 系统资源
1. 数据库创建代码
create database department on
( name = department_dat1,
filename = 'e:\\department_dat1.mdf', size = 2MB, maxsize = 5, filegrowth = 20% ),
( name = department_dat2,
filename = 'e:\\department_dat2.ndf', size = 2MB, maxsize = 5, filegrowth = 20%)
- 16 -
log on
( name =department_log,
filename = 'e:\\department_log.ldf', size = 1MB, maxsize = 2MB, filegrowth = 20% ) go
use department go
create table information ( ) go
use department go
create table operation (
num char(11) not null constraint pk_xh primary key, name char(10) not null,
idnum char(20) not null constraint un_xh unique, hometown varchar(50) not null, address varchar(50) not null, tel char(7) not null, base_pay char(20),
constraint ck_len check (len(idnum) = 15 or len(idnum) = 17)
onum char(5) not null constraint pk_kcdh primary key, oname char(20) not null, worth char(20) not null
- 17 -
) go
create unique index inx_course_kcm on dbo.operation(onum) go
use department go
create table wages (
num char(11) not null, onum char(5) not null, amount char(10) not null,
CONSTRAINT pk_id PRIMARY KEY(num,onum), CONSTRAINT fk_xh FOREIGN KEY (num) REFERENCES dbo.information (num) on delete cascade on update cascade, CONSTRAINT fk_nh FOREIGN KEY (onum) REFERENCES dbo.operation (onum) on delete cascade on update cascade )
use department go
create table users (
username char(11) not null CONSTRAINT pk_od PRIMARY KEY, code char(11) not null, limit char(10) not null,
- 18 -
CONSTRAINT fk_ah FOREIGN KEY (username) REFERENCES dbo.information (num) on delete cascade on update cascade ) go
use department go
insert into information
values('9607039','张昆','530120169021101','安徽','江岸小区45幢3单元408','5033226','1800') insert into information
values('9907002','李明','530120170060701','云南','江岸小区50幢2单元409','5033228','2000') insert into information
values('9801055','王风','530120171072501','湖北','白马小区15幢3单元101','4133224','2200') insert into information
values('9902006','黄光美','530120170122901','湖南','金星小区8幢3单元208','3133218','2500') insert into information
values('9704001','钱丽','530120168121101','云南','静园小区12幢3单元109','2133227','2600') insert into information
values('9603001','王迷','530120174050101','黑龙江','江岸小区40幢1单元612','5033219','1300') insert into information
values('9606005','赵人','530120175040702','江苏','江岸小区30幢4单元222','5033123','1000') insert into information
- 19 -
values('9803011','李游','530120173021201','四川','白马小区25幢3单元410','4133124','1200') insert into information
values('9908088','孙客如','530120172092801','贵州','金星小区55幢3单元214','3133177','1100') insert into information
values('9608066','于云','530120174092201','云南','阳光小区55幢5单元112','3133222','1500') insert into information
values('9805026','李春','530120174110901','云南','阳光小区2幢1单元444','3133789','3000') insert into information
values('9702033','高城','530120170080401','江苏','阳光小区8幢2单元808','3148212','2800')
use department go
insert into operation values('20511','业务一',4) insert into operation values('10101','业务二',2) insert into operation values('20801','业务三',3) insert into operation values('10218','业务四',4) insert into operation values('11001','业务五',6) insert into operation
- 20 -