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