¡Esta es una revisión vieja del documento!


Prestamo de Libros

Un sencillo sistema de préstamos de libros fue diseñado de la siguiente manera:

 CREATE TABLE TIPOSUSUARIO(
  ID SERIAL,
  NOMBRE VARCHAR(20) NOT NULL,
  NUMDIAS INT NOT NULL);
 CREATE TABLE USUARIOS(
  ID SERIAL,
  NOMBRE VARCHAR(40) NOT NULL, 
  TIPUSR_ID INT NOT NULL
 );
 CREATE TABLE LIBROS(
  ID SERIAL,
  TITULO VARCHAR(40) NOT NULL, 
  EJEMPLARES INT NOT NULL,
  PRESTADOS INT NOT NULL);
 CREATE TABLE PRESTAMOS(
  ID SERIAL,
  LIB_ID INT NOT NULL,
  USR_ID INT NOT NULL,
  FECHAPRESTAMO TIMESTAMP DEFAULT NOW(),
  FECHADEVOLUCION TIMESTAMP,
  ANULADA BOOLEAN DEFAULT FALSE);
 ALTER TABLE TIPOSUSUARIO ADD CONSTRAINT TIPUSR_PK PRIMARY KEY (ID);
 ALTER TABLE USUARIOS ADD CONSTRAINT USR_PK PRIMARY KEY (ID);
 ALTER TABLE LIBROS ADD CONSTRAINT LIB_PK PRIMARY KEY (ID);
 ALTER TABLE PRESTAMOS ADD CONSTRAINT PRES_PK PRIMARY KEY (ID);
 ALTER TABLE USUARIOS ADD CONSTRAINT USU_TIPUSR_FK FOREIGN KEY (TIPUSR_ID) REFERENCES TIPOSUSUARIO (ID);
 ALTER TABLE PRESTAMOS ADD CONSTRAINT PRES_LIB_FK FOREIGN KEY (LIB_ID) REFERENCES LIBROS (ID);
 ALTER TABLE PRESTAMOS ADD CONSTRAINT PRES_USR_FK FOREIGN KEY (USR_ID) REFERENCES USUARIOS (ID);

Validaciones Simples (CK)

Es posible realizar algunas validaciones básicas a nivel de registros, por ejemplo:

 ALTER TABLE TIPOSUSUARIO ADD CONSTRAINT TIPUSR_NUMDIAS_CK CHECK (NUMDIAS>0);
 ALTER TABLE LIBROS ADD CONSTRAINT LIB_EJEM_DISP_CK CHECK (EJEMPLARES >= PRESTADOS);
 ALTER TABLE PRESTAMOS ADD CONSTRAINT PRES_FECHAS_CK CHECK (FECHADEVOLUCION >= FECHAPRESTAMO);

Ahora podemos insertar algunos datos:

 INSERT INTO TIPOSUSUARIO (NOMBRE,NUMDIAS) VALUES ('NORMAL',3) , ('GOLD',7);
 INSERT INTO USUARIOS (NOMBRE, TIPUSR_ID) VALUES ('USUARIO 1',1), ('USUARIO 2',1),('USUARIO 3',2),('USUARIO 5',1);
 INSERT INTO LIBROS (TITULO,EJEMPLARES,PRESTADOS) VALUES ('LIBRO 1',3,0),('LIBRO 2',4,0),('LIBRO 3',1,0),('LIBRO 4',1,0);

Validaciones con Triggers

Sería deseable validar algunas reglas para la biblioteca. Por ejemplo, no se puede prestar un libro si no hay ejemplares disponibles:

  1. - Esta función valida que del libro que se va a prestar hay ejemplares disponibles.

CREATE OR REPLACE FUNCTION check_libros_disponibles() RETURNS TRIGGER AS $$

   DECLARE
     NUMDISP INT; -- NUMERO DE EJEMPLARES DISPONIBLES
   BEGIN   
 	  -- LOS PRESTAMOS QUE SE INSERTAN COMO ANULADOS NO SE VALIDAN
        IF NEW.ANULADA=TRUE THEN 
		RETURN NEW;
  ELSE

