Tema 17: Modelado lógico y físico de bases de datos. Arquitectura ANSI/SPARC

Diseño lógico relacional

El modelo relacional fue formulado por Edgar Frank Codd (IBM, 1970) en su artículo "A Relational Model of Data for Large Shared Data Banks". Es el fundamento teórico de las bases de datos relacionales y de su lenguaje asociado (el álgebra relacional y, después, SQL).

Niveles de abstracción

El estándar ANSI/SPARC define tres niveles de abstracción de una base de datos:

• Externo (de vistas): cómo ven los datos los distintos usuarios/aplicaciones.

• Conceptual (lógico): qué datos se almacenan y qué relaciones existen entre ellos, con independencia del soporte físico.

• Interno (físico): cómo se almacenan realmente los datos (ficheros, índices, bloques).

El nivel "sintáctico" NO existe como nivel de abstracción de bases de datos: es un distractor típico.

Fases del diseño: conceptual, lógico y físico

El diseño de una base de datos se aborda en tres etapas encadenadas:

1. Diseño conceptual: produce un esquema conceptual independiente del SGBD (habitualmente un modelo Entidad/Relación con entidades, atributos y relaciones).

2. Diseño lógico: su objetivo principal es transformar el esquema conceptual en un esquema lógico compatible con el modelo de datos del SGBD elegido (aquí, el relacional). El esquema lógico actúa como puente entre el esquema conceptual y el diseño físico, adaptando las estructuras de datos al modelo escogido.

3. Diseño físico: materializa el esquema lógico en estructuras concretas de almacenamiento y acceso.

El diseño conceptual y lógico tiene carácter iterativo: ambos procesos requieren refinamiento continuo, lo que contribuye a una comprensión más profunda del funcionamiento de la organización y de sus datos.

La relación: tuplas, grado y cardinalidad

En el modelo de Codd, una relación (tabla) es un conjunto de n-tuplas, donde cada n-tupla es una lista ordenada de valores atómicos. Una relación es, por tanto, un conjunto de tuplas. Dos medidas la describen:

• Grado: número de atributos (columnas).

• Cardinalidad: número de tuplas (filas).

Así, una entidad con 4 columnas y 7 filas tiene grado 4 y cardinalidad 7.

Claves: superclave, candidata, primaria y ajena

• Superclave: subconjunto de atributos del esquema R(A1,…,An) tal que no puede haber dos tuplas con la misma combinación de valores para dicho subconjunto (identifica unívocamente).

• Clave candidata: superclave mínima (sin atributos redundantes). Toda relación tiene al menos una clave candidata (en el peor caso, el conjunto de todos sus atributos); por tanto es FALSO afirmar que "una relación puede no tener clave candidata".

• Clave primaria: clave candidata elegida como identificador principal; conjunto de atributos que hacen único el acceso a cada ocurrencia de la entidad. Tanto la clave primaria como una clave candidata encajan con la definición de "atributos que identifican de forma única las filas".

• Clave ajena o foránea (foreign key): atributo(s) que referencian la clave primaria de otra relación. Las claves ajenas SÍ pueden ser nulas (salvo restricción explícita); afirmar que "nunca pueden ser nulas" es incorrecto.

Reglas de integridad

El modelo relacional impone dos reglas de integridad básicas:

• Integridad de entidad(es): ningún atributo que compone la clave primaria puede ser nulo, de modo que cada entidad sea única e identificable.

• Integridad referencial: el valor de una clave ajena, si no es nulo, debe coincidir con un valor existente de la clave primaria a la que referencia (o bien ser nulo). Garantiza que las relaciones entre tablas permanezcan sincronizadas durante inserciones, actualizaciones y borrados, evitando "filas hijas huérfanas". Si una clave externa apunta a una fila inexistente, se viola la integridad referencial; borrar una fila padre con hijos es un riesgo típico de violación.

Otras restricciones habituales: datos requeridos (campos NOT NULL que no pueden quedar vacíos) y restricciones de dominio.

Propiedades ACID de las transacciones

Las transacciones de un SGBD deben cumplir las propiedades ACID: Atomicidad, Consistencia, Aislamiento (Isolation) y Durabilidad/Persistencia.

Las 12 reglas de Codd

Codd definió 12 reglas (más la regla 0) para calificar un sistema como relacional. Entre las más preguntadas:

• Regla 2 — Acceso garantizado: todo dato es accesible mediante la combinación de nombre de tabla, valor de clave primaria y nombre de columna (implica clave primaria única en cada relación).

