domingo, 13 de noviembre de 2011

Bonus Point Prueba 3 , capitulos 10 y 11 (Introduccion a los sistemas de bases de datos)




Autor:
 Felipe Leal

Competencia: Aplica las Ciencias de la ingeniería. (Nivel 1) 
Palabras Clave: datos, MySql, Bonus.


Con el Fin De Aumentar Las Notas Obtenidas en la 3ª prueba del semestre, se nos encomendo Realizar Varios Ejercicios de la guia vista durante el semestre, especificamente capitulos 10 y 11.




Ejercicios capitulo 10: outer joins, self-­‐joins, and unions




empleados ( NoEmpleado, Apellido, Nombre, FechaNacimiento, Sexo, Sueldo, IdDepto, Ciudad)
departamento( IdDepto, Nombre_depto, NoJefe, depto_control)
proyectos( Numero_proy, Nombre, Lugar, IdDepto)
rel_empleado_py ( NoEmpleado, Numero_proy)
carga_familiar ( Rut_carga, NoEmpleado, Nombre_carga, Sexo, FechaNacimiento, parentesco) relacion_empleado_interes (NoEmpleado, id_interes)
intereses (id_interes, nombre_interes)


Nota: cada departamento depende de otro departamento (unidad) que lo controla 
(Ejemplo: cCompras es controlado por el departamento de Finanzas). 
La clave foránea depto_control (auto-­‐referencia), guarda el id del departamento controlador.
 Existen departamentos que se controlan así mismos.


1) Muestre los departamentos que aun no tienen empleados.
select nombre_depto from departamento left join empleados using(idDepto)
where NoEmpleado is null;


2) Muestre los empleados que no tienen cargas familiares.
select nombre, apellido from carga_familiar right join empleados using(NoEmpleado)
where rut_carga is null;


3) Muestre una lista con los diferentes nombres que aparecen en la tabla empleados y carga_familiar.


select nombre from empleados union
select nombre_carga from carga_familiar;


4) Muestre todos los nombres (incluso los repetidos) que aparecen en la tabla empleados y carga_familiar, ordenados alfabéticamente.


select nombre from empleados union all
select nombre_carga from carga_familiar order by nombre;


5) Muestre un lista de nombres que están en la tabla empleados y la tabla carga_familiar


select nombre from empleados intersect
select nombre_carga from carga_familiar;


6) Muestre un lista de nombres que están en la tabla empleados y la tabla carga_familiar.


select nombre from empleados except
select nombre_carga from carga_familiar;


7) Muestre el rut y nombre de todas las personas, junto al rut y nombre de todas sus cargas familiares, incluso si no tienen cargas.


select * from empleados as e
full outer join carga_familiar as cf 
on e.NoEmpleado=cf.NoEmpleado;


8) ¿Qué departamento controla el departamento de “Finanzas”?


select * from departamento as sumiso
inner join departamento as controlador on (sumiso.depto_control = controlador.iddepto)
where controlador.nombre_depto = “Finanzas”;


9) ¿Qué departamentos controlan mas de un departamento?


select * from departamento as sumiso inner join departamento as controlador
on (sumiso.depto_control = controlador.iddepto) group by sumiso.Nombre_depto having count(sumiso.depto_control) >1;


10)¿Que departamentos se controlan así mismos?


select * from departamento as sumiso inner join departamento as controlador
on (sumiso.depto_control = controlador.iddepto)
where controlador.nombre_depto = sumiso.nombre_depto;



11) ¿Qué departamentos no controlan ningún departamento?


select * from departamento as s left join departamento as c
on (s.depto_control = c.iddepto) where s.iddepto is NULL;


12)Nombre todos los departamentos, que son controlados por departamentos cuyo nombre comienza con “a”.


select s.Nombre_depto from departamento as sumiso inner join departamento as controlador
on (sumiso.depto_control = controlador.iddepto) where controlador.nombre_depto like “a%”;


