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