Del E/R al esquema físico

El paso del modelo conceptual al lógico relacional no es cuestión de criterio: hay un juego de reglas cerrado. La primera es la más simple: cada tipo de entidad se convierte en una relación, sus atributos en columnas y su identificador en la clave primaria.

Una relación 1:N se resuelve por propagación de clave: la clave primaria de la entidad del lado 1 se lleva a la relación del lado N, donde queda como clave ajena. No se crea ninguna tabla nueva. Si un curso agrupa muchos temas y cada tema pertenece a un solo curso, el identificador de curso viaja a la tabla de temas.

El sentido de la propagación es siempre el mismo, del lado 1 hacia el lado N, y nunca al revés. Llevar la clave del lado N al lado 1 obligaría a guardar varios valores en una sola columna, que es precisamente lo que la primera forma normal prohíbe.

Una relación 1:1 también se resuelve propagando clave, pero aquí hay elección: se puede llevar en cualquiera de los dos sentidos, o incluso fundir las dos entidades en una sola tabla. El criterio práctico es propagar hacia el lado cuya participación sea obligatoria, para no acabar con una columna llena de nulos. Esta regla no estaba en el material de partida y se añade porque el caso 1:1 aparece igual en el examen.

-- Relación 1:N entre curso y tema.
-- La clave del lado 1 (curso) se propaga al lado N (tema).

CREATE TABLE curso (
  id_curso   INTEGER      PRIMARY KEY,
  nombre     VARCHAR(80)  NOT NULL
);

CREATE TABLE tema (
  id_tema    INTEGER      PRIMARY KEY,
  titulo     VARCHAR(160) NOT NULL,
  id_curso   INTEGER      NOT NULL,
  CONSTRAINT fk_tema_curso
    FOREIGN KEY (id_curso) REFERENCES curso (id_curso)
);

Toda entidad se convierte en tabla. Una relación 1:N no crea tabla: propaga la clave del lado 1 al lado N, donde queda como clave ajena.

Una relación N:M no se puede resolver propagando clave, porque haría falta guardar varios valores en una columna por los dos lados. Se transforma en una relación nueva, llamada tabla intermedia o de asociación, cuya clave primaria es la combinación de las claves primarias de las dos entidades que enlaza. Cada una de esas dos mitades es además clave ajena hacia su tabla de origen.

Si la relación tenía atributos propios, esa tabla nueva es donde van. Ahí se ve por qué la regla es obligatoria y no una opción: no hay ningún otro sitio donde quepan.

-- Relación N:M entre alumno y curso, con un atributo propio
-- de la relación: la fecha de matriculación.

CREATE TABLE matricula (
  id_alumno    INTEGER NOT NULL,
  id_curso     INTEGER NOT NULL,
  fecha_alta   DATE    NOT NULL,
  PRIMARY KEY (id_alumno, id_curso),
  FOREIGN KEY (id_alumno) REFERENCES alumno (id_alumno),
  FOREIGN KEY (id_curso)  REFERENCES curso  (id_curso)
);

Las relaciones n-arias, con tres o más tipos de entidad participando, se tratan igual que las N:M y generalizan la regla: se crea una relación nueva y su clave primaria se forma con las claves primarias de todas las entidades participantes. Una relación ternaria entre alumno, subtema y pregunta fallada daría una tabla con una clave primaria de tres partes.

N:M y n-arias siempre generan tabla nueva, con clave primaria compuesta por las claves de las entidades que enlazan. Solo la 1:N y la 1:1 se resuelven propagando.

Una jerarquía de generalización admite tres transformaciones distintas, y elegir entre ellas es una decisión de diseño con consecuencias sobre el espacio y sobre el número de uniones que habrá que hacer al consultar.

La primera opción solo funciona bien cuando los subtipos tienen pocos atributos propios. La segunda solo es viable cuando la jerarquía es total y disjunta, porque si no, hay ejemplares que no caben en ninguna tabla o que habría que meter en dos. La tercera es la más fiel al modelo conceptual y la que soporta cualquier combinación de restricciones.

La entidad débil sigue una regla propia y muy concreta: se convierte en una tabla cuya clave primaria incluye la clave ajena que apunta a su entidad propietaria. Es decir, la columna que implanta esa relación identificadora no es una clave ajena cualquiera: forma parte de la clave primaria. Si además la entidad débil tenía un discriminante, ese atributo completa la clave.

-- Entidad débil: un intento solo tiene sentido dentro de un test.
-- La clave ajena hacia test forma parte de la clave primaria,
-- junto al discriminante (el número de intento del alumno).

CREATE TABLE intento (
  id_test        INTEGER NOT NULL,
  id_alumno      INTEGER NOT NULL,
  num_intento    INTEGER NOT NULL,   -- discriminante
  fecha          TIMESTAMP NOT NULL,
  aciertos       INTEGER NOT NULL,
  PRIMARY KEY (id_test, id_alumno, num_intento),
  FOREIGN KEY (id_test)   REFERENCES test   (id_test),
  FOREIGN KEY (id_alumno) REFERENCES alumno (id_alumno)
);

El esquema lógico ya dice qué relaciones hay, qué atributos tienen y cuáles son sus claves, pero todavía no es una base de datos. El diseño físico es el que traduce eso al lenguaje del gestor concreto y toma las decisiones que afectan al rendimiento.

• Elegir el tipo de dato de cada columna y su tamaño, que ya no es un dominio abstracto sino un tipo del gestor.
• Escribir las restricciones: clave primaria, unicidad, obligatoriedad, claves ajenas y comprobaciones de valor.
• Decidir los índices. Un índice acelera las búsquedas por una columna y penaliza las inserciones y modificaciones, así que se ponen donde compensa, no en todas partes.
• Decidir cómo se generan los identificadores: secuencias, columnas autonuméricas o identificadores generados por la aplicación.
• Decidir el almacenamiento cuando el volumen lo justifica: particionado de tablas, agrupaciones y ubicación física de los ficheros.

Es también en el diseño físico donde puede aparecer la desnormalización controlada: aceptar a sabiendas una redundancia para evitar uniones costosas en consultas muy frecuentes. Es una decisión de rendimiento y solo se justifica con medidas, nunca por comodidad al escribir consultas.

-- Decisiones típicas del esquema físico sobre el lógico ya cerrado.

CREATE SEQUENCE seq_intento START WITH 1 INCREMENT BY 1;

CREATE INDEX ix_intento_alumno_fecha
  ON intento (id_alumno, fecha);

ALTER TABLE intento
  ADD CONSTRAINT ck_intento_aciertos
  CHECK (aciertos >= 0);

El esquema lógico decide qué se guarda; el físico decide cómo se guarda y a qué velocidad se lee. Los índices, las secuencias y el particionado no aparecen hasta el físico.