13)Existen versiones de MySql que no permiten trabajar con full outer join, por lo tanto cree una consulta que permita entregar un resultado igual a un full outer join sin utilizar directamente esta sentencia, utilizando las tablas empleados y carga_familiar.


SELECT * FROM empleados as e LEFT JOIN carga_familiar as cf
ON e.NoEmpleado = cf.NoEmpleado UNION
SELECT * FROM empleados as e RIGHT JOIN carga_familiar as cf
ON e.NoEmpleado = cf.NoEmpleado;



14)¿Que empleados aun no están trabajado en ningún proyecto?


SELECT * FROM empleados LEFT JOIN rel_empleado_py USING(NoEmpleado) WHERE Numero_Proy IS NULL;


15)¿De que personas aun no se conocen sus intereses?


SELECT * FROM empleados as e LEFT JOIN rel_empleado_interes as r
ON e.NoEmpleado=r.NoEmpleado WHERE r.NoEmpleado is NULL;


16)¿Cuales departamentos no están a cargo de ningún proyecto?


SELECT * FROM departamento as d LEFT JOIN proyectos as p USING (IdDepto) where p.IdDepto is null;


17)¿Qué empleados aun no están trabajado en ningún proyecto y no tienen cargas familiares?


SELECT * FROM empleados as e LEFT JOIN carga_familiar as cf USING(NoEmpleado) LEFT JOIN rel_empleado_py as r USING(NoEmpleado) WHERE (cf.NoEmpleado IS NULL) AND r.NoEmpleado 
IS NOT NULL;


18)¿Cuales departamentos no están a cargo de ningún proyecto y tampoco tienen empleados asignados?


SELECT d.Nombre FROM empleados as e RIGHT JOIN departamento as d USING(IdDepto) LEFT JOIN proyectos as p USING (IdDepto) WHERE (e.IdDepto IS NULL) AND p.IdDepto iS NULL;


19)Muestre los nombres de empleados que trabajan en el departamento de “Informática”, pero que actualmente no están asignados a ningún proyecto.


SELECT e.Nombre FROM empleados as e LEFT JOIN departamento as d ON (e.IdDepto = d.IdDepto) LEFT JOIN rel_empleado_py as r ON(r.NoEmpleado = e.NoEmpleado) WHERE (d.Nombre_Depto LIKE “Informatica”) AND r.Numero_proy is NULL;


20)De las tablas empleados y cargas_familiares, muestre una lista de las personas que nacieron en el mes de febrero.


select Nombre, FechaNacimiento from empleados WHERE month(FechaNacimiento)=“02”
union
select nombre_carga, FechaNacimiento from carga_familiar WHERE month(FechaNacimiento)=“02”;


21)De las tablas empleados y cargas_familiares, muestre una lista de las personas que nacieron en el mes de febrero, cuyo nombre comience con “F”.


select Nombre, FechaNacimiento from empleados WHERE month(FechaNacimiento)=“02” AND Nombre LIKE “F%”
union
select nombre_carga, FechaNacimiento from carga_familiar WHERE month(FechaNacimiento)=“02” AND Nombre LIKE “F%”;


22)¿Qué empleados no están asignados a ningún proyecto, y que trabajen en el departamento de Finanzas o Producción o Informática?


SELECT e.Nombre FROM empleados as e LEFT JOIN rel_empleado_py as r ON(e.NoEmpleado = r.NoEmpleado) LEFT JOIN departamento as d ON (e.IdDepto = d.IdDepto) WHERE (r.NoEmpleado IS NULL AND) d.Nombre_depto IN (“Finanzas” , “Produccion” , “Informatica”);


23)Muestre el o los departamentos que son controlados por un departamento, cuyo nombre comienza con “C”.


select * from departamento as sumiso inner join departamento as controlador
on (sumiso.depto_control = controlador.iddepto) where controlador.nombre_depto like “C%”;


