Examen BASE DE DATOS

Carrera CIENCIAS INFORMÁTICAS Asignatura: BASE DE DATOS 2 FECHA Tema 1 30 Examen SEGUNDO FINAL

Secciones A y B (Noche) 11/07/2012 Tema 2 25

Tema 3 25 Tema 4 20 Profesora

Carmen Martínez W. Nombre Alumno

TOTAL 100

El siguiente diagrama muestra un modelo simplificado de una empresa de transporte de pasajeros. Se pide lo siguiente: Tema 1 (Tipo y especificaciones 10 p. Body 20p)

Cree un PAQUETE denominado PCK_TRANSPORTE: - El tipo T_VIAJES como un VARRAY de no más de 100 registros con los siguientes elementos:

o ID_VIAJE o FEC_HORA_SALIDA o FEC_HORA_LLEGADA o CHOFER (Nombre y apellido) o CANT_TOT_PASAJEROS NUMBER(4)

- La función F_VERIFICAR_CHOFER, que recibe como parámetro el ID del personal y devuelve un boolean que será TRUE si el personal está disponible para un viaje. El personal está disponible si no está asignado a ningún viaje en la fecha del sistema (SYSDATE) y con fec_hora_llegada aún pendiente. Si ya está asignado devuelve FALSE

- La función F_DETERMINAR_VIAJES, sin parámetros y que retorna un tipo T_VIAJES, con la lista de viajes realizados en la fecha del sistema. Para calcular la cantidad total de pasajeros, debe sumar la cantidad de pasajeros anotados en cada punto del itinerario conforme el registro de viajes.

CREATE OR REPLACE PACKAGE PCK_TRANSPORTE IS

TYPE R_VIAJES IS RECORD

(ID_VIAJE NUMBER(8),

FEC_HORA_SALIDA DATE,

FEC_HORA_LLEGADA DATE,

CHOFER VARCHAR2(60),

CANT_TOT_PASAJEROS NUMBER(4));

TYPE T_VIAJES IS VARRAY(100) OF R_VIAJES;

FUNCTION F_VERIFICAR_CHOFER(PID NUMBER) RETURN BOOLEAN;

FUNCTION F_DETERMINAR_VIAJES RETURN T_VIAJES;

END;

/

CREATE OR REPLACE PACKAGE BODY PCK_TRANSPORTE IS

FUNCTION F_VERIFICAR_CHOFER(PID NUMBER) RETURN BOOLEAN IS

V_CANT NUMBER(2);

BEGIN

SELECT COUNT(*) INTO V_CANT

FROM VIAJES

WHERE ID_CHOFER = PID

AND TRUNC(FEC_HORA_SALIDA) = TRUNC(SYSDATE)

AND FEC_HORA_LLEGADA IS NULL;

IF NVL(V_CANT,0) > 0 THEN

RETURN FALSE;

ELSE

RETURN TRUE;





TOTAL 100 END IF;

END;

FUNCTION F_DETERMINAR_VIAJES RETURN T_VIAJES IS

TAB_VIAJES T_VIAJES := T_VIAJES();

CURSOR C_VIAJES IS

SELECT V.ID ID_VIAJE, V.FEC_HORA_SALIDA,

V.FEC_HORA_LLEGADA, P.NOMBRE||', '||P.APELLIDO CHOFER,

SUM(R.CANT_PASAJEROS) CANTIDAD

FROM VIAJES V JOIN PERSONAL P

ON P.ID = V.ID_CHOFER JOIN

REGISTRO_VIAJE R ON

R.ID_VIAJE = V.ID

GROUP BY V.ID, V.FEC_HORA_SALIDA,

V.FEC_HORA_LLEGADA, P.NOMBRE||', '||P.APELLIDO;

V_CONT NUMBER:=1;

BEGIN

FOR REG IN C_VIAJES LOOP

TAB_VIAJES.EXTEND;

TAB_VIAJES(V_CONT).ID_VIAJE := REG.ID_VIAJE;

TAB_VIAJES(V_CONT).FEC_HORA_SALIDA := REG.FEC_HORA_SALIDA;

TAB_VIAJES(V_CONT).FEC_HORA_LLEGADA := REG.FEC_HORA_LLEGADA;

TAB_VIAJES(V_CONT).CHOFER := REG.CHOFER;

TAB_VIAJES(V_CONT).CANT_TOT_PASAJEROS := REG.CANTIDAD;

V_cONT := V_CONT + 1;

END LOOP;

RETURN TAB_VIAJES;

END;

END;

/

Tema 2:

Cree un trigger al INSERTAR un REGISTRO_VIAJE que deberá hacer lo siguiente: - Controlar que la fec_hora_llegada sea superior a la última fec_hora_llegada del registro de viajes para el mismo

itinerario. De no ser así, dará error. - Actualizar la tabla VIAJES sumando a la RECAUDACIÓN, la recaudación del registro que se inserta

