Con las vistas, trabajaremos con los esquemas externos.

Para crear una vista hay que utilizar la sentencia CREATE VIEW

ESTRUCTURA: CREATE VIEW nombre_vista [(lista_columnas)] AS (consulta) [WITH CHECK OPTION];

Las vistas no existen realmente como un conjunto de valores almacenados en la base de datos, sino que son tablas ficticias, llamadas derivadas (no materializadas). Se construyen a partir de tablas reales (materializadas) almacenadas en la base de datos.

Para borrar una vista hay que utilizar la sentencia DROP VIEW, que presenta el formato:

DROP VIEW nombre_vista {RESTRICT|CASCADE};

Las vistas pueden ser actualizables o no.

VENTAJAS

  • Independencia
  • Simplificación
  • Mejora de los datos y de las aplicaciones del uso para el usuario de la seguridad
  • Integridad de los datos
  • Rendimiento

DESVENTAJAS

  • Restricciones de actualizaciones
  • Restricciones de estructura; algunos SGBD no permiten construir una vista a partir de una consulta cualquiera.

ACTUALIZACIÓN DE LAS VISTAS

Las vistas siempre se pueden consultar, pero no siempre se pueden actualizar...

Una vista no es actualizable cuando:

  • Hay una clave primaria, atributo not null o UNIQUE de alguna tabla que no interviene en la vista generada.
  • Generalmente las vistas definidas sobre más de una tabla (Join)
  • Las vistas que incluyen cláusulas DISTINCT, HAVING, GROUP BY o funciones de agregación (AVG, MIN,..),

Los disparadores permiten efectuar modificaciones sobre tablas que NO son actualizables.

Creamos una vista sobre la base de datos Empresa_a que nos dé para cada cliente el número de proyectos que tiene encargados el cliente:

CREATE VIEW projectes_per_client (codi_cli, nombre_projectes) AS (

SELECT c.codi_cli, COUNT(*)

FROM projectes p, clients c

WHERE p.codi_client = c.codi_cli

GROUP BY c.codi_cli

);

La cláusula WITH CHECK OPTION asegura que nunca podremos actuar sobre partes de una tabla que no están en la vista.

ACTIVIDADES

Descargar script exposiciones.

  1. Dada la vista siguiente: CREATE VIEW AdrecesFotografs AS SELECT Nom, Adreca, Pais FROM Fotografs; INSERT INTO AdrecesFotografs VALUES ('Jack Shephard','St. Sebastian hospital, Los Angeles', 'EEUU'); Explica brevemente cuál sería el efecto sobre la vista anterior y la tabla Fotografs si ejecutamos la sentencia SQL siguiente: INSERT INTO AdrecesFotografs VALUES ('Jack Shephard','St. Sebastian hospital, Los Angeles', 'EEUU'); Que actualizaría la view AdrecesFotografs y Fotografs pero en Fotografs faltaría la PRIMARY KEY. ¿Cambiaría algo si la vista tuviera la cláusula WITH CHECK OPTION? En este caso no sucede nada diferente porque no hay un WHERE, y no añades nada que no esté en la lista, si tuviéramos algo de fuera de la lista, no lo permitiría.
  1. Crear una vista llamada FotografsDeRenom que obtenga todos los datos de la tabla Fotografs y que además, para cada fotógrafo, nos dé el precio medio que pagaron los visitantes para entrar a las exposiciones donde este fotógrafo ha expuesto, y que el listado salga ordenado por el precio medio de forma descendente. ¿Es actualizable esta vista? ¿Por qué? CREATE VIEW FotografsDeRenom AS SELECT f.passaport, f.nom, f.adreca, f.telefon, f.pais, f.classificacio_fotograf, avg(e.preu) as 'preu mig' FROM Fotografs f, Exposicions e, Exposen ex WHERE f.passaport=ex.passaport AND e.codi_exposicio=ex.codi_exposicio GROUP BY (*) ORDER BY preu_mig DESC; No es actualizable porque tiene un GROUP BY y es una vista definida sobre más de una tabla.

Actividad vistas: Manipulación de la base de datos Exposicions

