tema i: introducción a las bases de datos.users.dsic.upv.es/~jllabres/doc/prac3-7.pdf · práctica...

Práctica 3:

El lenguaje SQL (2ª parte).

(7ª sesión).

Práctica 1. Introducción al uso de una BD relacional (ACCESS).

Práctica 2. Representación de la realidad en el modelo

relacional de datos.

Práctica 3. El lenguaje SQL. (ORACLE)

Práctica 4. Estudio del SGBD ORACLE.

Programa de prácticas:

El lenguaje SQL.

• estudio del lenguaje de definición de datos del SQL.

• definir una base de datos relacional en el sistema

ORACLE.

• estudio del mecanismo de disparadores

(triggers) en el sistema ORACLE.

Objetivos (2ª parte):

cod_pro

nombre

teléfono

cod_dep

Profesor

cod_dep

nombre

director

teléfono

Departamento

cod_asg

nombre

semestre

teo

prac

cod_dep

Asignatura

cod_pro

cod_asg

gteo

gprac

Docencia

Clave primaria Clave Ajena

El lenguaje SQL.

Mecanismo de disparadores en ORACLE

evento - condición - acción

evento: especifica el suceso a cuya ocurrencia debe responder el sistema .

condición: especifica el contexto en el cual la regla cuyo evento se ha producido debe ser ejecutada.

acción: especifica las acciones que deben ser ejecutadas por el sistema como respuesta a la ocurrencia del evento cuando la condición es cierta .

Los disparadores permiten modelar un comportamiento activo (autónomo) del sistema como respuesta a la ocurrencia de sucesos.

regla de actividaddisparador ≡


Ejemplo1: “cuando un profesor se da de baja (es borrado) sus datos deben registrarse en un histórico de profesores”.

cod_pro

nombre

teléfono

cod_dep

Profesor

cod_pro

nombre

teléfono

cod_dep

fecha

Histórico-Profesores

Borrado Inserción

SGBD

Mecanismo de disparadores en ORACLE cod _p ro nom bre telé f on o cod _d e p

JC C Ju a n C . C asam a y or R ód en as 779 6 DS IC

RF C R obert Fuster i C apilla 678 9 M A T

JBD Jos é V . Ben lloch D u a ld e 576 0 D I SCA

M AF M ar ía Alpu e nte F ra s n ed o 356 0 DS IC

CPG C rist ina Pé re z Gui llo t 743 9 ID M

JT M Jos é M . Torra lba M art íne z 459 0 O EM

IG P I g n ac i o Gil P ech u án 342 3 O EM

D G T D a n i e l G il T o m ás 567 9 D I SCA

M CG M at i lde C elm a Giméne z 775 6 DS IC

Profesor

DELETE FROM Profesor WHERE cod_pro=“RFC”

INSERT INTO Histórico-Profesores

VALUES (“RFC”, “Robert Fuster Capilla”, 6789, MAT, SYSDATE)

SGBD

Histórico-Profesor

PPP Pedro Pérez Puerta 7795 DISCA 12/12/97

RFC Robert Fuster Capilla 6789 MAT 12/12/99

acción

evento


CREATE OR REPLACE TRIGGER borrado_profesor

AFTER DELETE ON Profesor

FOR EACH ROW

BEGIN


VALUES (:OLD.cod_pro, :OLD.nombre, :OLD.teléfono, :OLD.cod_dep, SYSDATE);

END;

Después (AFTER) de un borrado en Profesor (evento) por cada tupla borrada (FOR EACH ROW) se insertará una tupla en Histórico-Profesores (acción) construida con los valores viejos (OLD) de la tupla borrada.

cod_pro nombre teléfono cod_dep

JCC Juan C. Casamay or Ródenas 7796 DSIC

RFC Ro bert Fuster i Capilla 6789 MAT

JBD José V. Benlloch Dua lde 5760 DISCA

MAF Mar ía Alpuente Frasnedo 3560 DSIC

CPG Cristina Pérez Guillot 7439 IDM