SELECT EJEMPLARES-PRESTADOS INTO NUMDISP FROM LIBROS WHERE ID = NEW.LIB_ID FOR UPDATE;

	IF NUMDISP >0 THEN
		UPDATE LIBROS SET PRESTADOS = PRESTADOS+1 WHERE ID = NEW.LIB_ID;
		RETURN NEW;
	ELSE
		RAISE EXCEPTION 'LIBROS NO DISPONIBLES';
	END IF;

       END IF;
   END;
 $$ LANGUAGE plpgsql;

CREATE TRIGGER PRESTAMOS_NUM_EJEMPLARES_TRG

BEFORE INSERT ON PRESTAMOS
    FOR EACH ROW EXECUTE PROCEDURE check_libros_disponibles();

Ahora cada que se presta un libro se disminuye el número de ejemplares disponibles: bd1214⇒ select * from libros; id | titulo | ejemplares | prestados —-+———+————+———–

1 | LIBRO 1 |          3 |         0
2 | LIBRO 2 |          4 |         0
3 | LIBRO 3 |          1 |         0
4 | LIBRO 4 |          1 |         0

(4 filas)

INSERT INTO PRESTAMOS (LIB_ID,USR_ID) VALUES (2,3); INSERT INTO PRESTAMOS (LIB_ID,USR_ID) VALUES (3,1); bd1214⇒ SELECT * FROM LIBROS; id | titulo | ejemplares | prestados —-+———+————+———–

1 | LIBRO 1 |          3 |         0
4 | LIBRO 4 |          1 |         0
2 | LIBRO 2 |          4 |         1
3 | LIBRO 3 |          1 |         1

Además, no se puede prestar más ejemplares del LIBRO 3: bd1214⇒ INSERT INTO PRESTAMOS (LIB_ID,USR_ID) VALUES (3,1); ERROR: LIBROS NO DISPONIBLES

Para evitar fraudes, podríamos también validar que no se pueden borrar registros de la tabla prestamos:

CREATE OR REPLACE FUNCTION PRES_DELETE_ERROR() RETURNS TRIGGER AS $$

BEGIN
RAISE EXCEPTION 'NO SE PUEDEN BORRAR REGISTROS DE ESTA TABLA. INTENTE ANULAR EL PRESTAMO';
END;

$$ LANGUAGE plpgsql;

CREATE TRIGGER PRES_DELETE_ERROR_TRG

BEFORE DELETE ON PRESTAMOS
    FOR EACH ROW EXECUTE PROCEDURE PRES_DELETE_ERROR();

Por ejemplo: bd1214⇒ DELETE FROM PRESTAMOS WHERE ID = 1; ERROR: NO SE PUEDEN BORRAR REGISTROS DE ESTA TABLA. INTENTE ANULAR EL PRESTAMO

Cuando se anula un préstamo, debemos devolver el libro:

CREATE OR REPLACE FUNCTION ANULAR_PRESTAMO() RETURNS TRIGGER AS $$

BEGIN
  -- SE ESTA ANULANDO UN PRESTAMO
        IF (NEW.ANULADA=TRUE AND OLD.ANULADA=FALSE) THEN 
		UPDATE LIBROS SET PRESTADOS = PRESTADOS -1 WHERE ID = NEW.LIB_ID;
	RETURN NEW;
  ELSE
	IF (NEW.ANULADA=FALSE AND OLD.ANULADA=TRUE)  THEN
		RAISE EXCEPTION 'NO ES POSIBLE CAMBIAR EL ESTADO DEL PRESTAMO';
	END IF;
        END IF;
  RETURN NEW;
END;

$$ LANGUAGE plpgsql;

Ejercicios: 1) Adicione un trigger que cuando se devuelva el libro (se actualice la fecha de devolución), se disminuya el número de libros prestados. 2) Cree una tabla MULTAS(id, usr_id, numdias, pago) donde PAGO es un booleano que determina si ya se pagó la multa o no. 3) Modifique el trigger del primer ejercicio para que adicione un registro a la tabla MULTAS si el usuario devolvió el libro después de los días a los que tiene derecho según su TIPO.

 
materias/3er_taller_pl_pgsql.1335793188.txt.gz · Última modificación: 2012/04/30 08:39 por caolarte
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki