· 8 years ago · Apr 23, 2018, 10: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 Sample79
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema Sample79
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `Sample79` DEFAULT CHARACTER SET utf8 ;
15USE `Sample79` ;
16
17-- -----------------------------------------------------
18-- Table `Sample79`.`Publisher`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `Sample79`.`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 `Sample79`.`Book`
31-- -----------------------------------------------------
32CREATE TABLE IF NOT EXISTS `Sample79`.`Book` (
33 `BookId` VARCHAR(20) NOT NULL,
34 `Title` VARCHAR(20) NULL,
35 `Publisher_Name` VARCHAR(20) NULL,
36 PRIMARY KEY (`BookId`),
37 UNIQUE INDEX `BookId_UNIQUE` (`BookId` ASC),
38 INDEX `book_publisher_fk_idx` (`Publisher_Name` ASC),
39 CONSTRAINT `book_publisher_fk`
40 FOREIGN KEY (`Publisher_Name`)
41 REFERENCES `Sample79`.`Publisher` (`Name`)
42 ON DELETE NO ACTION
43 ON UPDATE NO ACTION)
44ENGINE = InnoDB;
45
46ALTER TABLE BOOK MODIFY COLUMN BookId VARCHAR(20);
47
48-- -----------------------------------------------------
49-- Table `Sample79`.`Library_branch`
50-- -----------------------------------------------------
51CREATE TABLE IF NOT EXISTS `Sample79`.`Library_branch` (
52 `BranchId` VARCHAR(20) NOT NULL,
53 `BranchName` VARCHAR(20) NULL,
54 `Address` VARCHAR(20) NULL,
55 PRIMARY KEY (`BranchId`),
56 UNIQUE INDEX `BranchId_UNIQUE` (`BranchId` ASC))
57ENGINE = InnoDB;
58
59
60-- -----------------------------------------------------
61-- Table `Sample79`.`Book_copies`
62-- -----------------------------------------------------
63CREATE TABLE IF NOT EXISTS `Sample79`.`Book_copies` (
64 `BookId` VARCHAR(20) NOT NULL,
65 `BranchId` VARCHAR(20) NOT NULL,
66 `No_Of_Copies` INT NULL,
67 PRIMARY KEY (`BookId`, `BranchId`),
68 INDEX `book_copies_library_branch_idx` (`BranchId` ASC),
69 CONSTRAINT `book_copies_book`
70 FOREIGN KEY (`BookId`)
71 REFERENCES `Sample79`.`Book` (`BookId`)
72 ON DELETE NO ACTION
73 ON UPDATE NO ACTION,
74 CONSTRAINT `book_copies_library_branch`
75 FOREIGN KEY (`BranchId`)
76 REFERENCES `Sample79`.`Library_branch` (`BranchId`)
77 ON DELETE NO ACTION
78 ON UPDATE NO ACTION)
79ENGINE = InnoDB;
80
81
82-- -----------------------------------------------------
83-- Table `Sample79`.`Borrower`
84-- -----------------------------------------------------
85CREATE TABLE IF NOT EXISTS `Sample79`.`Borrower` (
86 `CardNo` VARCHAR(20) NOT NULL,
87 `Name` VARCHAR(20) NULL,
88 `Address` VARCHAR(20) NULL,
89 `Phone` VARCHAR(20) NULL,
90 PRIMARY KEY (`CardNo`),
91 UNIQUE INDEX `CardNo_UNIQUE` (`CardNo` ASC))
92ENGINE = InnoDB;
93
94
95-- -----------------------------------------------------
96-- Table `Sample79`.`Book_loans`
97-- -----------------------------------------------------
98CREATE TABLE IF NOT EXISTS `Sample79`.`Book_loans` (
99 `BookId` VARCHAR(20) NOT NULL,
100 `BranchId` VARCHAR(20) NOT NULL,
101 `CardNo` VARCHAR(20) NOT NULL,
102 `DateOut` DATE NULL,
103 `DueDatel` DATE NULL,
104 PRIMARY KEY (`BookId`, `BranchId`, `CardNo`),
105 INDEX `book_loans_library_branch_idx` (`BranchId` ASC),
106 INDEX `book_loans_borrower_idx` (`CardNo` ASC),
107 CONSTRAINT `book_loans_ book`
108 FOREIGN KEY (`BookId`)
109 REFERENCES `Sample79`.`Book` (`BookId`)
110 ON DELETE NO ACTION
111 ON UPDATE NO ACTION,
112 CONSTRAINT `book_loans_library_branch`
113 FOREIGN KEY (`BranchId`)
114 REFERENCES `Sample79`.`Library_branch` (`BranchId`)
115 ON DELETE NO ACTION
116 ON UPDATE NO ACTION,
117 CONSTRAINT `book_loans_borrower`
118 FOREIGN KEY (`CardNo`)
119 REFERENCES `Sample79`.`Borrower` (`CardNo`)
120 ON DELETE NO ACTION
121 ON UPDATE NO ACTION)
122ENGINE = InnoDB;
123
124
125-- -----------------------------------------------------
126-- Table `Sample79`.`Book_Authors`
127-- -----------------------------------------------------
128CREATE TABLE IF NOT EXISTS `Sample79`.`Book_Authors` (
129 `BookId` VARCHAR(20) NOT NULL,
130 `AuthorName` VARCHAR(20) NOT NULL,
131 PRIMARY KEY (`BookId`, `AuthorName`),
132 CONSTRAINT `book_authors_book_fk`
133 FOREIGN KEY (`BookId`)
134 REFERENCES `Sample79`.`Book` (`BookId`)
135 ON DELETE NO ACTION
136 ON UPDATE NO ACTION)
137ENGINE = InnoDB;
138
139
140LOAD DATA LOCAL INFILE '/Users/User/Desktop/borrower.txt'
141INTO TABLE BORROWER FIELDS TERMINATED BY ',' LINES STARTING BY '#';
142
143LOAD DATA LOCAL INFILE '/Users/User/Desktop/LIBRARY_BRANCH.txt'
144INTO TABLE LIBRARY_BRANCH FIELDS TERMINATED BY ',' LINES STARTING BY '#';
145
146LOAD DATA LOCAL INFILE '/Users/User/Desktop/publisher.txt'
147INTO TABLE publisher FIELDS TERMINATED BY ',' LINES STARTING BY '#';
148
149LOAD DATA LOCAL INFILE '/Users/User/Desktop/BOOK.txt'
150INTO TABLE BOOK FIELDS TERMINATED BY ',' LINES STARTING BY '#';
151
152LOAD DATA LOCAL INFILE '/Users/User/Desktop/BOOK_AUTHORS.txt'
153INTO TABLE BOOK_AUTHORS FIELDS TERMINATED BY ',' LINES STARTING BY '#';
154
155LOAD DATA LOCAL INFILE '/Users/User/Desktop/BOOK_COPIES.txt'
156INTO TABLE BOOK_COPIES FIELDS TERMINATED BY ',' LINES STARTING BY '#';
157
158LOAD DATA LOCAL INFILE '/Users/User/Desktop/BOOK_LOANS.txt'
159INTO TABLE BOOK_LOANS FIELDS TERMINATED BY ',' LINES STARTING BY '#';
160
161
162SET SQL_MODE=@OLD_SQL_MODE;
163SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
164SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
165
166SHOW CREATE USER 'SAMPLE'@'localhost';