Enunciado

Para esta práctica se va a crear una base de datos usando SQL Developer que llevará como nombre Q-Vision, la cual consta de tres tablas que son EMPLEADO, CARGO y DEPARTAMENTO. A continuación, se visualiza el diagrama Entidad-Relación para que se tenga una mejor idea de la estructura de la base de datos.

De donde se tiene esta información de la tabla EMPLEADO:

• El atributo cedula es la clave primaria y es de tipo NUMBER con una longitud de 10 dígitos.
• El atributo cod_cargo es la clave foránea que hace referencia a la clave primaria de la tabla CARGO, este campo es de tipo NUMBER con una longitud de 3 dígitos y es no nulo.
• El atributo nombre es de tipo VARCHAR2 con una longitud de 40 caracteres y además es un campo obligatorio.
• El atributo apellido es de tipo VARCHAR2 con una longitud de 40 caracteres y es obligatorio.
• El atributo edad es de tipo NUMBER con una longitud de 2 dígitos y es obligatorio.
• El atributo telefono es de tipo NUMBER con una longitud de 10 dígitos y es un campo obligatorio.

Los atributos de la tabla CARGO cuentan con las siguientes características:

• El campo cod es la clave primaria y es de tipo NUMBER con una longitud de 3 dígitos.
• El campo cod_dept es la clave foránea que referencia a la clave primaria de la tabla DEPARTAMENTO, es no nulo y es de tipo NUMBER con una longitud de 3 dígitos.
• El nombre es un campo obligatorio de tipo VARCHAR2 y con longitud de 40 caracteres.
• El atributo salario es obligatorio y es de tipo NUMBER con una longitud de 20 dígitos.

Adicionalmente los atributos de la tabla DEPARTAMENTO poseen las siguientes características:

• El cod es de tipo NUMBER con una longitud de 3 dígitos y es la clave primaria.
• El atributo nombre es obligatorio y de tipo VARCHAR2 con una longitud de 40 caracteres.
• El presupuesto es un atributo obligatorio de tipo NUMBER y con una longitud de 30 dígitos

Crear las tablas EMPLEADO, CARGO y DEPARTAMENTO usando la sentencia EXECUTE IMMEDIATE.

Para insertar registros a la tabla EMPLEADO cree un procedimiento almacenado llamado ingresar_empleado que reciba como parámetro los valores necesarios para insertar un nuevo empleado a la BD. Dentro del procedimiento utilice EXECUTE IMMEDIATE y variables BIND para este propósito.

Cree también procedimientos almacenados con parámetros para insertar registros a las tablas CARGO y DEPARTAMENTO que tengan como nombre ingresar_cargo e ingresar_departamento respectivamente. Dentro de estos procedimientos use EXECUTE IMMEDIATE y variables BIND para lograr la inserción.

Adicionalmente se necesita que en los procedimientos mencionados anteriormente haya una excepción que capture específicamente el error que se produce cuando se intenta ingresar un valor duplicado en un campo que es clave primaria o clave alternativa y se debe de imprimir un mensaje informando que ese atributo no se puede ingresar porque ya está repetido en la base de datos. Por ejemplo: Si se intenta ingresar un registro de un nuevo empleado el cual su cédula es igual a una que está en la base de datos, el programa no debe salirse “abruptamente” sino que debe mostrar el mensaje descrito antes y luego salir de una forma “segura”.

Al insertar un empleado a la base de datos, la cédula y el teléfono deben ser verificados porque sus valores no pueden ser menor o igual a cero. Por este motivo se pide crear dos triggers:

El primero se llamará verificar_telefono que nos permitirá controlar el ingreso del número de teléfono de la persona y si este valor incumple la regla, entonces se debe lanzar una excepción definida por el usuario el cual contenga un procedimiento RAISE_APPLICATION_ERROR para imprimir en pantalla el mensaje “El número ingresado no es válido. Por favor revisar” y el trabajador no ingresará a la base de datos.

