dqxkz.sql

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

%% ============================================================
%%   Database name:  model_3                                   
%%   DBMS name:      Sybase SQL Anywhere                       
%%   Created on:     99.7.12  08:17                            
%% ============================================================

if exists(select 1 from systypes where name='T_bit') then
   drop datatype T_bit
end if;

create datatype T_bit tinyint not null ;

if exists(select 1 from systypes where name='T_datetime') then
   drop datatype T_datetime
end if;

create datatype T_datetime timestamp  ;

if exists(select 1 from systypes where name='T_image') then
   drop datatype T_image
end if;

create datatype T_image long binary  ;

if exists(select 1 from systypes where name='T_money') then
   drop datatype T_money
end if;

create datatype T_money numeric(19,4)  ;

if exists(select 1 from systypes where name='T_smalldatetime') then
   drop datatype T_smalldatetime
end if;

create datatype T_smalldatetime timestamp  ;

if exists(select 1 from systypes where name='T_smallmoney') then
   drop datatype T_smallmoney
end if;

create datatype T_smallmoney numeric(10,4)  ;

if exists(select 1 from systypes where name='T_sysname') then
   drop datatype T_sysname
end if;

create datatype T_sysname varchar(30) not null ;

if exists(select 1 from systypes where name='T_text') then
   drop datatype T_text
end if;

create datatype T_text long varchar  ;

%% ============================================================
%%   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    integer               not null,
    creste_date    timestamp                     ,
    opinion_id1    integer                       ,
    opinion_id2    integer                       ,
    opinion_id3    integer                       ,
    flages         char(1)                       ,
    primary key (license_id, supplement_id)
);

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

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

%% ============================================================
%%   Table: t_dm_cqh                                           
%% ============================================================
create table t_dm_cqh
(
    cqh            char(1)               not null,
    cq             char(20)                      ,
    primary key (cqh)
);

%% ============================================================
%%   Table: t_dm_xth                                           
%% ============================================================
create table t_dm_xth
(
    xth            char(2)               not null,
    hyxt           char(20)                      ,
    primary key (xth)
);

%% ============================================================
%%   Table: t_dm_xzh                                           
%% ============================================================
create table t_dm_xzh
(
    xzh            char(1)               not null,
    xz             char(20)                      ,
    primary key (xzh)
);

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

%% ============================================================
%%   Table: tolldept                                           
%% ============================================================
create table tolldept
(
    tolldept_id    integer               not null,
    tolldept_name  char(64)                      ,
    principal      char(6)                       ,
    adress         char(64)                      ,
    phone          char(11)                      ,
    postalcode     char(6)                       ,
    primary key (tolldept_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)
);

%% ============================================================
%%   Table: b_module                                           
%% ============================================================
create table b_module
(
    m_id           char(3)               not null,
    e_name         varchar(20)                   ,
    c_name         varchar(30)                   ,
    m_name         char(25)              not null,
    primary key (m_id, m_name)
);

%% ============================================================
%%   Table: b_user                                             
%% ============================================================
create table b_user
(
    u_id           char(3)               not null,
    e_name         varchar(20)                   ,
    c_name         varchar(20)                   ,
    pswd           varchar(20)                   ,
    isgroup        char(1)                       ,
    department     char(1)                       ,
    primary key (u_id)
);

%% ============================================================
%%   Table: b_rights                                           
%% ============================================================
create table b_rights
(
    u_id           char(3)               not null,
    m_id           char(3)               not null,
    m_name         char(25)              not null,
    c_name         varchar(30)                   ,
    isgroup        char(1)                       ,
    primary key (u_id, m_id, m_name)
);
%% ============================================================
%%   Table: t_zzrq                                      
%% ============================================================
create table t_zzrq
(
    license_id     char(8)               not null,
    end_date       date                          ,
    primary key (license_id)
);
alter table b_rights
    add foreign key fk_b_right1 (m_id, m_name)
       references b_module (m_id, m_name) on update restrict on delete restrict;

alter table b_rights
    add foreign key fk_b_right2 (u_id)
       references b_user (u_id) on update restrict on delete restrict;