db.sql

机房运营维护系统 java+oracle9i 使用struts 分用户权限 完成用户对机房信息的增删改查等功能

--部门表dept

drop table dept;

create table dept(
deptid number,
dname varchar2(20) not null,
adminid varchar2(20),
tel varchar(20),
fax varchar(20),
memo varchar(40),
constraint pk_dept primary key(deptid)
);

drop sequence dept_seq;

create sequence dept_seq start with 100 increment by 1;



--员工表emp

drop table emp;

create table emp(
empid varchar2(20),
ename varchar2(10) not null,
password varchar(20) not null,
deptid number,
emptype varchar(10),
tel varchar(20),
email varchar(40),
handset varchar(20),
fax varchar(20),
memo varchar(4000),
constraint pk_emp primary key(empid),
constraint fk_emp_deptid foreign key(deptid) references dept(deptid)
);

insert into emp (empid,ename,password,emptype)values('yan','闫海摈','yan','admin');

alter table dept add constraint fk_dept_empid foreign key(adminid) references emp(empid);

--机房信息表room

drop table room;

create table room(
roomid number,
rname varchar2(20)not null,
adminid varchar2(20),
deptid number,
address varchar2(50),
memo varchar2(400),
constraint pk_room primary key(roomid),
constraint fk_room_adminid foreign key(adminid) references emp(empid),
constraint fk_room_deptid foreign key(deptid) references dept(deptid)
);

drop sequence room_seq;

create sequence room_seq start with 10 increment by 1;


--角色表role

drop table role;

create table role(
roleid number,
rolename varchar2(20),
moduleid number,
constraint pk_role primary key(roleid)
);
alter table role add  constraint uni_role unique(rolename);
alter table role modify  rolename not null;
alter table role modify  moduleid not null;
drop sequence role_seq;

create sequence role_seq start with 1000 increment by 1;

insert into role values(role_seq.nextval,'权限管理员',5);
--中间表 emp_role

drop table emp_role;

create table emp_role(
empid varchar2(20),
roleid number
);

alter table emp_role add constraint pk_emp_role primary key(empid,roleid);
alter table emp_role add constraint fk_emp_role_empid foreign key (empid) references emp(empid);
alter table emp_role add constraint fk_emp_role_roleid foreign key (roleid) references role(roleid);
insert into emp_role values('yan',1000);

--模块表module

drop table module;

create table module(
moduleid number,
name varchar(20),
constraint pk_module primary key(moduleid)
);
insert into module (moduleid,name) values(1,'部门管理');
insert into module (moduleid,name) values(2,'机房管理');
insert into module (moduleid,name) values(3,'日志管理');
insert into module (moduleid,name) values(4,'人员管理');
insert into module (moduleid,name) values(5,'权限管理');
commit;

alter table role add constraint fk_role_moduleid foreign key (moduleid) references module(moduleid);


--权限表popedom

drop table popedom;

create table popedom(
popedomid number,
moduleid number,
popedom varchar(20),
constraint pk_popedom primary key(popedomid)
);
alter table popedom add constraint fk_popedom_moduleid foreign key(moduleid) references module(moduleid);

insert into popedom (popedomid,moduleid,popedom)values(10001,1,'新增部门');
insert into popedom (popedomid,moduleid,popedom)values(10002,1,'更改部门');
insert into popedom (popedomid,moduleid,popedom)values(10003,1,'删除部门');
insert into popedom (popedomid,moduleid,popedom)values(10004,1,'查询部门');

insert into popedom (popedomid,moduleid,popedom)values(10005,2,'新增机房');
insert into popedom (popedomid,moduleid,popedom)values(10006,2,'更改机房');
insert into popedom (popedomid,moduleid,popedom)values(10007,2,'删除机房');
insert into popedom (popedomid,moduleid,popedom)values(10008,2,'查询机房');

insert into popedom (popedomid,moduleid,popedom)values(10009,3,'查询日志');

insert into popedom (popedomid,moduleid,popedom)values(10010,4,'新增人员');
insert into popedom (popedomid,moduleid,popedom)values(10011,4,'更改人员');
insert into popedom (popedomid,moduleid,popedom)values(10012,4,'删除人员');
insert into popedom (popedomid,moduleid,popedom)values(10013,4,'查询人员');

insert into popedom (popedomid,moduleid,popedom)values(10014,5,'新增角色');
insert into popedom (popedomid,moduleid,popedom)values(10015,5,'配置角色');
insert into popedom (popedomid,moduleid,popedom)values(10016,5,'删除角色');
insert into popedom (popedomid,moduleid,popedom)values(10017,5,'配置用户权限');
commit;

--中间表role_popedom
drop table role_popedom;

create table role_popedom(
roleid number,
popedomid number
);
alter table role_popedom add constraint pk_role_popedom primary key(roleid,popedomid);
alter table role_popedom add constraint fk_role_popedom_roleid foreign key(roleid) references role(roleid);
alter table role_popedom add constraint fk_role_popedom_popedomid foreign key(popedomid) references popedom(popedomid);
insert into role_popedom values(1000,10014);
insert into role_popedom values(1000,10015);
insert into role_popedom values(1000,10016);
insert into role_popedom values(1000,10017);
 

--日志表log
drop table log;

create table log(
logid number,
empid varchar2(20) not null,
work number,
workdate date,
constraint pk_log primary key(logid)
);

--alter table log add constraint fk_log_empid foreign key(empid) references emp(empid);
alter table log add constraint fk_log_popedomid foreign key(work) references popedom(popedomid);
drop sequence log_seq;

create sequence log_seq start with 1 increment by 1;