ssmis.sql

学生管理系统.学生管理系统 学生管理系统

DROP SEQUENCE seq_student_id;
DROP SEQUENCE seq_course_id;
DROP SEQUENCE seq_class_id;
DROP SEQUENCE seq_user_id;
DROP SEQUENCE seq_role_id;
DROP SEQUENCE seq_module_id;
DROP SEQUENCE seq_studentcourse_id;
DROP SEQUENCE seq_roleuser_id;
DROP SEQUENCE seq_rolemodule_id;

DROP TRIGGER tr_student_id;
DROP TRIGGER tr_course_id;
DROP TRIGGER tr_class_id;
DROP TRIGGER tr_user_id;
DROP TRIGGER tr_role_id;
DROP TRIGGER tr_module_id;
DROP TRIGGER tr_studentcourse_id;
DROP TRIGGER tr_roleuser_id;
DROP TRIGGER tr_rolemodule_id;

drop table t_studentcourse cascade constraints;
drop table t_roleuser cascade constraints;
drop table t_rolemodule cascade constraints;
drop table t_student cascade constraints;
drop table t_course cascade constraints;
drop table t_class cascade constraints;
drop table t_user cascade constraints;
drop table t_role cascade constraints;
drop table t_module cascade constraints;

create sequence seq_student_id start with 1;
create sequence seq_course_id start with 1;
create sequence seq_class_id start with 1;
create sequence seq_user_id start with 1;
create sequence seq_role_id start with 1;
create sequence seq_module_id start with 1;
create sequence seq_studentcourse_id start with 1;
create sequence seq_roleuser_id start with 1;
create sequence seq_rolemodule_id start with 1;

/*==============================================================*/
/* Table: t_user                                                */
/*==============================================================*/
create table t_user  (
   n_user_id            NUMBER(10)                       not null,
   vc_user_uid          VARCHAR(20)			 not null,
   vc_user_type         VARCHAR(20),
   vc_user_password     VARCHAR(200)			 not null,
   vc_user_email        VARCHAR(50),
   dt_user_opdate       DATE                             default sysdate,
   n_user_opuser        NUMBER(10)                       default 1,
   n_user_isdelete      NUMBER(1)                        default 0,
   constraint PK_T_USER primary key (n_user_id),
   constraint UNIQ_T_USER unique (vc_user_uid),
   constraint FK_T_USER_REFERENCE_T_USER foreign key (n_user_opuser)
         references t_user (n_user_id)
);

comment on table t_user is
'用户表 t_user用于记录用户基本信息，并作为基础表与其他表联接';
comment on column t_user.n_user_id is'用户ID';
comment on column t_user.vc_user_uid is'用户登录ID';
comment on column t_user.vc_user_type is'用户类型';
comment on column t_user.vc_user_password is'用户登录密码';
comment on column t_user.vc_user_email is'用户email';
comment on column t_user.dt_user_opdate is'最后操作时间';
comment on column t_user.n_user_opuser is'最后操作人员ID';
comment on column t_user.n_user_isdelete is'是否被删除';

CREATE OR REPLACE TRIGGER tr_user_id
  BEFORE INSERT ON t_user
  FOR EACH ROW
BEGIN
   SELECT seq_user_id.NEXTVAL
      INTO :new.n_user_id
      FROM dual;
END tr_user_id;
/
/*==============================================================*/
/* Table: t_role                                                */
/*==============================================================*/
create table t_role(
   n_role_id            NUMBER(10)                       not null,
   vc_role_name         VARCHAR(30),
   vc_role_desc         VARCHAR(100),
   dt_role_opdate       DATE                             default sysdate,
   n_role_opuser        NUMBER(10)                       default 1,
   n_role_isdelete      NUMBER(1)                        default 0,
   constraint PK_T_ROLE primary key (n_role_id),
   constraint UNIQ_T_ROLE unique (vc_role_name),
   constraint FK_T_ROLE_REFERENCE_T_USER foreign key (n_role_opuser)
         references t_user (n_user_id)
);

comment on table t_role is
'角色表 t_role用于记录角色的基本信息，并作为基础表与其他表联接';
comment on column t_role.n_role_id is'角色ID';
comment on column t_role.vc_role_name is'角色名称';
comment on column t_role.vc_role_desc is'描述';
comment on column t_role.dt_role_opdate is'最后操作时间';
comment on column t_role.n_role_opuser is'最后操作人员ID';
comment on column t_role.n_role_isdelete is'是否被删除';

CREATE OR REPLACE TRIGGER tr_role_id
  BEFORE INSERT ON t_role
  FOR EACH ROW
BEGIN
   SELECT seq_role_id.NEXTVAL
      INTO :new.n_role_id
      FROM dual;
