Bases de Datos I

Cursada 2008 Clase 6: Vistas Facultad de Ciencias Exactas Universidad Nac. Centro de la Pcia. de Bs. As.

BASES DE DATOS I

Vistas







Una relación que está realmente almacenada en la BD  Tabla base (o básica) Una vista es una tabla virtual  relación que es definida en términos de otras tablas y/o vistas. Es un elemento del esquema de la BD (catálogo) Es parte del esquema externo (arquitectura estándar) Puede ser accedida mediante consultas al igual que cualquier otra tabla. Habitualmente no materializada  las tuplas de una vista se calculan cuando el usuario hace una consulta sobre ella.

BASES DE DATOS I

Vistas
Definición:

CREATE VIEW nombre_vista [n_col1, …, n_coln] AS expresión_tabla [WITH [CASCADED |LOCAL ] CHECK OPTION]

Donde,  nombre_vista: nombre de la vista  [nom_col1 ,....]: nombres de columna de la vista  expresión_tabla: consulta que define la relación derivada (SELECT)  WITH CHECK OPTION: impide que se realice una actualización sobre la vista que viole su definición.

1

BASES DE DATOS I

Vistas DROP VIEW nombre_vista permite eliminar una vista del esquema de la BD. 




 Cascade: se elimina cualquier definición de vista o restricción a la que haga referencia




 Restrict: se aborta si hay objetos del esquema que la mencionan.

BASES DE DATOS I

Ventajas de las Vistas o Simplifica la percepción que los usuarios tienen de

la base de datos  presenta la información necesaria y oculta el resto o Facilitan la independencia de los datos o Define consultas frecuentes para no especificarlas

cada vez que se utilizan. o Presenta diferentes datos a diferentes usuarios,

aún cuando los estén compartiendo   importante cuando usuarios con diferentes intereses y diferente destreza manejan la misma base de datos.

BASES DE DATOS I

Desventajas de las Vistas o Actualizaciones VISTA  Tabla BASE restringidas o Restricciones estructurales  la estructura de una vista

se determina en el momento de su creación. Si los componentes cambian, éstos no son considerados. o El estándar impone restricciones sobre la creación y utilización de vistas, por ejemplo: una vista agrupada no puede ser combinada con otras tablas o vistas para formar una nueva vista. o Rendimiento: el proceso de resolución de la vista puede

exigir el acceso a múltiples tablas cada vez que se accede a ella ⇒ recursos de procesamiento adicionales!!  técnicas alternativas de mantenimiento de vistas

2

BASES DE DATOS I

Aplicaciones de Vistas


Definición de esquemas externos




Definición de vistas parciales del esquema lógico de la BD para distintos grupos de usuarios




Preparación de consultas




Definir y almacenar en la BD consultas complejas que son utilizadas frecuentemente




Aplicar políticas de seguridad (privacidad) de los datos

BASES DE DATOS I

Aplicaciones de Vistas: Seguridad Definiendo diferentes vistas y otorgando privilegios selectivamente sobre ellas, puede restringirse el acceso de los usuarios a ciertos subconjuntos de datos: o Vistas sobre determinadas columnas, ocultando

otras reservadas para usuarios específicos  por ej. Antecedentes penales. o Vistas sobre determinadas filas, ocultando otras

reservadas para usuarios específicos  por ej. Películas no aptas para el público infantil.

BASES DE DATOS I

Aplicaciones de Vistas: Seguridad (cont.) o Vistas sobre determinadas columnas y filas,

ocultando otras filas y columnas reservadas para usuarios específicos. o Se pueden definir vistas sobre conjuntos de tablas

(ensambles), seleccionando luego filas y columnas. o Se puede restringir el acceso a subconjuntos de otra

vista o combinaciones de vistas y tablas básicas.

3

BASES DE DATOS I

Seguridad: Autorización en SQL usando Vistas GRANT access ON view TO user_list GRANT SELECT ON Clientes TO Pedro Las vistas pueden ser usadas para simular el control de accesos a columnas individuales de una tabla.

BASES DE DATOS I

