/*==============================================================*/
/* DBMS name:      Sybase AS Anywhere 8                         */
/* Created on:     2007-4-27 10:16:21                           */
/*==============================================================*/


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

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

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

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

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

if exists(
   select 1 from sys.systable 
   where table_name='BOOK'
     and table_type in ('BASE', 'GBL TEMP')
) then
    drop table BOOK
end if;

if exists(
   select 1 from sys.systable 
   where table_name='BORROWBOOK'
     and table_type in ('BASE', 'GBL TEMP')
) then
    drop table BORROWBOOK
end if;

if exists(
   select 1 from sys.systable 
   where table_name='POS'
     and table_type in ('BASE', 'GBL TEMP')
) then
    drop table POS
end if;

if exists(
   select 1 from sys.systable 
   where table_name='READER'
     and table_type in ('BASE', 'GBL TEMP')
) then
    drop table READER
end if;

if exists(
   select 1 from sys.systable 
   where table_name='RETURNBOOK'
     and table_type in ('BASE', 'GBL TEMP')
) then
    drop table RETURNBOOK
end if;

if exists(
   select 1 from sys.systable 
   where table_name='STUFF'
     and table_type in ('BASE', 'GBL TEMP')
) then
    drop table STUFF
end if;

/*==============================================================*/
/* Table: BOOK                                                  */
/*==============================================================*/
create table BOOK 
(
    ISBN                 varchar(20)                    not null,
    BOOK_NAME            varchar(50),
    WRITER               varchar(20),
    PUBLISHER            varchar(50),
    BOOK_NUM             int,
    BOOK_STATE           int,
    NOW_NUM              int,
    primary key (ISBN)
);

comment on table BOOK is 
'狀態：
  1-  可以出借
   0--不能出借';

/*==============================================================*/
/* Table: BORROWBOOK                                            */
/*==============================================================*/
create table BORROWBOOK 
(
    借出流水號                char(10)                       not null,
    MEMBER_ID            char(7),
    ISBN                 varchar(20),
    OUT_DATE             date,
    primary key (借出流水號)
);

/*==============================================================*/
/* Table: POS                                                   */
/*==============================================================*/
create table POS 
(
    POS_ID               int                            not null,
    POS_NAME             varchar(20),
    primary key (POS_ID)
);

/*==============================================================*/
/* Table: READER                                                */
/*==============================================================*/
create table READER 
(
    MEMBER_ID            char(7)                        not null,
    NAME                 varchar(20),
    DEPARTMENT           varchar(20),
    TYPE                 int,
    STATE                int,
    DAYS                 int,
    primary key (MEMBER_ID)
);

comment on table READER is 
'類型：1-學生，2教師
狀態：1-可借，2-已到達可借上限，3-有超期圖書
ID:學生由S，教師由T開頭
可借期限:以天為單位
';

/*==============================================================*/
/* Table: RETURNBOOK                                            */
/*==============================================================*/
create table RETURNBOOK 
(
    RETURN_ID            char(10)                       not null,
    ISBN                 varchar(20),
    MEMBER_ID            char(7),
    OUT_DATE             date,
    RETURN_DATE          date,
    primary key (RETURN_ID)
);

/*==============================================================*/
/* Table: STUFF                                                 */
/*==============================================================*/
create table STUFF 
(
    USER_ID              int                            not null,
    POS_ID               int,
    USER_NAME            varchar(20),
    PASSWORD             varchar(8),
    primary key (USER_ID)
);

alter table BORROWBOOK
   add foreign key FK_BORROWBO_REFERENCE_READER (MEMBER_ID)
      references READER (MEMBER_ID)
      on update restrict
      on delete restrict;

alter table BORROWBOOK
   add foreign key FK_BORROWBO_REFERENCE_BOOK (ISBN)
      references BOOK (ISBN)
      on update restrict
      on delete restrict;

alter table RETURNBOOK
   add foreign key FK_RETURNBO_REFERENCE_BOOK (ISBN)
      references BOOK (ISBN)
      on update restrict
      on delete restrict;

alter table RETURNBOOK
   add foreign key FK_RETURNBO_REFERENCE_READER (MEMBER_ID)
      references READER (MEMBER_ID)
      on update restrict
      on delete restrict;

alter table STUFF
   add foreign key FK_STUFF_REFERENCE_POS (POS_ID)
      references POS (POS_ID)
      on update restrict
      on delete restrict;