24)¿Qué empleados no están asignados a ningún proyecto, y trabajan en un departamento que está controlado por una unidad, cuyo nombre comienza con “C”?


SELECT e.Nombre FROM empleados as e LEFT JOIN rel_empleado_py as r ON(e.NoEmpleado = r.NoEmpleado) INNER JOIN departamento as s ON(d.IdDepto = e.IdDepto) INNER JOIN departamento as c ON(s.depto_control = c.iddepto) WHERE (r.Numero_proy IS NULL) and c.nombre_depto like “C%”;


25)Muestre los pasa tiempos (intereses) que no están relacionados con ningún empleado.


SELECT i.nombre_interes FROM intereses as i LEFT JOIN relación_empleado_interes as r USING(id_interes) WHERE r.id_interes IS NULL;


26)Muestre las personas que tienen entre sus pasatiempos (intereses) leer y bailar, pero que no tienen cargas familiares.


SELECT e.Nombre FROM relación_empleado_interes as r LEFT JOIN intereses as i ON(r.id_interes = i.id_interes) LEFT JOIN empleados as e ON(e.NoEmpleado = r.NoEmpleado) LEFT JOIN carga_familiar as cf ON(cf.NoEmpleado = r.NoEmpleado) WHERE ((i.nombre_interes LIKE “leer” OR i.nombre_interes LIKE “bailar”) AND cf.NoEmpleado IS NULL) GROUP BY e.Nombre HAVING count(e.Nombre)>1;


27)Muestre las personas que tienen entre sus pasatiempos (intereses) leer y bailar, y que ademas estén trabajando en un proyecto en Temuco.


SELECT e.Nombre FROM relación_empleado_interes as r LEFT JOIN intereses as i ON(r.id_interes = i.id_interes) LEFT JOIN empleados as e ON(e.NoEmpleado = r.NoEmpleado) LEFT JOIN rel_empleado_py as rep ON(re.NoEmpleado = r.NoEmpleado) LEFT JOIN proyectos as p ON(p.Numero_proy = rep.Numero_proy) WHERE ((i.nombre_interes LIKE “leer” OR i.nombre_interes LIKE “bailar”) AND p.Lugar LIKE “TEMUCO” ) GROUP BY e.Nombre HAVING 
count(e.Nombre)>1;


28)El FBI requiere una lista de los empleados que trabajan en los departamentos controlados por el departamento de Administración, y que no estén relacionados con ningún interés o pasatiempo.


SELECT e.Nombre FROM artamento as s INNER JOIN departamento as m
ON s.idDepto = m.depto_control
INNER JOIN empleados as e

ON m.idDepto = e.idDepto
LEFT JOIN relacion_empleado_interes as r
ON e.NoEmpleado = r.NoEmpleado
WHERE s.Nombre LIKE “Informatica” ADN r.NoEmpleado IS NULL;


29)Gerencia necesita, en una sola lista, el rut, nombre y apellido los empleados que trabajan en los departamentos controlados por el departamento de Administración, y los empleados que poseen como carga familiar solo hijos.


SELECT e.Rut, e.Nombre, e.Apellido FROM empleados as e LEFT JOIN departamento as s ON(c.IdDepto = e.IdDepto) INNER JOIN departamento as c ON(s.depto_control = c.iddepto) WHERE c.nombre_depto LIKE “Administracion”
Unión
SELECT e.Rut, e.Nombre, e.Apellido FROM empleados as e RIGHT JOIN carga_familiar as cf USING (NoEmpleado) WHERE cf.parentesco LIKE “hijo”;


30)Gerencia necesita, en una sola lista, el rut, nombre y apellido los empleados que
trabajan en los departamentos controlados por el departamento de Administración, y los empleados que actualmente no poseen cargas familiares.



