crebas.sql

很久以前设计的软件。物价局收费许可证管理系统 需要SQLAnywhere 5.0

%% ============================================================
%%   Database name:  MODEL_1                                   
%%   DBMS name:      Sybase SQL Anywhere 5.5                   
%%   Created on:     99.6.27  15:22                            
%% ============================================================

%% ============================================================
%%   Table: opinion                                            
%% ============================================================
create table opinion
(
    opinion_id     int                   not null,
    begin_date     date                          ,
    end_date       date                          ,
    opinion1       char(254)                     ,
    opinion2       char(64)                      ,
    audi_date      date                          ,
    item_total     int                           ,
    item_cant      numeric(10,2)                 ,
    nrcz           char(20)                      ,
    sjczse         numeric(10,2)                 ,
    primary key (opinion_id)
);

%% ============================================================
%%   Table: tolldept                                           
%% ============================================================
create table tolldept
(
    tolldept_id    int                   not null,
    tolldept_name  char(64)                      ,
    principal      char(6)                       ,
    adress         char(64)                      ,
    phone          char(11)                      ,
    postalcode     char(6)                       ,
    primary key (tolldept_id)
);

%% ============================================================
%%   Table: manage_dept                                        
%% ============================================================
create table manage_dept
(
    givedept_id    smallint              not null,
    givedept_name  char(64)              not null,
    primary key (givedept_id)
);

%% ============================================================
%%   Table: license                                            
%% ============================================================
create table license
(
    license_id     char(8)               not null,
    givedept_id    smallint                      ,
    toll_item      char(64)                      ,
    begin_date     date                          ,
    end_date       date                          ,
    give_date      date                          ,
    toll_dept      char(64)                      ,
    toll_adress    char(64)                      ,
    flags          char(1)                       ,
    primary key (license_id)
);

%% ============================================================
%%   Table: license_supplement                                 
%% ============================================================
create table license_supplement
(
    license_id     char(8)               not null,
    supplement_id  char(2)               not null,
    tolldept_id    int                   not null,
    creste_date    timestamp                     ,
    opinion_id1    int                           ,
    opinion_id2    int                           ,
    opinion_id3    int                           ,
    flages         char(1)                       ,
    primary key (license_id, supplement_id)
);

%% ============================================================
%%   Table: t_item                                             
%% ============================================================
create table t_item
(
    item_id        numeric(16)           not null,
    license_id     char(8)               not null,
    supplement_id  char(2)               not null,
    item_name      char(40)              not null,
    unit           char(16)              not null,
    standart       char(10)              not null,
    range          char(128)                     ,
    department     char(64)                      ,
    manager        char(28)                      ,
    opinion_id1    char(1)                       ,
    opinion_id2    char(1)                       ,
    opinion_id3    char(1)                       ,
    primary key (item_id)
);

%% ============================================================
%%   Table: zuofei                                             
%% ============================================================
create table zuofei
(
    license_id     char(8)               not null,
    supplement_id  char(2)               not null,
    neirong        varchar(254)                  ,
    primary key (license_id, supplement_id)
);

alter table license_supplement
    add foreign key fk_license__ref_330_tolldept (tolldept_id)
       references tolldept (tolldept_id) on update restrict on delete restrict;

alter table zuofei
    add foreign key fk_zuofei_ref_338_license_ (license_id, supplement_id)
       references license_supplement (license_id, supplement_id) on update restrict on delete restrict;