0031725.sql

这是一个简单的学生管理系统

prompt PL/SQL Developer import file
prompt Created on 2006年3月11日 by 杨波
set feedback off
set define off

prompt *****************************************
prompt *****创建学籍变更表CHANGE*********
prompt *****************************************
prompt Dropping CHANGE...
drop table CHANGE cascade constraints;
prompt Creating CHANGE...
create table CHANGE
(
  ID          NUMBER not null,
  STUDENT     CHAR(6) not null,
  CHANGE      CHAR(1) not null,
  REC_TIME    DATE not null,
  DESCRIPTION VARCHAR2(4000)
)
;

prompt **********************************************************
prompt *****创建学籍变更代码表CHANGE_CODE*********
prompt ***********************************************************
prompt Dropping CHANGE_CODE...
drop table CHANGE_CODE cascade constraints;
prompt Creating CHANGE_CODE...
create table CHANGE_CODE
(
  CODE        CHAR(1) not null,                    /*代码*/
  DESCRIPTION VARCHAR2(50) not null    /*描述*/
)
;
prompt Loading CHANGE_CODE...
insert into CHANGE_CODE (CODE, DESCRIPTION)
values ('0', '转系');
insert into CHANGE_CODE (CODE, DESCRIPTION)
values ('1', '休学');
insert into CHANGE_CODE (CODE, DESCRIPTION)
values ('2', '复学');
insert into CHANGE_CODE (CODE, DESCRIPTION)
values ('3', '退学');
insert into CHANGE_CODE (CODE, DESCRIPTION)
values ('4', '毕业');
commit;

prompt **********************************************************
prompt *****创建班级信息表CLASS*********
prompt ***********************************************************
prompt Dropping CLASS...
drop table CLASS cascade constraints;
prompt Creating CLASS...
create table CLASS
(
  ID         CHAR(4) not null,
  NAME       VARCHAR2(50) not null,
  MONITOR    CHAR(6),
  DEPARTMENT NUMBER not null
)
;
prompt Loading CLASS...
insert into CLASS (ID, NAME, MONITOR, DEPARTMENT)
values ('0041', '04计算机1班', null, 0);
insert into CLASS (ID, NAME, MONITOR, DEPARTMENT)
values ('0042', '04计算机2班', null, 0);
insert into CLASS (ID, NAME, MONITOR, DEPARTMENT)
values ('1041', '04工商管理1班', null, 1);
insert into CLASS (ID, NAME, MONITOR, DEPARTMENT)
values ('2031', '03电子技术1班', null, 2);
insert into CLASS (ID, NAME, MONITOR, DEPARTMENT)
values ('2042', '04电子技术2班', null, 2);
commit;

prompt **********************************************************
prompt *****创建计数保存表COUNTER*********
prompt ***********************************************************

prompt Dropping COUNTER...
drop table COUNTER cascade constraints;
prompt Creating COUNTER...
create table COUNTER
(
  ID            CHAR(1) not null,
  COUNTER_VALUE NUMBER not null
)
;
insert into counter (ID, counter_value)
values ('C', 0);
insert into counter (ID, counter_value)
values ('R', 0);
insert into counter (ID, counter_value)
values ('P', 0);
commit;

prompt **********************************************************
prompt *****创建院系表DEPARTMENT*********
prompt ***********************************************************
prompt Dropping DEPARTMENT...
drop table DEPARTMENT cascade constraints;
prompt Creating DEPARTMENT...
create table DEPARTMENT
(
  ID   NUMBER not null,
  NAME VARCHAR2(50) not null
)
;
prompt Loading DEPARTMENT...
insert into DEPARTMENT (ID, NAME)
values (0, '信息管理学院');
insert into DEPARTMENT (ID, NAME)
values (1, '工商管理学院');
insert into DEPARTMENT (ID, NAME)
values (2, '电子学院');
commit;

prompt **********************************************************
prompt *****创建处罚等级代码表PUNISH_LEVELS*********
prompt ***********************************************************

prompt Dropping PUNISH_LEVELS...
drop table PUNISH_LEVELS cascade constraints;
prompt Creating PUNISH_LEVELS...
create table PUNISH_LEVELS
(
  CODE        CHAR(1) not null,
  DESCRIPTION VARCHAR2(50) not null
)
;

prompt Loading PUNISH_LEVELS...
insert into PUNISH_LEVELS (CODE, DESCRIPTION)
values ('0', '警告');
insert into PUNISH_LEVELS (CODE, DESCRIPTION)
values ('1', '严重警告');
insert into PUNISH_LEVELS (CODE, DESCRIPTION)
values ('2', '记过');
insert into PUNISH_LEVELS (CODE, DESCRIPTION)
values ('3', '退学');
insert into PUNISH_LEVELS (CODE, DESCRIPTION)
values ('4', '开除学籍');
commit;

prompt **********************************************************
prompt *****创建处罚记录信息表PUNISHMENT*********
prompt ***********************************************************
prompt Dropping PUNISHMENT...
drop table PUNISHMENT cascade constraints;
prompt Creating PUNISHMENT...
create table PUNISHMENT
(
  ID          NUMBER not null,
  STUDENT     CHAR(6) not null,
  LEVELS      CHAR(1) not null,
  REC_TIME    DATE not null,
  DESCRIPTION VARCHAR2(4000)
)
;
prompt **********************************************************
prompt *****创建奖励记录信息表REWARD          *********
prompt ***********************************************************
prompt Dropping REWARD...
drop table REWARD cascade constraints;
prompt Creating REWARD...
create table REWARD
(
  ID          NUMBER not null,
  STUDENT     CHAR(6) not null,
  LEVELS      CHAR(1) not null,
  REC_TIME    DATE not null,
  DESCRIPTION VARCHAR2(4000)
)
;
prompt **********************************************************
prompt *****创建奖励等级代码表REWARD_LEVELS*********
prompt ***********************************************************

