数据环境.txt

PB实现的交通运输管理 系统 1．运输企业系统管理人员通过该系统来管理用户资料

if exists(select name from sysobjects
     	where name='userinfo'and type='u')
drop table userinfo
go
create table userinfo
(
userid 		char(20) primary key,
userpwd 	char(10) not null,
usertypeid	int not null
)

insert into userinfo
values('001','001',1);
insert into userinfo
values('002','002',2);

if exists(select name from sysobjects
     	where name='usertypeinfo'and type='u')
drop table usertypeinfo
go
create table usertypeinfo
(
usertypeid 	int primary key,
usertype	char(20) not null
)

insert into usertypeinfo
values(1,'系统管理员');
insert into usertypeinfo
values(2,'普通用户');

if exists(select name from sysobjects
     	where name='userright'and type='u')
drop table userright
go
create table userright
(
usertypeid 	int ,
resid 		char(10) not null,
useright bit not null
constraint PK_ur primary key(usertypeid,resid)
)

insert into userright
values(1,'001','1');
insert into userright
values(2,'001','1');
insert into userright
values(1,'002','1');
insert into userright
values(2,'002','1');
insert into userright
values(1,'003','1');
insert into userright
values(2,'003','1');
insert into userright
values(1,'004','1');
insert into userright
values(2,'004','1');
insert into userright
values(1,'005','1');
insert into userright
values(2,'005','0');

if exists(select name from sysobjects
     	where name='resourses'and type='u')
drop table resourses
go
create table resourses
(
resid 	char(10) primary key,
resname	char(20) not null,
)

insert into resourses
values('001','tab_1.tabpage_1');
insert into resourses
values('002','tab_1.tabpage_2');
insert into resourses
values('003','tab_1.tabpage_3');
insert into resourses
values('004','tab_1.tabpage_4');
insert into resourses
values('005','tab_1.tabpage_5');


if exists(select name from sysobjects
     	where name='driver'and type='u')
drop table driver
go
create table driver
(
sjid		char(20) primary key,
sjname		char(20) not null,	
sjsex		char(4)  not null,
sjbirthday	smalldatetime not null,
sjaddress	char(50) not null,
sjfirstdata	smalldatetime,
sjtype		char(10) not null,
sjbegin_date	smalldatetime,
sjend_date	smalldatetime
)

insert into driver
values('00001','张三','男','1980-02-22','西安市','1998-01-24','A2','1999-04-25','2006-04-25');

insert into driver
values('00002','李四','男','1981-3-19','瑜林市','2000-05-17','A1','2002-2-16','2010-2-16');


if exists(select name from sysobjects
     	where name='vehicle'and type='u')
drop table vehicle
go
create table vehicle
(
clid		char(10) primary key,
clkind		char(20) not null,
clowner		char(20) not null,
clcode1		char(10) not null,
clcode2		char(10),
clcode3		char(20),
cltotal		numeric	,
clpassenger	numeric	,
clregist_date	smalldatetime,
clcertifi_date	smalldatetime,
clcolor		char(4),
cldriver	char(10)
)

insert into vehicle
values('陕AT1584','出租车','王二麻子','fdg4567','ete4567','AFS455','1536','4','1998-4-15','1998-1-24','绿色','00001');

insert into vehicle
values('鲁GE8888','轿车','赵二宝','ert4564','fdf41657','GT768','1758','2','2001-2-16','2001-2-25','黑色','00002');

if exists(select name from sysobjects
     	where name='statistic'and type='u')
drop table statistic
go
create table statistic
(
yyid		char(10)not null,
yydriver	char(20)not null,
yybegin_date	smalldatetime not null,
yyend_date	smalldatetime not null,
yydistance	numeric,
yywaste	        numeric,
constraint PK_sta primary key(yyid,yydriver),
foreign key(yyid) references vehicle(clid),
foreign key(yydriver) references driver(sjid)
)

insert into statistic
values('陕AT1584','00001','1998-4-15','1998-1-24','500','5.6');

insert into statistic
values('鲁GE8888','00002','2001-2-16','2001-2-25','650','5.5');

if exists(select name from sysobjects
     	where name='accident'and type='u')
drop table accident
go
create table accident
(
sgid		char(10),
sgvehicle       char(10),
sgdate		smalldatetime not null,
sgplace		text not null,
sgreason	text,
sgdriver	char(20),
sgopp_dapt	char(20),
sgopp_id	char(10),
sgmode	text,
sgvalue	numeric,
constraint PK_acc primary key(sgid),
foreign key(sgvehicle) references vehicle(clid),
foreign key(sgdriver) references driver(sjid)
)

insert into accident
values('001','陕AT1584','2004-4-15','陕西西宝高速公路','疲劳驾驶','00001','二院','陕AS4578','罚款','300');

insert into accident
values('002','鲁GE8888','2001-2-16','西安市朱雀大街','酒后驾驶','00002','西安交通大学保卫处','陕AQ1256','扣驾照三个月','5000');