9TFwNBfm

1-- MySQL Script generated by MySQL Workbench
2-- Mon Feb 11 16:03:55 2019
3-- Model: New Model    Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @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';
9
10-- -----------------------------------------------------
11-- Schema mydb
12-- -----------------------------------------------------
13-- -----------------------------------------------------
14-- Schema biblo
15-- -----------------------------------------------------
16DROP SCHEMA IF EXISTS `biblo` ;
17
18-- -----------------------------------------------------
19-- Schema biblo
20-- -----------------------------------------------------
21CREATE SCHEMA IF NOT EXISTS `biblo` DEFAULT CHARACTER SET latin1 ;
22USE `biblo` ;
23
24-- -----------------------------------------------------
25-- Table `biblo`.`author`
26-- -----------------------------------------------------
27DROP TABLE IF EXISTS `biblo`.`author` ;
28
29CREATE TABLE IF NOT EXISTS `biblo`.`author` (
30  `authorID` VARCHAR(255) NOT NULL,
31  `fName` VARCHAR(255) NOT NULL,
32  `lName` VARCHAR(255) NOT NULL,
33  PRIMARY KEY (`authorID`))
34ENGINE = InnoDB
35DEFAULT CHARACTER SET = latin1;
36
37
38-- -----------------------------------------------------
39-- Table `biblo`.`publisher`
40-- -----------------------------------------------------
41DROP TABLE IF EXISTS `biblo`.`publisher` ;
42
43CREATE TABLE IF NOT EXISTS `biblo`.`publisher` (
44  `publisherID` INT(10) NOT NULL,
45  `publisherName` VARCHAR(30) NULL DEFAULT NULL,
46  `publisherAdressNumber` VARCHAR(4) NULL,
47  `publisherAdress` VARCHAR(40) NULL DEFAULT NULL,
48  `publisherZipCode` VARCHAR(6) NULL,
49  `publisherPhone` VARCHAR(10) NULL DEFAULT NULL,
50  PRIMARY KEY (`publisherID`))
51ENGINE = InnoDB
52DEFAULT CHARACTER SET = latin1;
53
54
55-- -----------------------------------------------------
56-- Table `biblo`.`item`
57-- -----------------------------------------------------
58DROP TABLE IF EXISTS `biblo`.`item` ;
59
60CREATE TABLE IF NOT EXISTS `biblo`.`item` (
61  `barcode` VARCHAR(255) NOT NULL,
62  `title` VARCHAR(255) NOT NULL,
63  `location` VARCHAR(255) NOT NULL,
64  `keyword` VARCHAR(255) NOT NULL,
65  `copies` INT(10) NOT NULL,
66  `PublisherpublisherID` INT(10) NOT NULL,
67  `loanTime` VARCHAR(45) NOT NULL,
68  PRIMARY KEY (`barcode`),
69  INDEX `FKItem381172` (`PublisherpublisherID` ASC),
70  CONSTRAINT `FKItem381172`
71    FOREIGN KEY (`PublisherpublisherID`)
72    REFERENCES `biblo`.`publisher` (`publisherID`))
73ENGINE = InnoDB
74DEFAULT CHARACTER SET = latin1;
75
76
77-- -----------------------------------------------------
78-- Table `biblo`.`books`
79-- -----------------------------------------------------
80DROP TABLE IF EXISTS `biblo`.`books` ;
81
82CREATE TABLE IF NOT EXISTS `biblo`.`books` (
83  `Itembarcode` VARCHAR(255) NOT NULL,
84  `ISBN` VARCHAR(255) NOT NULL,
85  `publisher` VARCHAR(255) NOT NULL,
86  PRIMARY KEY (`Itembarcode`),
87  CONSTRAINT `FKBooks212865`
88    FOREIGN KEY (`Itembarcode`)
89    REFERENCES `biblo`.`item` (`barcode`))
90ENGINE = InnoDB
91DEFAULT CHARACTER SET = latin1;
92
93
94-- -----------------------------------------------------
95-- Table `biblo`.`book`
96-- -----------------------------------------------------
97DROP TABLE IF EXISTS `biblo`.`book` ;
98
99CREATE TABLE IF NOT EXISTS `biblo`.`book` (
100  `BooksItembarcode` VARCHAR(255) NOT NULL,
101  PRIMARY KEY (`BooksItembarcode`),
102  CONSTRAINT `FKBook291020`
103    FOREIGN KEY (`BooksItembarcode`)
104    REFERENCES `biblo`.`books` (`Itembarcode`))
105ENGINE = InnoDB
106DEFAULT CHARACTER SET = latin1;
107
108
109-- -----------------------------------------------------
110-- Table `biblo`.`bookauthor`
111-- -----------------------------------------------------
112DROP TABLE IF EXISTS `biblo`.`bookauthor` ;
113
114CREATE TABLE IF NOT EXISTS `biblo`.`bookauthor` (
115  `bookAuthorID` INT(11) NOT NULL AUTO_INCREMENT,
116  `AuthorauthorID` VARCHAR(255) NOT NULL,
117  `BooksItembarcode` VARCHAR(255) NOT NULL,
118  PRIMARY KEY (`bookAuthorID`),
119  INDEX `FKBookAuthor103252` (`AuthorauthorID` ASC),
120  INDEX `FKBookAuthor768938` (`BooksItembarcode` ASC),
121  CONSTRAINT `FKBookAuthor103252`
122    FOREIGN KEY (`AuthorauthorID`)
123    REFERENCES `biblo`.`author` (`authorID`),
124  CONSTRAINT `FKBookAuthor768938`
125    FOREIGN KEY (`BooksItembarcode`)
126    REFERENCES `biblo`.`books` (`Itembarcode`))
127ENGINE = InnoDB
128DEFAULT CHARACTER SET = latin1;
129
130
131-- -----------------------------------------------------
132-- Table `biblo`.`courseliterature`
133-- -----------------------------------------------------
134DROP TABLE IF EXISTS `biblo`.`courseliterature` ;
135
136CREATE TABLE IF NOT EXISTS `biblo`.`courseliterature` (
137  `BooksItembarcode` VARCHAR(255) NOT NULL,
138  PRIMARY KEY (`BooksItembarcode`),
139  CONSTRAINT `FKCourseLite43722`
140    FOREIGN KEY (`BooksItembarcode`)
141    REFERENCES `biblo`.`books` (`Itembarcode`))
142ENGINE = InnoDB
143DEFAULT CHARACTER SET = latin1;
144
145
146-- -----------------------------------------------------
147-- Table `biblo`.`dvd`
148-- -----------------------------------------------------
149DROP TABLE IF EXISTS `biblo`.`dvd` ;
150
151CREATE TABLE IF NOT EXISTS `biblo`.`dvd` (
152  `Itembarcode` VARCHAR(255) NOT NULL,
153  `subtitles` VARCHAR(255) NULL DEFAULT NULL,
154  `length` VARCHAR(255) NOT NULL,
155  PRIMARY KEY (`Itembarcode`),
156  CONSTRAINT `FKDVD514137`
157    FOREIGN KEY (`Itembarcode`)
158    REFERENCES `biblo`.`item` (`barcode`))
159ENGINE = InnoDB
160DEFAULT CHARACTER SET = latin1;
161
162
163-- -----------------------------------------------------
164-- Table `biblo`.`employee`
165-- -----------------------------------------------------
166DROP TABLE IF EXISTS `biblo`.`employee` ;
167
168CREATE TABLE IF NOT EXISTS `biblo`.`employee` (
169  `employeeID` VARCHAR(255) NOT NULL,
170  `manager` VARCHAR(255) NOT NULL,
171  `fName` VARCHAR(255) NOT NULL,
172  `lName` VARCHAR(255) NOT NULL,
173  PRIMARY KEY (`employeeID`))
174ENGINE = InnoDB
175DEFAULT CHARACTER SET = latin1;
176
177
178-- -----------------------------------------------------
179-- Table `biblo`.`item_admin`
180-- -----------------------------------------------------
181DROP TABLE IF EXISTS `biblo`.`item_admin` ;
182
183CREATE TABLE IF NOT EXISTS `biblo`.`item_admin` (
184  `EmployeeemployeeID` VARCHAR(255) NOT NULL,
185  `Itembarcode` VARCHAR(255) NOT NULL,
186  `action` VARCHAR(255) NOT NULL,
187  `date` DATE NOT NULL,
188  INDEX `FKEmployee_I916877` (`EmployeeemployeeID` ASC), 
189  INDEX `FKEmployee_I682992` (`Itembarcode` ASC), 
190  CONSTRAINT `FKEmployee_I682992`
191    FOREIGN KEY (`Itembarcode`)
192    REFERENCES `biblo`.`item` (`barcode`),
193  CONSTRAINT `FKEmployee_I916877`
194    FOREIGN KEY (`EmployeeemployeeID`)
195    REFERENCES `biblo`.`employee` (`employeeID`))
196ENGINE = InnoDB
197DEFAULT CHARACTER SET = latin1;
198
199
200-- -----------------------------------------------------
201-- Table `biblo`.`patron`
202-- -----------------------------------------------------
203DROP TABLE IF EXISTS `biblo`.`patron` ;
204
205CREATE TABLE IF NOT EXISTS `biblo`.`patron` (
206  `patronID` INT(11) NOT NULL,
207  `fName` VARCHAR(255) NOT NULL,
208  `lName` VARCHAR(255) NOT NULL,
209  `phone` VARCHAR(255) NULL DEFAULT NULL,
210  `email` VARCHAR(255) NOT NULL,
211  `dateOfBirth` DATE NOT NULL,
212  `borrowAmmount` VARCHAR(45) NOT NULL,
213  PRIMARY KEY (`patronID`))
214ENGINE = InnoDB
215DEFAULT CHARACTER SET = latin1;
216
217
218-- -----------------------------------------------------
219-- Table `biblo`.`loan`
220-- -----------------------------------------------------
221DROP TABLE IF EXISTS `biblo`.`loan` ;
222
223CREATE TABLE IF NOT EXISTS `biblo`.`loan` (
224  `PatronpatronID` INT(11) NOT NULL,
225  `Itembarcode` VARCHAR(255) NOT NULL,
226  `loanDate` DATETIME NULL DEFAULT NULL,
227  `returnDate` DATETIME NULL,
228  `reminderSent` INT(10) NULL DEFAULT NULL,
229  INDEX `FKLoan472753` (`PatronpatronID` ASC) ,
230  INDEX `FKLoan208313` (`Itembarcode` ASC) ,
231  CONSTRAINT `FKLoan208313`
232    FOREIGN KEY (`Itembarcode`)
233    REFERENCES `biblo`.`item` (`barcode`),
234  CONSTRAINT `FKLoan472753`
235    FOREIGN KEY (`PatronpatronID`)
236    REFERENCES `biblo`.`patron` (`patronID`))
237ENGINE = InnoDB
238DEFAULT CHARACTER SET = latin1;
239
240
241-- -----------------------------------------------------
242-- Table `biblo`.`referenceliterature`
243-- -----------------------------------------------------
244DROP TABLE IF EXISTS `biblo`.`referenceliterature` ;
245
246CREATE TABLE IF NOT EXISTS `biblo`.`referenceliterature` (
247  `BooksItembarcode` VARCHAR(255) NOT NULL,
248  PRIMARY KEY (`BooksItembarcode`),
249  CONSTRAINT `FKReferenceL29003`
250    FOREIGN KEY (`BooksItembarcode`)
251    REFERENCES `biblo`.`books` (`Itembarcode`))
252ENGINE = InnoDB
253DEFAULT CHARACTER SET = latin1;
254
255
256-- -----------------------------------------------------
257-- Table `biblo`.`researcher`
258-- -----------------------------------------------------
259DROP TABLE IF EXISTS `biblo`.`researcher` ;
260
261CREATE TABLE IF NOT EXISTS `biblo`.`researcher` (
262  `PatronpatronID` INT(11) NOT NULL,
263  PRIMARY KEY (`PatronpatronID`),
264  CONSTRAINT `FKResearcher257881`
265    FOREIGN KEY (`PatronpatronID`)
266    REFERENCES `biblo`.`patron` (`patronID`))
267ENGINE = InnoDB
268DEFAULT CHARACTER SET = latin1;
269
270
271-- -----------------------------------------------------
272-- Table `biblo`.`student`
273-- -----------------------------------------------------
274DROP TABLE IF EXISTS `biblo`.`student` ;
275
276CREATE TABLE IF NOT EXISTS `biblo`.`student` (
277  `PatronpatronID` INT(11) NOT NULL,
278  PRIMARY KEY (`PatronpatronID`),
279  CONSTRAINT `FKStudent632071`
280    FOREIGN KEY (`PatronpatronID`)
281    REFERENCES `biblo`.`patron` (`patronID`))
282ENGINE = InnoDB
283DEFAULT CHARACTER SET = latin1;
284
285
286-- -----------------------------------------------------
287-- Table `biblo`.`teacher`
288-- -----------------------------------------------------
289DROP TABLE IF EXISTS `biblo`.`teacher` ;
290
291CREATE TABLE IF NOT EXISTS `biblo`.`teacher` (
292  `PatronpatronID` INT(11) NOT NULL,
293  PRIMARY KEY (`PatronpatronID`),
294  CONSTRAINT `FKTeacher770272`
295    FOREIGN KEY (`PatronpatronID`)
296    REFERENCES `biblo`.`patron` (`patronID`))
297ENGINE = InnoDB
298DEFAULT CHARACTER SET = latin1;
299
300USE `biblo`;
301
302DELIMITER $$
303
304USE `biblo`$$
305DROP TRIGGER IF EXISTS `biblo`.`loan_BEFORE_INSERT` $$
306USE `biblo`$$
307CREATE DEFINER = CURRENT_USER TRIGGER `biblo`.`loan_BEFORE_INSERT` BEFORE INSERT ON `loan` FOR EACH ROW
308BEGIN
309	SET @patronCount=(SELECT COUNT
310	FROM loan
311    WHERE PatronpatronID = new.PatronpatronID
312    AND returnDate = null);
313    
314    IF @patronCount > 2 THEN
315		SIGNAL SQLSTATE '45000';
316		END IF;
317END$$
318
319
320DELIMITER ;
321
322SET SQL_MODE=@OLD_SQL_MODE;
323SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
324SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;