gL4t3q31

1CREATE SCHEMA IF NOT EXISTS `mybookshop` DEFAULT CHARACTER SET utf8 ;
2USE `mybookshop` ;
3
4-- -----------------------------------------------------
5-- Table `mybookshop`.`book`
6-- -----------------------------------------------------
7CREATE TABLE IF NOT EXISTS `mybookshop`.`book` (
8  `idbook` INT NOT NULL AUTO_INCREMENT,
9  `title` VARCHAR(100) NOT NULL COMMENT 'Название книги',
10  `author` VARCHAR(255) NOT NULL COMMENT 'Автор книги',
11  `descrition` VARCHAR(5000) NULL COMMENT 'Описание книги',
12  `pubYear` YEAR NULL COMMENT 'Год издания',
13  `price` DECIMAL(6,2) NOT NULL DEFAULT 0.0 COMMENT 'Цена книги',
14  `genre` SET('классика', 'компьютерная', 'фантастика', 'детектив', 'ужасы') NULL DEFAULT 'классика',
15  PRIMARY KEY (`idbook`))
16ENGINE = InnoDB;
17
18
19-- -----------------------------------------------------
20-- Table `mybookshop`.`customer`
21-- -----------------------------------------------------
22CREATE TABLE IF NOT EXISTS `mybookshop`.`customer` (
23  `idcustomer` INT NOT NULL AUTO_INCREMENT,
24  `fio` VARCHAR(100) NOT NULL,
25  `phone` CHAR(10) NOT NULL,
26  `email` VARCHAR(45) NULL,
27  `gender` ENUM('M', 'F') NULL,
28  `bd` DATE NULL,
29  PRIMARY KEY (`idcustomer`))
30ENGINE = InnoDB;
31
32
33-- -----------------------------------------------------
34-- Table `mybookshop`.`order`
35-- -----------------------------------------------------
36CREATE TABLE IF NOT EXISTS `mybookshop`.`order` (
37  `idorder` INT NOT NULL AUTO_INCREMENT,
38  `dateorder` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
39  `address` VARCHAR(100) NOT NULL,
40  `status` ENUM('выполнен', 'обработка', 'принят', 'отказ') NOT NULL DEFAULT 'принят',
41  `customer_idcustomer` INT NOT NULL,
42  PRIMARY KEY (`idorder`),
43  INDEX `fk_order_customer1_idx` (`customer_idcustomer` ASC),
44  CONSTRAINT `fk_order_customer1`
45    FOREIGN KEY (`customer_idcustomer`)
46    REFERENCES `mybookshop`.`customer` (`idcustomer`)
47    ON DELETE NO ACTION
48    ON UPDATE NO ACTION)
49ENGINE = InnoDB;
50
51
52-- -----------------------------------------------------
53-- Table `mybookshop`.`order_has_book`
54-- -----------------------------------------------------
55CREATE TABLE IF NOT EXISTS `mybookshop`.`order_has_book` (
56  `order_idorder` INT NOT NULL,
57  `book_idbook` INT NOT NULL,
58  `q` TINYINT NULL DEFAULT 1,
59  PRIMARY KEY (`order_idorder`, `book_idbook`),
60  INDEX `fk_order_has_book_book1_idx` (`book_idbook` ASC),
61  INDEX `fk_order_has_book_order1_idx` (`order_idorder` ASC),
62  CONSTRAINT `fk_order_has_book_order1`
63    FOREIGN KEY (`order_idorder`)
64    REFERENCES `mybookshop`.`order` (`idorder`)
65    ON DELETE NO ACTION
66    ON UPDATE NO ACTION,
67  CONSTRAINT `fk_order_has_book_book1`
68    FOREIGN KEY (`book_idbook`)
69    REFERENCES `mybookshop`.`book` (`idbook`)
70    ON DELETE NO ACTION
71    ON UPDATE NO ACTION)
72ENGINE = InnoDB;