oa_database.sql

采用jsp+servlet技术

--用户表
create table staff(id integer primary key,
                   name varchar2(30),
                   email varchar2(30),
                   loginid varchar2(30),
                   password varchar2(30)
                  );
create sequence seq_staff minvalue 1 start with 1;
--邮件表
create table Mail(
                  id integer primary key,
--                docid integer,
                  sheme varchar2(100),
                  sizes integer,
                  sendTime timestamp,
                  isRead smallint,
                  importance integer,
                  content clob
);
create sequence seq_mail minvalue 1 start with 1;
--邮件附件表
create table MailDocument(id integer primary key,
                          mailid integer,               --FK 关联Mail表中的id
                          docName varchar2(50),
                          realPath varchar2(200)
                          );
alter table MailDocument add constraint FK_MailDoc_Mail foreign key(mailid)
            references Mail(id);
create sequence seq_maildoc minvalue 1 start with 1;

--字典表,表示邮件状态
create table MailStatus(id integer primary key,
                        name varchar2(50)
);
create sequence seq_Mail_status minvalue 1 start with 1;

--关系表,表示用户(发送者和接收者)和邮件之间的关系
create table MailRealation(senderid     integer not null, 
                           receiverid   integer not null,
                           ownerid      integer not null,
                           mailid       integer not null,
                           mail_sta     integer not null,       --表示邮件状态
                           receive_sta  integer not null,       --表示收件人状态
                           id           integer primary key     
                          ); 
create sequence seq_Mail_realation minvalue 1 start with 1;