SQL sobre integridad, vistas y confidencialidad, cada ejercicio corregido explora uno de estos conceptos.
Estados
El ejemplo utilizado es el de una base de datos “biblioteca” que gestiona lectores, libros identificados por un número y pertenecientes a una determinada categoría de obras (novela policial, novela de aventuras, etc.) y préstamos de libros.
LIBRO(Cquitar,Título,Autor,Categoría)
CONDUCIR(Número,Nombre,Dirección,Consumo)
LISTO(Calificación, número de lector, fecha de préstamo, fecha de regreso, fecha de reinicio)
Ejercicio 1
Defina las siguientes restricciones de integridad en SQL2:
- El consumo de una unidad es Alto, Medio o Bajo (restricción de dominio)
- Cualquier préstamo debe realizarse a un lector existente en la base de datos, en relación con un libro existente (restricción referencial)
- Cada autor sólo puede clasificarse en una categoría (división funcional: autor a categoría).
- ALTER TABLE Reader ADD (CONSTRAINT domain_conso CHECK (Consumo IN ('Alto', 'Medio', 'Bajo')));
- ALTER TABLE Préstamo AGREGAR (CONSTRAINT fk_reader CLAVE EXTRANJERA Número de lector REFERENCIAS Lector); ALTER TABLE Préstamo AGREGAR (RESTRICCIÓN fk_book EXTRANJERO CLAVE Cote REFERENCIAS Libro);
- ALTER TABLE Libro AGREGAR (RESTRICCIÓN autor_categoría VERIFICAR (NO EXISTE (SELECCIONAR Autor DEL GRUPO de libros POR Autor TENER CONTEO (Categoría) > 1)));
Ejercicio 2
Pregunta 1: Definición de una vista
Proporcione los comandos SQL para crear las siguientes vistas:
a) novela policíaca, visión definida por el siguiente diagrama:
OFICIAL DE POLICÍA (Calificación, Título, Autor)
b) lectores de novelas policíacas, visión definida por el siguiente diagrama:
LECTOR POLICÍA (Número, Nombre, Dirección, Calificación, Fecha de Préstamo, Fecha de Devolución)
c) lectores no autorizados a tomar prestados libros en caso de que hayan olvidado devolver alguno de sus préstamos anteriores:
LECTOR PROHIBIDO (Número, Nombre)
d) una relación STATS-DE-SPRETS que describe para cada lector el número de préstamos desde la fecha D.
Pregunta 2 Usando una vista.
Un usuario con derecho a consulta desde las vistas anteriores POLICÍA y POLICÍA-LECTOR formula las siguientes preguntas:
a) enumere los nombres de los lectores que pidieron prestadas novelas policiales en la fecha D,
b) proporcione los nombres de los lectores que pidieron prestadas más de tres novelas policíacas de Simenon el mismo día.
Exprese estas preguntas en SQL sobre las vistas anteriores. Expresa también estas preguntas sobre las relaciones básicas LECTOR, LIBRO y LISTO.
Pregunta 3: Procesar una pregunta relacionada con una vista
Las vistas definen relaciones virtuales cuyo contenido nunca se calcula realmente. Al crear una vista, solo se almacena la definición de la vista en una relación de metabase. Cuando un usuario hace una pregunta sobre una vista, el DBMS la transforma en una pregunta sobre las relaciones correspondientes en la base de datos.
Esta transformación se realiza directamente en la formulación LMD de la consulta [Stonebraker75] o modificando el árbol sintáctico algebraico de la pregunta [Astrahan76]. En ambos casos la transformación consiste en hacer un AND entre la pregunta del usuario y las preguntas de definición de vista.
Describa el algoritmo general para transformar una pregunta formulada en vistas en una pregunta expresada modificando el árbol de sintaxis de la pregunta. Muestre cómo funciona en la pregunta 2.b.
Pregunta 4: Actualización a través de una vista.
Las actualizaciones entre vistas plantean diferentes problemas según la definición de la vista: valores no definidos (por ejemplo, inserción de una nueva tupla en READER-PROHIBITED), posibles inconsistencias (por ejemplo, inserción o eliminación de una tupla de READER-DE-POLICIER), falta de definición de la actualización (por ejemplo eliminación de una tupla en STATS-DES-PRETS del lector nº 1347).
¿A qué tipo de definición de vista corresponde cada uno de estos problemas? ¿Bajo qué condiciones se puede pasar una actualización de una vista a la base de datos sin ambigüedad?
Pregunta 5: Visiones concretas o clichés
Algunos DBMS ofrecen, además del mecanismo de visualización, la noción de cliché Dónde vista concreta [Adiba 80] o vista materializada. A diferencia de una vista, el contenido de una vista materializada se calcula según su definición y se almacena en la base de datos. Una vista materializada refleja el estado de la base de datos en este momento. t donde fue calculado.
Más específicamente, llamamos instantánea a una vista concreta que el sistema recalcula periódicamente. Las actualizaciones de la base de datos deben reflejarse en la instantánea cuando se actualiza. ¿Cómo podemos evitar volver a calcular sistemáticamente la instantánea completa cada vez que se actualiza?
a) Definición de la visión del OFICIAL DE POLICÍA
crear vista OFICIAL DE POLICÍA
as seleccionar Calificación, Título, Autor
de LIBRO
dónde Categoría = “novela policial”;
Esta vista es un ejemplo de una definición de esquema externo donde el subconjunto de la base de datos se define como un subconjunto de nivel de ocurrencia.
b) Definición de la vista LECTOR POLICÍA
crear vista LECTOR DE POLICÍA
como seleccionar Número, nombre, dirección, calificación, fecha de préstamo, fecha de devolución
de LIBRO L, LECTOR E, LISTO P
dónde L.Cote = P.Cote y E.Número = P.NúmeroLector y L.Categoría = “novela policial”;
Esta definición de vista es un ejemplo de reestructuración de la información. El usuario sólo ve una relación en la vista cuando en realidad la base está formada por tres relaciones. Esta posibilidad permite simplificar el trabajo de los usuarios presentándoles la información en el formato que más les convenga, evitando por ejemplo la formulación de combinaciones al consultar la vista.
c) Definición de la vista PROHIBIDA PARA EL LECTOR
crear vista LECTOR PROHIBIDO (Número, Nombre)
como seleccionar Número, Nombre
de LECTOR E, LISTO P
dónde E.Número = P.NúmeroLector
y (FechaPréstamo + 15) < FechaSistema
y La fecha de retorno es NULA;
Suponemos que el sistema le permite obtener la fecha de hoy utilizando la palabra clave "system-date". La definición de la vista es dinámica, en el sentido de que su contenido varía cada día automáticamente según la fecha. Esta definición de vista también le permite cambiar los nombres de los atributos para adaptarse a los hábitos del usuario.
d) Definición de la vista STATS-DES-READS
crear vista ESTADÍSTICAS DE PRÉSTAMO (Número de lector, Número de préstamo, Nombre)
como seleccionar Número, contar(*)
de LECTOR E, LISTO P
dónde Número E = P.Numector
y Fecha de préstamo > “d”
agrupar por número de lector;
El atributo NumberLoans es información inferida en la medida en que no se almacena como tal en la base de datos, sino que se calcula a partir de la información almacenada.
Pregunta 2: Usando una vista
a) La pregunta del usuario es
seleccionar distinto apellido
de LECTOR DE POLICÍA
dónde FechaPréstamo = “d”;
La misma pregunta sobre las relaciones base sería:
seleccionar apellido
de LIBRO L, LECTOR E, LISTO P
dónde L.Cote = P.Cote
y Número E=Numector P.
y L.Categoría = “novela policial”
y FechaPréstamo = “d”;
Esta pregunta es mucho más compleja de formular porque tiene dos uniones, mientras que la pregunta de vista es muy sencilla ya que solo tiene una restricción. Ésta es una de las ventajas de las vistas: simplificar el trabajo de los usuarios.
b) La pregunta del usuario expresada en SQL es:
seleccionar distinto apellido
de POLICÍA LECTOR E, POLICÍA P
dónde Clasificación E = Clasificación P y Autor = “SIMENÓN”
grupo por Número de lector, Fecha de préstamo
teniendo contar (Número de libro)>3;
La pregunta como debería haberse formulado en la base de datos es:
seleccionar distinto apellido
de LECTOR E, LIBRO L, LISTO P
dónde P.Cote=L.Cote y Autor=”Simenon”
y L.Categoría=”novela policial”
y P.ReaderNum=E.Número
grupo por Número de lector, Fecha de préstamo
teniendo contar (Número de libro) > 3;
Pregunta 3: Manejar una pregunta sobre una vista
El algoritmo de transformación de una pregunta formulada en una o más vistas consiste en “sumar” la pregunta del usuario y las preguntas que definen cada una de las vistas. El procesamiento es iterativo porque una vista se puede construir sobre otras vistas.
Tanto que la pregunta implica el uso de vistas
- buscar en la metabase árboles de definición de vistas
- construir el árbol sintáctico de la pregunta formulada
- cambie los nombres de los atributos de la vista a los nombres de atributos correspondientes si hay un cambio de nombre de los atributos en la vista
- transformar los árboles en uno haciendo coincidir el resultado de una vista con el nombre de la vista en la pregunta
- posiblemente simplificar el árbol eliminando operaciones redundantes
ftq
Pasar el árbol de preguntas transformado al optimizador de preguntas
Aplicación del algoritmo en el ejemplo:
1) El árbol de preguntas es el siguiente:
2) Los árboles de las dos vistas son los siguientes:
3) Agregar árboles haciendo coincidir el resultado de una vista y el nombre de la vista:
4) En la definición de vistas no hay cambio de nombre de atributos.
5) Simplificación del árbol.
Usamos la relación LIBRO dos veces para unir un número de libro. También existen dos restricciones idénticas con el criterio . Solo mantenemos cada una de estas operaciones una vez.
Pregunta 4: Actualizar a través de una vista
La actualización mediante vistas está prohibida en el caso general porque plantea varios problemas:
(1) Atributos no definidos en la vista: cualquier inserción de una tupla en la vista resulta en la inserción de una tupla en la base con un valor indeterminado para atributos no visibles en la vista. Por ejemplo, la inserción de una nueva tupla en la vista LECTOR PROHIBIDO implica la inserción de una tupla LECTOR cuya dirección se desconoce. Cualquier vista que incluya una proyección en su definición provoca este tipo de problemas.
(2) Riesgos de inconsistencia cuando una relación de vista se obtiene uniendo varias relaciones reales. Por ejemplo, ¿insertar una tupla en POLICE-READER significa agregar un préstamo para un lector existente, o insertar un préstamo y un nuevo lector, o incluso la inserción de un préstamo, un libro y un lector?
La ambigüedad es la misma para las eliminaciones de tuplas. El impacto de una actualización depende de la semántica de la vista y la aplicación. No puede ser automático. La semántica de las operaciones de actualización debe describirse para cada vista.
(3) Pérdida de significado de la actualización: la eliminación, por ejemplo, de la tupla (1347, 45) de la vista STATS-DES-PRETS, no se puede pasar al nivel de relaciones base. Esta pérdida de significado es característica de las vistas construidas utilizando agregados y/o funciones de cálculo de suma o promedio.
En general, los DBMS prohíben las actualizaciones entre vistas. Algunos los permiten en el caso en que la semántica de las actualizaciones se pueda definir sin ambigüedades. Este es el caso si la definición de vista solo usa el operador de restricción de una relación (ni función de unión, ni de proyección, ni de agregado, ni de cálculo).
En este caso una tupla de la vista corresponde a una única tupla de la base. También podemos autorizar al operador de proyección si el SGBD gestiona valores indeterminados.
Pregunta 5: Visiones concretas o clichés
El tiempo necesario para calcular y almacenar una instantánea puede ser significativo si el subconjunto correspondiente de la base de datos es grande. Para reducir este tiempo de ejecución, que para una instantánea es repetitivo, el sistema puede utilizar un mecanismo de actualización diferencial. Sólo las tuplas insertadas, eliminadas o modificadas en la base de datos desde el último cálculo se insertan, eliminan o modifican a su vez en la instantánea.
Es necesario que el DBMS mantenga las tuplas insertadas o eliminadas de las relaciones de la base de datos en una relación especial. Las eliminaciones de tuplas plantean un problema particular en el caso general: una tupla instantánea puede provenir de varias tuplas base y la eliminación de sólo una de estas tuplas no debería dar lugar a la eliminación de la tupla instantánea.
Este problema se estudia en [Kerhervé 86]. La idea es asociar a cada tupla de la vista un contador que indique el número de motivos de su presencia. Al eliminar una tupla de la base, se decrementa el contador correspondiente. Cuando el contador llega a cero, se elimina la tupla de instantáneas.
Ejercicio 3
Pregunta 1: Ejemplo de uso
Escribir el comando que permite transmitir al usuario de Dupont los derechos de lectura e inserción sobre la relación LIBRO, garantizándole el derecho a transmitir sus derechos.
Pregunta 2: Comprobación de la asignación de derechos
Describir los principios del algoritmo GRANT. La información relativa a la gestión de autorizaciones se almacena en una relación del sistema de metabase llamada DERECHA. Especifique el esquema de esta relación según el algoritmo GRANT.
Pregunta 3: Verificación de la retirada de un derecho
En un sistema descentralizado, el procedimiento para retirar derechos es delicado. De hecho, es necesario retirar el derecho al usuario indicado, pero también retirarlo recursivamente a todos los usuarios a quienes éste se lo había transmitido. Por otro lado, un usuario puede recibir el mismo derecho de varios donantes diferentes. Cuando uno de ellos se lo quita, se queda con el que proviene de diferentes fuentes. Especifique los principios del algoritmo REVOKE. Indique cualquier modificación en el diagrama de la relación DERECHA.
Pregunta 4: Verificación de los derechos de acceso durante las manipulaciones.
La verificación de que un usuario tiene derecho a realizar una determinada operación sobre una relación debe realizarse rápidamente. ¿Cómo puede responder el sistema a esta restricción?
Pregunta 5: Derechos a una vista
Cuando un usuario crea una vista, ¿qué derechos se le asignan automáticamente en la relación virtual?
Pregunta 1: Ejemplo de uso
conceder seleccione insertar en LIBRO
para dupont
con opción de subvención
Pregunta 2: Verificación de la asignación de derechos.
Una solicitud GRANT tiene los siguientes parámetros:
- el usuario que emitió la SUBVENCIÓN, llamado donante;
- el usuario a quien se otorgan los derechos, denominado beneficiario;
- en la lista de derechos concedidos (un derecho concedido con o sin derecho de transmisión), cada una de las dos posibilidades se identifica como un derecho diferente;
- la relación sobre la cual se otorgan derechos se llama objeto.
La relación CORRECTA le permite almacenar esta información. Su diagrama puede ser el siguiente:
DERECHO (relación-objeto, nombre-beneficiario, nombre-donante,
derechos-intransferibles, derechos-transferibles)
Este esquema no está en la primera forma normal porque los atributos-derechos-transmisibles y derechos-no-transmisibles contienen múltiples valores, pero permite un almacenamiento más compacto. Por el contrario, requiere un tratamiento para analizar los derechos presentes.
El algoritmo GRANT incluye los siguientes pasos:
1) comprobar que el donante tiene los derechos que quiere conceder con el derecho de transmisión.
Para ello es necesario extraer de la relación DERECHO los derechos que tiene el autor de la CONCESIÓN sobre la relación 'objeto'.
seleccionar derechos-transferibles
de correcto
dónde nombre-beneficiario = “donante”
y relación-objeto = “objeto”;
2) si el donante realmente tiene el derecho de transmitir 'derechos de lista', estos deben almacenarse en la relación DERECHA mediante una consulta INSERT, ya sea como derechos transferibles o como derechos no transferibles dependiendo de la presencia o ausencia de la cláusula. con opción de subvención.
el algoritmo de conceder generará la siguiente consulta:
insertar en CORRECTO
valores(“objeto”, “beneficiario”, “donante”, cero, “lista-derechos”)
Dónde
insertar en CORRECTO
valores(“objeto”, “beneficiario”, “donante”, “lista-derechos”, nula)
Pregunta 3: Verificación de la retirada de un derecho
En un sistema descentralizado de gestión de derechos donde los derechos se otorgan, verifican y retiran dinámicamente, controlar la retirada de derechos no es trivial. En principio, verificar el origen de los derechos de un usuario requiere rastrear el origen de los derechos de un usuario. grafico transmisión de autorizaciones para verificar el origen de cada derecho y para garantizar que un usuario no se haya retransmitido un derecho a sí mismo por una ruta tortuosa.
En [Lindsay79] se desarrolló una solución más sencilla que construir el gráfico. Cada derecho lleva asociado un sello que indica la fecha en la que se concedió el derecho. El sello permite reconocer los derechos que le quedan a un usuario t desaparece. Todos los derechos derivados recursivamente de X y posteriores a t también debe desaparecer. El formato de la relación DERECHA pasa a ser el siguiente:
LEY (relación de objeto,
Nombre del Beneficiario,
nombre del donante,
derechos intransferibles,
sellos-derechos-transferibles)
Cada derecho transferible ya no está representado por un bit sino por el momento en que fue concedido. Un derecho no concedido tiene el sello 0. Un ejemplo del contenido de la relación (considerando sólo derechos transferibles) es el siguiente:
relación de objeto | Nombre del Beneficiario | nombre del donante | seleccionar | insertar | borrar | actualizar |
LIBRO | X | PARA | 15 | 15 | 0 | 0 |
LIBRO | X | B | 20 | 0 | 20 | 0 |
LIBRO | Y | X | 25 | 25 | 25 | 0 |
LIBRO | X | VS | 30 | 0 | 30 | 0 |
Supongamos que en el momento t = 35, B retira todos los derechos que había concedido a X en el momento t = 20. La tupla (LIBRO, X, B, 20, 0, 20, 0) debe desaparecer. Los derechos que quedan en X son entonces (LIBRO, X, (A,C), (15, 30), 15, 30, 0). Los derechos que X transmitió son (LIBRO, Y, X, 25, 25, 25, 0).
El derecho DELETE otorgado por X en el momento t = 25 debe desaparecer ya que ahora consideramos el de los derechos recibidos por X antes del momento 20. El algoritmo es el siguiente:
proceso REVOCAR (b: nombre-beneficiario, da: derecho-acceso, r: relación-objeto, d: nombre-donante)
/* elimina el privilegio 'da' otorgado por 'd' a 'b' en 'r' */
actualizar CORRECTO
establecer da = 0
dónde nombre-beneficiario = b
y nombre-donante = d
y relación de objeto = r;
/* define el sello mínimo que permanece en 'b' para la derecha 'da' en la relación 'r' */
seleccionar MIN(da) en min
de CORRECTO
dónde nombre-beneficiario = b
y relación de objeto = r;
/* elimina todos los derechos 'da' otorgados por 'b' en 'r' antes de 'min' */
seleccionar Nombre del Beneficiario en lista de retiro
de CORRECTO
dónde nombre-donante='b'
y relación-objeto='r'
y da<'min';
sí lista de sangría ¬ vacía
entonces para cada elemento 'l' de lista de retiro
hacer
REVOCAR (l, da, r, b)
para
fsi
fin del procedimiento
El algoritmo es recursivo y retira gradualmente los derechos de todos aquellos a quienes ya no se les pueden haber concedido.
Ejemplo :
Si A retira su derecho a X, debe retirarlo recursivamente a todos los demás. El algoritmo es el siguiente:
- 1er pase: REVOCAR (X, da, r, A)
elimina de X el 'da' correcto que le llega de A
mínimo de sellos restantes en X: 15
==> eliminar el derecho a Y dado en t = 10 (por lo tanto antes de 15): REVOCAR (Y, da, r, Y)
- 2do pase: REVOCAR (Y, da, r, X)
Y ya no tiene el derecho 'da', por lo que este derecho debe ser retirado a aquellos a quienes se lo transmitió:
REVOCAR (X, da, r, Y)
- 3er pase: REVOCAR (X, da, r, Y)
X ya no tiene el derecho 'da', por lo que debe ser retirado a aquellos a quienes se lo transmitió:
REVOCAR (Z, da, r, X)
Pregunta 4: Verificación de los derechos de acceso durante las manipulaciones.
Durante cada solicitud de un usuario, el sistema verifica que este usuario realmente tenga derecho a ejecutar este tipo de solicitud sobre las relaciones citadas. Esta verificación podría realizarse accediendo a la relación DERECHA, pero este acceso genera una entrada-salida por solicitud, lo cual es engorroso.
Una mejor solución es acceder solo a la relación CORRECTA una vez al inicio de la sesión para cada usuario. Cuando un usuario se conecta al DBMS, después de la autenticación (verificación de que tiene derecho a utilizar el DBMS), el sistema lee de la memoria central la parte de la relación DERECHA que le concierne. Este extracto de la relación DERECHA para un usuario 'U' se define por:
seleccionar * en DERECHOS-TM
de CORRECTO
dónde nombre-beneficiario = 'U';
La relación DERECHOS-MC deberá almacenarse en la memoria central en una ubicación reservada al sistema a la que un usuario nunca podrá tener acceso para evitar cualquier modificación fraudulenta o accidental de esta relación.
Pregunta 5: Derechos de acceso a una vista
Cuando un usuario crea una vista, no puede obtener derechos sobre ella mayores que los que tiene sobre las relaciones en la base de datos utilizada para construir la vista. Los derechos que se conceden automáticamente al creador de una vista son iguales a todos los derechos que tiene el usuario sobre cada una de las relaciones que componen la vista.
Los derechos adquiridos sobre la vista sólo son transferibles si fueran por relaciones de base. Para crear la vista, el usuario debe tener permiso de lectura (seleccionar) sobre las relaciones utilizadas para componer la vista. Por lo tanto, tendrá al menos derechos de lectura sobre la vista creada.
ES 
FR 
EN