crebas.sql

关于 ZFC 框架的自述文件 2003.6.18 ======================================================================

/*==============================================================*/
/* Database name:  PhysicalDataModel_table                      */
/* DBMS name:      Sybase AS Anywhere 8                         */
/* Created on:     2004-10-4 22:54:34                           */
/*==============================================================*/


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

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

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

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

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

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

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

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

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

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

/*==============================================================*/
/* Table: DAY_XS_REPORT                                         */
/*==============================================================*/
create table DAY_XS_REPORT 
(
    HID                  char(6)                        not null,
    StartTime            timestamp                      not null,
    OVERTIME             timestamp,
    XSJE                 numeric(7,1)                   not null,
    Start_EMP            varchar(12)                    not null,
    OVER_EMP             varchar(12),
    MEMO                 varchar(100),
    primary key (HID)
);

/*==============================================================*/
/* Table: DLM                                                   */
/*==============================================================*/
create table DLM 
(
    DLM                  char(2)                        not null,
    DLMMC                varchar(40)                    not null,
    MEMO                 varchar(100),
    primary key (DLM)
);

/*==============================================================*/
/* Table: GYS                                                   */
/*==============================================================*/
create table GYS 
(
    g_ID                 varchar(2)                     not null,
    g_name               varchar(50)                    not null,
    g_lxr                varchar(20),
    g_tel                varchar(20),
    g_czH                varchar(20),
    g_address            varchar(100),
    primary key (g_ID)
);

/*==============================================================*/
/* Table: SPDA                                                  */
/*==============================================================*/
create table SPDA 
(
    dlm                  char(2)                        not null,
    spmc                 varchar(200)                   not null,
    g_id                 varchar(2),
    spgg                 varchar(20),
    spdw                 char(2),
    spsj                 numeric(7,1)                   not null,
    spjj                 numeric(7,1)                   not null,
    sphyj                numeric(7,1),
    fhhh                 varchar(50),
    thfs                 varchar(100),
    thtj                 varchar(100),
    spzdj                numeric(7,1)                   not null,
    memo                 varchar(400),
    sppp                 varchar(20),
    dpsl                 numeric(5,0)                   not null,
    jdrq                 date                           not null,
    spmcpy               varchar(100),
    sid                  varchar(10)                    not null,
    primary key (sid)
);

/*==============================================================*/
/* Table: XJLSZ                                                 */
/*==============================================================*/
create table XJLSZ 
(
    hid                  integer                        not null,
    SZType               integer                        not null,
    JE                   numeric(7,1)                   not null,
    DATE_TIME            timestamp                      not null,
    MEMO                 varchar(200),
    primary key (hid)
);

/*==============================================================*/
/* Table: spstore                                                 */
/*==============================================================*/
create table spstore 
(
    sid                  varchar(10)                    not null,
    ZGKCL                numeric(5,0),
    DGRQ                 date,
    ZJDHRQ               date,
    ZJDHSL               numeric(5,0),
    ZXKCL                numeric(5,0)                   not null default 0,
    xssl                 numeric(5,0)                    not null default 0 ,
    primary key (sid)
);

/*==============================================================*/
/* Table: storein                                               */
/*==============================================================*/
create table storein 
(
    HID                  varchar(10)                    not null,
    RKRQ                 timestamp                      not null ,
    DJZT                 bit                            not null,
    EMP                  varchar(12)                    not null,
    G_ID                 varchar(2),
    FHHH                 varchar(20),
    FPH                  varchar(20),
    MEMO                 varchar(400),
    primary key (HID)
);

/*==============================================================*/
/* Table: storeindetail                                         */
/*==============================================================*/
create table storeindetail 
(
    HID                  varchar(10)                    not null,
    sid                  varchar(10)                    not null,
    RKJE                 numeric(7,1)                   not null,
    RKSL                 numeric(5,0)                   not null,
    primary key (HID, sid)
);

/*==============================================================*/
/* Table: xs                                                    */
/*==============================================================*/
create table xs 
(
    hid                  varchar(10)                    not null,
    XSTime               timestamp                      not null,
    XSJE                 numeric(7,1)                   not null,
    EMP                  integer                        not null,
    guest_id             varchar(10),
    MEMO                 varchar(100),
    primary key (hid)
);

/*==============================================================*/
/* Table: xsdetail                                              */
/*==============================================================*/
create table xsdetail 
(
    hid                  varchar(10)                    not null,
    sid                  varchar(10)                    not null,
    XSJR                 numeric(7,1)                   not null
         check (XSJR >= 0),
    XSSL                 numeric(5,0)                   not null,
    memo                 varchar(100),
    primary key (hid, sid)
);