sql, reglas de validación y programación.pdf

8/11/2019 SQL, reglas de validación y programación.pdf

http://slidepdf.com/reader/full/sql-reglas-de-validacion-y-programacionpdf 1/20

Nombre de la materia

Bases de datos.

Nombre de la Licenciatura

Ing. En sistemas computacionales.

Nombre del alumno

Luis Eduardo Chávez Gamboa

Matrícula000005491

Nombre de la Tarea

Operaciones SQL, ejercicios. S6act1.

Unidad #

Unidad 5. SQL, reglas de validación y

programación.

Nombre del Tutor

Hermenegildo Cisneros Carillo.

Fecha

22 de febrero de 2014



Unidad 5: Reglas de validación y programación.

Bases de datos. S6act1.

2

REGLAS DE VALIDACIÓN Y PROGRAMACIÓN. EJERCICIOS.

Instrucciones.

Realizar la siguiente programación:

PROGRAMACION INSTRUCCIONES

Constraints En cada tabla (PROPIETARIO, EQUIPO, VIAJE, etc), debesgenerar:

Llaves primarias “PRIMARY KEY”

Llaves foráneas “FOREING KEY” Clave únicas “UNIQUE”

Vistas Crear dos vistas: CLIENTE_VIAJE, que contenga las siguientes columnas

codigo_cliente, codigo_viaje, codigo_tripulante, valor_viaje yfecha_registro.

HELICOPTERO_MANTENIMIENTO_REPARACION, quecontenga las siguientes tablas codigo_helicoptero, codigo_mantenimiento, valor_mantenimiento,codigo_reparacion, valor_reparacion y fecha_registro

Triggers Antes de crear el trigger en cuestión, debes crear una tabla denombre REPORTE_REPARACION (genera los constrainscorrespondientes) que contenga las siguientes columnas:codigo_helicoptero, codigo_tripulante (del último viaje),

codigo_viaje (del último viaje) y fecha_registro. Crear un trigger que se dispare en el momento en que se

ingrese un registro a la tabla REPARACION convalor_reparacion mayor o igual a $ 15, 000, la información sedebe tomar de las tablas HELICOPTERO, TRIPULACION,VIAJE y REPARACION.

Stored procedures Debes generar un stored procedures que tome la información deunas tablas para generar la información de las vistasanteriormente creadas:

Vista CLIENTE_VIAJE, que contenga la informacióncodigo_cliente, nombre de la tabla CLIENTE y la informacióncodigo_viaje, codigo_tripulante, valor_viaje y fecha_registro de

la tabla VIAJE, esto del último mes. Vista HELICOPTERO_MANTENIMIENTO_REPARACION, que

contenga la información de codigo_helicoptero,

codigo_mantenimiento, valor_mantenimiento,

codigo_reparacion, valor_reparacion y fecha_registro, esto delúltimo mes.





3

Ejercicios.

Constraints:Partiendo de que ninguna de las tablas tiene asignados claves primarias, foráneas y

únicas:

A todas las tablas se le asigna como clave primaria, no nulo y única al atributo

código_XXX (imagen 1):

ALTER TABLE `test`.`cliente`CHANGE COLUMN `codigo_cliente` `codigo_cliente` VARCHAR(15) NOT NULL,ADD PRIMARY KEY (`codigo_cliente`),ADD UNIQUE INDEX `codigo_cliente_UNIQUE` (`codigo_cliente` ASC);

ALTER TABLE `test`.`equipo`CHANGE COLUMN `codigo_equipo` `codigo_equipo` VARCHAR(15) NOT NULL,ADD PRIMARY KEY (`codigo_equipo`),ADD UNIQUE INDEX `codigo_equipo_UNIQUE` (`codigo_equipo` ASC);

ALTER TABLE `test`.`helicoptero`CHANGE COLUMN `codigo_helicoptero` `codigo_helicoptero` VARCHAR(15) NOT NULL,ADD PRIMARY KEY (`codigo_helicoptero`),ADD UNIQUE INDEX `codigo_helicoptero_UNIQUE` (`codigo_helicoptero` ASC);

