Tarea 6 – Base de datos – 1º DAM/DAW e-learning

Crear un procedimiento que permita cambiar a todos los agentes de una familia determinada (familia origen) a otra familia (familia destino).

El procedimiento tendrá la siguiente cabecera CambiarAgentesFamilia( id_FamiliaOrigen, id_FamiliaDestino), donde cada uno de los argumentos corresponde a un identificador de Familia.

Cambiará la columna Identificador de Familia de todos los agentes, de la tabla AGENTES, que pertenecen a la Familia con código id_FamiliaOrigen por el código id_FamiliaDestino Previamente comprobará que ambas familias existen y que no son iguales.

Para la comprobación de la existencia de las familias se puede utilizar un cursor variable, o contar el número de filas y en caso de que no exista, se visualizará el mensaje correspondiente mediante una excepción del tipo RAISE_APPLICATION_ERROR.

También se mostrará un mensaje en caso de que ambos argumentos tengan el mismo valor.

El procedimiento visualizará el mensaje «Se han trasladado XXX agentes de la familia XXXXXX a la familia ZZZZZZ» donde XXX es el número de agentes que se han cambiado de familia, XXXXXX es el nombre de la familia origen y ZZZZZZ es el nombre de la familia destino.

Actividad 2.

Queremos controlar algunas restricciones a la hora de trabajar con agentes:

• La longitud de la clave de un agente no puede ser inferior a 6.
• La habilidad de un agente debe estar comprendida entre 0 y 9 (ambos inclusive).
• La categoría de un agente sólo puede ser igual a 0, 1 o 2.
• Si un agente tiene categoría 2 no puede pertenecer a ninguna familia y debe pertenecer a una oficina.
• Si un agente tiene categoría 1 no puede pertenecer a ninguna oficina y debe pertenecer a una familia.
• Todos los agentes deben pertenecer a una oficina o a una familia pero nunca a ambas a la vez.

Se pide crear un disparador para asegurar estas restricciones. El disparador deberá lanzar todos los errores que se puedan producir en su ejecución mediante errores que identifiquen con un mensaje adecuado por qué se ha producido dicho error.

Algunas de las restricciones implementadas con el disparador se pueden incorporar a la definición del esquema de la tabla utilizando el Lenguaje de Definición de Datos (Check,Unique,..).

Identifica cuáles son y con qué tipo de restricciones las implementarías

Aquí te puedes descargar la tarea.

La empresa ECOPEDIDOS registra los pedidos realizados por sus clientes a la empresa. Necesitamos almacenar:

• De los clientes queremos conocer el código del cliente, nombre y apellidos, DNI, teléfono, dirección, ciudad, provincia y límite del crédito del cliente.
• De las categorías de los productos: código de la categoría y la descripción.
• De los pedidos necesitaremos almacenar: código del pedido, fecha en la que se ha realizado el pedido, fecha en la que se espera que llegue el pedido, fecha en la que se ha entregado el pedido, estado del envío (P: Pendiente de envío, E: Enviado, D: Devuelto), si el pedido está pagado (S : si está pagado /N: No está pagado), Observaciones y el cliente que ha realizado el pedido.
• Cada pedido llevará una serie de productos en lo que denominamos detalles de los pedidos con los siguientes datos: código del pedido al que corresponde, código del producto pedido, cantidad pedida del producto, precio de venta, descuento realizado, nº de línea.

Actividad 1

Realizaremos una función llamada CALCULAR_PEDIDO a la que le daremos un código de pedido como entrada y nos devuelva como salida el total de ese pedido.

A cada producto del pedido se le realizará un descuento sobre el precio de venta que dependerá del nº de unidades pedidas:

• Si el nº de unidades está entre 0 y 5: no habrá descuento
• Si en nº de unidades pedidas está entre 6 y 10 el descuento es del 5%
• Si el nº de unidades pedidas está entre 11 y 15 el descuento es del 7%
• Si el nº de unidades pedidas es mayor15 el descuento será del 10%.  El total de un producto será el resultado de multiplicar el precio de venta (con el descuento realizado) por en nº de unidades pedidas.

Actividad 2

Realizaremos un procedimiento llamado CALCULAR_CLIENTE que dándole como entrada un código de cliente y un año, nos devuelva como salida dos parámetros : el total facturado por los pedidos que haya pagado y el total facturado por los pedidos que no haya pagado durante ese año ese cliente.

Sólo se tendrán en cuenta los pedidos que se hayan entregado ya.

Sólo tendremos en cuenta los clientes que hayan realizado más de dos pedido en ese año.

Si el cliente no existe saltaremos una excepción, visualizaremos un mensaje de que ese cliente no existe y finalizará el procedimiento devolviendo como salida -1 en ambos totales.

Si el cliente no tiene pedidos durante ese año, saltaremos una excepción y visualizaremos un mensaje de que ese cliente tiene no tiene pedidos ese año y finalizará el procedimiento devolviendo como salida -1 en ambos totales.

Por cada pedido realizaremos una llamada a la función CALCULAR_PEDIDO, realizada en la actividad 1, que nos calculará el total de cada pedido.

Estos totales se irán acumulando en el total de los pedidos pagados o en el total de los pedidos no pagados.

Finalmente devolveremos como parámetros dichos totales.

Actividad 3

Queremos crear los siguientes disparadores:

3.1. Un disparador llamado DISP_PEDIDOS que salte al insertar o actualizar en la tabla pedidos para que no nos deje insertar o actualiza si se produce uno de los siguientes casos:

• La fecha de pedido debe ser menor que la fecha esperada de entrega y la fecha de entrega.
• El estado de un pedido sólo puede tener los valores P, E o D (Pendiente de entregar, Entregado o Devuelto)
• La columna PedidoPagado sólo puede tener los valores : S o N (Pagado Si, Pagado No). Se lanzará una excepción mediante la cual el registro no se inserte y visualiza el mensaje adecuado

3.2. Un disparador llamado DISP_DETALLEPEDIDOS, en el que al insertar o actualizar en la tabla detallepedidos controle que la cantidad pedida del producto sea menor que la cantidad en stock de dicho producto.

Se lanzará una excepción mediante la cual el registro no se inserte y visualiza el mensaje adecuado.

Aquí puedes descargar la tarea.