· 7 years ago · Feb 13, 2019, 10:06 AM
1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
4
5CREATE SCHEMA IF NOT EXISTS `brainset` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci ;
6USE `brainset` ;
7
8-- -----------------------------------------------------
9-- Table `brainset`.`departamento`
10-- -----------------------------------------------------
11CREATE TABLE IF NOT EXISTS `brainset`.`departamento` (
12 `ID` TINYINT UNSIGNED NOT NULL ,
13 `departamento` VARCHAR(50) NOT NULL ,
14 PRIMARY KEY (`ID`) ,
15 UNIQUE INDEX `departamento_UNIQUE` (`departamento` ASC) )
16ENGINE = InnoDB;
17
18
19-- -----------------------------------------------------
20-- Table `brainset`.`documento_escopo`
21-- -----------------------------------------------------
22CREATE TABLE IF NOT EXISTS `brainset`.`documento_escopo` (
23 `ID` TINYINT UNSIGNED NOT NULL ,
24 `escopo` CHAR(7) NOT NULL ,
25 PRIMARY KEY (`ID`) ,
26 UNIQUE INDEX `escopo_UNIQUE` (`escopo` ASC) )
27ENGINE = InnoDB;
28
29
30-- -----------------------------------------------------
31-- Table `brainset`.`procedimento_tipo`
32-- -----------------------------------------------------
33CREATE TABLE IF NOT EXISTS `brainset`.`procedimento_tipo` (
34 `ID` TINYINT UNSIGNED NOT NULL ,
35 `tipo` VARCHAR(50) NOT NULL ,
36 PRIMARY KEY (`ID`) ,
37 UNIQUE INDEX `tipo_UNIQUE` (`tipo` ASC) )
38ENGINE = InnoDB
39COMMENT = ' ';
40
41
42-- -----------------------------------------------------
43-- Table `brainset`.`procedimento`
44-- -----------------------------------------------------
45CREATE TABLE IF NOT EXISTS `brainset`.`procedimento` (
46 `ID` SMALLINT UNSIGNED NOT NULL ,
47 `nome` VARCHAR(100) NOT NULL ,
48 `descricao` VARCHAR(1024) NOT NULL ,
49 `id_tipo` TINYINT UNSIGNED NOT NULL ,
50 `id_departamento` TINYINT UNSIGNED NOT NULL ,
51 `id_documento_complementar` INT UNSIGNED NOT NULL ,
52 PRIMARY KEY (`ID`) ,
53 INDEX `fk_procedimento-procedimento_tipo` (`id_tipo` ASC, `id_departamento` ASC) ,
54 INDEX `fk_procedimento-departamento` (`id_departamento` ASC) ,
55 CONSTRAINT `fk_procedimento-procedimento_tipo`
56 FOREIGN KEY (`id_tipo` , `id_departamento` )
57 REFERENCES `brainset`.`procedimento_tipo` (`ID` , `ID` )
58 ON DELETE NO ACTION
59 ON UPDATE NO ACTION,
60 CONSTRAINT `fk_procedimento-departamento`
61 FOREIGN KEY (`id_departamento` )
62 REFERENCES `brainset`.`departamento` (`ID` )
63 ON DELETE NO ACTION
64 ON UPDATE NO ACTION)
65ENGINE = InnoDB;
66
67
68-- -----------------------------------------------------
69-- Table `brainset`.`usuario`
70-- -----------------------------------------------------
71CREATE TABLE IF NOT EXISTS `brainset`.`usuario` (
72 `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
73 `nome` VARCHAR(100) NOT NULL ,
74 `foto` VARCHAR(200) NULL ,
75 `email` VARCHAR(45) NOT NULL ,
76 `senha` VARCHAR(45) NOT NULL ,
77 PRIMARY KEY (`ID`) )
78ENGINE = InnoDB;
79
80
81-- -----------------------------------------------------
82-- Table `brainset`.`documento`
83-- -----------------------------------------------------
84CREATE TABLE IF NOT EXISTS `brainset`.`documento` (
85 `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
86 `id_procedimento` SMALLINT UNSIGNED NOT NULL ,
87 `data` DATETIME NOT NULL ,
88 `revisao` TINYINT NOT NULL ,
89 `id_escopo` TINYINT UNSIGNED NOT NULL ,
90 `id_documento_complementar` INT UNSIGNED NULL ,
91 `id_usuario` INT UNSIGNED NOT NULL ,
92 PRIMARY KEY (`ID`) ,
93 INDEX `fk_documento-documento_escopo` (`id_escopo` ASC) ,
94 INDEX `fk_documento-procedimento` (`id_procedimento` ASC) ,
95 INDEX `fk_documento-documento` (`id_documento_complementar` ASC) ,
96 INDEX `fk_documento-usuario` (`id_usuario` ASC) ,
97 CONSTRAINT `fk_documento-documento_escopo`
98 FOREIGN KEY (`id_escopo` )
99 REFERENCES `brainset`.`documento_escopo` (`ID` )
100 ON DELETE NO ACTION
101 ON UPDATE NO ACTION,
102 CONSTRAINT `fk_documento-procedimento`
103 FOREIGN KEY (`id_procedimento` )
104 REFERENCES `brainset`.`procedimento` (`ID` )
105 ON DELETE NO ACTION
106 ON UPDATE NO ACTION,
107 CONSTRAINT `fk_documento-documento`
108 FOREIGN KEY (`id_documento_complementar` )
109 REFERENCES `brainset`.`documento` (`ID` )
110 ON DELETE NO ACTION
111 ON UPDATE NO ACTION,
112 CONSTRAINT `fk_documento-usuario`
113 FOREIGN KEY (`id_usuario` )
114 REFERENCES `brainset`.`usuario` (`ID` )
115 ON DELETE NO ACTION
116 ON UPDATE NO ACTION)
117ENGINE = InnoDB;
118
119
120-- -----------------------------------------------------
121-- Table `brainset`.`questao`
122-- -----------------------------------------------------
123CREATE TABLE IF NOT EXISTS `brainset`.`questao` (
124 `ID` INT UNSIGNED NOT NULL ,
125 `questao` VARCHAR(1024) NOT NULL ,
126 `descricao` VARCHAR(1024) NULL ,
127 `observacao` VARCHAR(1024) NULL ,
128 `data` DATETIME NOT NULL ,
129 PRIMARY KEY (`ID`) )
130ENGINE = InnoDB;
131
132
133-- -----------------------------------------------------
134-- Table `brainset`.`questao_tipo`
135-- -----------------------------------------------------
136CREATE TABLE IF NOT EXISTS `brainset`.`questao_tipo` (
137 `ID` TINYINT UNSIGNED NOT NULL ,
138 `nome` VARCHAR(45) NOT NULL ,
139 PRIMARY KEY (`ID`) )
140ENGINE = InnoDB;
141
142
143-- -----------------------------------------------------
144-- Table `brainset`.`questao_campo`
145-- -----------------------------------------------------
146CREATE TABLE IF NOT EXISTS `brainset`.`questao_campo` (
147 `ID` INT UNSIGNED NOT NULL ,
148 `id_questao` INT NOT NULL ,
149 `id_questao_tipo` TINYINT NOT NULL ,
150 `descricao` VARCHAR(1024) NULL ,
151 PRIMARY KEY (`ID`) ,
152 INDEX `fk_questao_campo-questao` (`id_questao` ASC) ,
153 INDEX `fk_questao_campo-questao-tipo` (`id_questao_tipo` ASC) ,
154 CONSTRAINT `fk_questao_campo-questao`
155 FOREIGN KEY (`id_questao` )
156 REFERENCES `brainset`.`questao` (`ID` )
157 ON DELETE CASCADE
158 ON UPDATE CASCADE,
159 CONSTRAINT `fk_questao_campo-questao-tipo`
160 FOREIGN KEY (`id_questao_tipo` )
161 REFERENCES `brainset`.`questao_tipo` (`ID` )
162 ON DELETE NO ACTION
163 ON UPDATE NO ACTION)
164ENGINE = InnoDB;
165
166
167-- -----------------------------------------------------
168-- Table `brainset`.`questao_escolha`
169-- -----------------------------------------------------
170CREATE TABLE IF NOT EXISTS `brainset`.`questao_escolha` (
171 `ID` INT UNSIGNED NOT NULL ,
172 `id_questao_campo` INT NOT NULL ,
173 `nome` VARCHAR(64) NOT NULL ,
174 `valor` VARCHAR(64) NOT NULL ,
175 PRIMARY KEY (`ID`) ,
176 INDEX `fk_questao_escolha-questao_campo` (`id_questao_campo` ASC) ,
177 CONSTRAINT `fk_questao_escolha-questao_campo`
178 FOREIGN KEY (`id_questao_campo` )
179 REFERENCES `brainset`.`questao_campo` (`ID` )
180 ON DELETE NO ACTION
181 ON UPDATE NO ACTION)
182ENGINE = InnoDB;
183
184
185-- -----------------------------------------------------
186-- Table `brainset`.`questao_resposta`
187-- -----------------------------------------------------
188CREATE TABLE IF NOT EXISTS `brainset`.`questao_resposta` (
189 `ID` INT UNSIGNED NOT NULL ,
190 `id_questao` INT NOT NULL ,
191 `resposta` VARCHAR(1024) NOT NULL ,
192 `data` DATETIME NOT NULL ,
193 PRIMARY KEY (`ID`) )
194ENGINE = InnoDB;
195
196
197-- -----------------------------------------------------
198-- Table `brainset`.`questao_consulta`
199-- -----------------------------------------------------
200CREATE TABLE IF NOT EXISTS `brainset`.`questao_consulta` (
201 `ID` INT UNSIGNED NOT NULL ,
202 `id_usuario` INT NOT NULL ,
203 `id_questao` INT NOT NULL ,
204 `id_questao_resposta` INT NOT NULL ,
205 `data` TIMESTAMP NOT NULL ,
206 PRIMARY KEY (`ID`) ,
207 INDEX `fk_questao_consulta-usuario` (`id_usuario` ASC) ,
208 INDEX `fk_questao_consulta-questao` (`id_questao` ASC) ,
209 INDEX `fk_questao_consulta-questao_resposta` (`id_questao_resposta` ASC) ,
210 CONSTRAINT `fk_questao_consulta-usuario`
211 FOREIGN KEY (`id_usuario` )
212 REFERENCES `brainset`.`usuario` (`ID` )
213 ON DELETE NO ACTION
214 ON UPDATE NO ACTION,
215 CONSTRAINT `fk_questao_consulta-questao`
216 FOREIGN KEY (`id_questao` )
217 REFERENCES `brainset`.`questao` (`ID` )
218 ON DELETE NO ACTION
219 ON UPDATE NO ACTION,
220 CONSTRAINT `fk_questao_consulta-questao_resposta`
221 FOREIGN KEY (`id_questao_resposta` )
222 REFERENCES `brainset`.`questao_resposta` (`ID` )
223 ON DELETE NO ACTION
224 ON UPDATE NO ACTION)
225ENGINE = InnoDB
226COMMENT = ' ';
227
228
229-- -----------------------------------------------------
230-- Table `brainset`.`departamento_equipe`
231-- -----------------------------------------------------
232CREATE TABLE IF NOT EXISTS `brainset`.`departamento_equipe` (
233 `ID` INT UNSIGNED NOT NULL ,
234 `id_departamento` TINYINT UNSIGNED NOT NULL ,
235 `id_usuario` INT UNSIGNED NOT NULL ,
236 PRIMARY KEY (`ID`) ,
237 INDEX `fk_departamento_equipe-departamento` (`id_departamento` ASC) ,
238 INDEX `fk_departamento_equipe-usuario` (`id_usuario` ASC) ,
239 CONSTRAINT `fk_departamento_equipe-departamento`
240 FOREIGN KEY (`id_departamento` )
241 REFERENCES `brainset`.`departamento` (`ID` )
242 ON DELETE CASCADE
243 ON UPDATE CASCADE,
244 CONSTRAINT `fk_departamento_equipe-usuario`
245 FOREIGN KEY (`id_usuario` )
246 REFERENCES `brainset`.`usuario` (`ID` )
247 ON DELETE CASCADE
248 ON UPDATE CASCADE)
249ENGINE = InnoDB;
250
251
252
253SET SQL_MODE=@OLD_SQL_MODE;
254SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
255SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;