ALTER TABLE `test`.`mantenimiento`CHANGE COLUMN `codigo_mantenimiento` `codigo_mantenimiento` VARCHAR(15) NOT NULL,ADD PRIMARY KEY (`codigo_mantenimiento`),ADD UNIQUE INDEX `codigo_mantenimiento_UNIQUE` (`codigo_mantenimiento` ASC);

ALTER TABLE `test`.`propietario`CHANGE COLUMN `codigo_propietario` `codigo_propietario` VARCHAR(15) NOT NULL,ADD PRIMARY KEY (`codigo_propietario`),ADD UNIQUE INDEX `codigo_propietario_UNIQUE` (`codigo_propietario` ASC);

ALTER TABLE `test`.`reparacion`CHANGE COLUMN `codigo_reparacion` `codigo_reparacion` VARCHAR(15) NOT NULL,ADD PRIMARY KEY (`codigo_reparacion`),ADD UNIQUE INDEX `codigo_reparacion_UNIQUE` (`codigo_reparacion` ASC);

ALTER TABLE `test`.`tipo_equipo`CHANGE COLUMN `codigo_tipo_equipo` `codigo_tipo_equipo` VARCHAR(15) NOT NULL,ADD PRIMARY KEY (`codigo_tipo_equipo`),ADD UNIQUE INDEX `codigo_tipo_equipo_UNIQUE` (`codigo_tipo_equipo` ASC);

ALTER TABLE `test`.`tripulacion`CHANGE COLUMN `codigo_tripulante` `codigo_tripulante` VARCHAR(15) NOT NULL,ADD PRIMARY KEY (`codigo_tripulante`),ADD UNIQUE INDEX `codigo_tripulante_UNIQUE` (`codigo_tripulante` ASC);

ALTER TABLE `test`.`viaje`CHANGE COLUMN `codigo_viaje` `codigo_viaje` VARCHAR(15) NOT NULL,ADD PRIMARY KEY (`codigo_viaje`),ADD UNIQUE INDEX `codigo_viaje_UNIQUE` (`codigo_viaje` ASC);





4

Imagen 1.- Asignación de PK, NOT NULL y UNUQUE a todas las tablas.

En la tabla equipo se crea el atributo para la asignación de clave foránea

código_helicoptero y poder hacer la propagación de claves primarias:

ALTER TABLE `test`.`equipo`

ADD COLUMN `codigo_helicoptaro` VARCHAR(15) NULL AFTER `fecha_registro`;

Se crea la tabla equipo_tipo_equipo por la tercera regla de relaciones, para la

asignación de llaves foráneas:

CREATE TABLE `test`.`equipo_tipo_equipo` (

`codigo_equipo` VARCHAR(15) NOT NULL,`codigo_tipo_equipo` VARCHAR(15) NOT NULL,

PRIMARY KEY (`codigo_equipo`, `codigo_tipo_equipo `),

UNIQUE INDEX `codigo_equipo_UNIQUE` (`codigo_equipo` ASC),

UNIQUE INDEX `codigo_tipo_equipo_UNIQUE` (`codigo_tipo_equipo` ASC));





5

Se hace la propagación de claves primarias a las tablas según sus relaciones

generando las claves foráneas (imagen 2):

ALTER TABLE mantenimiento ADD CONSTRAINT fk_ma_codigo_helicopteroFOREIGN KEY (fk_ma_codigo_helicoptero) REFERENCES helicoptero(codigo_helicoptero) ON DELETECASCADE ON UPDATE CASCADE;

ALTER TABLE equipo ADD CONSTRAINT fk_eq_codigo_tipo_equipoFOREIGN KEY (fk_eq_codigo_tipo_equipo) REFERENCES equipo_tipo_equipo(codigo_tipo_equipo) ONDELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE equipo ADD CONSTRAINT fk_eq_codigo_helicopteroFOREIGN KEY (fk_eq_codigo_helicoptero) REFERENCES helicoptero(codigo_helicoptero) ON DELETECASCADE ON UPDATE CASCADE;