Aplicaciones de Vistas: Seguridad Ejemplo: Definición de un esquema externo para el departamento DCyS sobre la BD de docentes: CREATE VIEW Asignaturas_DCyS AS SELECT IdAsig, nombre, cuatrim, teo, prac FROM Asignatura WHERE dep=‘DCyS’ CREATE VIEW Profesor_DCyS AS SELECT Nro_Leg, nombre, TE, categ FROM Profesor WHERE dep=‘DCyS’ CREATE VIEW Docencia_DCyS AS SELECT Nro_leg, IdAsig, teo, prac FROM Docencia WHERE Nro_leg IN (SELECT Nro_leg FROM Profesor WHERE dep=‘DCyS’)


Privacidad: Los usuarios de cada Depto sólo tendrían autorización para consultar el esquema externo correspondiente a su departamento ).

BASES DE DATOS I

Ventajas de las Vistas: Indep. de los Datos o Pueden ocultar a los usuarios cambios de estructura en las tablas reales, si no son necesarios para sus aplicaciones.

EJEMPLO: La tabla ALUMNOS se divide en dos: una para los datos personales ALUMNO_PERS y otra para los académicos ALUMNO_ACAD. La original puede ser obtenida mediante una vista que sea el ensamble de las nuevas  puede llamarse ALUMNOS. Cualquier pieza de código que antes se refería a la tabla ALUMNOS, ahora se refiere a la vista ALUMNOS !!  y los usuarios no notan la diferencia !! PERO …. La independencia es sólo parcial  la tabla ALUMNOS puede ser actualizada … pero la vista ALUMNOS, en general NO !!

4

BASES DE DATOS I

Creación de Vistas CREATE VIEW Pericos AS


Una vista es equivalente a una consulta que ha sido ‘guardada’  







Eficiencia: sólo se accede a los componentes y se genera una vez

El SELECT que la genera puede tener cualquier complejidad La vista puede ser usada en cualquier otro SELECT

SELECT * FROM Animal WHERE (Category = ‘loro’) AND (Color = ‘verde’) AND (habla = ‘si’);

SELECT Avg(Precio), max(Precio) FROM Pericos WHERE (Tamano LIKE “%mediano%”);

BASES DE DATOS I

Vistas: otros ejemplos


Puede ser obtenida a partir de varias tablas y/o vistas:

CREATE VIEW Mascotas AS SELECT P.Nombre, P.Precio, V.FechaVenta, V.IdTienda FROM Pericos P, Ventas V WHERE P.IdAnimal = V.IdAnimal;

BASES DE DATOS I

Consultas sobre Vistas CREATE VIEW Clientes AS (SELECT nombre-sucursal, nombre-clte FROM Depositante, CtaCte WHERE Depositante.NroCuenta = CtaCte.NroCuenta) UNION (SELECT nombre-sucursal, nombre-clte FROM Deudor, Prestamo WHERE Deudor.NroDeudor = Prestamo.NroDeudor)

Obtener los clientes de la sucursal ‘Centro’ SELECT nomre-clte FROM Clientes WHERE nombre-sucursal = ‘Centro’

5

BASES DE DATOS I

Acceso a una Vista


Puede ser consultada como cualquier tabla base




Pero hay limitaciones (muchas!!) para actualizar vistas  dificultad para propagar las actualizaciones en cascada hacia los componentes.




Acceso a los datos necesariamente debe limitarse:  




Usuarios deben identificarse  sistema de autenticación (passwords) Cada usuario tiene limitaciones para manipular los datos – autorización

SQL provee herramientas de autorización pero no de autenticación (propietarias de cada sistema)

BASES DE DATOS I

Procesamiento de las Vistas





El DBMS interpreta cada consulta como si la vista fuese una tabla base.  Los DBMS típicos traducen la consulta en un lenguaje del estilo del álgebra relacional (de bolsas). Las vistas usadas por la consulta son reemplazadas por su equivalente algebraico, y empalmadas en el árbol generado por la estructura de la consulta (este proceso puede afectar el rendimiento, recordar desventajas)

BASES DE DATOS I

Ejemplo: Expansión (o resolución) de la Vista PROYNombre SELECTIdTienda=15

CREATE VIEW Mascotas AS SELECT P.Nombre, P.Precio, V.FechaVenta, V.IdTienda FROM Pericos P, Ventas V WHERE P.IdAnimal = V.IdAnimal;