SELECT e.Rut, e.Nombre, e.Apellido FROM empleados as e LEFT JOIN departamento as s ON(c.IdDepto = e.IdDepto) INNER JOIN departamento as c ON(s.depto_control = c.iddepto) WHERE c.nombre_depto LIKE “Administracion”
Unión
SELECT e.Rut, e.Nombre, e.Apellido FROM empleados as e LEFT JOIN carga_familiar as cf USING (NoEmpleado) WHERE cf.parentesco IS NULL;


31)Muestre una lista que debe contener los empleados que ganan mas que el sueldo promedio y los empleados que poseen como mínimo 3 intereses (los empleados que se repiten, se muestran las dos veces).


SELECT * FROM empleados HAVING sueldo > AVG(sueldo) UNION
SELECT * FROM empleados HAVING sueldo >AVG(Sueldo) UNION

SELECT * FROM empleados as e
INNER JOIN relacion_empleado_interes as r
ON e.NoEmpleado = r.NoEmpleado GROUP BY r.NoEmpleado HAVING COUNT(r.NoEmpleado)>3;






El siguiente listado de ejercicios está destinado, principalmente, a reforzar uso de Restricciones, Vistas y Transacciones según lo presentado en el capítulo 11.
Para ello se considerará el modelo presentado en la guía de ejercicios del capitulo 10, cuando sea necesario.
En caso contrario se hará las referencias que correspondan. Para cada uno de los ejercicios, usted debe proponer el comando o la solución correspondiente.





Antes de comenzar




a) Explique y ejemplifique el concepto de vista. 


¿Cuál es la ventaja que presentan las vistas?


Una vista es una tipo de tabla virtual, si bien, cada vez que realizamos una consulta, generamos una tabla virtual, que despues de usarse es borrada; al Crear una VISTA le asignamos un nombre a esta tabla virtual y queda almacenada en la misma base de datos, por lo que podemos acceder a ella en otro momento, si asi estimamos conveniente.

¿Con qué fin?

Para acceder fácilmente a las consultas ( a veces tediosas o complejas), y también para acotar la informacio a mostrar.


Por Ejemplo:
En la Base de Datos de una tienda de Retail, se puede crear vistas con datos de ventas SEGÚN la marca para así entregar estos datos para trabajar a los vendedores internos ESPECIFICOS de la marca correspondiente. Acotando asi la información y limitando el acceso a los datos que no le conciernen.



b) Explique y ejemplifique el concepto de transacción. 


¿Cuál es el problema que resuelven las transacciones?


Las Transacciones Nos Dan la posibilidad de realizar varias sentencias de una sola vez, sintetizándolas todas en un solo paso.

¿Como Funciona?

Se inicia la transacción con un encabezado llamado START TRANSACTION; Luego escribimos línea a línea las sentencias que queramos ejecutar, cuando llegamoso al Final Podemos elegir, si estamos Conformes con las sentencias que queremos ejecutar o quizás hemos cambiado de opinión y no queremos llevar a cabo tales sentencias, en el caso de que estemos seguros de la transacción, cerramos la transacción con COMMIT; , en el caso de habernos arrepentido la cerramos con ROLLBACK; y ninguna sentencia se ejecutará. Cabe Destacar que Si hay errores en alguna sentencia, NINGUNA se llevara a cabo, aunque hayamos cerrado con COMMIT;

1.- Problema No. 1, Empleados del Listado No. 2


1.1.- Haga una vista que permita conocer el promedio de sueldo que tienen cada departamento de la empresa. La idea es que esa vista sea como la tabla de departamento pero extendida con el atributo promedio_sueldo.


CREATE VIEW v_prom_sueldo AS
SELECT AVG(e.Sueldo) AS “promedio_sueldo”,d.Nombre_depto,d.NoJefe,d.depto_control
FROM Empleados as e
INNER JOIN departamento as d
ON e.idDepto = d.IdDepto
GROUP BY d.idDepto;



