# SQL Manager 2005 for MySQL 3.7.0.1
# ---------------------------------------
# Host     : localhost
# Port     : 3306
# Database : BookManager


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES gbk */;

SET FOREIGN_KEY_CHECKS=0;

CREATE DATABASE `BookManager`
    CHARACTER SET 'utf8'
    COLLATE 'utf8_general_ci';

USE `BookManager`;

#
# Structure for the `author` table : 
#

DROP TABLE IF EXISTS `author`;

CREATE TABLE `author` (
  `AUTHOR_ID` int(11) NOT NULL auto_increment,
  `AUTHOR_NAME` varchar(255) default NULL,
  `COUNTRY` varchar(255) default NULL,
  PRIMARY KEY  (`AUTHOR_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Structure for the `catalogue` table : 
#

DROP TABLE IF EXISTS `catalogue`;

CREATE TABLE `catalogue` (
  `CATALOGUE_ID` int(11) NOT NULL auto_increment,
  `CATALOGUE_NAME` varchar(50) NOT NULL,
  PRIMARY KEY  (`CATALOGUE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Structure for the `book` table : 
#

DROP TABLE IF EXISTS `book`;

CREATE TABLE `book` (
  `BOOK_ID` int(11) NOT NULL auto_increment,
  `BOOK_NAME` VARCHAR(255) NOT NULL,
  `CATALOGUE_ID` int(11) default NULL,
  `ORIGINAL_PRESS` varchar(255) NOT NULL,
  `NEW_PRESS` varchar(255) default NULL,
  `AUTHOR` int(11) default NULL,
  `TRANSLATOR` int(11) default NULL,
  `PRICE` float default NULL,
  `BUY_PRICE` float default NULL,
  `BUY_PLACE` varchar(255) default NULL,
  `BUY_DATE` date default NULL,
  `PAGE` int(11) default NULL,
  `ISBN` varchar(255) default NULL,
  `FORMAT` varchar(10) default NULL,
  `WORD_COUNT` int(11) default NULL,
  `READ_STATUS` varchar(10) default NULL,
  `OWNER_STATUS` varchar(10) default NULL,
  PRIMARY KEY  (`BOOK_ID`),
  KEY `FK_Cat_Book` (`CATALOGUE_ID`),
  CONSTRAINT `FK_Cat_Book` FOREIGN KEY (`CATALOGUE_ID`) REFERENCES `catalogue` (`CATALOGUE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Structure for the `book_author` table : 
#

DROP TABLE IF EXISTS `book_author`;

CREATE TABLE `book_author` (
  `BOOK_ID` int(11) NOT NULL,
  `AUTHOR_ID` int(11) NOT NULL,
  PRIMARY KEY  (`BOOK_ID`,`AUTHOR_ID`),
  KEY `FK_Book_Author2` (`AUTHOR_ID`),
  CONSTRAINT `FK_Book_Author` FOREIGN KEY (`BOOK_ID`) REFERENCES `book` (`BOOK_ID`),
  CONSTRAINT `FK_Book_Author2` FOREIGN KEY (`AUTHOR_ID`) REFERENCES `author` (`AUTHOR_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Structure for the `book_translator` table : 
#

DROP TABLE IF EXISTS `book_translator`;

CREATE TABLE `book_translator` (
  `BOOK_ID` int(11) NOT NULL,
  `AUTHOR_ID` int(11) NOT NULL,
  PRIMARY KEY  (`BOOK_ID`,`AUTHOR_ID`),
  KEY `FK_Book_Translator2` (`AUTHOR_ID`),
  CONSTRAINT `FK_Book_Translator` FOREIGN KEY (`BOOK_ID`) REFERENCES `book` (`BOOK_ID`),
  CONSTRAINT `FK_Book_Translator2` FOREIGN KEY (`AUTHOR_ID`) REFERENCES `author` (`AUTHOR_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Structure for the `borrow_record` table : 
#

DROP TABLE IF EXISTS `borrow_record`;

CREATE TABLE `borrow_record` (
  `BORROW_ID` int(11) NOT NULL auto_increment,
  `BOOK_ID` int(11) default NULL,
  `BORROWER` varchar(50) default NULL,
  `BORROW_DATE` date default NULL,
  `RETURN_DATE` date default NULL,
  PRIMARY KEY  (`BORROW_ID`),
  KEY `FK_Book_BR` (`BOOK_ID`),
  CONSTRAINT `FK_Book_BR` FOREIGN KEY (`BOOK_ID`) REFERENCES `book` (`BOOK_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Structure for the `plan` table : 
#

DROP TABLE IF EXISTS `plan`;

CREATE TABLE `plan` (
  `PLAN_ID` int(11) NOT NULL auto_increment,
  `BOOK_ID` int(11) default NULL,
  `START_DATE` date default NULL,
  `END_DATE` date default NULL,
  `PAGE` int(11) default NULL,
  `ESTIMATE` int(11) default NULL,
  PRIMARY KEY  (`PLAN_ID`),
  KEY `FK_Book_Plan` (`BOOK_ID`),
  CONSTRAINT `FK_Book_Plan` FOREIGN KEY (`BOOK_ID`) REFERENCES `book` (`BOOK_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Structure for the `readrecord` table : 
#

DROP TABLE IF EXISTS `readrecord`;

CREATE TABLE `readrecord` (
  `READ_ID` int(11) NOT NULL auto_increment,
  `PLAN_ID` int(11) default NULL,
  `READ_DATE` date default NULL,
  `TIME_COST` float default NULL,
  `PAGE` int(11) default NULL,
  PRIMARY KEY  (`READ_ID`),
  KEY `FK_Plan_RR` (`PLAN_ID`),
  CONSTRAINT `FK_Plan_RR` FOREIGN KEY (`PLAN_ID`) REFERENCES `plan` (`PLAN_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Data for the `catalogue` table  (LIMIT 0,500)
#

INSERT INTO `catalogue` (`CATALOGUE_ID`, `CATALOGUE_NAME`) VALUES 
  (6,'test'),
  (7,'test1'),
  (8,'222'),
  (9,'1212'),
  (10,'3333'),
  (11,'111');

COMMIT;



/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;