DROP TABLE IF EXISTS library;
CREATE TABLE library (
  id int PRIMARY KEY AUTO_INCREMENT,
  libraryname varchar(50),
  curator varchar(30) DEFAULT '徐特立',
  tel varchar(12) DEFAULT '010-68915886',
  address varchar(100) DEFAULT '北京市海淀区',
  email varchar(100) DEFAULT 'lib@bit.edu.cn',
  url varchar(100) DEFAULT 'http://www.lib.bid.edu.cn',
  createDate varchar(10) DEFAULT '2007-09-17',
  introduce varchar(200)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table tb_library */

insert  into library(libraryname,introduce) 
	values ('徐特立图书馆','一共五层，藏书120万册。');

CREATE TABLE parameter (
  id int PRIMARY KEY  NOT NULL AUTO_INCREMENT,
  cost int DEFAULT 0,
  validity int DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert  into parameter(cost,validity) values (20,60);

DROP TABLE IF EXISTS bookcase;
CREATE TABLE bookcase (
	id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  	bookcase_name varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into bookcase(bookcase_name) values('A架'),('B架');

CREATE TABLE booktype (
  	id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  	booktype_name varchar(30) DEFAULT NULL,
  	borrow_days int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into booktype(booktype_name,borrow_days) 
	values ('计算机类',30),('文学类',35),('建筑类',20);

DROP TABLE IF EXISTS book;	
CREATE TABLE book (
	id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
	typeid int,
	bookcase int,
  	barcode varchar(8) DEFAULT NULL,
  	bookname varchar(32) DEFAULT NULL,	
  	author varchar(30) DEFAULT NULL,
  	borrow_time int DEFAULT 0,
  	FOREIGN KEY(typeid) REFERENCES booktype(id) ON UPDATE CASCADE ON DELETE CASCADE,
  	FOREIGN KEY(bookcase) REFERENCES bookcase(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into book(typeid,bookcase,barcode,bookname,author,borrow_time)
	values(2,1,'20150001','西游记','吴承恩',1),
	(2,1,'20150002','水浒传','施耐庵',2),
	(2,1,'20150003','红楼梦','曹雪芹',3),
	(2,1,'20150004','三国演义','罗贯中',4); 
insert into book(typeid,bookcase,barcode,bookname,author,ISBN,price,page)
	values(2,1,'20150004','三国演义','罗贯中','30010004',56,560); 
	
CREATE TABLE readertype (
  	id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  	readertype_name varchar(50) DEFAULT NULL,
  	readertype_number int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert  into readertype(readertype_name,readertype_number) 
	values ('学生',10),('教师',20),('其他',5);

DROP TABLE IF EXISTS reader;	
CREATE TABLE reader (
  id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  typeid int DEFAULT NULL,
  reader_name varchar(20) DEFAULT NULL,
  barcode varchar(8) DEFAULT NULL,
  id_type varchar(10) DEFAULT NULL,
  id_number varchar(18) DEFAULT NULL,
  tel varchar(11) DEFAULT NULL,
  email varchar(100) DEFAULT NULL,
  FOREIGN KEY(typeid) REFERENCES readertype(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert  into reader(reader_name,barcode,id_type,id_number,tel,email,typeid)
	values('刘备','10010001','身份证','250648199105087986','15896478523','liubei@163.com',1),
	('关羽','10010002','身份证','250648199212137986','15812138523','guanyu@163.com',2),
	('张飞','10010003','身份证','250648199303157986','15803158523','zhangfei@163.com',3);

DROP TABLE IF EXISTS manager;
CREATE TABLE manager (
  id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  username varchar(30) DEFAULT NULL,
  password varchar(30) DEFAULT NULL,
  sysset boolean DEFAULT false,
  readerset boolean DEFAULT false,
  bookset boolean DEFAULT false,
  borrowback boolean DEFAULT false,
  sysquery boolean DEFAULT false
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert  into manager(username,password) 
	values('java','1234'),('php','1234');
	
DROP TABLE IF EXISTS borrow;
CREATE TABLE borrow (
  id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  readerid int,
  bookid int,
  borrow_time varchar(10),
  back_time varchar(10) DEFAULT NULL,
  FOREIGN KEY (readerid) REFERENCES reader(id) 
  	ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (BOOKid) REFERENCES book(id) 
  	ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;