1.2.- Use la vista desarrollada en el punto 1.1.- para resolver: Mostrar todos los departamentos en que la diferencia de sueldos entre el jefe y el promedio de sus empleados es mayor al 40%.

SELECT v.Nombre_depto FROM v_prom_sueldo AS v
INNER JOIN empleados as e
ON v.NoJefe = e.NoEmpleado
WHERE (e.Sueldo - v.promedio_sueldo) > (0,4*v.promedio_sueldo);



1.3.- Resuelva el problema de generar una vista similar a la del punto 1.1.- pero que no considere el sueldo del jefe.


CREATE VIEW v_v2 AS
SELECT AVG(Sueldo) AS “promedio_sueldo”, idDepto, Nombre_depto, NoJefe, depto_control FROM departamento
GROUP BY idDepto;



1.4.- Describa una transacción que permita actualizar el sueldo de los empleados en base al reajuste que por ley deben hacer las empresas según el IPC del año anterior.


START TRANSACTION;
update empleados set sueldo = sueldo*IPC; COMMIT;
Esta transacción será realizada en las instancias que la ley lo determine.


1.5.- Los empleados tienen derecho a conocer su información de sueldos. Para ello se propone definir un usuario para cada empleado cuyo user_name será la unión de su nombre y su apellido mediante un guión y que su password estará compuesta por las últimas 2 letras de su nombre unidas al mes y día de su nacimiento y seguidas de las dos primeras letras de su apellido. Es decir, si el empleados se llama Juan Perez y su fecha de nacimiento es 1986-11-26 su username =“juan_perez” y password=“an1126pe”. Haga la vista que permite mantener estos valores asociados a cada empleado.


CREATE VIEW v_v3 AS
SELECT CONCAT(Nombre,”_”,Apellido) AS “username”, CONCAT(SUBSTRING(Nombre,LENGTH(Nombre)-1, LENGTH(Nombre)), MONTH(FechaNacimiento), DAY(FechaNacimiento), SUBSTRING(Nombre,1,2)) AS 
“password” FROM empleados;


1.7.- Se quiere una restricción que impida que exista empleados nuevos que sean menores de edad, es decir que tengan menos de 18 años.


ALTER TABLE empleados ADD CONSTRAINT CHECK ( YEAR(CURDATE()) - YEAR(FechaNacimiento) - (RIGHT(CURDATE(),5) < RIGHT(FechaNacimento,5)) > 18);


1.8.- Suponga que al Gerente de la empresa no le interesaría que cualquier empleado nuevo tenga un sueldo mayor a del jefe del departamento donde ese empleado trabajará. Explore si es factible crear una clausula Check que permita
hacer realidad la idea del gerente.


ALTER TABLE empleados ADD CONSTRAINT
CHECK idDepto IN (SELECT idDepto FROM departamento ) AND Sueldo < (SELECT e.Sueldo FROM departamento as d

INNER JOIN empleados as e ON d.NoJefe = e.NoEmpleado);


2.- Problema No. 3, Video Club, del Listado No. 2


Un video club, necesita implementar una base de datos que sea capaz de llevar un catalogo de sus películas a disposición, este catalogo debe permitir búsquedas por actor principal, país de origen de la película, censura (mayores de XX años), categoría (acción, romance, aventuras, etc.), para ello tiene el siguiente modelo relacional:




VIDEO ( id_video, titulo, id_idioma, id_actor, id_categoria, id_censura, id_pais, duración,año_estreno, subtitulo)
ACTOR ( id_actor, id_pais, id_categoria, nombre, apellido )
PAIS ( id_pais, id_ idioma, nombre ), IDIOMA(id_idioma, descripción ) 
CATEGORIA ( id_categoria, descripción )
CENSURA ( id_censura, descripción )




A partir de este modelo, complemente la base de datos actual de manera tal,
que se puedan hacer efectivos préstamos y devoluciones de videos a clientes.


Haga las sentencias SQL necesarias para permitir aquella incorporación.