El segundo tendrá como nombre verificar_cedula y será similar al disparador verificar_telefono con la diferencia en que en el procedimiento RAISE_APPLICATION_ERROR se debe imprimir el mensaje “La cédula ingresada no es válida. Por favor revisar”.

La empresa Q-Vision tiene como restricción o regla de negocio contratar a empleados con una edad mayor o igual a 19 años. Para controlar esto se debe crear el trigger verificar_edad para que cuando se vaya a ingresar un nuevo empleado y su edad sea menor a la requerida, se debe rechazar la inserción y mostrar un mensaje diciendo “El empleado no cumple con la edad necesaria para trabajar en Q-Vision”, utilizando el procedimiento RAISE_APPLICATION_ERROR.

En la siguiente tabla se muestra los nombres de los departamentos, cargos, cantidad máxima de empleados que se admite en cada cargo, el salario mensual del empleado y el presupuesto que tiene cada departamento de la empresa Q-Vision para pagar la nómina de sus empleados.

Se solicita crear un trigger con nombre cantidad_empleados que se va a encargar de controlar que no se sobrepase el límite de empleados que permite cada cargo de la empresa. Si se intenta ingresar un empleado a un cargo que ya tiene todos sus puestos ocupados, se debe lanzar un procedimiento RAISE_APPLICATION_ERROR para que el empleado nuevo no ingrese a la BD y adicional mostrar el siguiente mensaje en pantalla “No se puede ingresar este empleado debido a que el cargo nombre_cargo ya está con la capacidad máxima de trabajadores”; de lo contrario se podrá ingresar el empleado a la BD.

Mensualmente la empresa requiere sacar un informe de los nombres de los empleados que hay en cada departamento. El informe consta del nombre del departamento, luego los cargos, dentro de cada cargo los nombres de los empleados que laboran en él, y por último se debe mostrar la cantidad de trabajadores que hay actualmente en dicho departamento. Ahora mostramos un ejemplo de cómo se debería ver un informe.

Para lograr esto utilice un procedimiento almacenado con nombre generar_informe, el cual no recibe parámetros y deberá generar este reporte mostrándolo en pantalla.

La empresa Q-Vision le gusta celebrar los cumpleaños de sus trabajadores y hacerlos sentir que son parte fundamental de la empresa, por tal motivo, se le solicita crear un procedimiento almacenado que lleve como nombre happy_birthday, este procedimiento recibirá como parámetro la cédula del empleado que esté cumpliendo años y actualizará la edad de la persona en 1. Además, se tendrá que imprimir en pantalla el siguiente mensaje “Señor(a) nombre_empleado apellido_empleado, la empresa Q-Vision está dichosa de contar con una persona tan leal, humilde, profesional, y honesto(a) como lo eres tú. Por todo ello queremos formar parte de este día tan especial, deseándote un muy feliz cumpleaños, y un bonito año. ¡Muchísimas felicidades!”. En caso de que la cédula no exista en la BD, se deberá capturar la excepción de tipo NO_DATA_FOUND para que imprima en pantalla lo siguiente “La cédula cedula_empleado no se encuentra en la base de datos”.

Cree también un procedimiento con nombre eliminar_empleado que reciba como parámetro el documento de identidad del empleado. Si el empleado existe se debe eliminar de la BD e imprimir el mensaje “El empleado con cédula cedula_empleado ya no hace parte de nuestra empresa”. De lo contrario si el documento de identidad no existe en la base de datos, se debe ejecutar un procedimiento RAISE_APPLICATION_ERROR que imprima el siguiente mensaje “El empleado con cédula cedula_empleado no se encuentra en la BD”.

Por último, cree una función que se llame salario_empresa que permita calcular el salario total de todos los empleados que hay actualmente en la empresa.

Nota 1: Todo lo que se pide en la práctica se debe realizar usando PL/SQL.

Nota 2: Enviar un archivo .rar con todos los scripts que usó para crear las tablas, procedimientos almacenados, etc, del ejercicio práctico.

Nota 3: Colocar los mismos nombres sugeridos en el enunciado al momento de crear las tablas, triggers, funciones y procedimientos almacenados.