Ejercicio: (avanzado) Gestión de personal

Enunciado #

Una empresa necesita gestionar sus empleados, contratos, direcciones, departamentos y proyectos. Los empleados pueden tener varios contratos a lo largo del tiempo y participar en varios proyectos con un rol específico en cada uno. El país se reutiliza en direcciones y departamentos y debe almacenarse en una tabla propia.

Datos que debes almacenar #

• Pais: id_pais (entero, identificador), nombre (texto), idioma (texto) y bandera (texto; ruta o URL de la imagen).
• Direccion: id_direccion (entero, identificador), calle (texto), ciudad (texto), estado (texto), codigo_postal (texto) e id_pais (entero).
• Departamento: id_departamento (entero, identificador), nombre (texto) e id_pais (entero; país de localización).
• Empleado: id_empleado (entero, identificador), nombre (texto), apellido (texto), fecha_nacimiento (fecha), fecha_contratacion_inicial (fecha), id_direccion (entero) e id_departamento (entero).
• Contrato: id_contrato (entero, identificador), salario (decimal), fecha_inicio (fecha), fecha_fin (fecha, opcional mientras siga vigente), tipo (texto) e id_empleado (entero).
• Proyecto: id_proyecto (entero, identificador), nombre (texto), descripcion (texto), fecha_inicio (fecha), fecha_fin_estimada (fecha) e id_departamento (entero).
• Asignacion: id_empleado (entero), id_proyecto (entero), rol (texto) y fecha_inicio (fecha; inicio de la participación).

Reglas del negocio #

1. Cada empleado tiene una única dirección registrada. Para concretar la relación 1:1, cada fila de Direccion corresponde como máximo a un empleado: Empleado.id_direccion debe ser obligatorio y único. Dos personas que vivan en el mismo lugar pueden tener registros de dirección con los mismos datos; los valores de calle y ciudad no son únicos.
2. Un empleado pertenece a un departamento y un departamento puede tener muchos empleados.
3. Cada contrato pertenece a un empleado y un empleado puede tener varios contratos históricos.
4. Un país puede aparecer en muchas direcciones y en muchos departamentos. No repitas sus atributos en esas tablas.
5. La nota del documento propone relacionar proyectos y departamentos sin indicar cardinalidad. En esta solución, cada proyecto pertenece a un departamento y un departamento puede gestionar varios proyectos.
6. La relación entre empleados y proyectos es N:N. Usa (id_empleado, id_proyecto) como PK compuesta de Asignacion; se registra un rol y una fecha de inicio por pareja. No se pide un historial de cambios de rol o de reincorporaciones.
7. Los empleados pueden colaborar en proyectos de otros departamentos; no se exige que el departamento del empleado coincida con el del proyecto.

Diseña las siete tablas y sus relaciones. La coherencia de fechas y los rangos salariales se validarían al implementar la base de datos.

Criterios para el challenge #

Conserva los nombres de tablas y campos indicados; los nombres de campos están escritos sin tildes y con guiones bajos. Los tipos entre paréntesis corresponden a las opciones del editor. Todos los campos son obligatorios (NN), salvo los marcados como opcionales; marca las claves primarias (PK), foráneas (FK) y las restricciones de unicidad que se deducen de las reglas.

Deja desactivados autoincremento, clave alternativa e índice. Deja vacíos el valor por defecto, las acciones ON DELETE y ON UPDATE y, salvo que se indique expresamente, CHECK. Usa los extremos 1 y n, o 1 y 1, en las conexiones; una FK opcional tiene NN desmarcado.

Una PK compuesta identifica por la combinación de sus campos: no marques cada componente como único por separado. Si una FK referencia una PK compuesta, dibuja una conexión por cada pareja de campos; todas juntas representan una única referencia a la misma fila. En una implementación SQL se debe declarar una sola FOREIGN KEY con todas esas columnas, no varias referencias independientes. El editor no comprueba por sí solo las reglas sobre varias filas ni todas las restricciones compuestas.