Bases de Datos I

Cursada 2008 Clase 3: Restricciones de Integridad (de tablas y generales) Facultad de Ciencias Exactas Universidad Nac. Centro de la Pcia. de Bs. As.

1

BASES DE DATOS I

RESTRICCIONES DE INTEGRIDAD (RI)

Una RI describe condiciones que toda instancia legal de una relación debe cumplir  descripción de estados correctos en tiempo de diseño  Actualizaciones que violen una RI deben ser rechazadas.  Pueden utilizarse para:





Asegurar

la semántica de una aplicación (ej. DNI identifica a una persona, es clave)
Prevenir las inconsistencias (ej. edad < 100; NombreCarrera debe ser una hilera de caracteres)

2

BASES DE DATOS I

RESTRICCIONES DE INTEGRIDAD (RI)







Tipos de RI’s: Inherentes: clave primaria, clave extranjera. Clasificación:    

de dominio (DOMAIN) de tabla asociada a atributos (CHECK) de tabla asociada a tuplas (CHECK) generales de la base de datos (ASSERTION)

3

BASES DE DATOS I

RESTRICCIONES DE DOMINIO (ejemplo) CREATE DOMAIN ColorArcoIris AS char(15) DEFAULT ‘desconocido’ CONSTRAINT color-usado CHECK (VALUE IN (‘rojo’, ‘amarillo’, ‘azul’, ‘naranja’, ‘índigo’, ‘violeta’, ‘verde’)); CREATE TABLE Pintura ( PintId INTEGER, ColorPint ColorArcoIris, NroCatalogo INTEGER, Precio FLOAT, Descuento FLOAT, Caracteristica CHAR(30), …, PRIMARY KEY (PintId), …);

DOMINIO

4

BASES DE DATOS I SQL – INTEGRIDAD REFERENCIAL



Claves extranjeras  cláusula FOREIGN KEY como parte de CREATE TABLE NbreTabla sintaxis:

CREATE TABLE NbreTabla

...... [, FOREIGN KEY (ListaNbreColumna) REFERENCES NbreTablaReferenciada [(ListaNbreColReferenciada)] [ MATCH {FULL | PARTIAL | SIMPLE}] [ ON DELETE { NO ACTION | RESTRICT | CASCADE | SET NULL | SET DEFAULT}] [ ON UPDATE { NO ACTION | RESTRICT | CASCADE | SET NULL | SET DEFAULT}] [, FOREIGN KEY.....]]);

Formalmente: NbreTabla[ListaNbreColumna]0 • Rango  BETWEEN, NOT BETWEEN (incluye los extremos del rango) exp BETWEEN exp1 AND exp2 ≡ exp1exp2 CantMaterias BETWEEN 0 AND 45 • Membresía o pertenencia  IN, NOT IN Lista explícita  NomCarrera IN (‘Sistemas’, Fisica’, ‘Matematica’) Lista implícita  NomCarrera IN () • Test de Nulidad  IS NULL, IS NOT NULL exp IS NOT NULL ≡ NOT (exp IS NULL) FechaCreacion IS NULL

8

BASES DE DATOS I

