· 4 years ago · May 18, 2021, 04:32 PM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @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';
6
7-- -----------------------------------------------------
8-- Schema library
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema library
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `library` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
15USE `library` ;
16
17-- -----------------------------------------------------
18-- Table `library`.`publisher`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `library`.`publisher` (
21 `Name` VARCHAR(40) NOT NULL,
22 `Address` VARCHAR(255) NULL DEFAULT NULL,
23 `Phone` CHAR(12) NULL DEFAULT NULL,
24 PRIMARY KEY (`Name`))
25ENGINE = InnoDB
26DEFAULT CHARACTER SET = utf8mb4
27COLLATE = utf8mb4_0900_ai_ci;
28
29
30-- -----------------------------------------------------
31-- Table `library`.`book`
32-- -----------------------------------------------------
33CREATE TABLE IF NOT EXISTS `library`.`book` (
34 `Book_id` CHAR(20) NOT NULL,
35 `Title` VARCHAR(40) NOT NULL,
36 `Publisher_name` VARCHAR(40) NULL DEFAULT NULL,
37 PRIMARY KEY (`Book_id`),
38 INDEX `Publisher_name` (`Publisher_name` ASC) VISIBLE,
39 CONSTRAINT `book_ibfk_1`
40 FOREIGN KEY (`Publisher_name`)
41 REFERENCES `library`.`publisher` (`Name`)
42 ON UPDATE CASCADE)
43ENGINE = InnoDB
44DEFAULT CHARACTER SET = utf8mb4
45COLLATE = utf8mb4_0900_ai_ci;
46
47
48-- -----------------------------------------------------
49-- Table `library`.`book_authors`
50-- -----------------------------------------------------
51CREATE TABLE IF NOT EXISTS `library`.`book_authors` (
52 `Book_id` CHAR(20) NOT NULL,
53 `Author_name` VARCHAR(40) NOT NULL,
54 PRIMARY KEY (`Book_id`, `Author_name`),
55 CONSTRAINT `book_authors_ibfk_1`
56 FOREIGN KEY (`Book_id`)
57 REFERENCES `library`.`book` (`Book_id`)
58 ON UPDATE CASCADE)
59ENGINE = InnoDB
60DEFAULT CHARACTER SET = utf8mb4
61COLLATE = utf8mb4_0900_ai_ci;
62
63
64-- -----------------------------------------------------
65-- Table `library`.`library_branch`
66-- -----------------------------------------------------
67CREATE TABLE IF NOT EXISTS `library`.`library_branch` (
68 `Branch_id` CHAR(8) NOT NULL,
69 `Branch_name` VARCHAR(60) NOT NULL,
70 `Address` VARCHAR(255) NOT NULL,
71 PRIMARY KEY (`Branch_id`))
72ENGINE = InnoDB
73DEFAULT CHARACTER SET = utf8mb4
74COLLATE = utf8mb4_0900_ai_ci;
75
76
77-- -----------------------------------------------------
78-- Table `library`.`book_copies`
79-- -----------------------------------------------------
80CREATE TABLE IF NOT EXISTS `library`.`book_copies` (
81 `Book_id` CHAR(20) NOT NULL,
82 `Branch_id` CHAR(8) NOT NULL,
83 `No_of_Copies` INT NULL DEFAULT NULL,
84 INDEX `Book_id` (`Book_id` ASC) VISIBLE,
85 INDEX `Branch_id` (`Branch_id` ASC) VISIBLE,
86 CONSTRAINT `book_copies_ibfk_1`
87 FOREIGN KEY (`Book_id`)
88 REFERENCES `library`.`book` (`Book_id`)
89 ON DELETE CASCADE
90 ON UPDATE CASCADE,
91 CONSTRAINT `book_copies_ibfk_2`
92 FOREIGN KEY (`Branch_id`)
93 REFERENCES `library`.`library_branch` (`Branch_id`)
94 ON DELETE CASCADE
95 ON UPDATE CASCADE)
96ENGINE = InnoDB
97DEFAULT CHARACTER SET = utf8mb4
98COLLATE = utf8mb4_0900_ai_ci;
99
100
101-- -----------------------------------------------------
102-- Table `library`.`borrower`
103-- -----------------------------------------------------
104CREATE TABLE IF NOT EXISTS `library`.`borrower` (
105 `Card_no` CHAR(20) NOT NULL,
106 `Name` VARCHAR(40) NULL DEFAULT NULL,
107 `Address` VARCHAR(255) NULL DEFAULT NULL,
108 `Phone` CHAR(12) NULL DEFAULT NULL,
109 PRIMARY KEY (`Card_no`))
110ENGINE = InnoDB
111DEFAULT CHARACTER SET = utf8mb4
112COLLATE = utf8mb4_0900_ai_ci;
113
114
115-- -----------------------------------------------------
116-- Table `library`.`book_loans`
117-- -----------------------------------------------------
118CREATE TABLE IF NOT EXISTS `library`.`book_loans` (
119 `Book_id` CHAR(20) NOT NULL,
120 `Branch_id` CHAR(8) NOT NULL,
121 `Card_no` CHAR(20) NOT NULL,
122 `Date_out` DATE NOT NULL,
123 `due_date` DATE NOT NULL,
124 PRIMARY KEY (`Book_id`, `Branch_id`, `Card_no`),
125 INDEX `Branch_id` (`Branch_id` ASC) VISIBLE,
126 INDEX `Card_no` (`Card_no` ASC) VISIBLE,
127 CONSTRAINT `book_loans_ibfk_1`
128 FOREIGN KEY (`Book_id`)
129 REFERENCES `library`.`book` (`Book_id`)
130 ON UPDATE CASCADE,
131 CONSTRAINT `book_loans_ibfk_2`
132 FOREIGN KEY (`Branch_id`)
133 REFERENCES `library`.`library_branch` (`Branch_id`)
134 ON UPDATE CASCADE,
135 CONSTRAINT `book_loans_ibfk_3`
136 FOREIGN KEY (`Card_no`)
137 REFERENCES `library`.`borrower` (`Card_no`)
138 ON UPDATE CASCADE)
139ENGINE = InnoDB
140DEFAULT CHARACTER SET = utf8mb4
141COLLATE = utf8mb4_0900_ai_ci;
142
143
144SET SQL_MODE=@OLD_SQL_MODE;
145SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
146SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
147