libDDL.sql

-- MySQL Script generated by MySQL Workbench
-- Wed Jun 22 22:24:13 2022
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema library
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema library
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `library` DEFAULT CHARACTER SET utf8 ;
USE `library` ;

-- -----------------------------------------------------
-- Table `library`.`User`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `library`.`User` (
  `userID` VARCHAR(20) NOT NULL,
  `userPsd` VARCHAR(30) NOT NULL,
  `fine` INT NOT NULL DEFAULT 0,
  `dayNum` INT NOT NULL DEFAULT 30,
  PRIMARY KEY (`userID`))
ENGINE = InnoDB;

alter table user add constraint `user_fk1` 
	check(fine>=0);

-- -----------------------------------------------------
-- Table `library`.`Reader`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `library`.`Reader` (
  `IDnum` VARCHAR(18) NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  `gender` VARCHAR(20) NOT NULL,
  `telnum` VARCHAR(15) NOT NULL,
  `birthday` DATE NOT NULL,
  `User_userID` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`IDnum`, `User_userID`),
  UNIQUE INDEX `IDnum_UNIQUE` (`IDnum` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `library`.`Book`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `library`.`Book` (
  `bookID` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(50) NOT NULL,
  `ISBN` VARCHAR(60) NOT NULL,
  `writer` VARCHAR(50) NOT NULL,
  `publisher` VARCHAR(50) NOT NULL,
  `pubDate` DATE NOT NULL,
  `price` INT NOT NULL,
  `bookNum` INT NOT NULL,
  `BookLib_floor` VARCHAR(10) NOT NULL,
  `BookLib_room` VARCHAR(10) NOT NULL,
  `BookLib_shelf` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`bookID`, `BookLib_floor`, `BookLib_room`, `BookLib_shelf`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `library`.`BookLib`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `library`.`BookLib` (
  `floor` VARCHAR(10) NOT NULL,
  `room` VARCHAR(10) NOT NULL,
  `shelf` VARCHAR(10) NOT NULL,
  `libTelnum` VARCHAR(15) NOT NULL,
  PRIMARY KEY (`floor`, `room`, `shelf`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `library`.`Administrator`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `library`.`Administrator` (
  `adminID` VARCHAR(20) NOT NULL,
  `adminPsd` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`adminID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `library`.`Book-Borrow`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS bookBorrow(
  `User_userID` VARCHAR(20) NOT NULL,
  `Book_bookID` INT NOT NULL,
  `b_beginDate` DATE NOT NULL,
  `b_endDate` DATE NOT NULL,
  PRIMARY KEY (`User_userID`, `Book_bookID`),
  INDEX `fk_User_has_Book_Book1_idx` (`Book_bookID` ASC) VISIBLE,
  INDEX `fk_User_has_Book_User1_idx` (`User_userID` ASC) VISIBLE,
  CONSTRAINT `fk_User_has_Book_User1`
    FOREIGN KEY (`User_userID`)
    REFERENCES `library`.`User` (`userID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_User_has_Book_Book1`
    FOREIGN KEY (`Book_bookID`)
    REFERENCES `library`.`Book` (`bookID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

INSERT INTO administrator values('admin1','123456');

INSERT INTO booklib values('1','101','A','63350001');
INSERT INTO booklib values('1','101','B','63350001');
INSERT INTO booklib values('2','201','A','63350002');
INSERT INTO booklib values('2','201','B','63350002');
INSERT INTO booklib values('2','202','A','63350003');
INSERT INTO booklib values('3','301','A','63350004');
INSERT INTO booklib values('3','301','B','63350004');
INSERT INTO booklib values('3','302','A','63350005');
INSERT INTO booklib values('3','302','B','63350005');
INSERT INTO booklib values('3','302','C','63350005');


INSERT INTO book values(1,'数据库系统概念(第六版)','978-7-1113-7529-6','Korth','机械工业出版社','2012-04-01',99,10,'1','101','A');
INSERT INTO book values(null,'细说PHP','978-7-1213-7062-5','高浩峰','电子工业出版社','2019-07-01',159,15,'1','101','A');
INSERT INTO book values(null,"深入理解计算机系统(第三版)",'978-7-1115-4493-7','E.Bryant','机械工业出版社','2016-12-01',139,20,'1','101','B');
INSERT INTO book values(null,'计算机组成原理(第三版)','978-7-0405-4518-0','唐朔飞','高等教育出版社','2020-10-01',50,20,'1','101','B');
INSERT INTO book values(null,'万历十五年','978-7-1080-0982-1','黄仁宇','三联书店','2006-01-01',26,10,'2','201','A');
INSERT INTO book values(null,'隳三都：蒙古灭金围城史','978-7-2031-1659-2','周思成','山西人民出版社','2021-01-01',60,5,'2','201','A');
INSERT INTO book values(null,'资治通鉴','978-7-5194-0522-9','司马光','光明日报出版社','2016-04-01',800,2,'2','201','B');
INSERT INTO book values(null,'汉书','978-7-5120-0010-0','班固','线装书局','2010-11-01',598,5,'2','202','A');
INSERT INTO book values(null,'大清十二帝','978-7-5113-4623-0','尹子','中国华侨出版社','2020-07-01',68,5,'3','301','A');
INSERT INTO book values(null,'朱元璋传','978-7-5561-1894-6','吴晗著','湖南文艺出版社','2018-02-01',45,10,'3','301','A');
INSERT INTO book values(null,'基督山伯爵','978-7-5190-0480-4','大仲马 ','中国文联出版社','2015-09-01',45,3,'3','301','B');
INSERT INTO book values(null,'百年孤独','978-7-5442-9117-0','马尔克斯','南海出版公司','2017-08-01',55,2,'3','302','A');
INSERT INTO book values(null,'一九八四','978-7-5327-4799-3','奥威尔','上海译文出版社','2009-06-01',45,1,'3','302','A');
INSERT INTO book values(null,'大话数据结构','978-7-3025-6471-3','程杰','清华大学出版社','2020-09-01',119,5,'3','302','B');
INSERT INTO book values(null,'柯林斯COBUILD高阶英汉双解学习词典','978-7-5135-8311-4','英国柯林斯出版公司',' 外语教学与研究出版社','2017-01-01',169,10,'3','302','C');
INSERT INTO book values(null,'现代汉语词典','978-7-1001-2450-8','语言研究所词典编辑室','商务印书馆','2016-09-01',109,10,'3','302','C');

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;