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