· 6 years ago · Dec 30, 2019, 11:50 PM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10-- -----------------------------------------------------
11-- Schema it
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema it
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `it` DEFAULT CHARACTER SET latin1 ;
18USE `it` ;
19
20-- -----------------------------------------------------
21-- Table `it`.`categorias`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `it`.`categorias` (
24 `idCategoria` INT(11) NOT NULL AUTO_INCREMENT,
25 `Nombre` VARCHAR(30) NOT NULL,
26 `Hijo_de` INT(11) NULL DEFAULT '0',
27 PRIMARY KEY (`idCategoria`))
28ENGINE = InnoDB
29AUTO_INCREMENT = 8
30DEFAULT CHARACTER SET = utf8;
31
32
33-- -----------------------------------------------------
34-- Table `it`.`marcas`
35-- -----------------------------------------------------
36CREATE TABLE IF NOT EXISTS `it`.`marcas` (
37 `idMarca` INT(11) NOT NULL AUTO_INCREMENT,
38 `NombreMarca` VARCHAR(30) NOT NULL,
39 PRIMARY KEY (`idMarca`))
40ENGINE = InnoDB
41AUTO_INCREMENT = 15
42DEFAULT CHARACTER SET = utf8;
43
44
45-- -----------------------------------------------------
46-- Table `it`.`productos`
47-- -----------------------------------------------------
48CREATE TABLE IF NOT EXISTS `it`.`productos` (
49 `idProducto` INT(11) NOT NULL AUTO_INCREMENT,
50 `Nombre` VARCHAR(30) NOT NULL,
51 `Precio` DOUBLE NOT NULL,
52 `Marca` INT(11) NOT NULL,
53 `Categoria` INT(11) NOT NULL,
54 `Presentacion` VARCHAR(30) NOT NULL,
55 `Stock` INT(6) NOT NULL,
56 `Imagen` TINYTEXT NULL DEFAULT NULL,
57 PRIMARY KEY (`idProducto`),
58 INDEX `Marca` (`Marca` ASC),
59 INDEX `Rubro` (`Categoria` ASC),
60 CONSTRAINT `productos_ibfk_1`
61 FOREIGN KEY (`Marca`)
62 REFERENCES `it`.`marcas` (`idMarca`)
63 ON DELETE CASCADE
64 ON UPDATE CASCADE,
65 CONSTRAINT `productos_ibfk_2`
66 FOREIGN KEY (`Categoria`)
67 REFERENCES `it`.`categorias` (`idCategoria`)
68 ON DELETE CASCADE
69 ON UPDATE CASCADE)
70ENGINE = InnoDB
71AUTO_INCREMENT = 7
72DEFAULT CHARACTER SET = utf8;
73
74
75-- -----------------------------------------------------
76-- Table `it`.`usuarios`
77-- -----------------------------------------------------
78CREATE TABLE IF NOT EXISTS `it`.`usuarios` (
79 `idUsuario` INT(11) NOT NULL AUTO_INCREMENT,
80 `Nombre` VARCHAR(30) NOT NULL,
81 `Apellido` VARCHAR(30) NOT NULL,
82 `Email` VARCHAR(30) NOT NULL,
83 `Pass` TEXT NOT NULL,
84 `Activacion` TEXT NOT NULL,
85 `Estado` TINYINT(1) NULL DEFAULT '0',
86 PRIMARY KEY (`idUsuario`),
87 UNIQUE INDEX `Email` (`Email` ASC))
88ENGINE = InnoDB
89AUTO_INCREMENT = 2
90DEFAULT CHARACTER SET = utf8;
91
92
93-- -----------------------------------------------------
94-- Table `it`.`institutos`
95-- -----------------------------------------------------
96CREATE TABLE IF NOT EXISTS `it`.`institutos` (
97 `idtable1` INT NOT NULL,
98 `nombre` VARCHAR(45) NOT NULL,
99 PRIMARY KEY (`idtable1`))
100ENGINE = InnoDB;
101
102
103-- -----------------------------------------------------
104-- Table `it`.`categorias1`
105-- -----------------------------------------------------
106CREATE TABLE IF NOT EXISTS `it`.`categorias1` (
107 `idcategorias` INT NOT NULL,
108 `nombre` VARCHAR(45) NOT NULL,
109 PRIMARY KEY (`idcategorias`))
110ENGINE = InnoDB;
111
112
113-- -----------------------------------------------------
114-- Table `it`.`subcategorias`
115-- -----------------------------------------------------
116CREATE TABLE IF NOT EXISTS `it`.`subcategorias` (
117 `idsubcategorias` INT NOT NULL,
118 `nombre` VARCHAR(45) NOT NULL,
119 `idcategorias` INT NULL,
120 PRIMARY KEY (`idsubcategorias`),
121 INDEX `FKidcategorias_idx` (`idcategorias` ASC),
122 CONSTRAINT `FKidcategorias`
123 FOREIGN KEY (`idcategorias`)
124 REFERENCES `it`.`categorias1` (`idcategorias`)
125 ON DELETE NO ACTION
126 ON UPDATE NO ACTION)
127ENGINE = InnoDB;
128
129
130-- -----------------------------------------------------
131-- Table `it`.`proyectos`
132-- -----------------------------------------------------
133CREATE TABLE IF NOT EXISTS `it`.`proyectos` (
134 `idproyectos` INT NOT NULL,
135 `fechainicio` VARCHAR(45) NOT NULL,
136 `fechafin` VARCHAR(45) NOT NULL,
137 `aprobado` TINYINT NULL,
138 PRIMARY KEY (`idproyectos`))
139ENGINE = InnoDB;
140
141
142-- -----------------------------------------------------
143-- Table `it`.`roles`
144-- -----------------------------------------------------
145CREATE TABLE IF NOT EXISTS `it`.`roles` (
146 `idroles` INT NOT NULL,
147 `nombre` VARCHAR(45) NOT NULL,
148 PRIMARY KEY (`idroles`))
149ENGINE = InnoDB;
150
151
152-- -----------------------------------------------------
153-- Table `it`.`participantes`
154-- -----------------------------------------------------
155CREATE TABLE IF NOT EXISTS `it`.`participantes` (
156 `idparticipantes` INT NOT NULL,
157 `idrol` INT NOT NULL,
158 `idproyectos` INT NOT NULL,
159 `dni` INT NOT NULL,
160 PRIMARY KEY (`idparticipantes`),
161 INDEX `fkidrol_idx` (`idrol` ASC),
162 CONSTRAINT `fkidrol`
163 FOREIGN KEY (`idrol`)
164 REFERENCES `it`.`roles` (`idroles`)
165 ON DELETE CASCADE
166 ON UPDATE CASCADE)
167ENGINE = InnoDB;
168
169
170-- -----------------------------------------------------
171-- Table `it`.`nacionalidad`
172-- -----------------------------------------------------
173CREATE TABLE IF NOT EXISTS `it`.`nacionalidad` (
174 `idnacionalidad` INT NOT NULL,
175 `nacionalidad` VARCHAR(45) NOT NULL,
176 PRIMARY KEY (`idnacionalidad`))
177ENGINE = InnoDB;
178
179
180-- -----------------------------------------------------
181-- Table `it`.`resolucion`
182-- -----------------------------------------------------
183CREATE TABLE IF NOT EXISTS `it`.`resolucion` (
184 `idresolucion` INT NOT NULL,
185 `titulo` VARCHAR(45) NULL,
186 `descripcion` VARCHAR(200) NULL,
187 PRIMARY KEY (`idresolucion`))
188ENGINE = InnoDB;
189
190
191-- -----------------------------------------------------
192-- Table `it`.`investigadores`
193-- -----------------------------------------------------
194CREATE TABLE IF NOT EXISTS `it`.`investigadores` (
195 `DNI` INT NOT NULL,
196 `Nombre` VARCHAR(45) NULL,
197 `cod_instituto` INT NULL,
198 `idsubcategoria` INT NULL,
199 `telefono` VARCHAR(45) NULL,
200 `domicilio` VARCHAR(45) NULL,
201 `legajo` INT NULL,
202 `cod_nacionalidad` INT NULL,
203 `cod_proyecto` INT NULL,
204 `cod_resolucion` INT NULL,
205 PRIMARY KEY (`DNI`),
206 INDEX `FK_instituto_idx` (`cod_instituto` ASC),
207 INDEX `FK_subcategoria_idx` (`idsubcategoria` ASC),
208 INDEX `FK_nacionalidad_idx` (`cod_nacionalidad` ASC),
209 INDEX `FK_resolucion_idx` (`cod_resolucion` ASC),
210 CONSTRAINT `FK_instituto`
211 FOREIGN KEY (`cod_instituto`)
212 REFERENCES `it`.`institutos` (`idtable1`)
213 ON DELETE CASCADE
214 ON UPDATE CASCADE,
215 CONSTRAINT `FK_subcategoria`
216 FOREIGN KEY (`idsubcategoria`)
217 REFERENCES `it`.`subcategorias` (`idsubcategorias`)
218 ON DELETE NO ACTION
219 ON UPDATE NO ACTION,
220 CONSTRAINT `FK_nacionalidad`
221 FOREIGN KEY (`cod_nacionalidad`)
222 REFERENCES `it`.`nacionalidad` (`idnacionalidad`)
223 ON DELETE NO ACTION
224 ON UPDATE NO ACTION,
225 CONSTRAINT `FK_resolucion`
226 FOREIGN KEY (`cod_resolucion`)
227 REFERENCES `it`.`resolucion` (`idresolucion`)
228 ON DELETE NO ACTION
229 ON UPDATE NO ACTION)
230ENGINE = InnoDB;
231
232
233SET SQL_MODE=@OLD_SQL_MODE;
234SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
235SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;