• Regla 5 — Sublenguaje de datos completo: el sistema debe soportar al menos un lenguaje de manipulación de datos con sintaxis bien definida (definición de datos, vistas, manipulación, integridad, autorizaciones y transacciones).

• Regla 7 — Inserción, actualización y borrado de alto nivel: el sistema debe permitir operar sobre conjuntos de tuplas (no fila a fila) tanto en consulta como en inserción/actualización/borrado.

• Regla 11 — Independencia de la distribución: que la base de datos se almacene de forma distribuida NO debe afectar a las aplicaciones. Por eso una "dependencia de la distribución" NO es una de las 12 reglas (es la inversión del enunciado real).

Reglas de transformación (paso al modelo lógico/físico)

Al pasar del modelo conceptual al lógico relacional se aplican reglas de transformación:

• Transformación de entidades en tablas y de atributos en columnas.

• Transformación de relaciones N:M: se crea una tabla intermedia (de intersección o de enlace) cuyas claves foráneas son las claves primarias de las entidades participantes.

• Transformación de relaciones de grado n (n-arias): dividir las relaciones de grado n en relaciones binarias.

• Transformación de entidades débiles.

• Transformación de atributos compuestos (un atributo que contiene otros).

• Transformación de atributos multivalorados: se llevan a una tabla separada.

La "transformación de funciones" NO es una regla de transformación (distractor).

Álgebra relacional

Las operaciones fundamentales (primitivas) del álgebra relacional son: selección (σ), proyección (π), unión (∪), diferencia de conjuntos (−) y producto cartesiano (×) —a veces se añade el renombrado—. Son fundamentales porque ninguna puede eliminarse sin perder poder expresivo. La intersección NO es fundamental: es una operación derivada (se obtiene a partir de la diferencia).

Modelo dimensional (Kimball)

En el modelo dimensional de Kimball (data warehousing), una dimensión outrigger es una tabla de dimensión enlazada desde otra tabla de dimensión (una dimensión que cuelga de otra), utilizada de forma limitada para normalizar atributos correlacionados.

Fuentes

• Codd, E. F. — A Relational Model of Data for Large Shared Data Banks, ACM: https://dl.acm.org/doi/10.1145/362384.362685

• Codd's 12 rules — Wikipedia: https://en.wikipedia.org/wiki/Codd%27s_12_rules

• Codd's 12 Rules (Rule 2, 5, 7) — OLAP.com: https://olap.com/learn-bi-olap/codds-paper/

• Relational algebra (operadores primitivos) — Wikipedia: https://en.wikipedia.org/wiki/Relational_algebra

• Kimball Group — Outrigger Dimension (Data Warehouse glossary): https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/

• ANSI/SPARC three-schema architecture — ISO/IEC: https://en.wikipedia.org/wiki/Three-schema_approach

Diseño físico de bases de datos

El diseño físico es la última de las tres grandes fases del diseño de una base de datos (conceptual → lógico → físico). Su propósito principal es implementar el esquema lógico en la memoria secundaria, definiendo las estructuras de almacenamiento y los métodos de acceso que permitirán al Sistema Gestor de Bases de Datos (SGBD) almacenar y recuperar los datos de forma eficiente. Mientras el modelo lógico describe qué datos se guardan y sus relaciones, el modelo físico describe cómo se guardan realmente sobre el hardware y el software concretos.

Modelo lógico frente a modelo físico

La diferencia esencial es la dependencia tecnológica: el modelo lógico es independiente de la tecnología, mientras que el modelo físico depende de la tecnología (del SGBD elegido, del sistema operativo, del almacenamiento). Por eso, antes de comenzar la etapa de diseño físico debe estar decidido el SGBD que se utilizará (Oracle, PostgreSQL, SQL Server, etc.), ya que sus tipos de datos, mecanismos de indexación, particionamiento y opciones de almacenamiento condicionan todas las decisiones posteriores.

El objetivo de obtener el modelo físico a partir del modelo lógico es definir la estructura de la base de datos a nivel de tablas y relaciones, traduciendo cada relación normalizada a las estructuras concretas del gestor.

Arquitectura ANSI/SPARC y niveles de abstracción

La arquitectura de referencia ANSI/SPARC define tres niveles de abstracción de una base de datos:

• Nivel externo (o de vistas): las distintas visiones parciales de los usuarios.

• Nivel conceptual: la descripción global de entidades, atributos, relaciones y restricciones.

