· 11 years ago · Mar 17, 2015, 04:49 PM
1EXAMEN
2
3Tabla:
4-- MySQL Script generated by MySQL Workbench
5-- 03/17/15 16:47:03
6-- Model: New Model Version: 1.0
7-- MySQL Workbench Forward Engineering
8
9SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
10SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
11SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
12
13-- -----------------------------------------------------
14-- Schema mydb
15-- -----------------------------------------------------
16
17-- -----------------------------------------------------
18-- Schema mydb
19-- -----------------------------------------------------
20CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
21USE `mydb` ;
22
23-- -----------------------------------------------------
24-- Table `mydb`.`contratos`
25-- -----------------------------------------------------
26DROP TABLE IF EXISTS `mydb`.`contratos` ;
27
28CREATE TABLE IF NOT EXISTS `mydb`.`contratos` (
29 `id_contrato` INT NOT NULL,
30 `tipo` VARCHAR(45) NULL DEFAULT NULL,
31 PRIMARY KEY (`id_contrato`))
32ENGINE = InnoDB;
33
34
35-- -----------------------------------------------------
36-- Table `mydb`.`empresa`
37-- -----------------------------------------------------
38DROP TABLE IF EXISTS `mydb`.`empresa` ;
39
40CREATE TABLE IF NOT EXISTS `mydb`.`empresa` (
41 `numempresa` INT NOT NULL,
42 `nombre` VARCHAR(45) NULL DEFAULT NULL,
43 `CIF` VARCHAR(45) NULL DEFAULT NULL,
44 `CCC` VARCHAR(45) NULL DEFAULT NULL,
45 `domicilio` VARCHAR(45) NULL DEFAULT NULL,
46 `conveniaplicable` VARCHAR(45) NULL DEFAULT NULL,
47 PRIMARY KEY (`numempresa`))
48ENGINE = InnoDB;
49
50
51-- -----------------------------------------------------
52-- Table `mydb`.`trabajadores`
53-- -----------------------------------------------------
54DROP TABLE IF EXISTS `mydb`.`trabajadores` ;
55
56CREATE TABLE IF NOT EXISTS `mydb`.`trabajadores` (
57 `id_trabajador` INT NOT NULL,
58 `nombre` VARCHAR(45) NULL DEFAULT NULL,
59 `apellidos` VARCHAR(45) NULL DEFAULT NULL,
60 `NIF` VARCHAR(45) NULL DEFAULT NULL,
61 `numsegsocial` VARCHAR(45) NULL DEFAULT NULL,
62 `grpprofessional` VARCHAR(45) NULL DEFAULT NULL,
63 `grpcotizacion` VARCHAR(45) NULL DEFAULT NULL,
64 `telefono` VARCHAR(45) NULL DEFAULT NULL,
65 `email` VARCHAR(45) NULL DEFAULT NULL,
66 `domicilio` VARCHAR(45) NULL DEFAULT NULL,
67 `empresa_numempresa` INT NOT NULL,
68 PRIMARY KEY (`id_trabajador`),
69 INDEX `fk_trabajadores_empresa1_idx` (`empresa_numempresa` ASC),
70 CONSTRAINT `fk_trabajadores_empresa1`
71 FOREIGN KEY (`empresa_numempresa`)
72 REFERENCES `mydb`.`empresa` (`numempresa`)
73 ON DELETE NO ACTION
74 ON UPDATE NO ACTION)
75ENGINE = InnoDB;
76
77
78-- -----------------------------------------------------
79-- Table `mydb`.`trabajadores_has_contratos`
80-- -----------------------------------------------------
81DROP TABLE IF EXISTS `mydb`.`trabajadores_has_contratos` ;
82
83CREATE TABLE IF NOT EXISTS `mydb`.`trabajadores_has_contratos` (
84 `trabajadores_id_trabajador` INT NOT NULL,
85 `contratos_id_contrato` INT NOT NULL,
86 `empresa_numempresa` INT NOT NULL,
87 PRIMARY KEY (`trabajadores_id_trabajador`, `contratos_id_contrato`, `empresa_numempresa`),
88 INDEX `fk_trabajadores_has_contratos_contratos1_idx` (`contratos_id_contrato` ASC),
89 INDEX `fk_trabajadores_has_contratos_trabajadores1_idx` (`trabajadores_id_trabajador` ASC),
90 INDEX `fk_trabajadores_has_contratos_empresa1_idx` (`empresa_numempresa` ASC),
91 CONSTRAINT `fk_trabajadores_has_contratos_trabajadores1`
92 FOREIGN KEY (`trabajadores_id_trabajador`)
93 REFERENCES `mydb`.`trabajadores` (`id_trabajador`)
94 ON DELETE NO ACTION
95 ON UPDATE NO ACTION,
96 CONSTRAINT `fk_trabajadores_has_contratos_contratos1`
97 FOREIGN KEY (`contratos_id_contrato`)
98 REFERENCES `mydb`.`contratos` (`id_contrato`)
99 ON DELETE NO ACTION
100 ON UPDATE NO ACTION,
101 CONSTRAINT `fk_trabajadores_has_contratos_empresa1`
102 FOREIGN KEY (`empresa_numempresa`)
103 REFERENCES `mydb`.`empresa` (`numempresa`)
104 ON DELETE NO ACTION
105 ON UPDATE NO ACTION)
106ENGINE = InnoDB;
107
108
109SET SQL_MODE=@OLD_SQL_MODE;
110SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
111SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
112
113-- -----------------------------------------------------
114-- Data for table `mydb`.`contratos`
115-- -----------------------------------------------------
116START TRANSACTION;
117USE `mydb`;
118INSERT INTO `mydb`.`contratos` (`id_contrato`, `tipo`) VALUES (01, ' \'Indefinido\'');
119INSERT INTO `mydb`.`contratos` (`id_contrato`, `tipo`) VALUES (02, ' \'Eventual\'');
120INSERT INTO `mydb`.`contratos` (`id_contrato`, `tipo`) VALUES (03, ' \'Obra/Servicio\'');
121
122COMMIT;
123
124
125-- -----------------------------------------------------
126-- Data for table `mydb`.`empresa`
127-- -----------------------------------------------------
128START TRANSACTION;
129USE `mydb`;
130INSERT INTO `mydb`.`empresa` (`numempresa`, `nombre`, `CIF`, `CCC`, `domicilio`, `conveniaplicable`) VALUES (01, ' \'ADESA\'', ' \'11111111a\'', ' \'001\'', ' \'c\\\\Poligono industrial 05 07520\'', ' \'SA\'');
131INSERT INTO `mydb`.`empresa` (`numempresa`, `nombre`, `CIF`, `CCC`, `domicilio`, `conveniaplicable`) VALUES (02, ' \'INFORTISA\'', ' \'22222222c\'', ' \'002\'', ' \'c\\\\Escorchador 12 Derecha\'', ' \'SL\'');
132
133COMMIT;
134
135
136-- -----------------------------------------------------
137-- Data for table `mydb`.`trabajadores`
138-- -----------------------------------------------------
139START TRANSACTION;
140USE `mydb`;
141INSERT INTO `mydb`.`trabajadores` (`id_trabajador`, `nombre`, `apellidos`, `NIF`, `numsegsocial`, `grpprofessional`, `grpcotizacion`, `telefono`, `email`, `domicilio`, `empresa_numempresa`) VALUES (01, ' \'Alexander\'', ' \'Fiol Myers\'', ' \'111-111-111\'', ' \'0010001\'', ' \'001\'', ' \'001\'', ' \'638105070\'', ' \'afiol@gmail.com\'', ' \'c\\\\laNSAnopuedeencontrarme n42\'', 02);
142INSERT INTO `mydb`.`trabajadores` (`id_trabajador`, `nombre`, `apellidos`, `NIF`, `numsegsocial`, `grpprofessional`, `grpcotizacion`, `telefono`, `email`, `domicilio`, `empresa_numempresa`) VALUES (02, ' \'Omi\'', ' \'Kero Gibson\'', ' \'666-666-666\'', ' \'002002\'', ' \'002\'', ' \'002\'', ' \'657842596\'', ' \'omi@kero.tv\'', ' \'c\\\\nipponica Palma de mallorca 07600\'', 02);
143INSERT INTO `mydb`.`trabajadores` (`id_trabajador`, `nombre`, `apellidos`, `NIF`, `numsegsocial`, `grpprofessional`, `grpcotizacion`, `telefono`, `email`, `domicilio`, `empresa_numempresa`) VALUES (03, ' \'Juan\'', ' \'Gas\'', ' \'222-222-222\'', ' \'003003\'', ' \'003\'', ' \'003\'', ' \'635142587\'', ' \'JGas@gmail.com\'', ' \'c\\\\Jesús 16 Palma de Mallorca 07600\'', 01);
144INSERT INTO `mydb`.`trabajadores` (`id_trabajador`, `nombre`, `apellidos`, `NIF`, `numsegsocial`, `grpprofessional`, `grpcotizacion`, `telefono`, `email`, `domicilio`, `empresa_numempresa`) VALUES (04, ' \'Paco\'', ' \'Fiestas\'', ' \'333-333-333\'', ' \'004004\'', ' \'004\'', ' \'004\'', ' \'654785125\'', ' \'PFiestas@gmail.com\'', ' \'c\\\\Plaza españa n01 07600\'', 02);
145INSERT INTO `mydb`.`trabajadores` (`id_trabajador`, `nombre`, `apellidos`, `NIF`, `numsegsocial`, `grpprofessional`, `grpcotizacion`, `telefono`, `email`, `domicilio`, `empresa_numempresa`) VALUES (05, ' \'Miguel\'', ' \'Munar\'', ' \'444-444-444\'', ' \'005005\'', ' \'005\'', ' \'005\'', ' \'678541254\'', ' \'MMunar@gmail.com\'', ' \'c\\\\Magaluf n14\'', 01);
146INSERT INTO `mydb`.`trabajadores` (`id_trabajador`, `nombre`, `apellidos`, `NIF`, `numsegsocial`, `grpprofessional`, `grpcotizacion`, `telefono`, `email`, `domicilio`, `empresa_numempresa`) VALUES (06, ' \'Ricardo\'', ' \'Stallman\'', ' \'555-555-555\'', ' \'006006\'', ' \'006\'', ' \'006\'', ' \'645781235\'', ' \'richard@stallman.ort\'', ' \'NA\'', 01);
147INSERT INTO `mydb`.`trabajadores` (`id_trabajador`, `nombre`, `apellidos`, `NIF`, `numsegsocial`, `grpprofessional`, `grpcotizacion`, `telefono`, `email`, `domicilio`, `empresa_numempresa`) VALUES (07, ' \'Anita\'', ' \'Iglesias\'', ' \'777-777-777\'', ' \'007007\'', ' \'007\'', ' \'007\'', ' \'648751535\'', ' \'AIglesias@hotmail.com\'', ' \'pl\\\\reina esclaramunda n23 07100\'', 01);
148INSERT INTO `mydb`.`trabajadores` (`id_trabajador`, `nombre`, `apellidos`, `NIF`, `numsegsocial`, `grpprofessional`, `grpcotizacion`, `telefono`, `email`, `domicilio`, `empresa_numempresa`) VALUES (08, ' \'Steve\'', ' \'Ballmer\'', ' \'888-888-888\'', ' \'008008\'', ' \'008\'', ' \'008\'', ' \'654125478\'', ' \'developers@microsoft.com\'', ' \'c\\\\Redmond 01\'', 02);
149INSERT INTO `mydb`.`trabajadores` (`id_trabajador`, `nombre`, `apellidos`, `NIF`, `numsegsocial`, `grpprofessional`, `grpcotizacion`, `telefono`, `email`, `domicilio`, `empresa_numempresa`) VALUES (09, ' \'Steve\'', ' \'Aoki\'', ' \'999-999-999\'', ' \'009\'', ' \'009\'', ' \'009\'', ' \'65487515\'', ' \'aoki@partpeople.com\'', ' \'c\\\\Paseo Maritimo 05\'', 01);
150
151COMMIT;
152
153
154-- -----------------------------------------------------
155-- Data for table `mydb`.`trabajadores_has_contratos`
156-- -----------------------------------------------------
157START TRANSACTION;
158USE `mydb`;
159INSERT INTO `mydb`.`trabajadores_has_contratos` (`trabajadores_id_trabajador`, `contratos_id_contrato`, `empresa_numempresa`) VALUES (01, 03, 02);
160INSERT INTO `mydb`.`trabajadores_has_contratos` (`trabajadores_id_trabajador`, `contratos_id_contrato`, `empresa_numempresa`) VALUES (02, 02, 02);
161INSERT INTO `mydb`.`trabajadores_has_contratos` (`trabajadores_id_trabajador`, `contratos_id_contrato`, `empresa_numempresa`) VALUES (03, 01, 01);
162INSERT INTO `mydb`.`trabajadores_has_contratos` (`trabajadores_id_trabajador`, `contratos_id_contrato`, `empresa_numempresa`) VALUES (04, 01, 02);
163INSERT INTO `mydb`.`trabajadores_has_contratos` (`trabajadores_id_trabajador`, `contratos_id_contrato`, `empresa_numempresa`) VALUES (05, 02, 01);
164INSERT INTO `mydb`.`trabajadores_has_contratos` (`trabajadores_id_trabajador`, `contratos_id_contrato`, `empresa_numempresa`) VALUES (06, 02, 01);
165INSERT INTO `mydb`.`trabajadores_has_contratos` (`trabajadores_id_trabajador`, `contratos_id_contrato`, `empresa_numempresa`) VALUES (07, 03, 01);
166INSERT INTO `mydb`.`trabajadores_has_contratos` (`trabajadores_id_trabajador`, `contratos_id_contrato`, `empresa_numempresa`) VALUES (08, 03, 02);
167INSERT INTO `mydb`.`trabajadores_has_contratos` (`trabajadores_id_trabajador`, `contratos_id_contrato`, `empresa_numempresa`) VALUES (09, 01, 01);
168INSERT INTO `mydb`.`trabajadores_has_contratos` (`trabajadores_id_trabajador`, `contratos_id_contrato`, `empresa_numempresa`) VALUES (01, 03, 02);
169INSERT INTO `mydb`.`trabajadores_has_contratos` (`trabajadores_id_trabajador`, `contratos_id_contrato`, `empresa_numempresa`) VALUES (05, 03, 02);
170INSERT INTO `mydb`.`trabajadores_has_contratos` (`trabajadores_id_trabajador`, `contratos_id_contrato`, `empresa_numempresa`) VALUES (09, 03, 02);
171
172COMMIT;
173
174
175
176
177
178
179VIEW 1:
180
181 CREATE VIEW `new_view` AS SELECT `trabajadores`.`nombre`,`trabajadores`.`apellidos`,`empresa`.`nombre`,`contratos`.`tipo`
182 FROM `contratos`,`empresa`,`trabajadores`,`trabajadores_has_contratos`
183 WHERE `trabajadores`.`id_trabajador` = `trabajadores_has_contratos`.`trabajadores_id_trabajador`
184 AND `empresa`.`numempresa` = `trabajadores_has_contratos`.`empresa_numempresa`
185 AND `contratos`.`id_contrato` = `trabajadores_has_contratos`.`contratos_id_contrato`
186 ORDER BY `empresa`.`nombre`;
187
188VIEW 2:
189
190 CREATE VIEW `new_view2` AS SELECT `trabajadores`.`nombre`,`trabajadores`.`apellidos`,`empresa`.`nombre`
191 FROM empresa,trabajadores WHERE `trabajadores`.`empresa_numempresa` = `empresa`.`numempresa` AND `empresa`.`numempresa` = 01 ;
192
193VIEW 3:
194
195 CREATE VIEW `new_view3` AS SELECT `trabajadores`.`nombre`,`trabajadores`.`apellidos`,`contratos`.`tipo`
196 FROM `contratos`,`trabajadores_has_contratos`,`trabajadores`
197 WHERE `trabajadores_has_contratos`.`contratos_id_contrato` = `contratos`.`id_contrato`
198 AND `trabajadores_has_contratos`.`trabajadores_id_trabajador` = `trabajadores`.`id_trabajador`;
199
200Son actualizables?
201
202 Según la teoria para que un view sea actualizable tiene que permitir que hagas UPDATE,DELETE o INSERT
203 para actualizar los contenidos de la tabla subyacente. Para que esto pueda ocurrir tiene que haer una relación
204 one-to-one entre las filas en el view y las filas en la tabla subyacente.
205 También perdes la posibilidad de actualizar si usas comandos como DISTINC,GROUP BY, etc.
206 Pero no viene a ser el caso.
207 Tras experimentar con el â€view 1†he podido ver que ningún update, delete o insert que haga se refleja en la tabla original de
208 dónde procede la columna del view.
209 Es decir, he actualizaro el valor de la columna nombre de la tabla empleado dónde el nombre serÃa 'Alexander' Es decir nuestra primera entrada. Tras cambiarlo por Alex comprobamos que no se ejectura correctamente y no cambia el valor de la tabla suyaciente.
210
211 El Comando UPDATE es rechazado por la configuración del workbench del MySQL que prohibe cualquier UPDATE o DELETE dónde el WHERE no apunta a ningún tuple que sea PK para evitar que si borramos a "Miguel" y tuvieramos a dos contratados, se borraran ambos, cosa que no pasarÃa si borraramos el empleado usando su ID.
212
213 UPDATE trabajadores SET nombre='Alex' WHERE id_trabajador=01;
214 SELECT * FROM trabajadores;
215
216 Pero como el view1 no tiene PKs en su declaración vamos a modificarla con insert:
217 INSERT INTO `mydb`.`new_view` (`nombre`, `apellidos`, `numempresa`, `tipo`) VALUES ('Alex','Fiol',1,'Obra/Servicio');
218 SELECT * FROM trabajadores;
219
220 Tampoco nos deja por que no podemos y nos devolverá el siguiente error:
221 Error Code: 1393. Can not modify more than one base table through a join view 'mydb.new_view'
222 Es por que el Join alberga más de una tabla.
223
224 Por lo cual el primer View no es editable.
225 Lo mismo nos pasa con el View2
226 INSERT INTO `mydb`.`new_view2` (`nombre`, `apellidos`, `numempresa`) VALUES ('Alex','Fiol',1);
227 SELECT * FROM trabajadores;
228
229 Error Code: 1393. Can not modify more than one base table through a join view 'mydb.new_view2'
230
231 Y obviamente con el view3
232 INSERT INTO `mydb`.`new_view3` (`nombre`, `apellidos`, `tipo`) VALUES ('Alex','Fiol','Obra/Servicio');
233 Error Code: 1393. Can not modify more than one base table through a join view 'mydb.new_view3'.
234
235 Asà que llegaremos a la conclusión, que ninguno de los views son actualizables a pesar de no haber usado sub-selects ni predicados del SELECT que interfieren con la alterabilidad de los views, pero sin embargo si hemos inluido más de una tabla en cada uno de nuestros views, la tabla trabajador_has_contrato, que es una tabla intermedia dónde se referencian exclusivamnete las keys.
236
237
238Transacciones:
239
240 Nueva empresa:
241 INSERT INTO `mydb`.`empresa` (`numempresa`, `nombre`, `CIF`, `CCC`, `domicilio`, `conveniaplicable`) VALUES (03, ' \'IFEBAL\'', ' \'3333333333c\'', ' \'003\'', ' \'c\\\\Poligono levante 05 07519\'', ' \'SL\'');
242
243
244
245 Nuevo empleado:
246 INSERT INTO `mydb`.`trabajadores` (`id_trabajador`, `nombre`, `apellidos`, `NIF`, `numsegsocial`, `grpprofessional`, `grpcotizacion`, `telefono`, `email`, `domicilio`, `empresa_numempresa`)
247 VALUES (10, ' \'Nikola\'', ' \'Tesla\'', ' \'10101010\'', ' \'0100100\'', ' \'010\'', ' \'010\'', ' \'635124754\'', ' \'nikola@teslamotors.com\'', ' \'c\\\\boniato rojo, n42,07510\'', 01);
248
249 Modificar un trabajador por cambio de contrato de parcial al indefinido:
250
251 UPDATE trabajadores_has_contratos
252 SET contratos_id_contrato = REPLACE (contratos_id_contrato, 3, 2)
253 WHERE trabajadores_id_trabajador = 1 AND empresa_numempresa = 2;
254
255 2 row(s) affected Rows matched: 2 Changed: 2 Warnings: 0
256
257
258 Modificar un trabajador por cambio de empresa. Ojo!(hay que tener en cuenta que al cambiar de empresa no debe perderse la información que ese trabajador ha trabajado en las dos empresas)
259
260 INSERT INTO `mydb`.`trabajadores_has_contratos` (`trabajadores_id_trabajador`, `contratos_id_contrato`, `empresa_numempresa`)
261 VALUES (01, 03, 03);
262
263 1 row(s) affected
264
265 el trabajador 1 ahora pasará a tener un contrato de tipo 3 en la empresa 3.
266 Esto queda evidenciado en que antes un SELECT * FROM trabajadores_id_trabajador nos devolvÃa 12 rows y tras correr el anterior comando nos devuelve 13.
267
268 13 row(s) returned