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:

 CREATE OR REPLACE FUNCTION check_libros_disponibles() RETURNS TRIGGER AS $$
   DECLARE
     NUMDISP INT; -- NUMERO DE EJEMPLARES DISPONIBLES
   BEGIN   
        IF NEW.ANULADA=TRUE THEN  -- LOS PRESTAMOS QUE SE INSERTAN COMO ANULADOS NO SE VALIDAN
		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
        IF (NEW.ANULADA=TRUE AND OLD.ANULADA=FALSE) THEN   -- SE ESTA ANULANDO UN PRESTAMO
   		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

  • Adicione un trigger que cuando se devuelva el libro (i.e., se actualiza la fecha de devolución), se disminuya el número de libros prestados.
  • Cree una tabla MULTAS(id, usr_id, numdias, pago) donde PAGO es un booleano que determina si ya se pagó la multa o no.
  • 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.txt · Última modificación: 2012/04/30 08:44 por caolarte
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki