/*==============================================================*/
/* DBMS name:      Sybase SQL Anywhere 12                       */
/* Created on:     2021/9/6 23:14:39                            */
/*==============================================================*/


if exists(select 1 from sys.sysforeignkey where role='FK_ICU表_REFERENCE_用户表') then
    alter table icu表
       delete foreign key FK_ICU表_REFERENCE_用户表
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_ICU表_REFERENCE_医护表') then
    alter table icu表
       delete foreign key FK_ICU表_REFERENCE_医护表
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_ICU表_REFERENCE_住院表') then
    alter table icu表
       delete foreign key FK_ICU表_REFERENCE_住院表
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_住院表_REFERENCE_用户表') then
    alter table 住院表
       delete foreign key FK_住院表_REFERENCE_用户表
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_住院表_REFERENCE_病房表') then
    alter table 住院表
       delete foreign key FK_住院表_REFERENCE_病房表
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_医护表_REFERENCE_部门表') then
    alter table 医护表
       delete foreign key FK_医护表_REFERENCE_部门表
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_挂号表_REFERENCE_医护表') then
    alter table 挂号表
       delete foreign key FK_挂号表_REFERENCE_医护表
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_挂号表_REFERENCE_部门表') then
    alter table 挂号表
       delete foreign key FK_挂号表_REFERENCE_部门表
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_救护车表_REFERENCE_医护表') then
    alter table 救护车表
       delete foreign key FK_救护车表_REFERENCE_医护表
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_门诊表_REFERENCE_部门表') then
    alter table 门诊表
       delete foreign key FK_门诊表_REFERENCE_部门表
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_门诊表_REFERENCE_挂号表') then
    alter table 门诊表
       delete foreign key FK_门诊表_REFERENCE_挂号表
end if;

drop table if exists icu表;

drop table if exists 仪器表;

drop table if exists 住院表;

drop table if exists 医护表;

drop table if exists 器官表;

drop table if exists 库房;

drop table if exists 挂号表;

drop table if exists 救护车表;

drop index if exists 用户表.Index_1;

drop table if exists 用户表;

drop table if exists 病房表;

drop table if exists 血库表;

drop index if exists 部门表.Index_1;

drop table if exists 部门表;

drop table if exists 门诊表;

/*==============================================================*/
/* Table: icu表                                                  */
/*==============================================================*/
create table icu表 
(
   icuID                int                            not null,
   住院id                 int                            null,
   床位id                 int                            null,
   现用床位                 int                            null,
   用户id                 int                            null,
   人员id                 int                            null,
   constraint PK_ICU表 primary key clustered (icuID)
);

/*==============================================================*/
/* Table: 仪器表                                                   */
/*==============================================================*/
create table 仪器表 
(
   仪器id                 int                            not null,
   仪器名称                 varchar(20)                    null,
   分类                   varchar(10)                    null,
   数量                   int                            null,
   状态                   varchar(10)                    null,
   constraint PK_仪器表 primary key clustered (仪器id)
);

/*==============================================================*/
/* Table: 住院表                                                   */
/*==============================================================*/
create table 住院表 
(
   住院id                 int                            not null,
   用户id                 int                            null,
   床位号id                int                            not null,
   病因                   varchar(50)                    null,
   constraint PK_住院表 primary key clustered (住院id, 床位号id)
);

/*==============================================================*/
/* Table: 医护表                                                   */
/*==============================================================*/
create table 医护表 
(
   人员id                 int                            not null,
   人员姓名                 varchar(30)                    not null,
   人员年龄                 int                            null,
   人员性别                 varchar(2)                     not null,
   职位                   varchar(20)                    not null,
   部门id                 int                            not null,
   是否在值                 varchar(20)                    null,
   constraint PK_医护表 primary key clustered (人员id)
);

comment on column 医护表.是否在值 is 
'旷工，请假等';

/*==============================================================*/
/* Table: 器官表                                                   */
/*==============================================================*/
create table 器官表 
(
   器官id                 int                            not null,
   名称                   varchar(20)                    null,
   数量                   int                            null,
   分类                   varchar(20)                    null,
   状态                   varchar(10)                    null,
   入库时间                 datetime                       null,
   出库时间                 datetime                       null,
   constraint PK_器官表 primary key clustered (器官id)
);

/*==============================================================*/
/* Table: 库房                                                    */
/*==============================================================*/
create table 库房 
(
   药id                  int                            not null,
   药名                   varchar                        null,
   药数                   int                            null,
   单价                   decimal(100,2)                 null,
   出库时间                 datetime                       null,
   入库时间                 datetime                       null,
   constraint PK_库房 primary key clustered (药id)
);

/*==============================================================*/
/* Table: 挂号表                                                   */
/*==============================================================*/
create table 挂号表 
(
   流水号                  int                            not null,
   人员id                 int                            null,
   部门id                 int                            not null,
   用户id                 int                            null,
   constraint PK_挂号表 primary key clustered (流水号)
);

/*==============================================================*/
/* Table: 救护车表                                                  */
/*==============================================================*/
create table 救护车表 
(
   救护车id                int                            not null,
   驾驶人员                 varchar(20)                    null,
   人员id                 int                            null,
   使用情况                 varchar(10)                    null,
   constraint PK_救护车表 primary key clustered (救护车id)
);

comment on column 救护车表.使用情况 is 
'出外，在库';

/*==============================================================*/
/* Table: 用户表                                                   */
/*==============================================================*/
create table 用户表 
(
   用户id                 int                            not null,
   名字                   varchar(10)                    not null,
   年龄                   int                            not null,
   性别                   varchar(2)                     not null,
   地址                   varchar(30)                    null,
   电话                   int                            null,
   病史                   varchar(50)                    null,
   身份证                  varchar(18)                    null,
   constraint PK_用户表 primary key clustered (用户id)
);

/*==============================================================*/
/* Index: Index_1                                               */
/*==============================================================*/
create unique index Index_1 on 用户表 (
身份证 ASC
);

/*==============================================================*/
/* Table: 病房表                                                   */
/*==============================================================*/
create table 病房表 
(
   房号id                 int                            not null,
   床位号id                int                            not null,
   constraint PK_病房表 primary key clustered (床位号id)
);

/*==============================================================*/
/* Table: 血库表                                                   */
/*==============================================================*/
create table 血库表 
(
   血库id                 int                            not null,
   型号                   varchar(10)                    null,
   数量                   varchar(10)                    null,
   状态                   varchar(10)                    null,
   constraint PK_血库表 primary key clustered (血库id)
);

/*==============================================================*/
/* Table: 部门表                                                   */
/*==============================================================*/
create table 部门表 
(
   部门id                 int                            not null,
   部门名称                 varchar(30)                    not null,
   constraint PK_部门表 primary key clustered (部门id)
);

/*==============================================================*/
/* Index: Index_1                                               */
/*==============================================================*/
create unique index Index_1 on 部门表 (
部门名称 ASC
);

/*==============================================================*/
/* Table: 门诊表                                                   */
/*==============================================================*/
create table 门诊表 
(
   流水号                  int                            not null,
   单价                   decimal(100,2)                 null,
   数量                   int                            null,
   总金额                  decimal(100000,2)              null,
   部门id                 int                            null,
   门诊id                 int                            not null,
   constraint PK_门诊表 primary key clustered (门诊id)
);

alter table icu表
   add constraint FK_ICU表_REFERENCE_用户表 foreign key (用户id)
      references 用户表 (用户id)
      on update restrict
      on delete restrict;

alter table icu表
   add constraint FK_ICU表_REFERENCE_医护表 foreign key (人员id)
      references 医护表 (人员id)
      on update restrict
      on delete restrict;

alter table icu表
   add constraint FK_ICU表_REFERENCE_住院表 foreign key (住院id, 床位id)
      references 住院表 (住院id, 床位号id)
      on update restrict
      on delete restrict;

alter table 住院表
   add constraint FK_住院表_REFERENCE_用户表 foreign key (用户id)
      references 用户表 (用户id)
      on update restrict
      on delete restrict;

alter table 住院表
   add constraint FK_住院表_REFERENCE_病房表 foreign key (床位号id)
      references 病房表 (床位号id)
      on update restrict
      on delete restrict;

alter table 医护表
   add constraint FK_医护表_REFERENCE_部门表 foreign key (部门id)
      references 部门表 (部门id)
      on update restrict
      on delete restrict;

alter table 挂号表
   add constraint FK_挂号表_REFERENCE_医护表 foreign key (人员id)
      references 医护表 (人员id)
      on update restrict
      on delete restrict;

alter table 挂号表
   add constraint FK_挂号表_REFERENCE_部门表 foreign key (部门id)
      references 部门表 (部门id)
      on update restrict
      on delete restrict;

alter table 救护车表
   add constraint FK_救护车表_REFERENCE_医护表 foreign key (人员id)
      references 医护表 (人员id)
      on update restrict
      on delete restrict;

alter table 门诊表
   add constraint FK_门诊表_REFERENCE_部门表 foreign key (部门id)
      references 部门表 (部门id)
      on update restrict
      on delete restrict;

alter table 门诊表
   add constraint FK_门诊表_REFERENCE_挂号表 foreign key (流水号)
      references 挂号表 (流水号)
      on update restrict
      on delete restrict;