· 6 years ago · Sep 29, 2019, 01:22 PM
1-- MySQL Script generated by MySQL Workbench
2-- Sun Sep 29 15:13:24 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-- -----------------------------------------------------
15-- Schema mydb
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
18USE `mydb` ;
19
20-- -----------------------------------------------------
21-- Table `mydb`.`Publishers`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`Publishers` (
24 `idPublishers` INT NOT NULL,
25 `Name` VARCHAR(45) NOT NULL,
26 PRIMARY KEY (`idPublishers`))
27ENGINE = InnoDB;
28
29
30-- -----------------------------------------------------
31-- Table `mydb`.`Languages`
32-- -----------------------------------------------------
33CREATE TABLE IF NOT EXISTS `mydb`.`Languages` (
34 `Language` VARCHAR(45) NOT NULL,
35 PRIMARY KEY (`Language`))
36ENGINE = InnoDB;
37
38
39-- -----------------------------------------------------
40-- Table `mydb`.`Series`
41-- -----------------------------------------------------
42CREATE TABLE IF NOT EXISTS `mydb`.`Series` (
43 `idSeries` INT NOT NULL AUTO_INCREMENT,
44 PRIMARY KEY (`idSeries`))
45ENGINE = InnoDB;
46
47
48-- -----------------------------------------------------
49-- Table `mydb`.`Books`
50-- -----------------------------------------------------
51CREATE TABLE IF NOT EXISTS `mydb`.`Books` (
52 `idBooks` INT NOT NULL AUTO_INCREMENT,
53 `Title` VARCHAR(45) NOT NULL,
54 `Publishers_idPublishers` INT NOT NULL,
55 `Languages_Language` VARCHAR(45) NOT NULL,
56 `publication_date` DATE NOT NULL,
57 `ISBN` VARCHAR(45) NULL,
58 `Series_idSeries` INT NOT NULL,
59 `SeriesOrder` INT NULL,
60 PRIMARY KEY (`idBooks`),
61 INDEX `fk_Books_Publishers1_idx` (`Publishers_idPublishers` ASC) VISIBLE,
62 INDEX `fk_Books_Languages1_idx` (`Languages_Language` ASC) VISIBLE,
63 INDEX `fk_Books_Series1_idx` (`Series_idSeries` ASC) VISIBLE,
64 CONSTRAINT `fk_Books_Publishers1`
65 FOREIGN KEY (`Publishers_idPublishers`)
66 REFERENCES `mydb`.`Publishers` (`idPublishers`)
67 ON DELETE NO ACTION
68 ON UPDATE NO ACTION,
69 CONSTRAINT `fk_Books_Languages1`
70 FOREIGN KEY (`Languages_Language`)
71 REFERENCES `mydb`.`Languages` (`Language`)
72 ON DELETE NO ACTION
73 ON UPDATE NO ACTION,
74 CONSTRAINT `fk_Books_Series1`
75 FOREIGN KEY (`Series_idSeries`)
76 REFERENCES `mydb`.`Series` (`idSeries`)
77 ON DELETE NO ACTION
78 ON UPDATE NO ACTION)
79ENGINE = InnoDB;
80
81
82-- -----------------------------------------------------
83-- Table `mydb`.`Inventory`
84-- -----------------------------------------------------
85CREATE TABLE IF NOT EXISTS `mydb`.`Inventory` (
86 `Books_idBooks` INT NOT NULL,
87 `idInventory` INT NOT NULL AUTO_INCREMENT,
88 `stillInInventory` TINYINT NULL DEFAULT 1,
89 INDEX `fk_Inventory_Books1_idx` (`Books_idBooks` ASC) VISIBLE,
90 PRIMARY KEY (`idInventory`),
91 CONSTRAINT `fk_Inventory_Books1`
92 FOREIGN KEY (`Books_idBooks`)
93 REFERENCES `mydb`.`Books` (`idBooks`)
94 ON DELETE NO ACTION
95 ON UPDATE NO ACTION)
96ENGINE = InnoDB;
97
98
99-- -----------------------------------------------------
100-- Table `mydb`.`Authors`
101-- -----------------------------------------------------
102CREATE TABLE IF NOT EXISTS `mydb`.`Authors` (
103 `idAuthors` INT NOT NULL AUTO_INCREMENT,
104 `Name` VARCHAR(45) NOT NULL,
105 PRIMARY KEY (`idAuthors`))
106ENGINE = InnoDB;
107
108
109-- -----------------------------------------------------
110-- Table `mydb`.`Genres`
111-- -----------------------------------------------------
112CREATE TABLE IF NOT EXISTS `mydb`.`Genres` (
113 `Name` VARCHAR(45) NOT NULL,
114 PRIMARY KEY (`Name`))
115ENGINE = InnoDB;
116
117
118-- -----------------------------------------------------
119-- Table `mydb`.`Books_has_Authors`
120-- -----------------------------------------------------
121CREATE TABLE IF NOT EXISTS `mydb`.`Books_has_Authors` (
122 `Books_idBooks` INT NOT NULL,
123 `Authors_idAuthors` INT NOT NULL,
124 PRIMARY KEY (`Books_idBooks`, `Authors_idAuthors`),
125 INDEX `fk_Books_has_Authors_Authors1_idx` (`Authors_idAuthors` ASC) VISIBLE,
126 INDEX `fk_Books_has_Authors_Books1_idx` (`Books_idBooks` ASC) VISIBLE,
127 CONSTRAINT `fk_Books_has_Authors_Books1`
128 FOREIGN KEY (`Books_idBooks`)
129 REFERENCES `mydb`.`Books` (`idBooks`)
130 ON DELETE NO ACTION
131 ON UPDATE NO ACTION,
132 CONSTRAINT `fk_Books_has_Authors_Authors1`
133 FOREIGN KEY (`Authors_idAuthors`)
134 REFERENCES `mydb`.`Authors` (`idAuthors`)
135 ON DELETE NO ACTION
136 ON UPDATE NO ACTION)
137ENGINE = InnoDB;
138
139
140-- -----------------------------------------------------
141-- Table `mydb`.`Departments`
142-- -----------------------------------------------------
143CREATE TABLE IF NOT EXISTS `mydb`.`Departments` (
144 `idDepartments` INT NOT NULL AUTO_INCREMENT,
145 `Title` VARCHAR(45) NULL,
146 PRIMARY KEY (`idDepartments`))
147ENGINE = InnoDB;
148
149
150-- -----------------------------------------------------
151-- Table `mydb`.`Programmes`
152-- -----------------------------------------------------
153CREATE TABLE IF NOT EXISTS `mydb`.`Programmes` (
154 `idProgrammes` INT NOT NULL AUTO_INCREMENT,
155 `Title` VARCHAR(45) NULL,
156 `Departments_idDepartments` INT NOT NULL,
157 PRIMARY KEY (`idProgrammes`),
158 INDEX `fk_Programmes_Departments1_idx` (`Departments_idDepartments` ASC) VISIBLE,
159 CONSTRAINT `fk_Programmes_Departments1`
160 FOREIGN KEY (`Departments_idDepartments`)
161 REFERENCES `mydb`.`Departments` (`idDepartments`)
162 ON DELETE NO ACTION
163 ON UPDATE NO ACTION)
164ENGINE = InnoDB;
165
166
167-- -----------------------------------------------------
168-- Table `mydb`.`Users`
169-- -----------------------------------------------------
170CREATE TABLE IF NOT EXISTS `mydb`.`Users` (
171 `idUsers` INT NOT NULL AUTO_INCREMENT,
172 `first_name` VARCHAR(45) NULL,
173 `last_name` VARCHAR(45) NULL,
174 `email` VARCHAR(45) NULL,
175 `phone_number` VARCHAR(45) NULL,
176 `date_of_birth` DATE NULL,
177 `address` VARCHAR(45) NULL,
178 PRIMARY KEY (`idUsers`))
179ENGINE = InnoDB;
180
181
182-- -----------------------------------------------------
183-- Table `mydb`.`Students`
184-- -----------------------------------------------------
185CREATE TABLE IF NOT EXISTS `mydb`.`Students` (
186 `Programmes_idProgrammes` INT NOT NULL,
187 `Users_idUsers` INT NOT NULL,
188 INDEX `fk_Users_Programmes1_idx` (`Programmes_idProgrammes` ASC) VISIBLE,
189 INDEX `fk_Students_User1_idx` (`Users_idUsers` ASC) VISIBLE,
190 CONSTRAINT `fk_Users_Programmes1`
191 FOREIGN KEY (`Programmes_idProgrammes`)
192 REFERENCES `mydb`.`Programmes` (`idProgrammes`)
193 ON DELETE NO ACTION
194 ON UPDATE NO ACTION,
195 CONSTRAINT `fk_Students_User1`
196 FOREIGN KEY (`Users_idUsers`)
197 REFERENCES `mydb`.`Users` (`idUsers`)
198 ON DELETE NO ACTION
199 ON UPDATE NO ACTION)
200ENGINE = InnoDB;
201
202
203-- -----------------------------------------------------
204-- Table `mydb`.`Administrators`
205-- -----------------------------------------------------
206CREATE TABLE IF NOT EXISTS `mydb`.`Administrators` (
207 `Departments_idDepartments` INT NOT NULL,
208 `Users_idUsers` INT NOT NULL,
209 INDEX `fk_Administrators_Departments1_idx` (`Departments_idDepartments` ASC) VISIBLE,
210 INDEX `fk_Administrators_User1_idx` (`Users_idUsers` ASC) VISIBLE,
211 CONSTRAINT `fk_Administrators_Departments1`
212 FOREIGN KEY (`Departments_idDepartments`)
213 REFERENCES `mydb`.`Departments` (`idDepartments`)
214 ON DELETE NO ACTION
215 ON UPDATE NO ACTION,
216 CONSTRAINT `fk_Administrators_User1`
217 FOREIGN KEY (`Users_idUsers`)
218 REFERENCES `mydb`.`Users` (`idUsers`)
219 ON DELETE NO ACTION
220 ON UPDATE NO ACTION)
221ENGINE = InnoDB;
222
223
224-- -----------------------------------------------------
225-- Table `mydb`.`Loans`
226-- -----------------------------------------------------
227CREATE TABLE IF NOT EXISTS `mydb`.`Loans` (
228 `Inventory_idInventory` INT NOT NULL,
229 `idLoans` INT NOT NULL AUTO_INCREMENT,
230 `borrow_date` DATE NOT NULL,
231 `return_date` DATE NULL,
232 `expiry_date` DATE NOT NULL,
233 `User_idUser` INT NOT NULL,
234 INDEX `fk_Loans_Inventory1_idx` (`Inventory_idInventory` ASC) VISIBLE,
235 PRIMARY KEY (`idLoans`),
236 INDEX `fk_Loans_User1_idx` (`User_idUser` ASC) VISIBLE,
237 CONSTRAINT `fk_Loans_Inventory1`
238 FOREIGN KEY (`Inventory_idInventory`)
239 REFERENCES `mydb`.`Inventory` (`idInventory`)
240 ON DELETE NO ACTION
241 ON UPDATE NO ACTION,
242 CONSTRAINT `fk_Loans_User1`
243 FOREIGN KEY (`User_idUser`)
244 REFERENCES `mydb`.`Users` (`idUsers`)
245 ON DELETE NO ACTION
246 ON UPDATE NO ACTION)
247ENGINE = InnoDB;
248
249
250-- -----------------------------------------------------
251-- Table `mydb`.`Fines`
252-- -----------------------------------------------------
253CREATE TABLE IF NOT EXISTS `mydb`.`Fines` (
254 `Loans_idLoans` INT NOT NULL,
255 `amount` INT NOT NULL,
256 `paid` DATE NULL,
257 INDEX `fk_Fines_Loans1_idx` (`Loans_idLoans` ASC) VISIBLE,
258 PRIMARY KEY (`Loans_idLoans`),
259 CONSTRAINT `fk_Fines_Loans1`
260 FOREIGN KEY (`Loans_idLoans`)
261 REFERENCES `mydb`.`Loans` (`idLoans`)
262 ON DELETE NO ACTION
263 ON UPDATE NO ACTION)
264ENGINE = InnoDB;
265
266
267-- -----------------------------------------------------
268-- Table `mydb`.`Books_has_Genres`
269-- -----------------------------------------------------
270CREATE TABLE IF NOT EXISTS `mydb`.`Books_has_Genres` (
271 `Books_idBooks` INT NOT NULL,
272 `Genres_Name` VARCHAR(45) NOT NULL,
273 PRIMARY KEY (`Books_idBooks`, `Genres_Name`),
274 INDEX `fk_Books_has_Genres_Genres1_idx` (`Genres_Name` ASC) VISIBLE,
275 INDEX `fk_Books_has_Genres_Books1_idx` (`Books_idBooks` ASC) VISIBLE,
276 CONSTRAINT `fk_Books_has_Genres_Books1`
277 FOREIGN KEY (`Books_idBooks`)
278 REFERENCES `mydb`.`Books` (`idBooks`)
279 ON DELETE NO ACTION
280 ON UPDATE NO ACTION,
281 CONSTRAINT `fk_Books_has_Genres_Genres1`
282 FOREIGN KEY (`Genres_Name`)
283 REFERENCES `mydb`.`Genres` (`Name`)
284 ON DELETE NO ACTION
285 ON UPDATE NO ACTION)
286ENGINE = InnoDB;
287
288
289SET SQL_MODE=@OLD_SQL_MODE;
290SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
291SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;