Por otra parte las consultas a realizar son las siguientes:


2.1.- Comercialmente las películas que duran menos de 30 minutos no son un buen negocio para el video club, por lo cual el gerente ha ordenado que no se ingresen a la base de datos películas que duren menos de 30 minutos. Proponga una solución a ese problema.


ALTER TABLE video
ADD CONSTRAINT CHECK (duracion > 30);



2.2.- Asegúrese que las películas sean de una sola de las tres categorías: “Todo Exp.”, “14 y +” y “18 y +”.


ALTER TABLE video
ADD CONTRAINT CHECK censura IN(‘Todo Exp.’ , ‘14 y +’ , ‘18 y +’);



2.3.- En el problema se solicita que usted defina las tablas que permitirían que un cliente pueda arrendar y devolver películas. Usualmente eso se logra con una tabla CLIENTE(id_cliente, nombre, ...), una tabla de ejemplares en arriendo
EJEMPLARES(id_ejemplar, id_video,...) y una tabla PRESTAMOS que debe relacionar al cliente con los ejemplares, considerando el id_cliente y el id_ejemplar junto con datos como la fecha de arriendo y la fecha estimada de devolución . Haga las sentencias que permiten crear la tabla de PRESTAMOS considerando una clausula que impida que los empleados ingresen una fecha de devolución que sea menor a la fecha de préstamo.


CREATE TABLE Cliente( id_cliente INT PRIMARY KEY, Nombre VARCHAR(16),
Fono INT);

CREATE TABLE Ejemplares( id_ejemplar INT,
id_video INT);

ALTER TABLE Ejemplares ADD FOREING KEY (id_video) REFERENCES VIDEO(id_video);
CREATE TABLE Prestamos (
id_cliente INT,

id_ejemplar INT, fecha_arriendo DATE, fecha_devolucion DATE);
ALTER TABLE Prestamos ADD FOREING KEY (id_cliente) REFERENCES Cliente(id_cliente);
ALTER TABLE Prestamos ADD FOREING KEY (id_ejemplar) REFERENCES Ejemplares(id_ejemplares);
ALTER TABLE Prestamos ADD CONSTRAINT CHECK (fecha_devolucion > fecha_arriendo);


2.4.- Al devolver una película se debe registrar la fecha de devolución real y si hay morosidad esto debe ser registrado en alguna parte de modo que al cliente se le cobre una multa. Donde la registraría los datos que permiten chequear si el cliente tiene multa pendiente?


R: En la tabla Prestamos, pues es aquí donde se mantendrá información referente a la fecha de devolución y en base a esta es posible generar una mora. Para esto se tendría que agregar una columna que guarde un tag de multa, de este modo es posible evaluar que clientes poseen muchos retrasos respecto a la cantidad de arriendos.
ALTER TABLE Prestamos ADD COLUMN Atraso BOOL DEFAULT FALSE;


2.5.- Genere la vista de los morosos recalcitrantes para que los empleados que arriendan tengan una advertencia de número de películas máximas en arriendo. La idea es que esta vista contenga el todos los datos de los clientes pero con una columna adicional que diga recalcitrante si es que ese cliente tiene un porcentaje de morosidad mayor al 40%; es decir que si han arrendado 100 películas han devuelto con atraso más de 40.


CREATE VIEW arrdos_por_cliente AS
SELECT id_cliente, count(id_cliente) AS "total_arrdos" FROM prestamos GROUP BY id_cliente;

CREATE VIEW atrasos_por_cliente AS
SELECT id_cliente, count(id_cliente) AS "total_atrasos" FROM prestamos where at = 's' GROUP BY id_cliente;

CREATE VIEW dicom_video AS
SELECT * FROM arrdos_por_cliente as c INNER JOIN v2 AS v
ON c.id_cliente = v.id_cliente
WHERE c.total_arrdos*0.4 < v.total_atrasos;




No hay comentarios:

Publicar un comentario