ALTER TABLE helicóptero ADD CONSTRAINT fk_he_codigo_propietarioFOREIGN KEY (fk_he_codigo_propietario) REFERENCES propietario(codigo_propietario) ON DELETECASCADE ON UPDATE CASCADE;

ALTER TABLE reparación ADD CONSTRAINT fk_re_codigo_helicopteroFOREIGN KEY (fk_re_codigo_helicoptero) REFERENCES helicoptero(codigo_helicoptero) ON DELETECASCADE ON UPDATE CASCADE;

ALTER TABLE viaje ADD CONSTRAINT fk_vi_codigo_clienteFOREIGN KEY (fk_vi_codigo_cliente) REFERENCES cliente(codigo_cliente) ON DELETE CASCADE ONUPDATE CASCADE;

ALTER TABLE viaje ADD CONSTRAINT fk_vi_codigo_tripulanteFOREIGN KEY (fk_vi_codigo_tripulante) REFERENCES tripulacion(codigo_tripulante) ON DELETECASCADE ON UPDATE CASCADE;

ALTER TABLE viaje ADD CONSTRAINT fk_vi_codigo_helicopteroFOREIGN KEY (fk_vi_codigo_helicoptero) REFERENCES helicoptero(codigo_helicoptero) ON DELETECASCADE ON UPDATE CASCADE;

ALTER TABLE equipo_tipo_equipo ADD CONSTRAINT codigo_tipo_equipoFOREIGN KEY (codigo_tipo_equipo) REFERENCES tipo_equipo(codigo_tipo_equipo) ON DELETECASCADE ON UPDATE CASCADE;

ALTER TABLE equipo_tipo_equipo ADD CONSTRAINT codigo_equipoFOREIGN KEY (codigo_equipo) REFERENCES equipo(codigo_equipo) ON DELETE CASCADE ONUPDATE CASCADE;

ALTER TABLE tipo_equipo ADD CONSTRAINT fk_te_codigo_equipo_tipo_equipoFOREIGN KEY (fk_te_codigo_equipo_tipo_equipo) REFERENCESequipo_tipo_equipo(codigo_tipo_equipo) ON DELETE CASCADE ON UPDATE CASCADE;





6

Imagen 2.- Propagación de claves primarias.

Vistas:

CLIENTE_VIAJE, que contenga las siguientes columnas codigo_cliente, codigo_viaje,codigo_tripulante, valor_viaje y fecha_registro:

Se hace una consulta a la tabla “viaje” para dar origen a la vista “cliente_viaje” (imagen

4):

SELECT

fk_vi_codigo_cliente as 'Codigo del cliente',

codigo_viaje as 'Codigo del viaje',fk_vi_codigo_tripulante as 'Codigo del tripulante',

valor_viaje as 'Valor del viaje',

fecha_registro as 'fecha de registro'

FROM test.viaje

ORDER BY valor_viaje DESC;





7

Imagen 4.- Consulta a la tabla viaje.

Se crea la vista “cliente_viaje” (imagen 5):

USE `test`;

CREATE

OR REPLACE VIEW `cliente_viaje` AS

(SELECT

`viaje`.`fk_vi_codigo_cliente` AS `Codigo del cliente`,

`viaje`.`codigo_viaje` AS `Codigo del viaje`,

`viaje`.`fk_vi_codigo_tripulante` AS `Codigo del tripulante`,

`viaje`.`Valor_viaje` AS `Valor del viaje`,

`viaje`.`fecha_registro` AS `fecha de registro`

FROM

`viaje`order by `viaje`.`Valor_viaje` desc);





8

Imagen 5.- Creación de la vista cliente_viaje.

Visualización de la vista “cliente_viaje” (imagen 6):

USE `test`;

SELECT * FROM cliente_viaje;

Imagen 6.- Vista cliente_viaje.





9

HELICOPTERO_MANTENIMIENTO_REPARACION, que contenga las siguientes tablascodigo_helicoptero, codigo_mantenimiento, valor_mantenimiento, codigo_reparacion,valor_reparacion y fecha_registro:

Se hace una consulta a la unión de las tablas helicóptero y reparacion (imagen 7):

SELECT

codigo_helicoptero AS 'Helicoptero',

codigo_reparacion AS 'Reparacion_re',

valor_reparacion AS 'Costo_re',

reparacion.fecha_registro AS 'Fecha_re'

FROM test.helicoptero

LEFT OUTER JOIN test.reparacion

ON helicoptero.codigo_helicoptero = reparacion.fk_re_codigo_helicoptero

ORDER BY codigo_helicoptero DESC;

Imagen 7.- Consulta a las tablas helicóptero y reparacion.





10

Se crea la vista helicóptero_reparacion_3 (imagen 8):

USE `test`;

CREATEOR REPLACE VIEW `helicoptero_reparacion_3` AS

(SELECT

codigo_helicoptero AS 'Helicoptero',

codigo_reparacion AS 'Reparacion_re',

valor_reparacion AS 'Costo_re',

reparacion.fecha_registro AS 'Fecha_re'


LEFT OUTER JOIN test.reparacion

ON helicoptero.codigo_helicoptero = reparacion.fk_re_codigo_helicoptero

ORDER BY codigo_helicoptero DESC);

Imagen 8.- Creación de la vista helicóptero reparacion.

Se hace una consulta a la unión de las tablas helicóptero y mantenimiento (imagen 9):

SELECT

codigo_helicoptero AS 'Helicoptero_ma',

codigo_mantenimiento AS 'Mantenimiento_ma',

valor_mantenimiento AS 'Costo_ma',

mantenimiento.fecha_registro AS 'Fecha_ma'FROM test.helicoptero

LEFT OUTER JOIN test.mantenimiento

ON helicoptero.codigo_helicoptero = mantenimiento.fk_ma_codigo_helicoptero

ORDER BY codigo_helicoptero DESC;





11

Imagen 9.- Consulta a las tablas helicóptero y mantenimiento.

Se crea la vista helicóptero_mantenimiento_3 (imagen 10):

USE `test`;

CREATE

OR REPLACE VIEW `helicoptero_mantenimiento_3` AS

(SELECT

codigo_helicoptero AS 'Helicoptero_ma',

codigo_mantenimiento AS 'Mantenimiento_ma',

valor_mantenimiento AS 'Costo_ma',

mantenimiento.fecha_registro AS 'Fecha_ma'


LEFT OUTER JOIN test.mantenimiento

ON helicoptero.codigo_helicoptero = mantenimiento.fk_ma_codigo_helicoptero

ORDER BY codigo_helicoptero DESC);





12

Imagen 10.- Creación de la vista helicóptero mantenimiento.

Se hace una consulta a la unión “left outer join” de la vista helicóptero_reparacion_3 y

helicóptero_mantenimiento_3 “unida” a la unión “right outer join” de la vista

helicóptero_reparacion_3 y helicóptero_mantenimiento_3 (imagen 11):

SELECT Helicoptero, Reparacion_re, Mantenimiento_ma,

Costo_re, Fecha_re, Costo_ma, Fecha_ma

FROM helicoptero_reparacion_3

left outer join helicoptero_mantenimiento_3

on 'Reparacion_re' = 'Mantenimiento_ma'

union

SELECT Helicoptero_ma, Reparacion_re, Mantenimiento_ma,



right outer join helicoptero_mantenimiento_3


order by helicoptero, reparacion_re;





13

Imagen 11.- Consulta a las uniones de las vistas.

Se crea la vista helicóptero_mantenimiento_reparacion_3 (imagen 12):

USE `test`;

CREATE

OR REPLACE VIEW `helicoptero_mantenimiento_reparacion_3` AS

(SELECT Helicoptero, Reparacion_re, Mantenimiento_ma,



left outer join helicoptero_mantenimiento_3

on 'Reparacion_re' = 'Mantenimiento_ma')

union

(SELECT Helicoptero_ma, Reparacion_re, Mantenimiento_ma,



right outer join helicoptero_mantenimiento_3


order by helicoptero, reparacion_re);