Mascotas PROYNombre, Precio, FechaVenta, IdTienda SELECT Nombre FROM Mascotas WHERE IdTienda = 15;

JOIN Pericos

Ventas

6

BASES DE DATOS I

Vistas: Optimización de la Consulta El DBMS “optimiza” la consulta transformando la expresión algebraica en una que pueda ejecutarse más rápido: 1.

2.

Desplazar las selecciones hacia las hojas… Eliminar proyecciones innecesarias…

Nombre

IdTienda

Pericos

15

Ventas

La mayoría de las tuplas son eliminadas de Ventas antes del join (costoso).

BASES DE DATOS I

Diseño orientado a las prestaciones Fuente: Curso Diseño y Optimización de Bases de Datos. Depto. O.E.I. UPM

 Recurso que aconseja utilizar técnicas que buscan mejorar las prestaciones de un DBMS en la resolución de consultas, en la fase de diseño. Principales estrategias: o Particionamiento. o Desnormalización. Se incrementa la Redundancia de datos  Efectos indeseados en actualizaciones y borrados    realizar procesos extra para mantenimiento de la consistencia de la información. NO ABUSAR DE ESTE RECURSO !!!!

BASES DE DATOS I

Diseño orientado a las prestaciones Se basa en las consideraciones siguientes: o La

tasa de recuperaciones.

actualizaciones

con

respecto

a

o Las veces que se accede conjuntamente a los

atributos. o El tamaño de los atributos. o El tipo de proceso (Batch/On Line). o La prioridad de los procesos. o El tamaño de las tablas. o Otros …

7

BASES DE DATOS I

Diseño orientado a las prestaciones Desnormalización: Conjunto de técnicas empleadas en el diseño de BD para mejorar el rendimiento de las consultas, creando redundancia de datos. o Es dependiente del proceso de consulta a mejorar

 disminución de la independencia entre procesos y datos  Si cambia la consulta: el diseño debe ser revisado y adaptado si es necesario o No debe aplicarse sin un estudio previo muy

detallado.

BASES DE DATOS I

Diseño orientado a las prestaciones Hay cuatro técnicas de desnormalización: o Duplicación de Datos. o Derivación de Datos. o Claves Subrogadas. o Vector de Datos. o … y una adicional (sobrenormalización) 

Partición de Tablas.

BASES DE DATOS I

Diseño orientado a las prestaciones Duplicación de Datos: Introducción de atributos individuales de forma redundante con objeto de reducir el número de tuplas que deben ser revisadas en una consulta. EJEMPLO: En un esquema de BD dos tablas, CLIENTES y sus correspondientes PEDIDOS.  Se desea mejorar el tiempo de respuesta de una consulta de pedidos donde deben aparecer los correspondientes nombres de los clientes.

8

BASES DE DATOS I

Diseño orientado a las prestaciones Recuperar el Nombre del cliente para emitir los pedidos exige un acceso adicional al archivo Cliente. Esto puede mejorarse mediante la inserción del atributo Nombre en la tabla PEDIDO.

BASES DE DATOS I

Diseño orientado a las prestaciones Derivación de Datos: Creación de columnas para almacenar datos derivados de cálculos  reduce el número de tuplas accedidas al realizar un cálculo en una consulta. EJEMPLO: esquema de BD con tres tablas: PRODUCTOS, CLIENTES y PEDIDOS.  Se desea mejorar tiempo de respuesta de una consulta de facturación de clientes.

BASES DE DATOS I

Diseño orientado a las prestaciones El acceso a disco para recuperar totales cliente puede eliminarse insertando los atributos Tot_Productosy Facturación en la tabla CLIENTE.

9

BASES DE DATOS I

Diseño orientado a las prestaciones Claves Subrogantes: Reemplazo de la clave primaria original en caso de que ésta sea grande o de procesamiento computacionalmente complejo, por una clave artificial. EJEMPLO: En un esquema de BD de publicaciones con un encadenamiento de entidades débiles, la clave primaria para cada una de ellas incluye la clave primaria de sus antecesoras.  La recuperación de tuplas puede verse dificultada por el tamaño de la clave.

BASES DE DATOS I

Diseño orientado a las prestaciones

BASES DE DATOS I