prompt Dropping REWARD_LEVELS...
drop table REWARD_LEVELS cascade constraints;
prompt Creating REWARD_LEVELS...
create table REWARD_LEVELS
(
  CODE        CHAR(1) not null,
  DESCRIPTION VARCHAR2(50) not null
)
;
prompt Loading REWARD_LEVELS...
insert into REWARD_LEVELS (CODE, DESCRIPTION)
values ('0', '校特等奖学金');
insert into REWARD_LEVELS (CODE, DESCRIPTION)
values ('1', '校一等奖学金');
insert into REWARD_LEVELS (CODE, DESCRIPTION)
values ('2', '校二等奖学金');
insert into REWARD_LEVELS (CODE, DESCRIPTION)
values ('3', '校三等奖学金');
insert into REWARD_LEVELS (CODE, DESCRIPTION)
values ('4', '系一等奖学金');
insert into REWARD_LEVELS (CODE, DESCRIPTION)
values ('5', '系二等奖学金');
insert into REWARD_LEVELS (CODE, DESCRIPTION)
values ('6', '系三等奖学金');
commit;

prompt **********************************************************
prompt *****创建学生表STUDENT*********
prompt ***********************************************************
prompt Dropping STUDENT...
drop table STUDENT cascade constraints;
prompt Creating STUDENT...
create table STUDENT
(
  ID           CHAR(6) not null,
  NAME         VARCHAR2(20) not null,
  SEX          CHAR(1),
  CLASS        CHAR(4),
  DEPARTMENT   INTEGER,
  BIRTHDAY     DATE,
  NATIVE_PLACE VARCHAR2(50)
)
;
prompt Loading STUDENT...
insert into STUDENT (ID, NAME, SEX, CLASS, DEPARTMENT, BIRTHDAY, NATIVE_PLACE)
values ('030001', '杨过', 'M', '2031', 2, to_date('12-09-1982', 'dd-mm-yyyy'), '桃花岛');
insert into STUDENT (ID, NAME, SEX, CLASS, DEPARTMENT, BIRTHDAY, NATIVE_PLACE)
values ('030010', '小龙女', 'W', '1041', 1, to_date('06-12-1985', 'dd-mm-yyyy'), '古墓');
insert into STUDENT (ID, NAME, SEX, CLASS, DEPARTMENT, BIRTHDAY, NATIVE_PLACE)
values ('040001', '张山', 'M', '0041', 0, to_date('01-03-1985', 'dd-mm-yyyy'), '江西南昌');
insert into STUDENT (ID, NAME, SEX, CLASS, DEPARTMENT, BIRTHDAY, NATIVE_PLACE)
values ('040002', '李红', 'W', '0042', 0, to_date('16-03-1982', 'dd-mm-yyyy'), '江苏南京');
commit;

prompt **********************************************************
prompt *****创建用户表USERT*********
prompt ***********************************************************
prompt Dropping USERT...
drop table USERT cascade constraints;
prompt Creating USERT...
create table USERT
(
  ID           CHAR(10) not null,
  PASSWD       CHAR(10) not null
)
;
prompt Loading USERT...
insert into USERT (ID, PASSWD)
values ('jenny','123456');
commit;

prompt **********************************************************
prompt *****修改表的主键和外部建*********
prompt ***********************************************************
alter table CHANGE
  add constraint CHANGE_ID primary key (ID);

alter table CHANGE_CODE
  add constraint CHANGECODE_CODE primary key (CODE);

alter table CLASS
  add constraint CLASS_ID primary key (ID);

alter table COUNTER
  add constraint COUNTERID primary key (ID);

alter table DEPARTMENT
  add constraint DEPARTMENT_ID primary key (ID);

alter table PUNISH_LEVELS
  add constraint PUNISH_LEVELSID primary key (CODE);

alter table PUNISHMENT
  add constraint PUNISHMENT_ID primary key (ID);

alter table REWARD
  add constraint REWARD_ID primary key (ID);

alter table STUDENT
  add constraint ID primary key (ID);

alter table REWARD_LEVELS
  add constraint REWARDLEVELS_CODE primary key (CODE);

alter table USERT
  add constraint USERID primary key (ID);

alter table CHANGE
  add constraint CHANGE foreign key (CHANGE)
  references CHANGE_CODE (CODE);
alter table CHANGE
  add constraint STUDENT foreign key (STUDENT)
  references STUDENT (ID);

alter table CLASS
  add constraint DEPARTMENT foreign key (DEPARTMENT)
  references DEPARTMENT (ID);
alter table CLASS
  add constraint MONITOR foreign key (MONITOR)
  references STUDENT (ID);

alter table PUNISHMENT
  add constraint PUNISHMENT_LEVELS foreign key (LEVELS)
  references PUNISH_LEVELS (CODE);
alter table PUNISHMENT
  add constraint PUNISHMENT_STUDENT foreign key (STUDENT)
  references STUDENT (ID);

alter table REWARD
  add constraint REWARD_LEVELS foreign key (LEVELS)
  references REWARD_LEVELS (CODE);
alter table REWARD
  add constraint REWARD_STUDENT foreign key (STUDENT)
  references STUDENT (ID);

alter table STUDENT
  add constraint CLASS foreign key (CLASS)
  references CLASS (ID);
alter table STUDENT
  add constraint STUDENTDEPARTMENT foreign key (DEPARTMENT)
  references DEPARTMENT (ID);

set feedback on
set define on
prompt Done.