ZQnZJKWx

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