create_db.sql

一个详细的学生管理系统

/*==============================================================*/
/* Database name:  学生管理系统                                       */
/* DBMS name:      Sybase AS Anywhere 8                         */
/* Created on:     2003-8-27 19:43:58                           */
/*==============================================================*/


if exists(select 1 from sys.sysforeignkey where role='FK_CHOOSE_C_CHOOSE_CO_COURSES') then
    alter table choose_course
       delete foreign key FK_CHOOSE_C_CHOOSE_CO_COURSES
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_CHOOSE_C_CHOOSE_CO_STUDENT') then
    alter table choose_course
       delete foreign key FK_CHOOSE_C_CHOOSE_CO_STUDENT
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_STUDENT_HAVE_STUD_SCHOOL') then
    alter table student
       delete foreign key FK_STUDENT_HAVE_STUD_SCHOOL
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_TEACH_TE_TEACH_TEA_COURSES') then
    alter table teach_teachers
       delete foreign key FK_TEACH_TE_TEACH_TEA_COURSES
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_TEACH_TE_TEACH_TEA_TEACHER') then
    alter table teach_teachers
       delete foreign key FK_TEACH_TE_TEACH_TEA_TEACHER
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_TEACHER_HAVE_TEAC_SCHOOL') then
    alter table teacher
       delete foreign key FK_TEACHER_HAVE_TEAC_SCHOOL
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='choose_course2_FK'
     and t.table_name='choose_course'
) then
   drop index choose_course.choose_course2_FK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='choose_course_FK'
     and t.table_name='choose_course'
) then
   drop index choose_course.choose_course_FK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='choose_course_PK'
     and t.table_name='choose_course'
) then
   drop index choose_course.choose_course_PK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='courses_PK'
     and t.table_name='courses'
) then
   drop index courses.courses_PK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='school_PK'
     and t.table_name='school'
) then
   drop index school.school_PK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='have_student_FK'
     and t.table_name='student'
) then
   drop index student.have_student_FK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='student_PK'
     and t.table_name='student'
) then
   drop index student.student_PK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='teach_teachers2_FK'
     and t.table_name='teach_teachers'
) then
   drop index teach_teachers.teach_teachers2_FK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='teach_teachers_FK'
     and t.table_name='teach_teachers'
) then
   drop index teach_teachers.teach_teachers_FK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='teach_teachers_PK'
     and t.table_name='teach_teachers'
) then
   drop index teach_teachers.teach_teachers_PK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='have_teacher_FK'
     and t.table_name='teacher'
) then
   drop index teacher.have_teacher_FK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='teacher_PK'
     and t.table_name='teacher'
) then
   drop index teacher.teacher_PK
end if;

if exists(
   select 1 from sys.systable 
   where table_name='choose_course'
     and table_type='BASE'
) then
    drop table choose_course
end if;

if exists(
   select 1 from sys.systable 
   where table_name='courses'
     and table_type='BASE'
) then
    drop table courses
end if;

if exists(
   select 1 from sys.systable 
   where table_name='school'
     and table_type='BASE'
) then
    drop table school
end if;

if exists(
   select 1 from sys.systable 
   where table_name='student'
     and table_type='BASE'
) then
    drop table student
end if;

if exists(
   select 1 from sys.systable 
   where table_name='teach_teachers'
     and table_type='BASE'
) then
    drop table teach_teachers
end if;

if exists(
   select 1 from sys.systable 
   where table_name='teacher'
     and table_type='BASE'
) then
    drop table teacher
end if;

/*==============================================================*/
/* Table: choose_course                                         */
/*==============================================================*/
create table choose_course 
(
    course_id            varchar(16)                    not null,
    student_id           varchar(16)                    not null,
    score                decimal(5,2),
    primary key (course_id, student_id)
);

/*==============================================================*/
/* Index: choose_course_PK                                      */
/*==============================================================*/
create unique index choose_course_PK on choose_course (
course_id ASC,
student_id ASC
);

/*==============================================================*/
/* Index: choose_course_FK                                      */
/*==============================================================*/
create  index choose_course_FK on choose_course (
course_id ASC
);

/*==============================================================*/
/* Index: choose_course2_FK                                     */
/*==============================================================*/
create  index choose_course2_FK on choose_course (
student_id ASC
);