• Nivel interno (o físico): cómo se almacenan realmente los datos (estructuras de ficheros, índices, páginas). El nivel interno es, por tanto, uno de los niveles de abstracción válidos.

De esta separación surge la independencia de datos:

• Independencia física: capacidad de modificar el esquema físico sin realizar cambios en el esquema conceptual (ni en los programas de aplicación). Los cambios en las estructuras de almacenamiento —reorganizar ficheros, añadir o quitar índices, reubicar tablas— no tienen efecto sobre el esquema lógico/conceptual, porque ambos niveles son independientes.

• Independencia lógica: capacidad de cambiar el esquema conceptual sin afectar a las vistas externas.

Componentes clave del diseño físico

En el diseño físico de una base de datos relacional se deben diseñar los componentes fundamentales:

1. El conjunto de relaciones (tablas) derivadas del modelo lógico.

2. Las estructuras de almacenamiento y los métodos de acceso (organización de ficheros, índices, clústeres).

3. El modelo de seguridad del sistema (usuarios, roles, privilegios).

Tareas en la obtención del modelo físico

Siguiendo metodologías como Métrica v3 (actividad DSI 6, Diseño Físico de Datos), las tareas típicas incluyen: transformar las relaciones lógicas en tablas físicas, seleccionar tipos de datos, definir claves e índices, analizar los caminos de acceso de cada módulo, aplicar desnormalización controlada cuando el rendimiento lo exige, y decidir el particionamiento. Crear diagramas de flujo de datos (DFD) NO es una tarea del modelo físico: el DFD pertenece a las fases de análisis/diseño de procesos, no al diseño físico de datos.

Entre las técnicas de optimización destacan:

• Índices: el uso de índices es la característica del modelo físico que permite optimizar el rendimiento de las consultas al acelerar la localización de las filas.

• Partición horizontal: consiste en dividir una tabla en varias tablas (o fragmentos) más pequeñas según ciertos criterios (por ejemplo, por rango de fechas o por región), repartiendo las filas. La partición vertical, en cambio, divide por columnas.

• Atributos derivados: un atributo derivado (calculable a partir de otros) puede almacenarse materializado o calcularse en tiempo real. Al pasar del modelo lógico al físico, el enfoque de calcular el valor en tiempo real evita redundancia y problemas de consistencia, a costa de recomputarlo en cada acceso.

Restricciones en el modelo físico

En la definición del modelo físico se implementan restricciones como las de clave primaria, clave ajena (integridad referencial), unicidad, not null y las restricciones de verificación (CHECK). Las restricciones de cardinalidad NO son un tipo de restricción propio del modelo físico: la cardinalidad es un concepto del modelado conceptual/lógico (entidad-relación) que expresa cuántas ocurrencias participan en una interrelación.

Transacciones y propiedades ACID

Una transacción es una unidad lógica de trabajo que el SGBD ejecuta como un todo indivisible. Toda transacción debe cumplir las propiedades ACID:

Así, las propiedades de las transacciones son atomicidad, consistencia, aislamiento y persistencia (durabilidad). La encapsulación NO es una propiedad de las transacciones: es un concepto de la programación orientada a objetos, ajeno al modelo ACID.

Resumen operativo

En síntesis, el diseño físico materializa el esquema lógico sobre un SGBD concreto —decidido de antemano—, produciendo tablas, estructuras de almacenamiento, índices, métodos de acceso y seguridad. Gracias a la independencia física, sus ajustes no alteran el nivel conceptual; y sobre este soporte las transacciones garantizan la integridad mediante las propiedades ACID.

Fuentes

• Ministerio de Hacienda y Función Pública (España). Metodología Métrica versión 3 — Diseño del Sistema de Información (DSI), Actividad DSI 6: Diseño Físico de Datos. https://administracionelectronica.gob.es/pae_Home/pae_Documentacion/pae_Metodolog/pae_Metrica_v3.html

• Métrica v3, Tarea DSI 6.1 «Diseño del Modelo Físico de Datos» (mirror docente). https://alarcos.esi.uclm.es/ipsw/metrica3/metrica_3/tasks/DSI_06_1_41DFD8E6.html

• ANSI/X3/SPARC Study Group on Data Base Management Systems — arquitectura de tres niveles (externo, conceptual, interno) e independencia de datos. https://en.wikipedia.org/wiki/ANSI-SPARC_Architecture

• ISO/IEC 9075 (SQL), propiedades ACID de las transacciones — ISO. https://www.iso.org/standard/76583.html