JTM José M. Torralba Martínez 4590 OEM

IGP Ign acio Gil Pechuán 3423 OEM

DGT Dan iel Gil To más 5679 DISCA

MCG Matilde Celma Giménez 7756 DSIC

Profesor

DELETE FROM Profesor WHERE cod_pro=“RFC”

evento

OLD.cod_pro OLD.nombre OLD.teléfono OLD.cod_dep



Parámetros del evento: campos de la tupla actualizada.

OLD.nombre_campo: valor antes de la actualización

NEW.nombre_campo: valor después de la actualización

INSERT: NEW.nombre_campo

DELETE: OLD.nombre_campo

UPDATE: OLD.nombre_campo, NEW.nombre_campo

Dependiendo del tipo de evento sólo tienen sentido un tipo de parámetros (NEW o OLD)

Mecanismo de disparadores en ORACLE cod _p ro nom bre telé f on o cod _d e p

JC C Ju a n C . C asam a y or R ód en as 779 6 DS IC

RF C R obert Fuster i C apilla 678 9 M A T

JBD Jos é V . Ben lloch D u a ld e 576 0 D I SCA

M AF M ar ía Alpu e nte F ra s n ed o 356 0 DS IC

CPG C rist ina Pé re z Gui llo t 743 9 ID M

JT M Jos é M . Torra lba M art íne z 459 0 O EM

IG P I g n ac i o Gil P ech u án 342 3 O EM

D G T D a n i e l G il T o m ás 567 9 D I SCA

M CG M at i lde C elm a Giméne z 775 6 DS IC

Profesor

DELETE FROM Profesor WHERE cod_dep=“DSIC”

Histórico-Profesor

JCC Juan C. Casamayor Ródenas 7796 DSIC 12/12/99

MAF María Alpuente Frasnedo 3560 DSIC 12/12/99

MCG Matilde Celma Giménez 7756 DSIC 12/12/99


VALUES ( )

SGBD


Ejemplo2: “cuando se realiza una actualización en Profesor se debe hacer un registro de seguridad en la tabla Accesos”.

cod_pro

nombre

teléfono

cod_dep

Profesor

nro

usuario

fecha

Accesos

DELETE-UPDATE-INSERTINSERT

SGBD

acción

evento


CREATE OR REPLACE TRIGGER borrado_profesor

AFTER INSERT OR UPDATE OR DELETE ON Profesor

BEGIN

INSERT INTO Accesos

VALUES (nro, USER, SYSDATE);

END;

Después (AFTER) de una operación (inserción, borrado o actualización) en Profesor (evento) se insertará una tupla en Accesos (acción) independientemente del numero de tuplas actualizadas.

Nota: USER y SYSDATE son funciones predefinidas que devuelven el identificador del usuario conectado y la fecha del sistema.

FOR EACH ROW

Ejemplo 3:

Restricción de integridad: “Los profesores que imparten la asignatura “EST1” deben ser del departamento de Estadística (EST)”.


SQL

Restricción de integridad: Los profesores que imparten la asignatura “EST1” deben ser del departamento de Estadística (EST).

CREATE ASSERTION CHECK