14

Imagen 12.- Creación de la vista helicóptero mantenimiento reparacion.

Consulta la vista helicóptero_mantenimiento_reparacion_3 (imagen 13):

select * from helicoptero_mantenimiento_reparacion_3

order by helicoptero, mantenimiento_ma;

Imagen 13.- vista helicóptero_mantenimiento_reparacion.





15

Triggers.

Antes de crear el trigger en cuestión, debes crear una tabla de nombre

REPORTE_REPARACION (genera los constrains correspondientes) que contenga las

siguientes columnas: codigo_helicoptero, codigo_tripulante (del último viaje), codigo_viaje

(del último viaje) y fecha_registro.

Se crea la tabla reporte_reparacion:

CREATE TABLE `test`.`reporte_reparacion` (

ìd_rr` INT NOT NULL AUTO_INCREMENT,

`codigo_helicoptero_rr` VARCHAR(15) NULL,`fecha_ultimo_viaje_rr` DATE NULL,

`codigo_tripulante_rr` VARCHAR(15) NULL,

`codigo_viaje_rr` VARCHAR(15) NULL,

`fecha_registro_rr` DATE NULL,

UNIQUE INDEX ìdreporte_reparacion_UNIQUE` (ìdreporte_reparacion` ASC),

PRIMARY KEY (ìd_rr`),

INDEX `codigo_helicoptero_rr_idx` (`codigo_helicoptero_rr` ASC),

INDEX `fecha_ultimo_viaje_rr_idx` (`fecha_ultimo_viaje_rr` ASC),

INDEX `codigo_tripulante_rr_idx` (`codigo_tripulante_rr` ASC),

INDEX `codigo_viaje_rr_idx` (`codigo_viaje_rr` ASC),

CONSTRAINT ̀ codigo_helicoptero_rr`

FOREIGN KEY (`codigo_helicoptero_rr`)

REFERENCES `test`.`helicoptero` (`codigo_helicoptero`)

ON DELETE NO ACTION

ON UPDATE NO ACTION);





16

Se crea un trigger que se dispare en el momento en que se ingrese un registro a la

tabla REPARACION con valor_reparacion mayor o igual a $ 15, 000, la información se

debe tomar de las tablas HELICOPTERO,

TRIPULACION, VIAJE y REPARACION

(imagen 14):

DELIMITER //

CREATE TRIGGER re_15000_o_mayor AFTER INSERT ON test.reparacionFOR EACH ROW

BEGINIF (new.Valor_reparacion >= 15000)then insert into

test.reporte_reparacion(codigo_helicoptero_rr, fecha_ultimo_viaje_rr,codigo_tripulante_rr, codigo_viaje_rr, fecha_registro_rr)

values

((select helicoptero.codigo_helicoptero from test.helicopterowhere helicoptero.codigo_helicoptero = new.fk_re_codigo_helicoptero) ,

(select max(fecha_viaje) from test.tripulacion, test.viajewhere fk_vi_codigo_helicoptero = (select elicoptero.codigo_helicopterofrom test.helicoptero where helicoptero.codigo_helicoptero =new.fk_re_codigo_helicoptero)and fk_vi_codigo_tripulante = codigo_tripulante),

(select fk_vi_codigo_tripulante from test.viajewhere fk_vi_codigo_helicoptero = (select helicoptero.codigo_helicopterofrom test.helicoptero where helicoptero.codigo_helicoptero =new.fk_re_codigo_helicoptero)and fecha_viaje = (select max(fecha_viaje) from tripulacion, viaje wherefk_vi_codigo_helicoptero = (select helicoptero.codigo_helicoptero fromtest.helicoptero where helicoptero.codigo_helicoptero =new.fk_re_codigo_helicoptero))),

(select codigo_viaje from test.viajewhere fk_vi_codigo_helicoptero = (select helicoptero.codigo_helicopterofrom test.helicoptero where helicoptero.codigo_helicoptero =new.fk_re_codigo_helicoptero)and fecha_viaje = (select max(fecha_viaje) from tripulacion, viaje wherefk_vi_codigo_helicoptero = (select helicoptero.codigo_helicoptero fromtest.helicoptero where helicoptero.codigo_helicoptero =new.fk_re_codigo_helicoptero))),

now());

END IF;END//

DELIMITER ;





17

Imagen 14.- Creación de trigger.

Muestra de 5 registros agregados a la tabla reparación con el trigger creado (imagen 15):

Imagen 15.- Tabla reparación con 5 registros nuevos.

Consulta a la tabla reporte_reparacion mostrando solo los 4 registros de reparaciones iguales

o mayores a $15,000.00 (imagen 16):

Imagen 16.- Consulta a la tabla reporte_reparacion con 4 registros nuevos.





18

Sotred procedures.

generar un stored procedures que tome la información de unas tablas para generar la

información de las vistas anteriormente creadas:

Vista CLIENTE_VIAJE, que contenga la información codigo_cliente, nombre de la

tabla CLIENTE y la información codigo_viaje, codigo_tripulante, valor_viaje y

fecha_registro de la tabla VIAJE, esto del último mes.

Creación del procedimiento para la vista cliente_viaje_ultimo_mes:

DELIMITER //

CREATE PROCEDURE CLIENTE_VIAJE_ULTIM_MES

(INOUT

fk_vi_codigo_cliente varchar(15),

codigo_viaje varchar(15),

fk_vi_codigo_tripulante varchar(15),

valor_viaje decimal(15,2),

fecha_registro date)

BEGIN

select * from cliente_viaje_ultimo_mes WHERE fecha_registro >=(now( ) - INTERVAL 30 DAY);

END//

DELIMITER ;

Vista cliente_viaje_ultimo_mes (imagen 17):

Imagen 17.- Consulta a la vista cliente_viaje_ultimo_mes.





19

Vista HELICOPTERO_MANTENIMIENTO_REPARACION, que contenga la información

de codigo_helicoptero,

codigo_mantenimiento, valor_mantenimiento,

codigo_reparacion, valor_reparacion y fecha_registro, esto del último mes.

Creación del procedimiento para la vista helicóptero_mantenimiento_reparacion_ultimo_mes:

DELIMITER //

CREATE PROCEDURE helicoptero_mantenimiento_reparacion_ultimo_mes

(INOUT

Helicoptero varchar(15),

Reparacion_re varchar(15),

Mantenimiento_ma varchar(15),

Costo_re decimal(15,2),

Fecha_re date,

Costo_ma decimal(15,2),

Fecha_ma date)

BEGIN

select * from helicoptero_mantenimiento_reparacion_3_ultimo_mes

WHERE fecha_re >=(now( ) - INTERVAL 30 DAY)

or fecha_ma >=(now( ) - INTERVAL 30 DAY);

END//

DELIMITER ;

Vista helicóptero_mantenimiento_reparacion_ultimo_mes (imagen 18):

Imagen 18.- Consulta a la vista helicóptero_mantenimiento_reparacion_ultimo_mes.





Fuentes.

Fuentes de información consultada o recuperada.Base de datos I . Guía 9. (2014). [PDF] Facultad: Ingeniería, Escuela: Computación. En

línea: www.udb.edu.sv/udb/archivo/guia/informatica-ingenieria/base-de-datos-i/2013/i/guia-

9.pdf [Consultado: 23 Feb 2014].

Casares, C. (2014). Tutorial de sql . [PDF] ASP-ADO Tutoriales de la Cueva Pedro Rufo

http.//usuarios.tripod.es/smaug. En línea: http://www.unalmed.edu.co/~mstabare/Sql.pdf

[Consultado: 22 Feb 2014].

GUIA 9, PROCEDIMIENTOS ALMACENADOS. (2014). [PDF] En línea:

http://www.udb.edu.sv/udb/archivo/guia/informatica-ingenieria/base-de-datos-i/2013/i/guia-9.pdf [Consultado: 23 Feb 2014].

sql, reglas de validación y programación.pdf

Documents