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