( NOT EXISTS

(SELECT *

FROM Docencia NATURAL JOIN Profesor

WHERE Docencia.cod_asg = “EST1”

AND

Profesor.cod_dep <> ”EST” )


El lenguaje de definición de datos de ORACLE:Las principales diferencias respecto al estándar SQL son:

no contempla el concepto de esquema de base de datos

no permite la definición de dominios

sólo contempla el tipo de integridad referencial débil

sólo admite directrices de restauración de la integridad referencial frente a la operación de borrado (ON DELETE)

las restricciones de integridad generales que se pueden definir con la cláusula CHECK son muy limitadas (no se pueden usar subconsultas, ni funciones agregadas, ....)

no admite la sentencia CREATE ASSERTION.



CREATE ASSERTION CHECK

( NOT EXISTS

(SELECT *

FROM Docencia NATURAL JOIN Profesor

WHERE Docencia.cod_asg = “EST1”

AND

Profesor.cod_dep <> ”EST” )

ORACLE


La restricción de integridad que no se puede definir en ORACLE puede ser comprobada por medio de disparadores.



Se deben analizar los eventos relevantes para la restricción, es decir que pueden violarla, y definir los disparadores necesarios.

Operaciones sobre la relación Docencia

Operaciones sobre la relación Profesor

DocenciaINSERT

si cod_dep de NEW.cod_pro <>”EST”

entonces RECHAZAR

UPDATE (cod_asg)

UPDATE (cod_pro)

Evento Condición Acción

REGLA DE ACTIVIDAD


NEW.cod_asg=EST1

Disparadores sobre Docencia:


CREATE OR REPLACE TRIGGER control_docencia1

AFTER INSERT OR UPDATE OF cod_asg, cod_pro ON Docencia

FOR EACH ROW

WHEN NEW.cod_asg = ‘EST1’

DECLARE X CHAR(4);

BEGIN

SELECT P.cod_dep INTO X FROM Profesor P WHERE P.cod-prof = :NEW.cod-prof; IF X <> “EST” THEN

RAISE-APPLICATION-ERROR (---, ‘actualización no válida’)

END IF; END

evento

condición acción

ORACLE

Profesor

si NEW.cod_prof imparte EST1 entonces RECHAZAR

UPDATE (cod_dep)


REGLA DE ACTIVIDAD


OLD.cod_dep=EST

AND

NEW.cod_dep<>EST

Disparadores sobre Profesor:


CREATE OR REPLACE TRIGGER control_docencia2

AFTER UPDATE OF cod_dep ON Profesor

FOR EACH ROW

WHEN OLD.cod_dep = ‘EST’ AND NEW.cod_dep<>”EST”

DECLARE X INTEGER;

BEGIN

SELECT COUNT(*) INTO X FROM Docencia D WHERE D.cod-prof = :NEW.cod-prof AND D.cod_asg=“EST1”

IF X <> 0 THEN RAISE-APPLICATION-ERROR (---, ‘actualización no válida’)

END IF; END

evento

condición acción

ORACLE


Ejemplo4:”en la relación Profesor existe un atributo derivado créditos que debe ser mantenido automáticamente por el sistema”.

Ley de derivación del atributo créditos: “el valor del atributo créditos de Profesor es la suma de todos los créditos que en la relación Docencia aparecen impartidos por el profesor”

cod_pro

nombre

teléfono

cod_dep

créditos*

Profesor

cod_dep

nombre

director

teléfono

Departamento

cod_asg

nombre

semestre

teo

prac

cod_dep

Asignatura

cod_pro

cod_asg

créditos

Docencia

Clave primaria Clave Ajena


* atributo derivado

CREATE TABLE Profesor

(cod_pro CHAR(5),

nombre VARCHAR(50) NOT NULL,

teléfono CHAR(11),

cod_dep CHAR(5),

créditos NUMBER (4,1) DEFAULT 0,

CONSTRAINT CP_prof PRIMARY KEY (cod_pro),

CONSTRAINT CAj_prof_dpto FOREIGN KEY (cod_dep)

REFERENCES Departamento(cod_dep))


El atributo derivado créditos debe ser mantenido automáticamente por el sistema.

DocenciaINSERT

aplicar la ley de derivación del atributo créditos en Profesor y actualizar dicho atributo:

UPDATE (creditos)

DELETE

UPDATE (cod_pro)


REGLA DE ACTIVIDAD

UPDATE (creditos)

Profesor


CREATE TRIGGER total_creditosAFTER INSERT OR DELETE OR UPDATE OF creditos, cod_pro ON DocenciaFOR EACH ROWBEGINIF INSERTING THEN

UPDATE Profesor SET créditos=créditos+:NEW.creditosWHERE cod_pro=:NEW.cod_pro;

ELSEIF UPDATING THENIF :NEW.cod_pro != :OLD.cod_prof THEN

UPDATE Profesor SET créditos=créditos+:NEW.creditosWHERE cod_pro=:NEW.cod_pro;

UPDATE Profesor SET créditos=créditos -:OLD.creditosWHERE cod_pro=:OLD.cod_pro;

ELSE UPDATE Profesor

SET créditos + :NEW.creditos - :OLD.creditos;WHERE cod_pro=:NEW.cod_pro;

END IF;ELSE

UPDATE Profesor SET creditos=creditos - :OLD.creditosWHERE cod_pro=:OLD.cod_pro;

END IF;END;

evento

acción


definición_regla :=

CREATE [OR REPLACE ] TRIGGER nombre_regla

{BEFORE | AFTER | INSTEAD OF}

evento [OR evento [OR evento [...] ]] ON {nombre_tabla | nombre_vista}

[[REFERENCING OLD AS nombre_ref [NEW AS nombre_ref] ]

[FOR EACH ROW [WHEN ( condición ) ] ]

bloque PL/SQL


evento := {INSERT | DELETE | UPDATE [OF nom_atributo1, nom_atributo2...]}

Sólo los disparadores de tipo FOR EACH ROW pueden llevar condición.

Los disparadores de tipo INSTEAD OF sólo actúan sobre vistas.

Creación: CREATE TRIGGER nombre_regla ……..

Borrado: DROP TRIGGER nombre_regla

Modificación: REPLACE TRIGGER nombre_regla

Recompilación: ALTER TRIGGER nombre_regla COMPILE

Consulta: consultas a las tablas del diccionario del sistema: USER_TRIGGERS, ALL_TRIGGERS, DBA_TRIGGERS

Prioridad entre reglas: no existe

Habilitar y deshabilitar reglas:

ALTER TRIGGER nombre_regla [ENABLE | DISABLE]

ALTER TABLE nombre_relación [{ENABLE | DISABLE} ALL TRIGGERS]


Tipos de reglas: 4 tipos de reglas

[FOR EACH STATEMENT] FOR EACH ROW

BEFORE

AFTER

se ejecuta la regla una vez antes de la ejecución del evento

se ejecuta la regla una vez después de la ejecución del evento

se ejecuta la regla una vez antes de la actualización de cada tupla afectada por el evento

se ejecuta la regla una vez después de la actualización de cada tupla afectada por el evento



Anexo: Lenguaje PL/SQL de ORACLEEstructura de un bloque PL/SQL:

DECLARE

Sección de declaración

de variables

BEGIN

Sentencias del bloque

END

Sección de declaración de variables:

- Variables locales al bloque:

nombre_variable tipo_dato

tipo_dato::= {NUMBER | CHAR( ) | DATE }

(las declaraciones de esta sección deben ir separadas por punto y coma)


Sentencias del cuerpo del bloque PL/SQL:

- Secuencia_de_sentencias::= sentencia; [sentencia;] ...

- Sentencia de selección:

IF condición THEN secuencia_de_sentencias

[ELSE secuencia_de_sentencias] END IF;

- Sentencias de repetición:

WHILE condición LOOP

secuencia_de sentencias ;

END LOOP;

FOR contador IN mínimo .. máximo LOOP

secuencia_de_sentencias ;

END LOOP;


Sentencias del cuerpo del bloque PL/SQL:- Sentencia de asignación :

nombre_variable := expresión

-Sentencias SQL:

INSERT, DELETE, UPDATE, SELECT... INTO.......

- Sentencias de entrada-salida: dbms_output.put_line ('mensaje'). Para usar esta función el paquete dbms_output debe estar activado, esto se hace con la sentencia SQL SET SERVEROUTPUT ON .


Manejo de errores:Si durante la ejecución de una regla (trigger) se produce un error predefinido en el sistema o definido por el usuario, entonces se anulan todas las actualizaciones realizadas por la acción de la regla así como el evento que la activó.

La sentencia RAISE_APPLICATION_ERROR (nro_error, ' mensaje' ) provoca la ocurrencia del error de número interno nro_error y envía al usuario el mensaje 'mensaje'. (nro_error debe ser un número negativo entre -20000 y -20999).


Se desea diseñar una base de datos para la gestión de una pequeña biblioteca de un departamento. Después de realizar el análisis del sistema, se han identificado los requerimientos que van a realizarse con más frecuencia; éstos son:

-consultar los datos de un libro: código del libro, título, autor(es), temática y en caso de estar prestado, el socio que lo tiene actualmente en préstamo.

-consultar la información sobre un socio: código del socio, nombre, dirección, teléfono y libros que actualmente tiene en préstamo así como la fecha del préstamo.

-consultar los préstamos históricos de un socio: código del libro, fecha del préstamo y fecha de la devolución.

-dar de alta, dar de baja y modificar los datos de un socio.

-gestionar los préstamos: prestar un libro a un socio y registrar la devolución de un libro.

Ejercicio de prácticas.

Algunas restricciones de integridad que se han detectado son:

-el código del libro identifica unívocamente al libro.

-el código del socio identifica unívocamente al socio.

-el conjunto de temas utilizados para clasificar un libro son: física, electricidad, mecánica y óptica.

-la fecha de devolución de un libro debe ser posterior a la fecha de préstamo.

-el número total de libros que tiene prestados un socio es un dato derivado que será mantenido automáticamente por el sistema.

Realizar las siguientes tareas:

•definir el esquema relacional de la base datos anterior (usando los conceptos del modelo relacional).

•definir la base de datos en el sistema ORACLE8.

•realizar actualizaciones y consultas sobre la base de datos creada.



lcod

título

temática

scod

fecha_pre

Libro

scod

nombre

dirección

tel

libros*

Socio

scod

lcod

fecha_pre

fecha_devP

rést

amo

lcod

autor

Autores

libros: atributo derivado

CREATE TABLE Socio (scod char(5) CONSTRAINT cp_socio PRIMARY KEY DEFERRABLE,nombre varchar2 (60),direccion varchar2 (50),tel varchar2 (20),libros number(3) DEFAULT 0);

CREATE TABLE Libro(lcod char(5) CONSTRAINT cp_libro PRIMARY KEY DEFERRABLE,titulo varchar2 (100),tematica varchar2 (15) CONSTRAINT tematica_nulo NOT NULL DEFERRABLE,scod char(5) CONSTRAINT ca_libro_socio REFERENCES socio DEFERRABLE,fecha_pre date,CONSTRAINT c_tematica CHECK (tematica in ('física','electricidad','mecánica','óptica')) DEFERRABLE,CONSTRAINT c_prestamo CHECK((scod IS NULL and fecha_pre IS NULL) OR

(scod IS NOT NULL and fecha_pre IS NOT NULL)) DEFERRABLE);


CREATE TABLE Autores

(lcod char(5) CONSTRAINT ca_autor_libro REFERENCES libro(lcod) DEFERRABLE,

autor varchar2(40),

CONSTRAINT cp_autores PRIMARY KEY (lcod, autor) DEFERRABLE);

CREATE TABLE Prestamo

(scod char(5) CONSTRAINT ca_pre_socio REFERENCES socio(scod) DEFERRABLE

CONSTRAINT scod_nulo NOT NULL DEFERRABLE,

lcod char(5) CONSTRAINT ca_pre_libro REFERENCES libro(lcod) DEFERRABLE,

fecha_pre date,

fecha_dev date CONSTRAINT fecha_dev_nulo NOT NULL DEFERRABLE,

CONSTRAINT cp_prestamo PRIMARY KEY (lcod,fecha_pre) DEFERRABLE,

CONSTRAINT fechas CHECK (fecha_dev>fecha_pre) DEFERRABLE);



Actualizaciones sobre la BD:

Dar de alta un socio:

INSERT INTO Socio (scod, nombre, dirección, tel)

VALUES ('s1', 'Juan Ruiz', 'Jesús 92', '123456')

Dar de alta un libro:

INSERT INTO Libro (lcod, título, tematica)

VALUES ('l1', 'Dinámica', 'física')

INSERT INTO Autores (lcod, autor)

VALUES ('l1', , ’J. Holiday')



Prestar un libro: prestar el libro l1 al socio s1

UPDATE Libro SET scod= 's1', fecha_pre=SYSDATE

WHERE lcod='l1'



Devolver un libro: devolver el libro l1

Inicio T

UPDATE Libro SET scod= NULL, fecha_pre=NULL

WHERE lcod='l1’

INSERT INTO Prestamo (lcod, scod, fecha_pre, fecha_dev)

VALUES (l1, ..., ..., SYSDATE)

Fin T



Devolver un libro: devolver el libro l1

Inicio T

UPDATE Libro SET scod= NULL, fecha_pre=NULL

WHERE lcod='l1’

INSERT INTO Prestamo (lcod, scod, fecha_pre, fecha_dev)

VALUES (l1, ..., ..., SYSDATE)

Fin T

La inserción en Prestamo (para registrar el préstamo histórico) puede hacerse automáticamente con un disparador.

LibroINSERT

aplicar la ley de derivación del atributo libros en Socio y actualizar dicho atributo:

UPDATE (scod)

DELETE


REGLA DE ACTIVIDAD

UPDATE (libros)

Socio


Diseño de los disparadores:


Soluciones:

- Solución 1: un disparador por cada evento-condición sobre Libro que signifique la ejecución de una acción distinta.

- Solución 2: un único disparador que contemple todos los eventos relevantes sobre Libro.


CREATE OR REPLACE TRIGGER total_libros1

AFTER INSERT ON Libro

FOR EACH ROW

WHEN ( NOT (NEW.scod IS NULL) )

BEGIN

UPDATE Socio SET libros=libros+1 WHERE scod= :NEW.scod;

END;

Solución 1: con varios disparadores



AFTER DELETE ON Libro

FOR EACH ROW

WHEN ( NOT(OLD.scod IS NULL) )

BEGIN

UPDATE Socio SET libros=libros-1 WHERE scod= :OLD.scod;

END;




AFTER UPDATE OF scod ON Libro

FOR EACH ROW

WHEN ( (OLD.scod IS NULL) AND NOT(NEW.scod IS NULL) )

BEGIN -- préstamo de un libro


END;





FOR EACH ROW

WHEN ( NOT(OLD.scod IS NULL) AND (NEW.scod IS NULL) )

BEGIN -- devolución de libro


END;





FOR EACH ROW

WHEN ( (OLD.scod <> NEW.scod) AND NOT(OLD.scod IS NULL)

AND NOT(NEW.scod IS NULL) ) -- cambio de libro prestado

BEGIN



END;


CREATE OR REPLACE TRIGGER total_libros

AFTER INSERT OR DELETE OR UPDATE OF scod ON Libro

FOR EACH ROW

BEGIN

IF INSERTING AND NOT (:NEW.scod IS NULL) THEN --INSERT

UPDATE socio SET libros=libros+1 WHERE scod= :NEW.scod;

ELSE

IF DELETING AND NOT(:OLD.scod IS NULL) THEN --DELETE

UPDATE socio SET libros=libros-1 WHERE scod= :OLD.scod;

ELSE Bloque del evento UPDATE

END IF;

END IF;

END;


Solución 2: con un único disparador

IF (:OLD.scod IS NULL) AND NOT(:NEW.scod IS NULL)

THEN -- préstamo de un libro


ELSE

IF NOT(:OLD.scod IS NULL) AND :NEW.scod IS NULL

THEN -- devolución de libro


ELSE

IF (:OLD.scod <> :NEW.scod) AND NOT(:OLD.scod IS NULL)

AND NOT(:NEW.scod IS NULL)

THEN -- cambio de libro prestado



END IF;

END IF;

END IF;

Bloque del evento UPDATE

tema i: introducción a las bases de datos.users.dsic.upv.es/~jllabres/doc/prac3-7.pdf · práctica...

Documents