Diseño orientado a las prestaciones Vectorización de Datos: La vectorización de un atributo multivaluado, generando un atributo para cada uno de los elementos (despliegue horizontal). EJEMPLO: En un esquema de BD con dos tablas: SOCIOS y PAGOS mensuales a lo largo de un año  Queremos obtener la información de cada uno de ellos con las cantidades correspondientes.

10

BASES DE DATOS I

Diseño orientado a las prestaciones

BASES DE DATOS I

Diseño orientado a las prestaciones Partición de Tablas: Técnica utilizada para mejorar tiempos de respuesta de consultas. Consiste en la división de una relación en dos o más, atendiendo a diferentes criterios obtenidos a partir del análisis de prestaciones que debe dar la BD. o Existen dos técnicas diferentes: oPartición Horizontal. oPartición Vertical.

BASES DE DATOS I

Diseño orientado a las prestaciones Partición Horizontal: Consiste en reducir el número de tuplas de relaciones sobre las que se realizan consultas  procura disminuir la cardinalidad de cada relación resultado de la partición. o Se basa en las operaciones de álgebra relacional selección y unión  resultan relaciones unióncompatibles respecto de la relación inicial. o La estrategia de acceso en estos casos se basa en técnicas de SQL dinámico (el patrón de acceso a la base de datos se conoce en tiempo de ejecución).

11

BASES DE DATOS I

Diseño orientado a las prestaciones Partición Vertical: Consiste en reducir el número de atributos de las relaciones sobre las que se realizan las consultas  procura disminuir el grado de cada relación resultante. o Se basa en la operación de proyección del

álgebra relacional  resultan relaciones que deben mantener la clave primaria de relación original.

BASES DE DATOS I

Actualización de Vistas Las vistas reciben las actualizaciones provenientes de sus componentes: Tabla Básica  Vista Si las vistas son actualizables deben propagar las actualizaciones a las tablas básicas: Vista  Tabla Básica

BASES DE DATOS I

Actualización de Vistas Actualización datos de la BD  las vistas deben ser actualizadas. . Recálculo . Mantenimiento Incremental: computa y aplica sólo los cambios incrementales a las vistas o

o Modificación de vistas  problemas inherentes de ambigüedad. o Suprimir una tupla en una vista  ‘borrarla de la tabla básica’ ? o ‘actualizar alguna columna para que ya no sea seleccionada para la vista’?

12

BASES DE DATOS I

Actualización de Vistas (cont.) o Insertar una fila en una vista  ‘insertar una tupla en la tabla base’? o ‘actualizar una tupla existente para que ahora pueda ser seleccionada para la vista’? o Actualizar una tupla en una vista que involucra join puede cambiar la semántica de otras columnas que no son proyectadas por la vista.

BASES DE DATOS I

Actualización de Vistas Las vistas no mantienen COPIAS de los datos  cuando se modifica una vista, se están modificando las tablas base. Cuándo es posible sin ambigüedades? o No afecta más de una tabla. o Si la vista deriva de más de una tabla, pero la actualización afecta sólo a una.

BASES DE DATOS I

Actualización de Vistas (cont.) o Si no afecta una columna que contiene información derivada. o Si no causa error afectando tablas que no tienen valores por defecto definidos o que no aceptan nulos para alguna columna. o Se verifica si WITH CHECK OPTION ha sido especificado.

13

BASES DE DATOS I

Vistas actualizables: en síntesis Vista sobre una tabla básica: • el sistema traducirá la actualización sobre la vista en una operación de actualización sobre la relación básica  siempre que no se viole ninguna restricción de integridad definida sobre dicha relación

BASES DE DATOS I

Vistas actualizables: en síntesis Vista sobre una concatenación de relaciones: • la actualización sólo puede modificar UNA de las tablas básicas • la actualización modificará la relación básica que cumpla la propiedad de conservación de la clave (‘Key preserved’, aquella relación tal que su clave primaria podría ser también clave de la vista) • la actualización no se realizará si viola alguna de las restricciones definidas sobre la relación básica que se va a actualizar

BASES DE DATOS I

Vistas Actualizables de una tabla básica