Descargar script exposiciones.

  1. Crear una vista de aquellas exposiciones que se han llevado a cabo entre el 3-6-2010 y el 15-11-2010. Mostrar todos los datos de las exposiciones y de sus participantes, y que tengan un precio superior a 9€. DROP VIEW Exposicions_estiu; CREATE VIEW Exposicions_estiu AS SELECT ex., f. FROM Fotografs f NATURAL JOIN Exposen e JOIN Exposicions ex ON ex.codi_exposicio=e.codi_exposicio WHERE (ex.data_inici BETWEEN '2010-6-3' AND '2010-11-15') AND (ex.data_final BETWEEN '2010-6-3' AND '2010-11-15') AND ex.preu>9 GROUP BY codi_exposicio; select * FROM Exposicions_estiu; No es actualizable, porque utiliza JOINs + GROUP BY
  1. Crear una vista que muestre el pasaporte, el nombre y el país de los fotógrafos que participan en alguna exposición con precio superior o igual a la media de todas las exposiciones. DROP VIEW Participants_de_preu_elevat; CREATE VIEW Participants_de_preu_elevat AS SELECT f.passaport, f.nom, f.pais FROM Fotografs f NATURAL JOIN Exposen e JOIN Exposicions ex ON ex.codi_exposicio=e.codi_exposicio WHERE ex.preu>=(select avg(ex2.preu) FROM Exposicions ex2) GROUP BY passaport; select * FROM Participants_de_preu_elevat; No es actualizable, porque utiliza JOINs + GROUP BY
  1. Crear una vista que contenga los códigos de exposición de las exposiciones realizadas en 2010, donde los fotógrafos sean de Inglaterra. Hay que mostrar los códigos de exposición, la fecha y nombre y apellido de los fotógrafos. DROP VIEW Expos2010; CREATE VIEW Expos2010 AS SELECT ex.codi_exposicio, ex.data_inici, ex.data_final, f.nom FROM Fotografs f NATURAL JOIN Exposen e JOIN Exposicions ex ON ex.codi_exposicio=e.codi_exposicio WHERE 2010=YEAR(data_inici) AND 2010=YEAR(data_final) AND f.pais="Anglaterra"; select * FROM Expos2010; No es actualizable, porque utiliza JOINs.
  1. Crea una vista donde a partir de las exposiciones realizadas en 2010 los fotógrafos participantes hayan expuesto más de 2 fotografías. Muestra nombre y apellido del fotógrafo, exposición (código y nombre) y número de fotografías. DROP VIEW Expos2010mes2f; CREATE VIEW Expos2010mes2f AS SELECT f.nom, ex.codi_exposicio, ex.titol, e.num_fotos FROM Fotografs f NATURAL JOIN Exposen e JOIN Exposicions ex ON ex.codi_exposicio=e.codi_exposicio WHERE YEAR(data_inici)=2010 AND e.num_fotos>2; select * FROM Expos2010mes2f; No es actualizable, porque utiliza JOINs
  1. Inventa una vista que sea actualizable y otra que no lo sea, a partir de la BD Exposicions.
  1. Queremos crear una vista actualizable para que el personal pueda añadir el pasaporte, el nombre y la dirección de los fotógrafos que entren. DROP VIEW VistaActualitzable; CREATE VIEW VistaActualitzable AS SELECT passaport,nom,adreca FROM Fotografs WITH CHECK OPTION; select * FROM VistaActualitzable;
  1. Queremos crear una vista que muestre el nombre de los fotógrafos con el título de las exposiciones. DROP VIEW VistaNoActualitzable; CREATE VIEW VistaNoActualitzable AS SELECT f.nom, ex.titol FROM Fotografs f NATURAL JOIN Exposen e JOIN Exposicions ex ON ex.codi_exposicio=e.codi_exposicio GROUP BY f.nom asc,ex.titol WITH CHECK OPTION; select * FROM VistaNoActualitzable;

EJERCICIOS VISTAS SOBRE LA BD MATRICULES

Descargar script matrículas.

Para cada ejercicio argumenta si es o no actualizable, es decir, si admite o no operaciones de inserción, de borrado y de modificación.

  1. Crear una vista que recupere el DNI, el nombre y el año de inicio de todos los estudiantes que llevan más de cinco años en la escuela. DROP VIEW Alumnes_antics; CREATE VIEW Alumnes_antics AS SELECT e.DNI, e.nom, m.Any FROM ESTUDIANT e NATURAL JOIN MATRICULES m WHERE (YEAR(CURDATE())-(m.Any))>5 GROUP BY DNI; select * FROM Alumnes_antics; No es actualizable porque utiliza JOIN y GROUP BY.
  1. Cread una vista que recupere el código de la asignatura, el nombre, el responsable, el área, y el número de estudiantes que la están cursando, para todas las asignaturas del área "M". CREATE VIEW Estudiants_cursen_M AS SELECT e.codi_assignatura, e.nom, e.responsable, e.area, count(c.dni) FROM Assignatures e NATURAL JOIN Cursen c WHERE e.area="M" GROUP BY c.codi_assignatura; select * FROM Estudiants_cursen_M; No es actualizable; como en el ejercicio anterior, la utilización de JOIN hace que no lo sea y el GROUP BY.
  1. Crea una vista que muestre el número de matriculaciones de cada asignatura cada año, concretamente debe mostrar el año, el nombre de la asignatura y el número de matriculaciones. Hay que ordenar los resultados por año y número de semestre de forma descendente. CREATE VIEW N_MATRICULACIONS AS SELECT m.any, a.nom, count(m.codi_matricula) FROM MATRICULES m NATURAL JOIN ASSIGNATURA a JOIN ESTUDIANT e ON m.codi_matricula=e.codi_matricula GROUP BY a.nom, m.any, m.numero ORDER BY m.any desc,m.numero desc ; select * FROM N_MATRICULACIONS; No es actualizable, como en los ejercicios anteriores, por la utilización de JOIN y GROUP BY.