crebas.sql

医药管理信息系统

/*==============================================================*/
/* Database name:  ConceptualDataModel_1                        */
/* DBMS name:      Sybase AS Anywhere 7                         */
/* Created on:     2006-12-27 19:26:56                          */
/*==============================================================*/


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

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

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

if exists(select 1 from sys.sysindex I, sys.systable T
 where I.table_id=T.table_id and I.index_name='Relationship_3_FK' and T.table_name='SupplyInfor') then
   drop index SupplyInfor.Relationship_3_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='Relationship_4_FK' and T.table_name='SupplyInfor') then
   drop index SupplyInfor.Relationship_4_FK
end if;

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

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

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

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

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

/*==============================================================*/
/* Table: FirmInfor                                             */
/*==============================================================*/
create table FirmInfor 
(
    FirmCode             char(4)                        not null,
    FirmName             varchar(16)                    not null,
    Link                 varchar(12),
    LinkTell             char(11),
    City                 varchar(8),
    primary key (FirmCode)
);

/*==============================================================*/
/* Table: MedInfor                                              */
/*==============================================================*/
create table MedInfor 
(
    MedicineCode         char(4)                        not null,
    MedicineName         varchar(32)                    not null,
    MedKindCode          char(1)                        not null,
    Price                numeric(8,2),
    ListPrice            numeric(8,2),
    StockNum             varchar(4),
    Unit                 char(2),
    UsefulLife           timestamp,
    RatifyCode           varchar(10),
    primary key (MedicineCode)
);

/*==============================================================*/
/* Table: MedKindInfor                                          */
/*==============================================================*/
create table MedKindInfor 
(
    MedKindCode          char(1)                        not null,
    KindExplanation      varchar(16),
    primary key (MedKindCode)
);

/*==============================================================*/
/* Table: SupplyInfor                                           */
/*==============================================================*/
create table SupplyInfor 
(
    MedicineCode         char(4)                        not null,
    FirmCode             char(4)                        not null,
    MedicineName         varchar(32),
    Number               varchar(4),
    Unit                 char(2),
    ListPrice            numeric(8,2),
    Amount               numeric(8,2),
    primary key (MedicineCode, FirmCode)
);

/*==============================================================*/
/* Index: Relationship_3_FK                                     */
/*==============================================================*/
create  index Relationship_3_FK on SupplyInfor (
MedicineCode ASC
);

/*==============================================================*/
/* Index: Relationship_4_FK                                     */
/*==============================================================*/
create  index Relationship_4_FK on SupplyInfor (
FirmCode ASC
);

/*==============================================================*/
/* Table: UserInfor                                             */
/*==============================================================*/
create table UserInfor 
(
    UserRegName          char(6)                        not null,
    Password             char(8)                        not null,
    Position             varchar(10)                    not null,
    Power                char(4),
    primary key (UserRegName)
);

alter table MedInfor
   add foreign key FK_MEDINFOR_RELATIONS_MEDKINDI (MedKindCode)
      references MedKindInfor (MedKindCode)
      on update restrict
      on delete restrict;

alter table SupplyInfor
   add foreign key FK_SUPPLYIN_RELATIONS_MEDINFOR (MedicineCode)
      references MedInfor (MedicineCode)
      on update restrict
      on delete restrict;

alter table SupplyInfor
   add foreign key FK_SUPPLYIN_RELATIONS_FIRMINFO (FirmCode)
      references FirmInfor (FirmCode)
      on update restrict
      on delete restrict;