Una vista obtenida a partir de una única tabla básica es actualizable si  conserva una clave (primaria o alternativa)  no contiene funciones de agregación  no incluye DISTINCT  No incluye subconsultas en el SELECT




Únicas aceptadas por SQL:1992




Se denominan vistas π - σ (vistas PS)

14

BASES DE DATOS I

Vistas Actualizables de una tabla básica


Vista obtenida a partir de varias tablas básicas es actualizable si 






la actualización altera la tabla correspondiente a la Key Preserved Manifiestos para SQL:1999 y versiones posteriores  incluir otras operaciones, por ej. intersección

Se denominan vistas π - σ - (vistas PSJ)

BASES DE DATOS I

Vistas Actualizables FacturaxCompra(IDFact, IDProd, Cant) 121 121 122

VISTA 

57 82 57

Prod(IDProd, Descripcion)

3 2 1

57 82 59

Alim Gatos Alim Peces Alim Perros

LineaFact(IDFact, Prod.IDProd, Descripcion, Cantidad) 121 121 122

57 82 57

32

Alim Gatos Alim Peces Alim Gatos

3 2 1

Si se intenta modificar Prod.IDProd en la vista, se cambia la PK de la tabla Prod  ésto no agregará una nueva fila en FacturaxCompra, que pasa con la primera tupla de FacturaxCompra?

BASES DE DATOS I

Actualización de Vistas Cuando se inserta o actualiza una tupla en una tabla base, puede ocurrir que esta tupla ya no pertenezca a la vista. Ejemplo: CREATE VIEW alumnos_Tandil AS ( SELECT * FROM Alumnos WHERE ciudad=‘Tandil’ ) Si se hace UPDATE alumnos_Tandil SET ciudad=‘Rauch‘ Todos los registros de la vista son actualizados con un valor diferente de ciudad y dejan de pertenecer a la vista.

15

BASES DE DATOS I

Vistas Actualizables Esto es legal, pero este efecto puede propagar errores inadvertidos por el usuario. Solución  WITH CHECK OPTION en la creación de la vista. CREATE VIEW alumnos_Tandil AS ( SELECT * FROM Alumnos WHERE ciudad=‘Tandil’ ) WITH CHECK OPTION Cualquier inserción o actualización que haga ‘migrar’ una tupla de la vista ≡ la tupla ya no satisface la condición del query que define la vista  error en tiempo de ejecución  rechaza la operación

BASES DE DATOS I

Vistas Actualizables: With Check Option Cláusula necesaria para vistas actualizables. Todos los INSERT y UPDATE sobre la vista serán verificados para asegurar que los datos satisfacen la definición de la vista  si no es así : Rollback. LOCAL Chequea la integridad de la vista corriente. CASCADE (opción por defecto) Chequea la integridad en la vista corriente y en toda otra dependiente de ella.

BASES DE DATOS I

Modificación de Vistas vía Triggers


Se puede “interceptar” una modificación a una vista mediante un trigger instead-of  opción especial para vistas (recordar que los triggers se definen sobre tablas !!)




Triggers INSTEAD OF proveen una manera transparente para actualizar vistas que no pueden ser modificadas directamente vía las sentencias SQL DML.

16

BASES DE DATOS I

Modificación de Vistas vía Triggers


El trigger INSTEAD OF se dispara en lugar de ejecutar la sentencia disparadora  el trigger ejecuta UPDATE, INSERT, o DELETE directamente sobre las tablas subyacentes, en forma invisible para el usuario.




Por defecto, triggers INSTEAD OF se activan ‘for each row’.

BASES DE DATOS I

Modificación de Vistas vía Triggers: Ejemplo CREATE VIEW info_supervisor AS SELECT E.nombre,E.Nro_estud,E.Id_superv,P.id_oficina FROM Estudiante E, Profesor P WHERE E.Id_superv = P.legajo; CREATE TRIGGER insert_info_supervisor INSTEAD OF INSERT ON info_supervisor REFERENCING NEW AS n – nuevo supervisor FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM Estudiante E WHERE E.Nro_estud = n.Nro_estud) THEN INSERT INTO Estudiante(Nro_estud,nombre,supervisor) VALUES(n.Nro_estud, n.nombre, n.Id_superv); ELSE UPDATE Estudiante SET Estudiante.Id_superv = n.Id_superv WHERE Estudiante.Nro_estud = n.Nro_estud; END IF; IF NOT EXISTS (SELECT * FROM Profesor P WHERE P.legajo= n.Id_superv) THEN INSERT INTO Profesor VALUES(n.Id_superv, n.id_oficina); ELSE UPDATE Profesor SET Profesor.id_oficina = n.id_oficina WHERE Profesor.legajo = n.id_superv; Pueden plantearse triggers similares para las END IF; operaciones de UPDATE y DELETE. END;

