/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2020/7/25 17:37:59                           */
/*==============================================================*/


drop table if exists tb_book;

drop table if exists tb_bookborrow;

drop table if exists tb_booktype;

drop table if exists tb_case;

drop table if exists tb_library;

drop table if exists tb_logintype;

drop table if exists tb_permission;

drop table if exists tb_press;

drop table if exists tb_stock;

drop table if exists tb_user;

/*==============================================================*/
/* Table: tb_book                                               */
/*==============================================================*/
create table tb_book
(
   id                   int(11) not null,
   bookName             varchar(255) default NULL,
   bookTypeId           int(11) not null,
   bookCaseId           int(11) not null,
   bookPressId          int(11) not null,
   bookAuthor           varchar(255) default NULL,
   bookPrice            double default NULL,
   primary key (id)
)
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

/*==============================================================*/
/* Table: tb_bookborrow                                         */
/*==============================================================*/
create table tb_bookborrow
(
   id                   int(11) not null,
   bookId               int(11) not null,
   userId               int(11),
   borrowData           date not null,
   returnFlag           int(1) not null,
   returnData           date not null,
   primary key (id)
)
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*==============================================================*/
/* Table: tb_booktype                                           */
/*==============================================================*/
create table tb_booktype
(
   id                   int(11) not null auto_increment,
   typeName             varchar(255) default NULL,
   primary key (id)
)
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

/*==============================================================*/
/* Table: tb_case                                               */
/*==============================================================*/
create table tb_case
(
   id                   int(11) not null auto_increment,
   caseName             varchar(255) default NULL,
   primary key (id)
)
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

/*==============================================================*/
/* Table: tb_library                                            */
/*==============================================================*/
create table tb_library
(
   id                   int(11) not null,
   libraryName          varchar(255) default NULL,
   curator              varchar(255) default NULL,
   address              varchar(255) default NULL,
   phone                varchar(255) default NULL,
   URL                  varchar(255) default NULL,
   introduce            varchar(255) default NULL,
   primary key (id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*==============================================================*/
/* Table: tb_logintype                                          */
/*==============================================================*/
create table tb_logintype
(
   id                   int(11) not null auto_increment,
   typeName             varchar(255) default NULL,
   primary key (id)
)
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*==============================================================*/
/* Table: tb_permission                                         */
/*==============================================================*/
create table tb_permission
(
   id                   int(11) not null,
   userId               int(11),
   manager              int(1) default NULL,
   caseInfo             int(1) default NULL,
   readerType           int(11),
   readerInfo           int(1) default NULL,
   bookType             int(1) default NULL,
   bookInfo             int(1) default NULL,
   primary key (id)
)
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*==============================================================*/
/* Table: tb_press                                              */
/*==============================================================*/
create table tb_press
(
   id                   int(11) not null auto_increment,
   pressName            varchar(255) default NULL,
   pressAddress         varchar(255) default NULL,
   pressPhone           varchar(255) default NULL,
   primary key (id)
)
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

/*==============================================================*/
/* Table: tb_stock                                              */
/*==============================================================*/
create table tb_stock
(
   id                   int(11) not null,
   bookSum              int(11) default NULL,
   bookId               int(11) not null,
   primary key (id)
)
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

/*==============================================================*/
/* Table: tb_user                                               */
/*==============================================================*/
create table tb_user
(
   id                   int(11) not null,
   name                 varchar(255) default NULL,
   password             varchar(255) default NULL,
   loginTypeId          int(11) not null,
   primary key (id)
)
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

alter table tb_book add constraint FK_Reference_4 foreign key (bookTypeId)
      references tb_press (id) on delete restrict on update restrict;

alter table tb_book add constraint FK_Reference_5 foreign key (bookCaseId)
      references tb_booktype (id) on delete restrict on update restrict;

alter table tb_book add constraint FK_Reference_6 foreign key (bookPressId)
      references tb_case (id) on delete restrict on update restrict;

alter table tb_bookborrow add constraint FK_Reference_2 foreign key (bookId)
      references tb_book (id) on delete restrict on update restrict;

alter table tb_bookborrow add constraint FK_Reference_3 foreign key (userId)
      references tb_user (id) on delete restrict on update restrict;

alter table tb_permission add constraint FK_Reference_10 foreign key (bookType)
      references tb_booktype (id) on delete restrict on update restrict;

alter table tb_permission add constraint FK_Reference_8 foreign key (userId)
      references tb_user (id) on delete restrict on update restrict;

alter table tb_stock add constraint FK_Reference_9 foreign key (bookId)
      references tb_book (id) on delete restrict on update restrict;

alter table tb_user add constraint FK_Reference_7 foreign key (loginTypeId)
      references tb_logintype (id) on delete restrict on update restrict;

