· 4 years ago · May 18, 2021, 12:00 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 SAMPLE23_48
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema SAMPLE23_48
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `SAMPLE23_48` DEFAULT CHARACTER SET utf8 ;
15USE `SAMPLE23_48` ;
16
17-- -----------------------------------------------------
18-- Table `SAMPLE23_48`.`PUBLISHER`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `SAMPLE23_48`.`PUBLISHER` (
21 `Name` VARCHAR(45) NOT NULL,
22 `Address` VARCHAR(100) NULL,
23 `Phone` VARCHAR(45) NULL,
24 PRIMARY KEY (`Name`))
25ENGINE = InnoDB;
26
27
28-- -----------------------------------------------------
29-- Table `SAMPLE23_48`.`BOOK`
30-- -----------------------------------------------------
31CREATE TABLE IF NOT EXISTS `SAMPLE23_48`.`BOOK` (
32 `BookId` INT NOT NULL,
33 `Title` VARCHAR(45) NULL,
34 `PublisherName` VARCHAR(45) NOT NULL,
35 PRIMARY KEY (`BookId`),
36 UNIQUE INDEX `BookId_UNIQUE` (`BookId` ASC) VISIBLE,
37 INDEX `PublisherName_idx` (`PublisherName` ASC) VISIBLE,
38 CONSTRAINT `fk_book_publisher_publishername`
39 FOREIGN KEY (`PublisherName`)
40 REFERENCES `SAMPLE23_48`.`PUBLISHER` (`Name`)
41 ON DELETE CASCADE
42 ON UPDATE CASCADE)
43ENGINE = InnoDB;
44
45
46-- -----------------------------------------------------
47-- Table `SAMPLE23_48`.`LIBRARY_BRANCH`
48-- -----------------------------------------------------
49CREATE TABLE IF NOT EXISTS `SAMPLE23_48`.`LIBRARY_BRANCH` (
50 `BranchId` INT NOT NULL,
51 `BranchName` VARCHAR(45) NULL,
52 `Address` VARCHAR(45) NULL,
53 PRIMARY KEY (`BranchId`))
54ENGINE = InnoDB;
55
56
57-- -----------------------------------------------------
58-- Table `SAMPLE23_48`.`BOOK_COPIES`
59-- -----------------------------------------------------
60CREATE TABLE IF NOT EXISTS `SAMPLE23_48`.`BOOK_COPIES` (
61 `BookId` INT NOT NULL,
62 `BranchId` INT NOT NULL,
63 `No_Of_Copies` INT NULL,
64 INDEX `BranchId_idx` (`BranchId` ASC) VISIBLE,
65 PRIMARY KEY (`BookId`, `BranchId`),
66 CONSTRAINT `fk_book_copies_book_bookid`
67 FOREIGN KEY (`BookId`)
68 REFERENCES `SAMPLE23_48`.`BOOK` (`BookId`)
69 ON DELETE CASCADE
70 ON UPDATE CASCADE,
71 CONSTRAINT `fk_book_copies_library_branch_branchid`
72 FOREIGN KEY (`BranchId`)
73 REFERENCES `SAMPLE23_48`.`LIBRARY_BRANCH` (`BranchId`)
74 ON DELETE CASCADE
75 ON UPDATE CASCADE)
76ENGINE = InnoDB;
77
78
79-- -----------------------------------------------------
80-- Table `SAMPLE23_48`.`BOOK_AUTHORS`
81-- -----------------------------------------------------
82CREATE TABLE IF NOT EXISTS `SAMPLE23_48`.`BOOK_AUTHORS` (
83 `BookId` INT NOT NULL,
84 `AuthorName` VARCHAR(45) NOT NULL,
85 PRIMARY KEY (`AuthorName`, `BookId`),
86 CONSTRAINT `fk_book_authors_book_bookid`
87 FOREIGN KEY (`BookId`)
88 REFERENCES `SAMPLE23_48`.`BOOK` (`BookId`)
89 ON DELETE CASCADE
90 ON UPDATE CASCADE)
91ENGINE = InnoDB;
92
93
94-- -----------------------------------------------------
95-- Table `SAMPLE23_48`.`BORROWER`
96-- -----------------------------------------------------
97CREATE TABLE IF NOT EXISTS `SAMPLE23_48`.`BORROWER` (
98 `CardNo` INT NOT NULL,
99 `Name` VARCHAR(45) NULL,
100 `Address` VARCHAR(45) NULL,
101 `Phone` VARCHAR(45) NULL,
102 PRIMARY KEY (`CardNo`))
103ENGINE = InnoDB;
104
105
106-- -----------------------------------------------------
107-- Table `SAMPLE23_48`.`BOOK_LOANS`
108-- -----------------------------------------------------
109CREATE TABLE IF NOT EXISTS `SAMPLE23_48`.`BOOK_LOANS` (
110 `BookId` INT NOT NULL,
111 `BranchId` INT NOT NULL,
112 `CardNo` INT NOT NULL,
113 `DataOut` DATETIME NULL,
114 `DueDate` DATETIME NULL,
115 INDEX `BranchId_idx` (`BranchId` ASC) VISIBLE,
116 INDEX `CardNo_idx` (`CardNo` ASC) VISIBLE,
117 PRIMARY KEY (`BookId`, `BranchId`, `CardNo`),
118 CONSTRAINT `fk_book_loans_book_bookid`
119 FOREIGN KEY (`BookId`)
120 REFERENCES `SAMPLE23_48`.`BOOK` (`BookId`)
121 ON DELETE CASCADE
122 ON UPDATE CASCADE,
123 CONSTRAINT `fk_book_loans_library_branch_branchid`
124 FOREIGN KEY (`BranchId`)
125 REFERENCES `SAMPLE23_48`.`LIBRARY_BRANCH` (`BranchId`)
126 ON DELETE CASCADE
127 ON UPDATE CASCADE,
128 CONSTRAINT `fk_book_loans_borrower_cardno`
129 FOREIGN KEY (`CardNo`)
130 REFERENCES `SAMPLE23_48`.`BORROWER` (`CardNo`)
131 ON DELETE CASCADE
132 ON UPDATE CASCADE)
133ENGINE = InnoDB;
134
135
136SET SQL_MODE=@OLD_SQL_MODE;
137SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
138SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
139