(cant_pasajeros por costo del itinerario) CREATE OR REPLACE TRIGGER T_BI_REGISTRO

BEFORE INSERT ON REGISTRO_VIAJE

FOR EACH ROW

DECLARE

R_DETALLE DETALLE_ITINERARIO%ROWTYPE;

V_COSTO NUMBER(8);

V_FEC_LLEGADA DATE;

BEGIN

BEGIN

SELECT FEC_HORA_LLEGADA INTO V_FEC_LLEGADA

FROM REGISTRO_VIAJE

WHERE ID_VIAJE = :NEW.ID_VIAJE

AND ID_ITINERARIO = :NEW.ID_ITINERARIO

AND ORDEN = (SELECT MAX(ORDEN)

FROM REGISTRO_VIAJE

WHERE ID_VIAJE = :NEW.ID_VIAJE

AND ID_ITINERARIO = :NEW.ID_ITINERARIO);

EXCEPTION

WHEN OTHERS THEN

V_FEC_LLEGADA := NULL;

END;

IF (V_FEC_LLEGADA IS NOT NULL) AND

(V_FEC_LLEGADA > :NEW.FEC_HORA_LLEGADA) THEN





TOTAL 100 RAISE_APPLICATION_ERROR(-20000, 'Horario anterior a la ultima');

END IF;

SELECT COSTO INTO V_COSTO

FROM ITINERARIO WHERE ID = :NEW.ID_ITINERARIO;

UPDATE VIAJES

SET RECAUDACION = NVL(RECAUDACION,0) +

:NEW.CANT_PASAJEROS * V_COSTO

WHERE ID = :NEW.ID_VIAJE;

END;

/

Tema 3: Cree una vista materializada denominada V_RENDIMIENTO para determinar cuál ha sido el chofer con mayor rendimiento durante el mes de julio del corriente. (El chofer que ha tenido mayor cantidad de viajes realizados). La vista se refrescará diariamente a la medianoche

Id Chofer Nombre y Apellido Cantidad

de viajes

Recaudación Demora

acumulada

CREATE MATERIALIZED VIEW V_RENDIMIENTO

BUILD IMMEDIATE REFRESH

START WITH SYSDATE NEXT NEXT_DAY(TRUNC(SYSDATE), 'SÁBADO')

AS SELECT V.ID_CHOFER, P.NOMBRE||', '||P.APELLIDO "Nombre y Apellido",

COUNT(V.ID) CANTIDAD_VIAJES, SUM(V.RECAUDACION),

SUM(V.DURACION_DEMORA) DEMORA_ACUMULADA

FROM VIAJES V JOIN PERSONAL P

ON V.ID_CHOFER = P.ID

WHERE TO_CHAR(FEC_HORA_SALIDA,'MM/YYYY') = '07/2012'

GROUP BY V.ID_CHOFER, P.NOMBRE||', '||P.APELLIDO

HAVING COUNT(V.ID) = (SELECT MAX(CANTIDAD) FROM

(SELECT ID_CHOFER, COUNT(ID) CANTIDAD FROM

VIAJES

WHERE TO_CHAR(FEC_HORA_SALIDA,'MM/YYYY') = '07/2012'

GROUP BY ID_CHOFER));

Tema 4

Cree el procedimiento P_AGREGAR_DATAFILE que recibe como parámetro el nombre de un TABLESPACE y el nombre de un DATAFILE. El procedimiento deberá:

- Determinar cual es el directorio del último DATAFILE (el path) para dicho tablespace. - Alterar dinámicamente el tablespace agregando el datafile introducido como parámetro con un tamaño de 50 MB.

TIPS: Vea la sentencia de AGREGAR un datafile en la clase “TABLESPACES”. Podrá determinar el nombre del datafile con el path en la tabla DBA_DATA_FILES. CREATE OR REPLACE PROCEDURE P_AGREGAR_DATAFILE(PTABLESPACE VARCHAR2, PDATAFILE VARCHAR2)

IS

V_PATH VARCHAR2(500);

V_SENTENCIA VARCHAR2(1000);

BEGIN

SELECT SUBSTR(FILE_NAME, 1, INSTR(FILE_NAME,'\',-1)) INTO V_PATH

FROM DBA_DATA_FILES

WHERE FILE_ID = (SELECT MAX(FILE_ID) FROM DBA_DATA_fILES WHERE TABLESPACE_NAME =

UPPER(PTABLESPACE));

V_SENTENCIA := 'ALTER TABLESPACE '||PTABLESPACE || q'[ ADD DATAFILE ']' ||

RTRIM(V_PATH)||LTRIM(PDATAFILE) || q'[' SIZE 50 M]';

DBMS_OUTPUT.PUT_LINE(V_SENTENCIA);

END;





TOTAL 100 /

Documents

Examen BASE DE DATOS