crebas.sql

图书管理系统

%% ============================================================
%%   Database name:  MODEL_1                                   
%%   DBMS name:      Sybase SQL Anywhere                       
%%   Created on:     2006-12-24  18:56                         
%% ============================================================

%% ============================================================
%%   Table: XTYH                                               
%% ============================================================
create table XTYH
(
    USER_NO        char(8)               not null,
    USER_NAME      char(8)                       ,
    USER_PASSWORD  char(8)                       ,
    primary key (USER_NO)
);

%% ============================================================
%%   Table: XK                                                 
%% ============================================================
create table XK
(
    COUNO          char(8)               not null,
    STUNO          char(8)               not null,
    primary key (COUNO, STUNO)
);

%% ============================================================
%%   Table: COLLEGE                                            
%% ============================================================
create table COLLEGE
(
    COL_NO         char(2)               not null,
    COL_NAME       char(20)                      ,
    COL_REMARK     varchar(20)                   ,
    primary key (COL_NO)
);

%% ============================================================
%%   Table: CLASSROOM                                          
%% ============================================================
create table CLASSROOM
(
    CL_NO          char(8)               not null,
    CL_PEP         char(3)                       ,
    CL_REMARK      varchar(20)                   ,
    primary key (CL_NO)
);

%% ============================================================
%%   Table: COURSE                                             
%% ============================================================
create table COURSE
(
    COU_NO         char(4)               not null,
    COL_NO         char(2)                       ,
    COU_COLLEGE    char(2)                       ,
    COU_TYPE       char(4)                       ,
    COU_CREDIT     real                          ,
    PERIOD         float                         ,
    COU_TIME       char(18)                      ,
    COU_ROOM       char(6)                       ,
    COU_REMARK     varchar(20)                   ,
    primary key (COU_NO)
);

%% ============================================================
%%   Table: TEACHER                                            
%% ============================================================
create table TEACHER
(
    TEC_NO         char(8)               not null,
    COL_NO         char(2)                       ,
    TEC_NAME       char(8)                       ,
    TEC_SEX        char(2)                       ,
    TEC_PASSWORD   char(8)                       ,
    TEC_TITLE      varchar(20)                   ,
    TEC_COLLEGE    char(2)                       ,
    TEC_COURSE     char(8)                       ,
    TEC_REMARK     varchar(50)                   ,
    primary key (TEC_NO)
);

%% ============================================================
%%   Table: STUDENTS                                           
%% ============================================================
create table STUDENTS
(
    STU_DENTSID    char(10)              not null,
    COL_NO         char(2)                       ,
    TEC_NO         char(8)                       ,
    STU_NAME       char(8)                       ,
    STU_SEX        char(2)                       ,
    STU_PASSWORD   char(8)                       ,
    COLLEGEID      char(3)                       ,
    STU_REMARK     varchar(50)                   ,
    primary key (STU_DENTSID)
);

%% ============================================================
%%   Table: SREPORT                                            
%% ============================================================
create table SREPORT
(
    COURSEID       char(4)               not null,
    STUDENTID      char(8)               not null,
    STU_DENTSID    char(10)                      ,
    SCORE          char(3)                       ,
    STU_REMARK     varchar(50)                   ,
    primary key (COURSEID, STUDENTID)
);

%% ============================================================
%%   Table: RELATION_37                                        
%% ============================================================
create table RELATION_37
(
    STU_DENTSID    char(10)              not null,
    COU_NO         char(4)               not null,
    primary key (STU_DENTSID, COU_NO)
);

%% ============================================================
%%   Table: RELATION_76                                        
%% ============================================================
create table RELATION_76
(
    COU_NO         char(4)               not null,
    CL_NO          char(8)               not null,
    primary key (COU_NO, CL_NO)
);

alter table COURSE
    add foreign key FK_COURSE_RELATION__COLLEGE (COL_NO)
       references COLLEGE (COL_NO) on update restrict on delete restrict;

alter table TEACHER
    add foreign key FK_TEACHER_RELATION__COLLEGE (COL_NO)
       references COLLEGE (COL_NO) on update restrict on delete restrict;

alter table STUDENTS
    add foreign key FK_STUDENTS_RELATION__COLLEGE (COL_NO)
       references COLLEGE (COL_NO) on update restrict on delete restrict;

alter table STUDENTS
    add foreign key FK_STUDENTS_RELATION__TEACHER (TEC_NO)
       references TEACHER (TEC_NO) on update restrict on delete restrict;

alter table SREPORT
    add foreign key FK_SREPORT_RELATION__STUDENTS (STU_DENTSID)
       references STUDENTS (STU_DENTSID) on update restrict on delete restrict;

alter table RELATION_37
    add foreign key FK_RELATION_RELATION__STUDENTS (STU_DENTSID)
       references STUDENTS (STU_DENTSID) on update restrict on delete restrict;

alter table RELATION_37
    add foreign key FK_RELATION_RELATION__COURSE (COU_NO)
       references COURSE (COU_NO) on update restrict on delete restrict;

alter table RELATION_76
    add foreign key FK_RELATION_RELATION__COURSE (COU_NO)
       references COURSE (COU_NO) on update restrict on delete restrict;

alter table RELATION_76
    add foreign key FK_RELATION_RELATION__CLASSROO (CL_NO)
       references CLASSROOM (CL_NO) on update restrict on delete restrict;