Cuestión 1 — Diseño conceptual (Diagrama E-R extendido)

Entidades y atributos (subrayado = identificador):

• ALUMNO( nia, nombre, apellidos, grupo )
• PORTATIL( num_serie, marca, modelo, fecha_compra, estado )
• PRESTAMO( id_prestamo, fecha_prestamo, fecha_dev_prevista, fecha_dev_real )
• INCIDENCIA( id_incidencia, descripcion, fecha )

Decisión de modelado. El préstamo asocia un alumno con un portátil, pero un mismo alumno repite portátil y un mismo portátil se presta muchas veces en fechas distintas. Por tanto la relación alumno-portátil es N:M con atributos propios (fechas) y la modelamos como entidad asociativa PRESTAMO con identificador artificial id_prestamo (más cómodo que la clave compuesta natural nia+num_serie+fecha, que además fallaría si se permitieran dos préstamos el mismo día). La incidencia es una entidad débil por identificación respecto al portátil (o entidad fuerte con FK; se opta por id propio).

Relaciones y cardinalidades (mín, máx):

Lectura de las participaciones: ALUMNO y PORTATIL tienen participación parcial en realiza/afecta_a (mínimo 0: pueden existir sin préstamos). PRESTAMO tiene participación total hacia ambos (1,1): no existe préstamo sin alumno ni sin portátil. INCIDENCIA es total hacia PORTATIL (1,1); PORTATIL es parcial (0,N).

Figura 1. Diagrama Entidad-Relación extendido: ALUMNO y PORTATIL con PRESTAMO como entidad asociativa (N:M con atributos) e INCIDENCIA colgando de PORTATIL; cada arco rotulado con su cardinalidad (mín, máx).

Cuestión 2 — Modelo relacional normalizado (3FN)

Reglas de transformación: cada entidad fuerte → una tabla; la entidad asociativa PRESTAMO → tabla con FK a ALUMNO y a PORTATIL; INCIDENCIA → tabla con FK a PORTATIL.

ALUMNO    ( nia (PK), nombre, apellidos, grupo )
PORTATIL  ( num_serie (PK), marca, modelo, fecha_compra, estado )
PRESTAMO  ( id_prestamo (PK), nia (FK→ALUMNO), num_serie (FK→PORTATIL),
            fecha_prestamo, fecha_dev_prevista, fecha_dev_real )
INCIDENCIA( id_incidencia (PK), num_serie (FK→PORTATIL), descripcion, fecha )

Figura 2. Modelo relacional en 3FN: cuatro tablas con clave primaria (PK) y claves ajenas (FK); las flechas muestran las referencias PRESTAMO.nia→ALUMNO, PRESTAMO.num_serie→PORTATIL e INCIDENCIA.num_serie→PORTATIL.

Definición física (DDL MariaDB/MySQL, motor InnoDB):

CREATE TABLE alumno (
    nia        CHAR(8)      PRIMARY KEY,            -- NIA: 8 dígitos GVA
    nombre     VARCHAR(60)  NOT NULL,
    apellidos  VARCHAR(120) NOT NULL,
    grupo      VARCHAR(20)  NOT NULL
) ENGINE=InnoDB;

CREATE TABLE portatil (
    num_serie    VARCHAR(40) PRIMARY KEY,
    marca        VARCHAR(40) NOT NULL,
    modelo       VARCHAR(60) NOT NULL,
    fecha_compra DATE,
    estado       ENUM('disponible','prestado','baja') NOT NULL DEFAULT 'disponible'
) ENGINE=InnoDB;