RESTRICCIONES DE INTEGRIDAD (RI) CREATE DOMAIN ColorArcoIris CHAR (11) DEFAULT ‘desconocido’ CONSTRAINT color-usado CHECK (VALUE IN (‘rojo’, ‘amarillo’, ‘azul’, ‘naranja’, ‘índigo’, ‘violeta’, ‘verde’)); DOMINIO CREATE TABLE Pintura ( PintId INTEGER, ColorPint ColorArcoIris, NroCatalogo INTEGER CHECK (NroCatalogo >= 1 AND NroCatalogo 0 ) OR (Precio representa parámetro opcional DROP TRIGGER [nombre trigger]

24

BASES DE DATOS I

TRIGGERS: tiempo de activación AFTER | BEFORE  después o antes de ejecutar la sentencia disparadora. Recientemente  INSTEAD OF si la relación es una vista, para ejecutar sus actualizaciones y propagar adecuadamente las modificaciones a las tablas base. Generalmente, es imposible modificar una vista, porque no existe !!. Los triggers INSTEAD OF permiten interpretar las modificaciones, de manera semánticamente correcta.

25

BASES DE DATOS I

TRIGGERS: granularidad Los triggers pueden ser a nivel row (fila) o sentencia (statement). Por defecto  FOR EACH STATEMENT. Triggers a nivel fila se ejecutan una vez por cada tupla modificada. Triggers a nivel sentencia se ejecutan una vez por cada sentencia SQL, independientemente de la cantidad de tuplas que ésta modifique.

26

BASES DE DATOS I

TRIGGERS: referencias Las sentencias INSERT implican una nueva tupla (para triggers de fila) o un nuevo conjunto de tuplas (para triggers de sentencia). DELETE implica una tupla o tabla ‘vieja’. UPDATE implica ambas cosas. Corresponde referirse a estos elementos como: [NEW | OLD] [TUPLE | TABLE] AS

27

BASES DE DATOS I

TRIGGERS: condición Cualquier expresión booleana. Se evalúa antes o después del evento disparador, según se haya indicado BEFORE o AFTER. Accede a la nueva/vieja tupla (o conjunto de tuplas) mediante los nombres establecidos en la cláusula REFERENCING. Debe evaluar en VERDADERO para promover la ejecución de la acción.

28

BASES DE DATOS I

TRIGGERS: acción Puede ser una sentencia SQL o bien una pieza de código más extensa, escrita en el lenguaje de Persistent Stored Modules (PSM), que puede contener sentencias SQL empotradas. Utilizar BEGIN . . . END si no es una sentencia SQL aislada.

Si los triggers son BEFORE, no debería contener sentencias de actualización en el cuerpo, pues esto puede disparar nuevos triggers BEFORE cuyas acciones van quedando pendientes.

29

BASES DE DATOS I

TRIGGERS: mantenimiento de integridad Ejemplo, para mantenimiento de integridad: create trigger after when La acción puede ser el rechazo rollback de la transacción (acción pasiva), u operaciones de reparación (acción activa)

30

BASES DE DATOS I

TRIGGERS: mantenimiento de integridad Dada una relación R, la idea es que el DBMS mantenga 4 relaciones transitorias (tablas de transición) para R mientras se ejecuta la transacción T. – – – –

Rdeleted  tuplas borradas de R por T Rinserted  tuplas insertadas en R por T Rupdated old  valores viejos de las tuplas actualizadas por T Rupdated new  valores nuevos de las tuplas actualizadas por T

Rnueva, obtenida luego de T es: Rnueva = R − Rdeleted ∪ Rinserted − Rupdated old ∪ Rupdated new. • Mediante estas tablas el proceso de verificación de integridad es optimizado.

31

BASES DE DATOS I

TRIGGERS: mantenimiento de integridad Supuesto: Antes de T, todas las restricciones de integridad se satisfacían. Ejemplo: – Sea la restricción ‘Cada producto debe ser provisto por al menos un proveedor’. Existe una relación N:N entre PRODUCTO y PROVEEDOR – Operaciones críticas  insert en PRODUCTOS, delete en OFRECE y update en OFRECE y PRODUCTOS – Es necesario verificar solamente los productos insertados por T. → Tuplas en PRODUCTOSinserted (análogamente para DELETE en OFRECE)

32

BASES DE DATOS I

TRIGGERS: ejemplo Actualiza la conformación de grupos de proyecto. CREATE TRIGGER modificar_alum AFTER UPDATE OF cod_grupo ON Alumno REFERENCING OLD AS v_alum NEW AS n_alum FOR EACH ROW BEGIN ATOMIC (UPDATE Grupo SET num_alums = num_alums + 1 WHERE Grupo.cod_grupo=n_alum.cod_grupo); (UPDATE Grupo SET num_alums = num_alums - 1 WHERE Grupo.cod_grupo=v_alum.cod_grupo); END;

33

BASES DE DATOS I

TRIGGERS: Ejemplo Asegura que el salario de un empleado nunca se reduzca: CREATE TRIGGER salario_no_se_reduce AFTER UPDATE OF salario ON empleado REFERENCING OLD AS viejo_empleado REFERENCING NEW AS nuevo_empleado FOR EACH ROW WHEN (viejo_empleado.salario > nuevo_empleado.salario) BEGIN ATOMIC SIGNAL 1000 (‘Problema !! Intenta reducir el salario de un empleado!'); END;

34

BASES DE DATOS I

TRIGGERS SQL statement UPDATE T1 SET …; Activa UPDATE-T1










Útiles para definir operatividad en una BD Su uso excesivo puede provocar interdependencias complejas, difíciles de mantener en una aplicación. Ej.: cuando se activa un trigger, una sentencia SQL en su acción puede activar otros triggers  cascading.

UPDATE_T1 Trigger BEFORE UPDATE ON T1 FOR EACH ROW BEGIN ... INSERT INTO t2 VALUES (...); ... END;

Activa INSERT-T2 INSERT_T2 Trigger BEFORE UPDATE ON T2 FOR EACH ROW BEGIN ... INSERT INTO ... VALUES (...); ... END;

35

BASES DE DATOS I

TRIGGERS y Restricciones declarativas








Triggers  permiten definir y forzar reglas de integridad, pero no son una restricción de integridad. Un trigger definido para forzar una regla de integridad NO verifica su cumplimiento para los datos ya almacenados en la base de datos. Los triggers deberían usarse, sólo   Cuando una regla de integridad no puede ser expresada mediante: NOT NULL, UNIQUE key, PRIMARY KEY, FOREIGN KEY, CHECK, update CASCADE, update and delete SET NULL, update and delete SET DEFAULT  Cuando se requiere forzar integridad referencial entre tablas de distintos nodos en una BD distribuida.  Para forzar restricciones complejas del negocio que no pueden definirse usando restricciones de integridad

36

BASES DE DATOS I

TRIGGERS: Modelo de ejecución SQL:1999









1. Ejecuta todos los triggers BEFORE-statement. 2. Realiza un ciclo por todas las filas afectadas por la sentencia SQL.  a. Ejecuta todos los triggers BEFORE-row.  b. Bloquea y cambia cada fila y ejecuta los chequeos de integridad. (El bloqueo no se levanta hasta que la transacción termina)  c. Ejecuta todos los triggers AFTER-row. 3. Completa las acciones correspondientes a la verificación diferida de integridad expresada declarativamente. 4. Ejecuta todos los triggers AFTER-statement.

37

BASES DE DATOS I

TRIGGERS: Modelo de ejecución SQL:1999

Extraído de: “Semantic Integrity Support in SQL-99 and Commercial (Object-)Relational Database Management Systems” Can Türker y Michael Gertz http://www.db.cs.ucdavis.edu/ publications/#2000

38

BASES DE DATOS I

TRIGGERS: Características


Si varios se activan al mismo tiempo, su orden de ejecución depende del orden de creación.




Oracle n eventos/trigger




n triggers/evento row/statement Before/after Condición DB2 UDB  1 evento/trigger n triggers/evento row/statement Before/after Condición  Propuesta incorporada al estándar SQL:1999

39

BASES DE DATOS I

TRIGGERS: Pros y Cons





PROS:  Factoriza la lógica compartida de las aplicaciones y reglas del negocio  menor redundancia  Amplía la capacidad de expresión de restricciones, más allá de las declarativas. CONS:  Mayor complejidad al trasladar funcionalidad de las aplicaciones a la BD  complica el diseño  Funcionalidad oculta  efectos colaterales inesperados (x ej. Cascading)  Sobrecarga  detección del conjunto de triggers a ser activados ante un evento.  Conjunto reducido de eventos (operaciones básicas)  Implementación diferente en distintos DBMSs  difícil portabilidad  Comportamiento ‘impredecible’ por su ejecución intercalada con restr. declarativas en distintos DBMSs  Dificultades para interpretar su interacción con otras aplicaciones

40

BASES DE DATOS I

TRIGGERS: Pros y Cons


Para entender el comportamiento 




TERMINACIÓN  garantiza que el conjunto de triggers terminará después de un conjunto de cambios  número máximo de triggers activados anidadamente y modos de acoplamiento en la finalización




CONFLUENCIA  El estado final de una BD al terminar la ejecución, no debería depender del orden de activación de los triggers  si se pudiese construir un grafo de activación éste debería tener sólo un estado final  orden de activación lo vuelve determinístico

41