script_bd.sql

-- ============================================================
-- TALLER 4 - BASES DE DATOS II
-- Politécnico Colombiano Jaime Isaza Cadavid
-- Profesor: Manolo Pajaro Borras
-- Motor: PostgreSQL 15
-- ============================================================

-- ============================================================
-- PARTE 1: DDL — Creación de tablas
-- ============================================================

CREATE TABLE IF NOT EXISTS niveles (
    id_nivel     SERIAL PRIMARY KEY,
    nombre_nivel VARCHAR(20) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS docentes (
    id_docente       SERIAL PRIMARY KEY,
    nombres          VARCHAR(60) NOT NULL,
    primer_apellido  VARCHAR(40) NOT NULL,
    segundo_apellido VARCHAR(40),
    profesion        VARCHAR(80) NOT NULL,
    edad             INT
);

CREATE TABLE IF NOT EXISTS materias (
    id_materia     VARCHAR(10) PRIMARY KEY,
    nombre_materia VARCHAR(60) NOT NULL
);

CREATE TABLE IF NOT EXISTS materia_nivel (
    id_materia VARCHAR(10) REFERENCES materias(id_materia),
    id_nivel   INT         REFERENCES niveles(id_nivel),
    PRIMARY KEY (id_materia, id_nivel)
);

CREATE TABLE IF NOT EXISTS estudiantes (
    id_estudiante    INT PRIMARY KEY,
    primer_nombre    VARCHAR(60) NOT NULL,
    primer_apellido  VARCHAR(40) NOT NULL,
    segundo_apellido VARCHAR(40),
    genero           CHAR(1) CHECK (genero IN ('M','F')),
    fecha_nacimiento DATE,
    id_nivel         INT REFERENCES niveles(id_nivel)
);

CREATE TABLE IF NOT EXISTS asignaciones (
    id_asignacion SERIAL PRIMARY KEY,
    id_materia    VARCHAR(10) REFERENCES materias(id_materia),
    id_docente    INT         REFERENCES docentes(id_docente),
    id_nivel      INT         REFERENCES niveles(id_nivel),
    UNIQUE (id_materia, id_docente, id_nivel)
);

CREATE TABLE IF NOT EXISTS auditoria_nivel (
    id_auditoria   SERIAL PRIMARY KEY,
    id_estudiante  INT REFERENCES estudiantes(id_estudiante),
    nivel_anterior INT,
    nivel_nuevo    INT,
    fecha_cambio   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- PARTE 2: VISTAS
-- ============================================================

CREATE OR REPLACE VIEW vista_carga_docentes AS
SELECT
    d.nombres,
    d.primer_apellido,
    d.profesion,
    m.nombre_materia,
    n.nombre_nivel
FROM asignaciones a
JOIN docentes d ON a.id_docente = d.id_docente
JOIN materias m ON a.id_materia = m.id_materia
JOIN niveles  n ON a.id_nivel   = n.id_nivel
ORDER BY d.primer_apellido, m.nombre_materia;

CREATE OR REPLACE VIEW vista_estudiantes_por_nivel AS
SELECT
    n.nombre_nivel,
    e.id_estudiante,
    e.primer_nombre,
    e.primer_apellido,
    e.segundo_apellido,
    e.genero,
    e.fecha_nacimiento
FROM estudiantes e
JOIN niveles n ON e.id_nivel = n.id_nivel
ORDER BY n.id_nivel, e.primer_apellido;

-- ============================================================
-- PARTE 3: PROCEDIMIENTO ALMACENADO
-- ============================================================

CREATE OR REPLACE PROCEDURE sp_asignar_docente(
    p_id_materia VARCHAR(10),
    p_id_docente INT,
    p_id_nivel   INT
)
LANGUAGE plpgsql AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM docentes WHERE id_docente = p_id_docente) THEN
        RAISE EXCEPTION 'El docente con id % no existe', p_id_docente;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM materia_nivel WHERE id_materia = p_id_materia AND id_nivel = p_id_nivel) THEN
        RAISE EXCEPTION 'La materia % no se dicta en el nivel %', p_id_materia, p_id_nivel;
    END IF;

    IF EXISTS (
        SELECT 1 FROM asignaciones
        WHERE id_materia = p_id_materia
          AND id_docente = p_id_docente
          AND id_nivel   = p_id_nivel
    ) THEN
        RAISE EXCEPTION 'El docente % ya está asignado a la materia % en el nivel %',
            p_id_docente, p_id_materia, p_id_nivel;
    END IF;

    INSERT INTO asignaciones (id_materia, id_docente, id_nivel)
    VALUES (p_id_materia, p_id_docente, p_id_nivel);

    RAISE NOTICE 'Asignación creada: materia=%, docente=%, nivel=%',
        p_id_materia, p_id_docente, p_id_nivel;
END;
$$;

-- ============================================================
-- PARTE 4: FUNCIONES
-- ============================================================

-- Función 1: Edad promedio de los docentes
CREATE OR REPLACE FUNCTION fn_edad_promedio_docentes()
RETURNS NUMERIC(5,2)
LANGUAGE plpgsql AS $$
DECLARE
    resultado NUMERIC(5,2);
BEGIN
    SELECT ROUND(AVG(edad), 2)
    INTO resultado
    FROM docentes
    WHERE edad IS NOT NULL;

    RETURN resultado;
END;
$$;

-- Función 2: Total de materias disponibles para un estudiante según su nivel
CREATE OR REPLACE FUNCTION fn_materias_por_estudiante(p_id_estudiante INT)
RETURNS INT
LANGUAGE plpgsql AS $$
DECLARE
    total INT;
BEGIN
    IF NOT EXISTS (SELECT 1 FROM estudiantes WHERE id_estudiante = p_id_estudiante) THEN
        RAISE EXCEPTION 'El estudiante % no existe', p_id_estudiante;
    END IF;

    SELECT COUNT(*)
    INTO total
    FROM materia_nivel mn
    JOIN estudiantes e ON mn.id_nivel = e.id_nivel
    WHERE e.id_estudiante = p_id_estudiante;

    RETURN total;
END;
$$;

-- ============================================================
-- PARTE 5: TRIGGERS
-- ============================================================

-- Trigger 1: Bloquear asignación duplicada con mensaje claro
CREATE OR REPLACE FUNCTION fn_trg_evitar_duplicado()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM asignaciones
        WHERE id_materia = NEW.id_materia
          AND id_docente = NEW.id_docente
          AND id_nivel   = NEW.id_nivel
    ) THEN
        RAISE EXCEPTION 'El docente ya está asignado a esa materia en ese nivel';
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_evitar_duplicado_asignacion
BEFORE INSERT ON asignaciones
FOR EACH ROW EXECUTE FUNCTION fn_trg_evitar_duplicado();

-- Trigger 2: Auditoría al cambiar el nivel de un estudiante
CREATE OR REPLACE FUNCTION fn_trg_auditoria_nivel()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF OLD.id_nivel IS DISTINCT FROM NEW.id_nivel THEN
        INSERT INTO auditoria_nivel (id_estudiante, nivel_anterior, nivel_nuevo)
        VALUES (OLD.id_estudiante, OLD.id_nivel, NEW.id_nivel);
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_auditoria_nivel_estudiante
AFTER UPDATE ON estudiantes
FOR EACH ROW EXECUTE FUNCTION fn_trg_auditoria_nivel();