CREATE TABLE prestamo (
    id_prestamo        INT AUTO_INCREMENT PRIMARY KEY,
    nia                CHAR(8)     NOT NULL,
    num_serie          VARCHAR(40) NOT NULL,
    fecha_prestamo     DATE        NOT NULL,
    fecha_dev_prevista DATE        NOT NULL,
    fecha_dev_real     DATE        NULL,           -- NULL = aún no devuelto
    CONSTRAINT fk_prestamo_alumno
        FOREIGN KEY (nia) REFERENCES alumno(nia)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_prestamo_portatil
        FOREIGN KEY (num_serie) REFERENCES portatil(num_serie)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE incidencia (
    id_incidencia INT AUTO_INCREMENT PRIMARY KEY,
    num_serie     VARCHAR(40)  NOT NULL,
    descripcion   VARCHAR(255) NOT NULL,
    fecha         DATE         NOT NULL,
    CONSTRAINT fk_incidencia_portatil
        FOREIGN KEY (num_serie) REFERENCES portatil(num_serie)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

Justificación de las políticas referenciales.

• prestamo → alumno/portatil: ON DELETE RESTRICT. No debe poder borrarse un alumno o un portátil que conserva historial de préstamos (integridad y trazabilidad del programa de digitalización). ON UPDATE CASCADE por si se corrige un NIA o número de serie.
• incidencia → portatil: ON DELETE CASCADE es admisible (si se da de baja físicamente y se elimina el portátil, sus incidencias carecen de sentido); en un sistema con auditoría se preferiría RESTRICT y dar de baja con estado='baja'.

Justificación de la 3FN. Una tabla está en 3FN si está en 2FN y ningún atributo no primo depende transitivamente de la clave (todo atributo no clave depende de la clave, de toda la clave y de nada más que la clave).

Formalmente, las únicas dependencias funcionales de cada relación parten de su clave completa, por ejemplo:

No existe ninguna dependencia transitiva del tipo \text{nia} \rightarrow \text{grupo} \rightarrow X: como el grupo se guarda solo como atributo descriptivo (sin atributos propios del grupo), no hay ningún X que dependa de \text{grupo} en la tabla.

• 1FN: todos los atributos son atómicos (no hay listas ni grupos repetitivos como «varias incidencias en una celda»).
• 2FN: todas las claves primarias son simples (nia, num_serie, id_prestamo, id_incidencia), luego no puede haber dependencias parciales de una clave compuesta. Inmediata.
• 3FN: se comprueba la ausencia de dependencias transitivas:
  • En alumno: nombre, apellidos y grupo dependen directamente de nia. Atención al grupo: no se almacena ningún atributo del grupo (p. ej. tutor, aula) en esta tabla; si lo hubiera, nia → grupo → tutor sería transitiva y obligaría a extraer una tabla GRUPO. Como solo guardamos el nombre del grupo como atributo descriptivo del alumno, no hay transitividad.
  • En portatil: marca, modelo, fecha_compra, estado dependen de num_serie. No se deriva ningún atributo de otro (p. ej. no guardamos «fabricante de la marca»), luego no hay transitividad.
  • En prestamo: las tres fechas y las dos FK dependen únicamente de id_prestamo. Los datos del alumno o del portátil no se replican aquí (solo sus claves), evitando la dependencia transitiva id_prestamo → nia → nombre.
  • En incidencia: descripcion y fecha dependen de id_incidencia; no hay derivados.

Por tanto, las cuatro relaciones están en 3FN (y de hecho en BCNF, al ser claves candidatas simples).

Cuestión 3 — Consultas SQL

3.1 — Portátiles disponibles, ordenados por marca y modelo:

SELECT num_serie, marca, modelo, fecha_compra
FROM   portatil
WHERE  estado = 'disponible'
ORDER  BY marca, modelo;

3.2 — Nº de préstamos por grupo, solo grupos con más de 10 (JOIN + GROUP BY + HAVING):

SELECT a.grupo,
       COUNT(*) AS total_prestamos
FROM   prestamo p
JOIN   alumno   a ON a.nia = p.nia
GROUP  BY a.grupo
HAVING COUNT(*) > 10
ORDER  BY total_prestamos DESC;

Se usa HAVING (no WHERE) porque el filtro actúa sobre el resultado de la agregación COUNT(*), que no existe a nivel de fila.

3.3 — Préstamos vencidos no devueltos (NIA, nombre, nº de serie):

SELECT a.nia,
       a.nombre,
       p.num_serie,
       p.fecha_dev_prevista
FROM   prestamo p
JOIN   alumno   a ON a.nia = p.nia
WHERE  p.fecha_dev_real IS NULL          -- aún no devuelto
  AND  p.fecha_dev_prevista < CURDATE(); -- la fecha prevista ya pasó

Clave: comprobar la no devolución con IS NULL (no = NULL, que en SQL siempre es desconocido) y comparar la fecha prevista con CURDATE().

3.4 — Portátiles que nunca tuvieron incidencias (dos soluciones):

Con LEFT JOIN ... IS NULL (antijunción):

SELECT po.num_serie, po.marca, po.modelo
FROM   portatil po
LEFT   JOIN incidencia i ON i.num_serie = po.num_serie
WHERE  i.id_incidencia IS NULL;

Equivalente con subconsulta NOT EXISTS (suele ser la más eficiente en InnoDB):

SELECT po.num_serie, po.marca, po.modelo
FROM   portatil po
WHERE  NOT EXISTS (
    SELECT 1 FROM incidencia i
    WHERE i.num_serie = po.num_serie
);

Cuestión 4 — Trigger de integridad

DELIMITER //

CREATE TRIGGER trg_prestamo_before_insert
BEFORE INSERT ON prestamo
FOR EACH ROW
BEGIN
    DECLARE v_estado VARCHAR(20);

    -- Recuperar el estado actual del portátil que se quiere prestar
    SELECT estado INTO v_estado
    FROM   portatil
    WHERE  num_serie = NEW.num_serie;

    -- Si no está disponible, abortar la operación
    IF v_estado IS NULL OR v_estado <> 'disponible' THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'El portatil no esta disponible para prestamo';
    END IF;

    -- Si es válido, marcar el portátil como prestado
    UPDATE portatil
    SET    estado = 'prestado'
    WHERE  num_serie = NEW.num_serie;
END//

DELIMITER ;

¿BEFORE o AFTER, y por qué? Se usa BEFORE INSERT:

1. La validación debe ejecutarse antes de materializar la fila, para poder abortar la inserción con SIGNAL SQLSTATE '45000' (en MySQL/MariaDB un error en un trigger BEFORE cancela la sentencia y, dentro de una transacción InnoDB, deshace sus efectos: la operación es atómica).
2. Si usáramos AFTER, el préstamo ya estaría insertado cuando lanzáramos el error; aunque el SIGNAL también revierte la sentencia en InnoDB, BEFORE expresa mejor la intención de «portero» (gatekeeper) y evita trabajo innecesario.
3. La actualización estado='prestado' se hace sobre otra tabla (portatil), no sobre la fila NEW que se está insertando, por lo que es perfectamente legal en un trigger BEFORE (la restricción de no poder modificar NEW en AFTER, o de no tocar la propia tabla disparadora, no aplica aquí).

Conviene complementarlo con un trigger AFTER UPDATE de devolución (cuando fecha_dev_real pasa de NULL a una fecha) que devuelva el portátil a disponible.

Figura 3. Ciclo de estados del portátil: disponible↔prestado según préstamo/devolución, y transición a baja desde cualquiera de los dos estados activos.

Cuestión 5 — Mini-cuestión: estado «a mano» vs. vista derivada

Problema de integridad. El campo portatil.estado es un dato derivado (redundante): su valor «verdadero» puede deducirse de si existe un préstamo abierto (fecha_dev_real IS NULL). Si se mantiene manualmente, aparece el riesgo de anomalía de actualización / inconsistencia: basta olvidar actualizarlo al prestar o al devolver para que la BD afirme que un portátil está disponible cuando en realidad tiene un préstamo abierto (o viceversa). El dato almacenado y el dato real divergen, y dos «fuentes de verdad» (la columna y la tabla prestamo) se contradicen. Es exactamente la redundancia que la normalización trata de evitar.

Alternativa basada en una VISTA. En lugar de almacenar el estado «activo/prestado», se deriva en tiempo de consulta a partir de los préstamos abiertos, garantizando que siempre es coherente:

CREATE OR REPLACE VIEW v_portatil_estado AS
SELECT  po.num_serie,
        po.marca,
        po.modelo,
        CASE
            WHEN po.estado = 'baja' THEN 'baja'
            WHEN EXISTS (
                SELECT 1 FROM prestamo p
                WHERE p.num_serie = po.num_serie
                  AND p.fecha_dev_real IS NULL
            ) THEN 'prestado'
            ELSE 'disponible'
        END AS estado_calculado
FROM    portatil po;

Así, disponible/prestado deja de mantenerse a mano y se calcula siempre desde la única fuente de verdad (la tabla prestamo); solo baja (estado físico, no derivable de los préstamos) sigue requiriendo un marcado explícito. La vista no se puede «desincronizar», a costa de un pequeño coste de cálculo en cada consulta. En la práctica suele combinarse el trigger (para mantener la columna y poder indexarla/consultarla rápido) con esta vista de verificación que permite auditar inconsistencias.