END tr_role_id;
/
/*==============================================================*/
/* Table: t_module                                              */
/*==============================================================*/
create table t_module  (
   n_module_id          	NUMBER(10)              not null,
   vc_module_name       	VARCHAR(20)		not null,
   n_module_parent_id   	NUMBER(10),
   vc_module_url        	VARCHAR(30),
   vc_module_privs_flag 	VARCHAR(2),
   dt_module_opdate     	DATE                    default sysdate,
   n_module_opuser      	NUMBER(10)              default 1,
   n_module_isdelete     	NUMBER(1)               default 0,
   constraint PK_T_MODULE primary key (n_module_id),
   constraint UNIQ_T_MODULE unique (vc_module_name),
   constraint FK_T_MODULE_REFERENCE_T_USER foreign key (n_module_opuser)
         references t_user (n_user_id)
);

comment on table t_module is
'功能模块表 t_module用于记录功能模块的关联信息';
comment on column t_module.n_module_id is'模块ID';
comment on column t_module.vc_module_name is'模块名称';
comment on column t_module.n_module_parent_id is'父模块ID';
comment on column t_module.vc_module_url is'模块url';
comment on column t_module.vc_module_privs_flag is'模块权限标志';
comment on column t_module.dt_module_opdate is'最后操作时间';
comment on column t_module.n_module_opuser is'最后操作人员ID';
comment on column t_module.n_module_isdelete is'是否被删除';

CREATE OR REPLACE TRIGGER tr_module_id
  BEFORE INSERT ON t_module
  FOR EACH ROW
BEGIN
   SELECT seq_module_id.NEXTVAL
      INTO :new.n_module_id
      FROM dual;
END tr_module_id;
/
/*==============================================================*/
/* Table: t_student                                             */
/*==============================================================*/
create table t_student  (
   n_student_id         NUMBER(10)                       not null,
   vc_student_no        VARCHAR(10)                      not null,
   vc_student_name      VARCHAR(10)                      not null,
   dt_student_birthdate DATE,
   vc_student_sex       VARCHAR(1),
   n_student_class_id   NUMBER(10),
   dt_student_opdate    DATE                             default sysdate,
   n_student_opuser     NUMBER(10)                       default 1,
   n_student_isdelete   NUMBER(1)                        default 0,
   constraint PK_T_STUDENT primary key (n_student_id),
   constraint UNIQ_T_STUDENT unique (vc_student_no),
   constraint FK_T_STUDENT_REFERENCE_T_USER foreign key (n_student_opuser)
         references t_user (n_user_id)
);

comment on table t_student is
'用户表 t_student用于记录学生基本信息，并作为基础表与其他表联接';
comment on column t_student.n_student_id is'学生编号';
comment on column t_student.vc_student_no is'学生学号';
comment on column t_student.vc_student_name is'学生姓名';
comment on column t_student.dt_student_birthdate is'出生日期';
comment on column t_student.vc_student_sex is'学生性别';
comment on column t_student.n_student_class_id is'所在班级ID';
comment on column t_student.dt_student_opdate is'最后操作时间';
comment on column t_student.n_student_opuser is'最后操作人员ID';
comment on column t_student.n_student_isdelete is'是否被删除';

CREATE OR REPLACE TRIGGER tr_student_id
  BEFORE INSERT ON t_student
  FOR EACH ROW
BEGIN
   SELECT seq_student_id.NEXTVAL
      INTO :new.n_student_id
      FROM dual;
END tr_student_id;
/
/*==============================================================*/
/* Table: t_course                                              */
/*==============================================================*/
create table t_course  (
   n_course_id          NUMBER(10)                       not null,
   vc_course_no         VARCHAR(10)                      not null,
   vc_course_name       VARCHAR(20)                      not null,
   n_course_hours       NUMBER(3),
   n_course_points     	NUMBER(3,1),
   vc_course_desc       VARCHAR(100),
   dt_course_opdate     DATE                             default sysdate,
   n_course_opuser      NUMBER(10)                       default 1,
   n_course_isdelete    NUMBER(1)                        default 0,
   constraint PK_T_COURSE primary key (n_course_id),
   constraint UNIQ_T_COURSE unique (vc_course_no),
   constraint FK_T_COURSE_REFERENCE_T_USER foreign key (n_course_opuser)
         references t_user (n_user_id)
);

comment on table t_course is
'课程表 t_course 用于记录课程基本信息，并作为基础表与其他表联接';
comment on column t_course.n_course_id is'课程编号';
comment on column t_course.vc_course_no is'课程号';
comment on column t_course.vc_course_name is'课程名称';
comment on column t_course.n_course_hours is'课程学时';
comment on column t_course.n_course_points is'课程学分';
comment on column t_course.vc_course_desc is'课程描述';
comment on column t_course.dt_course_opdate is'最后操作时间';
comment on column t_course.n_course_opuser is'最后操作人员ID';
comment on column t_course.n_course_isdelete is'是否被删除';

CREATE OR REPLACE TRIGGER tr_course_id
  BEFORE INSERT ON t_course
  FOR EACH ROW
BEGIN
   SELECT seq_course_id.NEXTVAL
      INTO :new.n_course_id
      FROM dual;
END tr_course_id;