BASES DE DATOS I

Vistas Materializables





Una vista puede ser materializada  su contenido puede ser precomputado y almacenado por el DBMS Características:  Cuestiones de performance en la elaboración de la consulta  Confiabilidad  Mantenimiento de vistas
cómo mantener consistencia entre las tablas de la BD y los resultados materializados?
Actualización por regeneración o incremental?
Implementación de triggers para realizar estas funciones o programas o soporte por el DBMS.  

Selección  cuáles conviene materializar? Obtener resultados de consultas usando vistas  reescritura de consultas para utilizar los resultados materializados.

17

BASES DE DATOS I

Vistas Materializables: Actualización Incremental


Los cambios a una tabla o derivados se denomina: diferencial




El conjunto de tuplas insertadas  ∆R




El conjunto de tuplas borradas  ∇R




El conjunto de tuplas modificadas puede obtenerse considerando la baja de los estados viejos, y las altas de los nuevos.




Las operaciones que representan altas y bajas 

⊕ unión de conjuntos disjuntos



 diferencia de conjuntos estrictamente incluidos uno en el otro

Rnueva = (Rvieja  ∇R) ⊕ ∆R Para las vistas  Vnueva = (Vvieja  ∇V) ⊕ ∆V

BASES DE DATOS I

Vistas Materializables: Actualización Incremental








Selección 

σc(R  S) ⇒ σc(R)  σc(S)



σc(R ⊕ S) ⇒ σc(R) ⊕ σc(S)

Proyección 

πL(R  S) ⇒ πL(R)  (πL(R) - πL(R - S))



πL(R ⊕ S) ⇒ πL(R) ⊕ (πL(S) - πL(R))

Producto Cartesiano 

(R  S) X T ⇒ (R X T)  (S X T)



(R ⊕ S) X T ⇒ (R X T) ⊕ (S X T)

BASES DE DATOS I

Vistas Materializables: Actualización Incremental








Unión 

(R  S) T ⇒ (R T)  (S - T)



(R ⊕ S) T ⇒ (R T) ⊕ (S - T)

Intersección 

(R  S)  T ⇒ (R  T)  (S  T)



(R ⊕ S)  T ⇒ (R  T) ⊕ (S  T)

Ensamble 

(R  S)  T ⇒ (R  T)  (S  T)



(R ⊕ S)  T ⇒ (R  T) ⊕ (S  T)

18

BASES DE DATOS I

Vistas Materializables: Actualización Incremental


Diferencia 

(R  S) - T ⇒ (R - T)  (S - T)



(R ⊕ S) - T ⇒ (R - T) ⊕ (S - T)



T - (R  S) ⇒ (T - R) ⊕ (T  S)



T - (R ⊕ S) ⇒ (T - R)  (T  S)

BASES DE DATOS I

Actualización de Vistas: consideraciones


Actualizar una vista (como si estuviera materializada) debe resultar en la misma relación que si se modificaran las tablas base aplicando una o más actualizaciones acti y luego aplicando la definición de la vista Actualizar(V(D)) = V(acti(D))




La actualización es una propiedad semántica  las vistas deberían ser actualizables independientemente de su estructura




Simetría  Si una vista resulta de la intersección de tablas, la baja debe ser reflejada en ambas tablas, aunque algebraicamente con una es suficiente

BASES DE DATOS I

Actualización de Vistas: consideraciones


Tener en cuenta acciones referenciales y la activación de triggers asociados




Una actualización en una vista debería transformarse en el mismo tipo de operación en las tablas base




Las reglas de actualización no deben dar por supuesto que las tablas están normalizadas




Considerar las modificaciones como una sucesión de bajas + altas puede tener consecuencias indeseables.

19