Taller PL/pgSQL

Dadas las siguientes relaciones de un sistema de facturación de Internet:

PLANES
Codigo Nombre VMinuto PDescuento
1 Plan X 400 5
2 Plan Y 300 1
3 Plan Z 500 7
USUARIOS
Login Plan_Cod
login1 2
login2 1
login3 2
login4 3
CONEXIONES
Id Usr Fecha Duracion Valor
1 login1 ’01-jan-2004 13:22:14’ 20 NULL
2 login2 ’01-jan-2004 15:34:20’ 30 NULL
3 login1 ’02-jan-2004 08:32:30’ 40 NULL
4 login4 ’02-jan-2004 16:25:31’ 42 NULL
5 login3 ’02-jan-2004 22:32:15’ 52 NULL
6 login2 ’02-jan-2004 23:24:17’ 31 NULL
7 login4 ’03-jan-2004 07:32:58’ 23 NULL
LIQUIDACIONES
Id Usr Periodo TotalMin ValorTotal
1 login1 ’jan-2004’ 60 18000
2 login2 ’jan-2004’ 61 23780

Construir un procedimiento PL/pgSQL que recorra la tabla Conexiones actualizando el valor de la conexión según el Plan del usuario. Para lo anterior, se debe tener en cuenta que las conexiones que inician entre las 23 horas (11PM) y las 5AM se les asigna el descuento PDescuento según el plan. Luego de calcular el valor de cada conexión, se debe llenar la tabla Liquidaciones en donde se totaliza el tiempo total consumido por el usuario y el valor a pagar (suponga que no hay cargos básicos, ni impuestos).

Script de Creación de Tablas

CREATE TABLE PLANES (
  CODIGO SERIAL NOT NULL, 
  NOMBRE VARCHAR(20) NOT NULL,
  VMINUTO INT NOT NULL,
  PDESCUENTO REAL DEFAULT 0
);
CREATE TABLE USUARIOS (
 LOGIN VARCHAR(20) NOT NULL,
 PLAN_COD INT NOT NULL);
CREATE TABLE CONEXIONES (
 ID SERIAL NOT NULL,
 USR VARCHAR(20) NOT NULL,
 FECHA TIMESTAMP NOT NULL,
 DURACION REAL NOT NULL,
 VALOR REAL);
CREATE TABLE LIQUIDACIONES (
 ID SERIAL NOT NULL,
 USR VARCHAR(20) NOT NULL,
 PERIODO DATE NOT NULL,
 TOTALMIN REAL NOT NULL,
 VALORTOTAL REAL NOT NULL
);
 ALTER TABLE PLANES ADD CONSTRAINT PLA_PK PRIMARY KEY(CODIGO);
 ALTER TABLE USUARIOS ADD CONSTRAINT USR_PK PRIMARY KEY(LOGIN);
 ALTER TABLE CONEXIONES ADD CONSTRAINT CNX_PK PRIMARY KEY(ID);
 ALTER TABLE LIQUIDACIONES ADD CONSTRAINT LIQ_PK PRIMARY KEY(ID);
 ALTER TABLE USUARIOS ADD CONSTRAINT USR_PLA_FK FOREIGN KEY(PLAN_COD)
  REFERENCES PLANES(CODIGO);
 ALTER TABLE CONEXIONES ADD CONSTRAINT CNX_USR_FK FOREIGN KEY(USR)
  REFERENCES USUARIOS(LOGIN);
 ALTER TABLE LIQUIDACIONES ADD CONSTRAINT LIQ_USR_FK FOREIGN KEY(USR)
  REFERENCES USUARIOS(LOGIN);
 ALTER TABLE CONEXIONES ADD CONSTRAINT CNX_CK CHECK (DURACION>=0 AND VALOR>=0);
 ALTER TABLE LIQUIDACIONES ADD CONSTRAINT LIQ_CK CHECK (TOTALMIN>=0 AND VALORTOTAL>=0);
 INSERT INTO PLANES (NOMBRE, VMINUTO, PDESCUENTO)
  VALUES ('PLAN X',400,5), ('PLAN Y',300,1), ('PLAN Z',500,7);
 INSERT INTO USUARIOS (LOGIN, PLAN_COD)
  VALUES ('LOGIN 1',2), ('LOGIN 2',1),('LOGIN 3',2),  ('LOGIN 4',3);
INSERT INTO CONEXIONES (USR,FECHA,DURACION)
 VALUES ('LOGIN 1','01-01-2010 14:23:12',20),('LOGIN 1','02-01-2010 16:00:11',20),('LOGIN 1','01-01-2010 23:10:10',15),('LOGIN 2','05-01-2010 12:05:07',55),('LOGIN 2','10-01-2010 10:10:10',70),('LOGIN 3','06-01-2010 11:22:12',13),('LOGIN 3','08-01-2010 18:16:12',11),('LOGIN 4','08-01-2010 22:30:10',100),('LOGIN 4','09-01-2010 23:15:12',20);
 

Solución

 
materias/2do_taller_pl_pgsql.txt · Última modificación: 2012/10/22 11:17 por caolarte
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki