pBLAGYB4

1-- -----------------------------------------------------
2-- Table `book`
3-- -----------------------------------------------------
4CREATE TABLE IF NOT EXISTS `book` (
5  `id` INT(11) NOT NULL AUTO_INCREMENT,
6  `name` VARCHAR(128) COLLATE 'utf8mb4_bin' NOT NULL,
7  `publishing_company` VARCHAR(64) COLLATE 'utf8mb4_bin' NOT NULL,
8  `year_of_publication` SMALLINT(6) NOT NULL,
9  `library_id` INT(11) NOT NULL,
10  `author` VARCHAR(128) COLLATE 'utf8mb4_bin' NOT NULL,
11  PRIMARY KEY (`id`, `library_id`),
12  INDEX `fk_book_library1_idx` (`library_id` ASC),
13  CONSTRAINT `fk_book_library1`
14    FOREIGN KEY (`library_id`)
15    REFERENCES `library` (`id`))
16ENGINE = InnoDB
17AUTO_INCREMENT = 5;
18
19
20-- -----------------------------------------------------
21-- Table `book_unit`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `book_unit` (
24  `signature` BINARY(16) NOT NULL,
25  `checked_out` BIT(1) NOT NULL DEFAULT b'0',
26  `book_id` INT(11) NOT NULL,
27  PRIMARY KEY (`signature`),
28  INDEX `fk_book_unit_book1_idx` (`book_id` ASC),
29  CONSTRAINT `fk_book_unit_book1`
30    FOREIGN KEY (`book_id`)
31    REFERENCES `book` (`id`)
32    ON DELETE CASCADE
33    ON UPDATE CASCADE)
34ENGINE = InnoDB;
35
36
37-- -----------------------------------------------------
38-- Table `books_order`
39-- -----------------------------------------------------
40CREATE TABLE IF NOT EXISTS `books_order` (
41  `id` INT(11) NOT NULL AUTO_INCREMENT,
42  `created_at` DATE NOT NULL,
43  `ready_to_release` BIT(1) NOT NULL DEFAULT 0,
44  PRIMARY KEY (`id`))
45ENGINE = InnoDB;
46
47
48-- -----------------------------------------------------
49-- Table `book_order_unit`
50-- -----------------------------------------------------
51CREATE TABLE IF NOT EXISTS `book_order_unit` (
52  `id` INT(11) NOT NULL AUTO_INCREMENT,
53  `ready_to_rent` BIT(1) NOT NULL DEFAULT 0,
54  `book_signature` BINARY(16) NOT NULL,
55  `books_order_id` INT(11) NOT NULL,
56  PRIMARY KEY (`id`),
57  INDEX `fk_book_order_unit_1_idx` (`book_signature` ASC),
58  INDEX `fk_book_order_unit_2_idx` (`books_order_id` ASC),
59  CONSTRAINT `fk_book_order_unit_1`
60    FOREIGN KEY (`book_signature`)
61    REFERENCES `book_unit` (`signature`)
62    ON DELETE RESTRICT
63    ON UPDATE CASCADE,
64  CONSTRAINT `fk_book_order_unit_2`
65    FOREIGN KEY (`books_order_id`)
66    REFERENCES `books_order` (`id`)
67    ON DELETE CASCADE
68    ON UPDATE CASCADE)
69ENGINE = InnoDB;