· 7 years ago · Dec 31, 2018, 02:06 AM
1-- MySQL Workbench Synchronization
2-- Generated: 2018-12-31 01:55
3-- Model: New Model
4-- Version: 1.0
5-- Project: Name of the project
6-- Author: nelson
7
8SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
9SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
10SET @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';
11
12CREATE TABLE IF NOT EXISTS `libraryRat`.`users` (
13 `userID` INT(11) NOT NULL AUTO_INCREMENT,
14 `name` VARCHAR(45) NULL DEFAULT NULL,
15 `email` VARCHAR(45) NULL DEFAULT NULL,
16 `password` VARCHAR(80) NULL DEFAULT NULL,
17 `birthdate` DATE NULL DEFAULT NULL,
18 `booksReaden` VARCHAR(45) NULL DEFAULT NULL,
19 `wishList` VARCHAR(45) NULL DEFAULT NULL,
20 `bookRegistration_userID` INT(11) NOT NULL,
21 PRIMARY KEY (`userID`),
22 INDEX `fk_users_bookRegistration_idx` (`bookRegistration_userID` ASC) VISIBLE,
23 CONSTRAINT `fk_users_bookRegistration`
24 FOREIGN KEY (`bookRegistration_userID`)
25 REFERENCES `libraryRat`.`bookRegistration` (`userID`)
26 ON DELETE NO ACTION
27 ON UPDATE NO ACTION)
28ENGINE = InnoDB
29DEFAULT CHARACTER SET = utf8;
30
31CREATE TABLE IF NOT EXISTS `libraryRat`.`books` (
32 `bookID` INT(11) NOT NULL,
33 `name` VARCHAR(80) NULL DEFAULT NULL,
34 `description` VARCHAR(200) NULL DEFAULT NULL,
35 `isbn` INT(11) NULL DEFAULT NULL,
36 `cover` VARCHAR(150) NULL DEFAULT NULL,
37 `rating` DECIMAL(50) NULL DEFAULT NULL,
38 `genres` VARCHAR(45) NULL DEFAULT NULL,
39 PRIMARY KEY (`bookID`))
40ENGINE = InnoDB
41DEFAULT CHARACTER SET = utf8;
42
43CREATE TABLE IF NOT EXISTS `libraryRat`.`author` (
44 `authorID` INT(11) NOT NULL AUTO_INCREMENT,
45 `name` VARCHAR(45) NULL DEFAULT NULL,
46 `birthdate` DATE NULL DEFAULT NULL,
47 `about` VARCHAR(500) NULL DEFAULT NULL,
48 PRIMARY KEY (`authorID`))
49ENGINE = InnoDB
50DEFAULT CHARACTER SET = utf8;
51
52CREATE TABLE IF NOT EXISTS `libraryRat`.`admin` (
53 `idAdmin` INT(11) NOT NULL AUTO_INCREMENT,
54 `name` VARCHAR(45) NULL DEFAULT NULL,
55 `password` VARCHAR(45) NULL DEFAULT NULL,
56 `approvals` VARCHAR(45) NULL DEFAULT NULL,
57 `bookApprovals_adminID` INT(11) NOT NULL,
58 PRIMARY KEY (`idAdmin`),
59 INDEX `fk_admin_bookApprovals1_idx` (`bookApprovals_adminID` ASC) VISIBLE,
60 CONSTRAINT `fk_admin_bookApprovals1`
61 FOREIGN KEY (`bookApprovals_adminID`)
62 REFERENCES `libraryRat`.`bookApprovals` (`adminID`)
63 ON DELETE NO ACTION
64 ON UPDATE NO ACTION)
65ENGINE = InnoDB
66DEFAULT CHARACTER SET = utf8;
67
68CREATE TABLE IF NOT EXISTS `libraryRat`.`ratings` (
69 `ratingID` DECIMAL(50) NOT NULL,
70 `bookID` INT(11) NOT NULL,
71 `date` DATE NULL DEFAULT NULL,
72 `rating` INT(11) NULL DEFAULT NULL,
73 `comments` VARCHAR(150) NULL DEFAULT NULL,
74 PRIMARY KEY (`ratingID`, `bookID`))
75ENGINE = InnoDB
76DEFAULT CHARACTER SET = utf8;
77
78CREATE TABLE IF NOT EXISTS `libraryRat`.`bookRegistration` (
79 `userID` INT(11) NOT NULL AUTO_INCREMENT,
80 `date` VARCHAR(45) NULL DEFAULT NULL,
81 `bookID` INT(11) NOT NULL,
82 `name` VARCHAR(45) NULL DEFAULT NULL,
83 `authorID` VARCHAR(45) NULL DEFAULT NULL,
84 `genres` VARCHAR(45) NULL DEFAULT NULL,
85 `state` VARCHAR(45) NULL DEFAULT NULL,
86 `books_bookID` INT(11) NOT NULL,
87 `isbn` VARCHAR(45) NULL DEFAULT NULL,
88 PRIMARY KEY (`userID`, `bookID`),
89 INDEX `fk_bookRegistration_books1_idx` (`books_bookID` ASC) VISIBLE,
90 CONSTRAINT `fk_bookRegistration_books1`
91 FOREIGN KEY (`books_bookID`)
92 REFERENCES `libraryRat`.`books` (`bookID`)
93 ON DELETE NO ACTION
94 ON UPDATE NO ACTION)
95ENGINE = InnoDB
96DEFAULT CHARACTER SET = utf8;
97
98CREATE TABLE IF NOT EXISTS `libraryRat`.`bookApprovals` (
99 `adminID` INT(11) NOT NULL AUTO_INCREMENT,
100 `date` VARCHAR(45) NULL DEFAULT NULL,
101 `bookID` VARCHAR(45) NULL DEFAULT NULL,
102 `name` VARCHAR(45) NULL DEFAULT NULL,
103 `authorID` VARCHAR(45) NULL DEFAULT NULL,
104 `genres` VARCHAR(45) NULL DEFAULT NULL,
105 `userID` VARCHAR(45) NULL DEFAULT NULL,
106 `state` VARCHAR(45) NULL DEFAULT NULL,
107 `bookRegistration_userID` INT(11) NOT NULL,
108 PRIMARY KEY (`adminID`),
109 INDEX `fk_bookApprovals_bookRegistration1_idx` (`bookRegistration_userID` ASC) VISIBLE,
110 CONSTRAINT `fk_bookApprovals_bookRegistration1`
111 FOREIGN KEY (`bookRegistration_userID`)
112 REFERENCES `libraryRat`.`bookRegistration` (`userID`)
113 ON DELETE NO ACTION
114 ON UPDATE NO ACTION)
115ENGINE = InnoDB
116DEFAULT CHARACTER SET = utf8;
117
118CREATE TABLE IF NOT EXISTS `libraryRat`.`books_has_ratings` (
119 `books_bookID` INT(11) NOT NULL,
120 `ratings_ratingID` DECIMAL(50) NOT NULL,
121 `ratings_bookID` INT(11) NOT NULL,
122 PRIMARY KEY (`books_bookID`, `ratings_ratingID`, `ratings_bookID`),
123 INDEX `fk_books_has_ratings_ratings1_idx` (`ratings_ratingID` ASC, `ratings_bookID` ASC) VISIBLE,
124 INDEX `fk_books_has_ratings_books_idx` (`books_bookID` ASC) VISIBLE,
125 CONSTRAINT `fk_books_has_ratings_books`
126 FOREIGN KEY (`books_bookID`)
127 REFERENCES `libraryRat`.`books` (`bookID`)
128 ON DELETE NO ACTION
129 ON UPDATE NO ACTION,
130 CONSTRAINT `fk_books_has_ratings_ratings1`
131 FOREIGN KEY (`ratings_ratingID` , `ratings_bookID`)
132 REFERENCES `libraryRat`.`ratings` (`ratingID` , `bookID`)
133 ON DELETE NO ACTION
134 ON UPDATE NO ACTION)
135ENGINE = InnoDB
136DEFAULT CHARACTER SET = utf8;
137
138CREATE TABLE IF NOT EXISTS `libraryRat`.`users_has_ratings` (
139 `users_userID` INT(11) NOT NULL,
140 `ratings_ratingID` DECIMAL(50) NOT NULL,
141 `ratings_bookID` INT(11) NOT NULL,
142 PRIMARY KEY (`users_userID`, `ratings_ratingID`, `ratings_bookID`),
143 INDEX `fk_users_has_ratings_ratings1_idx` (`ratings_ratingID` ASC, `ratings_bookID` ASC) VISIBLE,
144 INDEX `fk_users_has_ratings_users1_idx` (`users_userID` ASC) VISIBLE,
145 CONSTRAINT `fk_users_has_ratings_users1`
146 FOREIGN KEY (`users_userID`)
147 REFERENCES `libraryRat`.`users` (`userID`)
148 ON DELETE NO ACTION
149 ON UPDATE NO ACTION,
150 CONSTRAINT `fk_users_has_ratings_ratings1`
151 FOREIGN KEY (`ratings_ratingID` , `ratings_bookID`)
152 REFERENCES `libraryRat`.`ratings` (`ratingID` , `bookID`)
153 ON DELETE NO ACTION
154 ON UPDATE NO ACTION)
155ENGINE = InnoDB
156DEFAULT CHARACTER SET = utf8;
157
158CREATE TABLE IF NOT EXISTS `libraryRat`.`author_has_books` (
159 `author_authorID` INT(11) NOT NULL,
160 `books_bookID` INT(11) NOT NULL,
161 PRIMARY KEY (`author_authorID`, `books_bookID`),
162 INDEX `fk_author_has_books_books1_idx` (`books_bookID` ASC) VISIBLE,
163 INDEX `fk_author_has_books_author1_idx` (`author_authorID` ASC) VISIBLE,
164 CONSTRAINT `fk_author_has_books_author1`
165 FOREIGN KEY (`author_authorID`)
166 REFERENCES `libraryRat`.`author` (`authorID`)
167 ON DELETE NO ACTION
168 ON UPDATE NO ACTION,
169 CONSTRAINT `fk_author_has_books_books1`
170 FOREIGN KEY (`books_bookID`)
171 REFERENCES `libraryRat`.`books` (`bookID`)
172 ON DELETE NO ACTION
173 ON UPDATE NO ACTION)
174ENGINE = InnoDB
175DEFAULT CHARACTER SET = utf8;
176
177
178SET SQL_MODE=@OLD_SQL_MODE;
179SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
180SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;