· 8 years ago · Jan 04, 2018, 05:32 AM
1-- MySQL Script generated by MySQL Workbench
2-- Fri Dec 29 02:31:41 2017
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='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema inspect
12-- -----------------------------------------------------
13DROP SCHEMA IF EXISTS `inspect` ;
14
15-- -----------------------------------------------------
16-- Schema inspect
17-- -----------------------------------------------------
18CREATE SCHEMA IF NOT EXISTS `inspect` DEFAULT CHARACTER SET utf8 ;
19USE `inspect` ;
20
21-- -----------------------------------------------------
22-- Table `inspect`.`User`
23-- -----------------------------------------------------
24DROP TABLE IF EXISTS `inspect`.`User` ;
25
26CREATE TABLE IF NOT EXISTS `inspect`.`User` (
27 `idUser` INT NOT NULL,
28 `name` VARCHAR(45) NOT NULL,
29 `login` VARCHAR(45) NOT NULL,
30 `password` VARCHAR(45) NOT NULL,
31 `email` VARCHAR(45) NOT NULL,
32 PRIMARY KEY (`idUser`))
33ENGINE = InnoDB;
34
35
36-- -----------------------------------------------------
37-- Table `inspect`.`Costumer`
38-- -----------------------------------------------------
39DROP TABLE IF EXISTS `inspect`.`Costumer` ;
40
41CREATE TABLE IF NOT EXISTS `inspect`.`Costumer` (
42 `idCostumer` INT NOT NULL,
43 `name` VARCHAR(45) NOT NULL,
44 `cnpj` VARCHAR(45) NOT NULL,
45 `adress` VARCHAR(45) NULL,
46 `contact` VARCHAR(45) NULL,
47 `fone` VARCHAR(45) NULL,
48 `User_idUser` INT NOT NULL,
49 PRIMARY KEY (`idCostumer`, `User_idUser`),
50 INDEX `fk_Costumer_User1_idx` (`User_idUser` ASC),
51 CONSTRAINT `fk_Costumer_User1`
52 FOREIGN KEY (`User_idUser`)
53 REFERENCES `inspect`.`User` (`idUser`)
54 ON DELETE NO ACTION
55 ON UPDATE NO ACTION)
56ENGINE = InnoDB;
57
58
59-- -----------------------------------------------------
60-- Table `inspect`.`Place`
61-- -----------------------------------------------------
62DROP TABLE IF EXISTS `inspect`.`Place` ;
63
64CREATE TABLE IF NOT EXISTS `inspect`.`Place` (
65 `idPlace` INT NOT NULL,
66 `location` VARCHAR(45) NOT NULL,
67 `name` VARCHAR(45) NOT NULL,
68 `Costumer_idCostumer` INT NOT NULL,
69 PRIMARY KEY (`idPlace`, `Costumer_idCostumer`),
70 INDEX `fk_Place_Costumer_idx` (`Costumer_idCostumer` ASC),
71 CONSTRAINT `fk_Place_Costumer`
72 FOREIGN KEY (`Costumer_idCostumer`)
73 REFERENCES `inspect`.`Costumer` (`idCostumer`)
74 ON DELETE NO ACTION
75 ON UPDATE NO ACTION)
76ENGINE = InnoDB;
77
78
79-- -----------------------------------------------------
80-- Table `inspect`.`Sector`
81-- -----------------------------------------------------
82DROP TABLE IF EXISTS `inspect`.`Sector` ;
83
84CREATE TABLE IF NOT EXISTS `inspect`.`Sector` (
85 `idSector` INT NOT NULL,
86 `Place_idPlace` INT NOT NULL,
87 `Place_Costumer_idCostumer` INT NOT NULL,
88 `name` VARCHAR(45) NOT NULL,
89 `description` VARCHAR(45) NULL,
90 PRIMARY KEY (`idSector`, `Place_idPlace`, `Place_Costumer_idCostumer`),
91 INDEX `fk_Sector_Place1_idx` (`Place_idPlace` ASC, `Place_Costumer_idCostumer` ASC),
92 CONSTRAINT `fk_Sector_Place1`
93 FOREIGN KEY (`Place_idPlace` , `Place_Costumer_idCostumer`)
94 REFERENCES `inspect`.`Place` (`idPlace` , `Costumer_idCostumer`)
95 ON DELETE NO ACTION
96 ON UPDATE NO ACTION)
97ENGINE = InnoDB;
98
99
100-- -----------------------------------------------------
101-- Table `inspect`.`Severity`
102-- -----------------------------------------------------
103DROP TABLE IF EXISTS `inspect`.`Severity` ;
104
105CREATE TABLE IF NOT EXISTS `inspect`.`Severity` (
106 `idSeverity` INT NOT NULL,
107 `description` VARCHAR(45) NULL,
108 `name` VARCHAR(45) NOT NULL,
109 PRIMARY KEY (`idSeverity`))
110ENGINE = InnoDB;
111
112
113-- -----------------------------------------------------
114-- Table `inspect`.`Deadline`
115-- -----------------------------------------------------
116DROP TABLE IF EXISTS `inspect`.`Deadline` ;
117
118CREATE TABLE IF NOT EXISTS `inspect`.`Deadline` (
119 `idDeadline` INT NOT NULL,
120 `name` VARCHAR(45) NULL,
121 `description` VARCHAR(45) NULL,
122 PRIMARY KEY (`idDeadline`))
123ENGINE = InnoDB;
124
125
126-- -----------------------------------------------------
127-- Table `inspect`.`Problem_type`
128-- -----------------------------------------------------
129DROP TABLE IF EXISTS `inspect`.`Problem_type` ;
130
131CREATE TABLE IF NOT EXISTS `inspect`.`Problem_type` (
132 `idProblem_type` INT NOT NULL,
133 `description` VARCHAR(45) NULL,
134 `name` VARCHAR(45) NOT NULL,
135 PRIMARY KEY (`idProblem_type`))
136ENGINE = InnoDB;
137
138
139-- -----------------------------------------------------
140-- Table `inspect`.`Inspection`
141-- -----------------------------------------------------
142DROP TABLE IF EXISTS `inspect`.`Inspection` ;
143
144CREATE TABLE IF NOT EXISTS `inspect`.`Inspection` (
145 `idInspection` INT NOT NULL,
146 `name` VARCHAR(45) NOT NULL,
147 `description` VARCHAR(45) NULL,
148 `Sector_idSector` INT NOT NULL,
149 `Sector_Place_idPlace` INT NOT NULL,
150 `Sector_Place_Costumer_idCostumer` INT NOT NULL,
151 `Sector_idSector1` INT NOT NULL,
152 `Sector_Place_idPlace1` INT NOT NULL,
153 `Sector_Place_Costumer_idCostumer1` INT NOT NULL,
154 PRIMARY KEY (`idInspection`, `Sector_idSector`, `Sector_Place_idPlace`, `Sector_Place_Costumer_idCostumer`, `Sector_idSector1`, `Sector_Place_idPlace1`, `Sector_Place_Costumer_idCostumer1`),
155 INDEX `fk_Inspection_Sector1_idx` (`Sector_idSector1` ASC, `Sector_Place_idPlace1` ASC, `Sector_Place_Costumer_idCostumer1` ASC),
156 CONSTRAINT `fk_Inspection_Sector1`
157 FOREIGN KEY (`Sector_idSector1` , `Sector_Place_idPlace1` , `Sector_Place_Costumer_idCostumer1`)
158 REFERENCES `inspect`.`Sector` (`idSector` , `Place_idPlace` , `Place_Costumer_idCostumer`)
159 ON DELETE NO ACTION
160 ON UPDATE NO ACTION)
161ENGINE = InnoDB;
162
163
164-- -----------------------------------------------------
165-- Table `inspect`.`Occurrence`
166-- -----------------------------------------------------
167DROP TABLE IF EXISTS `inspect`.`Occurrence` ;
168
169CREATE TABLE IF NOT EXISTS `inspect`.`Occurrence` (
170 `idOccurrence` INT NOT NULL,
171 `Sector_idSector` INT NOT NULL,
172 `Sector_Place_idPlace` INT NOT NULL,
173 `Sector_Place_Costumer_idCostumer` INT NOT NULL,
174 `description` VARCHAR(45) NOT NULL,
175 `Severity_idSeverity` INT NOT NULL,
176 `Deadline_idDeadline` INT NOT NULL,
177 `Problem_type_idProblem_type` INT NOT NULL,
178 `User_idUser` INT NOT NULL,
179 `Research_idResearch` INT NOT NULL,
180 `Research_Sector_idSector` INT NOT NULL,
181 `Research_Sector_Place_idPlace` INT NOT NULL,
182 `Research_Sector_Place_Costumer_idCostumer` INT NOT NULL,
183 PRIMARY KEY (`idOccurrence`, `Sector_idSector`, `Sector_Place_idPlace`, `Sector_Place_Costumer_idCostumer`, `Severity_idSeverity`, `Deadline_idDeadline`, `Problem_type_idProblem_type`, `User_idUser`, `Research_idResearch`, `Research_Sector_idSector`, `Research_Sector_Place_idPlace`, `Research_Sector_Place_Costumer_idCostumer`),
184 INDEX `fk_Occurrence_Severity1_idx` (`Severity_idSeverity` ASC),
185 INDEX `fk_Occurrence_Deadline1_idx` (`Deadline_idDeadline` ASC),
186 INDEX `fk_Occurrence_Problem_type1_idx` (`Problem_type_idProblem_type` ASC),
187 INDEX `fk_Occurrence_User1_idx` (`User_idUser` ASC),
188 INDEX `fk_Occurrence_Research1_idx` (`Research_idResearch` ASC, `Research_Sector_idSector` ASC, `Research_Sector_Place_idPlace` ASC, `Research_Sector_Place_Costumer_idCostumer` ASC),
189 CONSTRAINT `fk_Occurrence_Severity1`
190 FOREIGN KEY (`Severity_idSeverity`)
191 REFERENCES `inspect`.`Severity` (`idSeverity`)
192 ON DELETE NO ACTION
193 ON UPDATE NO ACTION,
194 CONSTRAINT `fk_Occurrence_Deadline1`
195 FOREIGN KEY (`Deadline_idDeadline`)
196 REFERENCES `inspect`.`Deadline` (`idDeadline`)
197 ON DELETE NO ACTION
198 ON UPDATE NO ACTION,
199 CONSTRAINT `fk_Occurrence_Problem_type1`
200 FOREIGN KEY (`Problem_type_idProblem_type`)
201 REFERENCES `inspect`.`Problem_type` (`idProblem_type`)
202 ON DELETE NO ACTION
203 ON UPDATE NO ACTION,
204 CONSTRAINT `fk_Occurrence_User1`
205 FOREIGN KEY (`User_idUser`)
206 REFERENCES `inspect`.`User` (`idUser`)
207 ON DELETE NO ACTION
208 ON UPDATE NO ACTION,
209 CONSTRAINT `fk_Occurrence_Research1`
210 FOREIGN KEY (`Research_idResearch` , `Research_Sector_idSector` , `Research_Sector_Place_idPlace` , `Research_Sector_Place_Costumer_idCostumer`)
211 REFERENCES `inspect`.`Inspection` (`idInspection` , `Sector_idSector` , `Sector_Place_idPlace` , `Sector_Place_Costumer_idCostumer`)
212 ON DELETE NO ACTION
213 ON UPDATE NO ACTION)
214ENGINE = InnoDB;
215
216
217-- -----------------------------------------------------
218-- Table `inspect`.`Solution`
219-- -----------------------------------------------------
220DROP TABLE IF EXISTS `inspect`.`Solution` ;
221
222CREATE TABLE IF NOT EXISTS `inspect`.`Solution` (
223 `idSolution` INT NOT NULL,
224 `Occurrence_idOccurrence` INT NOT NULL,
225 `Occurrence_Sector_idSector` INT NOT NULL,
226 `Occurrence_Sector_Place_idPlace` INT NOT NULL,
227 `Occurrence_Sector_Place_Costumer_idCostumer` INT NOT NULL,
228 `Occurrence_Severity_idSeverity` INT NOT NULL,
229 `Occurrence_Deadline_idDeadline` INT NOT NULL,
230 `Occurrence_Problem_type_idProblem_type` INT NOT NULL,
231 `description` VARCHAR(45) NOT NULL,
232 PRIMARY KEY (`idSolution`, `Occurrence_idOccurrence`, `Occurrence_Sector_idSector`, `Occurrence_Sector_Place_idPlace`, `Occurrence_Sector_Place_Costumer_idCostumer`, `Occurrence_Severity_idSeverity`, `Occurrence_Deadline_idDeadline`, `Occurrence_Problem_type_idProblem_type`),
233 INDEX `fk_Solution_Occurrence1_idx` (`Occurrence_idOccurrence` ASC, `Occurrence_Sector_idSector` ASC, `Occurrence_Sector_Place_idPlace` ASC, `Occurrence_Sector_Place_Costumer_idCostumer` ASC, `Occurrence_Severity_idSeverity` ASC, `Occurrence_Deadline_idDeadline` ASC, `Occurrence_Problem_type_idProblem_type` ASC),
234 CONSTRAINT `fk_Solution_Occurrence1`
235 FOREIGN KEY (`Occurrence_idOccurrence` , `Occurrence_Sector_idSector` , `Occurrence_Sector_Place_idPlace` , `Occurrence_Sector_Place_Costumer_idCostumer` , `Occurrence_Severity_idSeverity` , `Occurrence_Deadline_idDeadline` , `Occurrence_Problem_type_idProblem_type`)
236 REFERENCES `inspect`.`Occurrence` (`idOccurrence` , `Sector_idSector` , `Sector_Place_idPlace` , `Sector_Place_Costumer_idCostumer` , `Severity_idSeverity` , `Deadline_idDeadline` , `Problem_type_idProblem_type`)
237 ON DELETE NO ACTION
238 ON UPDATE NO ACTION)
239ENGINE = InnoDB;
240
241
242-- -----------------------------------------------------
243-- Table `inspect`.`Image`
244-- -----------------------------------------------------
245DROP TABLE IF EXISTS `inspect`.`Image` ;
246
247CREATE TABLE IF NOT EXISTS `inspect`.`Image` (
248 `Imagecol` VARCHAR(45) NOT NULL,
249 `path` VARCHAR(45) NOT NULL,
250 `Occurrence_idOccurrence` INT NOT NULL,
251 `Occurrence_Sector_idSector` INT NOT NULL,
252 `Occurrence_Sector_Place_idPlace` INT NOT NULL,
253 `Occurrence_Sector_Place_Costumer_idCostumer` INT NOT NULL,
254 `Occurrence_Severity_idSeverity` INT NOT NULL,
255 `Occurrence_Deadline_idDeadline` INT NOT NULL,
256 `Occurrence_Problem_type_idProblem_type` INT NOT NULL,
257 `Occurrence_User_idUser` INT NOT NULL,
258 `Occurrence_Research_idResearch` INT NOT NULL,
259 `Occurrence_Research_Sector_idSector` INT NOT NULL,
260 `Occurrence_Research_Sector_Place_idPlace` INT NOT NULL,
261 `Occurrence_Research_Sector_Place_Costumer_idCostumer` INT NOT NULL,
262 `Solution_idSolution` INT NOT NULL,
263 `Solution_Occurrence_idOccurrence` INT NOT NULL,
264 `Solution_Occurrence_Sector_idSector` INT NOT NULL,
265 `Solution_Occurrence_Sector_Place_idPlace` INT NOT NULL,
266 `Solution_Occurrence_Sector_Place_Costumer_idCostumer` INT NOT NULL,
267 `Solution_Occurrence_Severity_idSeverity` INT NOT NULL,
268 `Solution_Occurrence_Deadline_idDeadline` INT NOT NULL,
269 `Solution_Occurrence_Problem_type_idProblem_type` INT NOT NULL,
270 PRIMARY KEY (`Imagecol`),
271 INDEX `fk_Image_Occurrence1_idx` (`Occurrence_idOccurrence` ASC, `Occurrence_Sector_idSector` ASC, `Occurrence_Sector_Place_idPlace` ASC, `Occurrence_Sector_Place_Costumer_idCostumer` ASC, `Occurrence_Severity_idSeverity` ASC, `Occurrence_Deadline_idDeadline` ASC, `Occurrence_Problem_type_idProblem_type` ASC, `Occurrence_User_idUser` ASC, `Occurrence_Research_idResearch` ASC, `Occurrence_Research_Sector_idSector` ASC, `Occurrence_Research_Sector_Place_idPlace` ASC, `Occurrence_Research_Sector_Place_Costumer_idCostumer` ASC),
272 INDEX `fk_Image_Solution1_idx` (`Solution_idSolution` ASC, `Solution_Occurrence_idOccurrence` ASC, `Solution_Occurrence_Sector_idSector` ASC, `Solution_Occurrence_Sector_Place_idPlace` ASC, `Solution_Occurrence_Sector_Place_Costumer_idCostumer` ASC, `Solution_Occurrence_Severity_idSeverity` ASC, `Solution_Occurrence_Deadline_idDeadline` ASC, `Solution_Occurrence_Problem_type_idProblem_type` ASC),
273 CONSTRAINT `fk_Image_Occurrence1`
274 FOREIGN KEY (`Occurrence_idOccurrence` , `Occurrence_Sector_idSector` , `Occurrence_Sector_Place_idPlace` , `Occurrence_Sector_Place_Costumer_idCostumer` , `Occurrence_Severity_idSeverity` , `Occurrence_Deadline_idDeadline` , `Occurrence_Problem_type_idProblem_type` , `Occurrence_User_idUser` , `Occurrence_Research_idResearch` , `Occurrence_Research_Sector_idSector` , `Occurrence_Research_Sector_Place_idPlace` , `Occurrence_Research_Sector_Place_Costumer_idCostumer`)
275 REFERENCES `inspect`.`Occurrence` (`idOccurrence` , `Sector_idSector` , `Sector_Place_idPlace` , `Sector_Place_Costumer_idCostumer` , `Severity_idSeverity` , `Deadline_idDeadline` , `Problem_type_idProblem_type` , `User_idUser` , `Research_idResearch` , `Research_Sector_idSector` , `Research_Sector_Place_idPlace` , `Research_Sector_Place_Costumer_idCostumer`)
276 ON DELETE NO ACTION
277 ON UPDATE NO ACTION,
278 CONSTRAINT `fk_Image_Solution1`
279 FOREIGN KEY (`Solution_idSolution` , `Solution_Occurrence_idOccurrence` , `Solution_Occurrence_Sector_idSector` , `Solution_Occurrence_Sector_Place_idPlace` , `Solution_Occurrence_Sector_Place_Costumer_idCostumer` , `Solution_Occurrence_Severity_idSeverity` , `Solution_Occurrence_Deadline_idDeadline` , `Solution_Occurrence_Problem_type_idProblem_type`)
280 REFERENCES `inspect`.`Solution` (`idSolution` , `Occurrence_idOccurrence` , `Occurrence_Sector_idSector` , `Occurrence_Sector_Place_idPlace` , `Occurrence_Sector_Place_Costumer_idCostumer` , `Occurrence_Severity_idSeverity` , `Occurrence_Deadline_idDeadline` , `Occurrence_Problem_type_idProblem_type`)
281 ON DELETE NO ACTION
282 ON UPDATE NO ACTION)
283ENGINE = InnoDB;
284
285SET SQL_MODE = '';
286GRANT USAGE ON *.* TO inspectUser;
287 DROP USER inspectUser;
288SET SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
289CREATE USER 'inspectUser' IDENTIFIED BY '@123';
290
291GRANT ALL ON `inspect`.* TO 'inspectUser';
292GRANT SELECT ON TABLE `inspect`.* TO 'inspectUser';
293GRANT SELECT, INSERT, TRIGGER ON TABLE `inspect`.* TO 'inspectUser';
294GRANT SELECT, INSERT, TRIGGER, UPDATE, DELETE ON TABLE `inspect`.* TO 'inspectUser';
295
296
297SET SQL_MODE=@OLD_SQL_MODE;
298SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
299SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;