Story Transcript
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