Proyecto final
Requerimientos funcionales
RF-01: Gestión de Pacientes: CRUD (Crear, Leer, Actualizar, Borrar).
RF-02: Gestión de Médicos: CRUD.
RF-03: Gestión de Sucursales: CRUD.
RF-04: Gestión de Citas: CRUD (Agendar, consultar, modificar, cancelar).
RF-05: Autenticación: Permitir el "Inicio de sesión" (Login) de usuarios.
Requerimientos no funcionales
1. RNF-01: Arquitectura por Capas: El sistema debe usar una arquitectura de
Monolito Modular con estricta separación de capas (Web, Service, Repository).
2. RNF: Seguridad - RBAC (Control de Acceso Basado en Roles): (Tu
"Seguridad" la hacemos específica). El sistema debe usar roles de base de
datos (rol_recepcionista, rol_medico, rol_admin).
3. RNF-04: Seguridad - Cifrado en Tránsito (SSL/TLS): (Tu "Cifrado" lo hacemos
específico). Toda la comunicación debe ser cifrada.
(Evidencia: Laboratorio de OpenSSL y SSL/MySQL).
4. RNF-05: Seguridad - Cifrado en Reposo: (Este lo debemos AGREGAR). Los
datos sensibles (ej. doc_identidad) deben estar cifrados en la base de datos.
(Evidencia: Requisito del estudio de caso).
5. RNF-06: Operaciones - Estrategia de Backup: Deben existir scripts para el
backup de hospital_demo.
6. RNF-07: Operaciones - Auditoría:El sistema debe registrar (log) eventos
críticos (quién accede a qué paciente).
7. RNF-08: Fiabilidad - Integridad de Datos: El sistema debe usar las llaves
foráneas (Foreign Keys)
8. RNF-09: Rendimiento - Manejo de Picos de Carga: La arquitectura debe ser
capaz de manejar picos (ej. al agendar citas a inicio de mes).
-- CREACIÓN DE LA BASE DE DATOS
CREATE DATABASE hospital_final;
USE hospital_final;
TABLA DE ROLES
CREATE TABLE roles (
id_rol INT AUTO_INCREMENT PRIMARY KEY,
nombre_rol VARCHAR(50) NOT NULL UNIQUE,
descripcion TEXT
);
TABLA DE USUARIOS
(Incluye datos de médicos y enfermeros)
CREATE TABLE usuarios (
id_usuario INT AUTO_INCREMENT PRIMARY KEY,
nombre_usuario VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
nombre VARCHAR(50) NOT NULL,
apellido VARCHAR(50) NOT NULL,
telefono VARCHAR(20),
correo VARCHAR(100),
especialidad VARCHAR(100),
area_asignada VARCHAR(100),
turno ENUM('Mañana','Tarde','Noche') DEFAULT NULL,
id_rol INT NOT NULL,
FOREIGN KEY (id_rol) REFERENCES roles(id_rol)
ON UPDATE CASCADE ON DELETE RESTRICT
);
TABLA DE PACIENTES
CREATE TABLE pacientes (
id_paciente INT AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(50) NOT NULL,
apellido VARCHAR(50) NOT NULL,
fecha_nacimiento DATE,
sexo ENUM('M','F','Otro'),
direccion VARCHAR(150),
telefono VARCHAR(20),
email VARCHAR(100),
fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
TABLA DE CITAS
CREATE TABLE citas (
id_cita INT AUTO_INCREMENT PRIMARY KEY,
id_paciente INT NOT NULL,
id_usuario INT NOT NULL, -- ahora se enlaza con el médico o usuario que la crea
fecha_cita DATE NOT NULL,
hora_cita TIME NOT NULL,
estado ENUM('Pendiente','Confirmada','Cancelada') DEFAULT 'Pendiente',
observaciones TEXT,
FOREIGN KEY (id_paciente) REFERENCES pacientes(id_paciente)
ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (id_usuario) REFERENCES usuarios(id_usuario)
ON UPDATE CASCADE ON DELETE RESTRICT
);
TABLA DE ATENCIÓN
CREATE TABLE atencion (
id_atencion INT AUTO_INCREMENT PRIMARY KEY,
id_cita INT NOT NULL,
id_usuario INT NOT NULL, -- usuario (médico) que atendió
fecha_atencion DATETIME DEFAULT CURRENT_TIMESTAMP,
observaciones TEXT,
FOREIGN KEY (id_cita) REFERENCES citas(id_cita)
ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (id_usuario) REFERENCES usuarios(id_usuario)
ON UPDATE CASCADE ON DELETE RESTRICT
);
TABLA DE DIAGNÓSTICOS
CREATE TABLE diagnostico (
id_diagnostico INT AUTO_INCREMENT PRIMARY KEY,
id_atencion INT NOT NULL,
descripcion TEXT NOT NULL,
tratamiento TEXT,
fecha_registro DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (id_atencion) REFERENCES atencion(id_atencion)
ON UPDATE CASCADE ON DELETE CASCADE
);
TABLA DE LABORATORIOS
CREATE TABLE laboratorios (
id_laboratorio INT AUTO_INCREMENT PRIMARY KEY,
id_paciente INT NOT NULL,
id_usuario INT NULL, -- ✅ Debe permitir NULL si usamos ON DELETE SET NULL
tipo_examen VARCHAR(100) NOT NULL,
fecha_solicitud DATE DEFAULT (CURRENT_DATE),
resultado TEXT,
estado ENUM('Pendiente','Completado','Entregado') DEFAULT 'Pendiente',
FOREIGN KEY (id_paciente) REFERENCES pacientes(id_paciente)
ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (id_usuario) REFERENCES usuarios(id_usuario)
ON UPDATE CASCADE ON DELETE SET NULL
);
TABLA DE RESULTADOS DE LABORATORIO
CREATE TABLE resultado_laboratorio (
id_resultado INT AUTO_INCREMENT PRIMARY KEY,
id_laboratorio INT NOT NULL,
descripcion TEXT,
valor_resultado VARCHAR(100),
unidad_medida VARCHAR(20),
fecha_registro DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (id_laboratorio) REFERENCES laboratorios(id_laboratorio)
ON UPDATE CASCADE ON DELETE CASCADE
);
-- TABLA DE BITÁCORA (LOG DE AUDITORÍA)
CREATE TABLE bitacora (
id_bitacora INT AUTO_INCREMENT PRIMARY KEY,
id_usuario INT,
accion VARCHAR(255),
fecha_hora DATETIME DEFAULT CURRENT_TIMESTAMP,
detalle TEXT,
FOREIGN KEY (id_usuario) REFERENCES usuarios(id_usuario)
ON UPDATE CASCADE ON DELETE SET NULL
);
-- INSERTAR ROLES INICIALES
INSERT INTO roles (nombre_rol, descripcion) VALUES
('Administrador', 'Acceso total al sistema'),
('Médico', 'Realiza citas, atenciones y diagnósticos'),
('Recepcionista', 'Gestiona citas y pacientes'),
('Laboratorista', 'Registra resultados de laboratorio'),
('Paciente', 'Consulta citas y resultados');
USUARIOS MYSQL (PERMISOS)
CREATE USER 'admin_hosp'@'localhost' IDENTIFIED BY 'Admin@1234';
GRANT ALL PRIVILEGES ON hospital_final.* TO 'admin_hosp'@'localhost' WITH
GRANT OPTION;
CREATE USER 'recepcionista'@'localhost' IDENTIFIED BY 'Recep@1234';
GRANT SELECT, INSERT, UPDATE ON hospital_final.pacientes TO
'recepcionista'@'localhost';
GRANT SELECT, INSERT, UPDATE ON hospital_final.citas TO
'recepcionista'@'localhost';
CREATE USER 'doctor'@'localhost' IDENTIFIED BY 'Doctor@1234';
GRANT SELECT, INSERT, UPDATE ON hospital_final.atencion TO
'doctor'@'localhost';
GRANT SELECT, INSERT, UPDATE ON hospital_final.diagnostico TO
'doctor'@'localhost';
GRANT SELECT, INSERT ON hospital_final.laboratorios TO 'doctor'@'localhost';
CREATE USER 'lab_user'@'localhost' IDENTIFIED BY 'Lab@1234';
GRANT SELECT, INSERT, UPDATE ON hospital_final.resultado_laboratorio TO
'lab_user'@'localhost';
CREATE USER 'paciente_usr'@'localhost' IDENTIFIED BY 'Paciente@1234';
GRANT SELECT ON hospital_final.citas TO 'paciente_usr'@'localhost';
GRANT SELECT ON hospital_final.resultado_laboratorio TO
'paciente_usr'@'localhost';
FLUSH PRIVILEGES;
USE hospital_final;
Ampliar `diagnostico` para poder reemplazar `resultado_laboratorio`
Permitir id_atencion NULL
Agregar vínculo opcional a laboratorios
Agregar campos de resultado
ALTER TABLE diagnostico
MODIFY COLUMN id_atencion INT NULL,
ADD COLUMN id_laboratorio INT NULL AFTER id_atencion,
ADD COLUMN valor_resultado VARCHAR(100) NULL AFTER descripcion,
ADD COLUMN unidad_medida VARCHAR(20) NULL AFTER valor_resultado;
-- Índice y FK hacia laboratorios (para mantener integridad al borrar/actualizar)
ALTER TABLE diagnostico
ADD INDEX idx_diag_id_laboratorio (id_laboratorio),
ADD CONSTRAINT fk_diag_laboratorio
FOREIGN KEY (id_laboratorio)
REFERENCES laboratorios(id_laboratorio)
ON UPDATE CASCADE
ON DELETE CASCADE;
-- Regla de integridad: exactamente uno de los dos (atención o laboratorio)
-- Nota: CHECK se hace cumplir desde MySQL 8.0.16+. En versiones anteriores se
ignora.
ALTER TABLE diagnostico
ADD CONSTRAINT chk_diag_origen
CHECK (
(id_atencion IS NOT NULL AND id_laboratorio IS NULL)
OR (id_atencion IS NULL AND id_laboratorio IS NOT NULL)
);
2) Migrar datos desde `resultado_laboratorio` a `diagnostico`
Mapeo:
[Link] -> [Link] (con COALESCE por si hay NULLs)
rl.valor_resultado -> diagnostico.valor_resultado
rl.unidad_medida -> diagnostico.unidad_medida
rl.fecha_registro -> diagnostico.fecha_registro
rl.id_laboratorio -> diagnostico.id_laboratorio
diagnostico.id_atencion = NULL (porque son resultados, no atenciones clínicas)
[Link] = NULL (no aplica a resultados)
INSERT INTO diagnostico
(id_atencion, id_laboratorio, descripcion, tratamiento, valor_resultado,
unidad_medida, fecha_registro)
SELECT
NULL AS id_atencion,
rl.id_laboratorio,
COALESCE([Link], 'Resultado de laboratorio') AS descripcion,
NULL AS tratamiento,
rl.valor_resultado,
rl.unidad_medida,
rl.fecha_registro
FROM resultado_laboratorio rl;
-- 3) Actualizar privilegios antiguos (si existían) y eliminar la tabla vieja
REVOKE SELECT, INSERT, UPDATE ON hospital_final.resultado_laboratorio FROM
'lab_user'@'localhost';
REVOKE SELECT ON hospital_final.resultado_laboratorio FROM
'paciente_usr'@'localhost';
DROP TABLE IF EXISTS resultado_laboratorio;
-- 4) (Recomendado) Crear una vista que exponga SOLO los resultados de laboratorio
-- Esto mantiene separado lo clínico (diagnósticos de atención) de lo de laboratorio
CREATE OR REPLACE VIEW vista_resultados_laboratorio AS
SELECT
d.id_diagnostico AS id_resultado,
d.id_laboratorio,
[Link],
d.valor_resultado,
d.unidad_medida,
d.fecha_registro
FROM diagnostico d
WHERE d.id_laboratorio IS NOT NULL;
-- 5) Conceder permisos sobre la vista (en sustitución de la tabla eliminada)
GRANT SELECT, INSERT, UPDATE ON hospital_final.vista_resultados_laboratorio TO
'lab_user'@'localhost';
GRANT SELECT ON hospital_final.vista_resultados_laboratorio TO
'paciente_usr'@'localhost';
FLUSH PRIVILEGES;