/*==============================================================*/
/* Table: courses                                               */
/*==============================================================*/
create table courses 
(
    course_id            varchar(16)                    not null,
    course_name          varchar(32),
    course_content       varchar(255),
    course_book          varchar(64),
    primary key (course_id)
);

/*==============================================================*/
/* Index: courses_PK                                            */
/*==============================================================*/
create unique index courses_PK on courses (
course_id ASC
);

/*==============================================================*/
/* Table: school                                                */
/*==============================================================*/
create table school 
(
    school_id            varchar(16)                    not null,
    school_sname         varchar(32)                    not null,
    school_fname         varchar(255),
    school_admin         varchar(16),
    school_jl            varchar(255),
    primary key (school_id)
);

/*==============================================================*/
/* Index: school_PK                                             */
/*==============================================================*/
create unique index school_PK on school (
school_id ASC
);

/*==============================================================*/
/* Table: student                                               */
/*==============================================================*/
create table student 
(
    student_id           varchar(16)                    not null,
    school_id            varchar(16),
    student_name         varchar(16)                    not null,
    student_sex          varchar(2)                     not null,
    student_birth        date,
    student_party        varchar(16),
    student_from         varchar(32),
    student_addr         varchar(64),
    student_tel          varchar(32),
    student_head         varchar(32),
    student_detail       varchar(255),
    student_photo        long binary,
    primary key (student_id)
);

/*==============================================================*/
/* Index: student_PK                                            */
/*==============================================================*/
create unique index student_PK on student (
student_id ASC
);

/*==============================================================*/
/* Index: have_student_FK                                       */
/*==============================================================*/
create  index have_student_FK on student (
school_id ASC
);

/*==============================================================*/
/* Table: teach_teachers                                        */
/*==============================================================*/
create table teach_teachers 
(
    course_id            varchar(16)                    not null,
    teacher_id           varchar(16)                    not null,
    primary key (course_id, teacher_id)
);

/*==============================================================*/
/* Index: teach_teachers_PK                                     */
/*==============================================================*/
create unique index teach_teachers_PK on teach_teachers (
course_id ASC,
teacher_id ASC
);

/*==============================================================*/
/* Index: teach_teachers_FK                                     */
/*==============================================================*/
create  index teach_teachers_FK on teach_teachers (
course_id ASC
);

/*==============================================================*/
/* Index: teach_teachers2_FK                                    */
/*==============================================================*/
create  index teach_teachers2_FK on teach_teachers (
teacher_id ASC
);

/*==============================================================*/
/* Table: teacher                                               */
/*==============================================================*/
create table teacher 
(
    teacher_id           varchar(16)                    not null,
    school_id            varchar(16),
    teacher_name         varchar(16)                    not null,
    teacher_sex          varchar(2),
    teacher_birth        date,
    teacher_level        varchar(16),
    teacher_duty         varchar(16),
    teacher_addr         varchar(64),
    teacher_tel          varchar(32),
    primary key (teacher_id)
);

/*==============================================================*/
/* Index: teacher_PK                                            */
/*==============================================================*/
create unique index teacher_PK on teacher (
teacher_id ASC
);

/*==============================================================*/
/* Index: have_teacher_FK                                       */
/*==============================================================*/
create  index have_teacher_FK on teacher (
school_id ASC
);

alter table choose_course
   add foreign key FK_CHOOSE_C_CHOOSE_CO_COURSES (course_id)
      references courses (course_id)
      on update restrict
      on delete restrict;

alter table choose_course
   add foreign key FK_CHOOSE_C_CHOOSE_CO_STUDENT (student_id)
      references student (student_id)
      on update restrict
      on delete restrict;

alter table student
   add foreign key FK_STUDENT_HAVE_STUD_SCHOOL (school_id)
      references school (school_id)
      on update restrict
      on delete restrict;

alter table teach_teachers
   add foreign key FK_TEACH_TE_TEACH_TEA_COURSES (course_id)
      references courses (course_id)
      on update restrict
      on delete restrict;

alter table teach_teachers
   add foreign key FK_TEACH_TE_TEACH_TEA_TEACHER (teacher_id)
      references teacher (teacher_id)
      on update restrict
      on delete restrict;

alter table teacher
   add foreign key FK_TEACHER_HAVE_TEAC_SCHOOL (school_id)
      references school (school_id)
      on update restrict
      on delete restrict;