Ingeniería inversa de base de datos
Copyright © 2003 Alejandro Narancio
Este artículo fue primero publicado en el Boletín para Desarrolladores de Software
 |
Índice
- Introducción
- Modelo Relacional
- Modelo entidad-relación
- Definiciones sobre ingeniería inversa de base de datos
- Análisis de la implementación
- Aplicación de ejemplo
 |
Hace algún tiempo se me encargó realizar una aplicación que aplicara ingeniería inversa a una base de datos. La idea principal de aplicar ingeniería inversa a una base de datos es poder obtener un modelo entidad–relación partir de una base de datos ya implementada.
En una primera instancia apenas conocía la definición de lo que se me encargaba. Por lo tanto comencé a buscar información al respecto (libros, revistas, Internet, etc.). El problema fue que lo único que encontré sobre el tema fueron definiciones (cosa que yo ya tenía), no existiendo ningún tipo de análisis acerca de como realizar esta tarea (ni siquiera en inglés). Por esta razón decidí escribir el análisis que realicé sobre el tema, dado que tal vez sirva aunque sea de guía inicial para aquellas personas que se enfrenten al mismo problema.
En este texto partiré de la base que el lector conoce los conceptos del modelo relacional y del modelo entidad–relación planteado por Chen, pero de todas formas haré una breve reseña sobre estos dos en las próximas secciones. Además recomiendo que el lector posea conocimientos de normalización de base de datos.
En mi caso, trabajé con Delphi y Oracle 8i Lite, pero de todas formas mi idea es que tras leer el análisis se pueda realizar en cualquier lenguaje y con cualquier base de datos.
En esta sección intentaré dar un panorama general acerca del modelo relacional. De todas formas recomiendo que consulten material específico sobre el tema, como ser el libro de C. J. Date o que busquen en Internet. Aquí les suministro algunas direcciones:
- http://www.programacion.com/tutorial.modrel.html
- http://www.javahispano.org/download/bd/tema3.pdf
- http://www3.uji.es/~mmarques/f47/apun/node43.html
- http://redii.cic.ipn.mx/librospro/dbdd/cap3/indice.html
El modelo relacional tiene tres partes principales, que tienen que ver con la estructura de datos, la integridad de los datos y la manipulación de datos. Cada una de las partes que nombramos anteriormente tiene su propia terminología especial.
En el caso de la estructura de datos, los términos estructurales más importantes son las relaciones. Una relación se encuentra compuesta por un conjunto de tuplas (cuerpo de la relación) y un conjunto de pares atributo–dominio (cabecera de la relación) y podría llegar a ser representada físicamente por una tabla. Además cada relación posee su clave primaria, una cardinalidad y un grado asociado a ella.
La cardinalidad de una relación se encuentra dada por el número de tuplas que contiene la relación (en el caso de que se represente la relación como una tabla, entonces una tupla es el equivalente a una fila de la tabla). La máxima cardinalidad posible para una relación es el producto cartesiano de todos los dominios que componen la cabecera de la relación.
El grado es el número de atributos que contiene una relación (en el caso de que nuevamente consideremos a la relación como una tabla, el grado sería el número de columnas de la misma).
Un dominio es un conjunto de valores atómicos que nos define el conjunto de valores válidos para un atributo.
Un dominio no es más que un tipo de datos (para abreviar, un tipo), y puede llegar a ser un tipo definido por el propio sistema (integer, char, etc.), o un tipo definido por el propio usuario.
Un tipo no es más que un conjunto de valores válidos. Por ejemplo, el tipo integer es el conjunto de todos los enteros posibles. Además, junto con la noción de un tipo dado está asociada la noción de los operadores válidos que se pueden aplicar legalmente a valores de ese tipo, es decir, se puede operar exclusivamente sobre valores de ese tipo por medio de operadores definidos para el mismo.
Las relaciones poseen ciertas propiedades que se desprenden directamente de su propia definición.
No existen tuplas duplicadas
Debido a que el cuerpo de la relación es un conjunto matemático (de tuplas); estos no admiten que existan valores duplicados.
Esta primera propiedad sirve para ilustrar el punto de que, en general, una relación y una tabla no son lo mismo, ya que una tabla en sí misma puede contener filas duplicadas, mientras que una relación no admite bajo ningún concepto tuplas duplicadas.
Las tuplas están en desorden, de arriba hacia abajo
Al igual que la propiedad anterior, ésta surge del hecho que el cuerpo de la relación es un conjunto matemático: en matemáticas, los conjuntos no están ordenados.
Por ejemplo, dada una relación con un cierto número de tuplas (por ejemplo, con 10 tuplas) no podemos referirnos a ellas por medio de un índice, como ser la quinta tupla, etc. Esto se debe a que no existe el concepto de direccionamiento posicional.
Como se mencionó en la propiedad anterior, esta segunda propiedad sirve también para ilustrar la idea de que una relación y una tabla no son lo mismo, ya que las filas de una tabla obviamente tienen un ordenamiento de arriba hacia abajo, mientras que las tuplas de una relación no lo tienen.
Los atributos están en desorden, de izquierda a derecha
Esta propiedad surge del hecho de que el encabezado de una relación también es un conjunto (de atributos). Por esta razón no existe algo como el "primer atributo", etc. Por lo tanto, siempre se hace referencia a los atributos por nombre y nunca por posición. Esto tiene como resultado la reducción de errores y una programación más clara.
Nuevamente esta propiedad marca diferencias entre una relación y una tabla, por la sencilla razón que en una tabla las columnas se encuentran ordenadas, y se pueden acceder a ellas a través de posiciones.
Cada tupla contiene exactamente un valor para cada atributo
Esta propiedad surge inmediatamente de la definición de una tupla: una tupla es un conjunto de n componentes o pares ordenados de la forma Ai:vi (i = 1,2, ..., n). Se dice que una relación que satisface esta propiedad se encuentra normalizada o -lo que es equivalente- que está en la primera forma normal.
El término integridad se refiere a exactitud o corrección de los datos en la base de datos.
Está restricción especifica los valores válidos para un tipo dado.
En esencia, una restricción de tipo es (o es equivalente de manera lógica a) una sola enumeración de los valores válidos del tipo. Por lo tanto, una restricción de tipo es básicamente sólo una especificación de los valores que conforman el tipo en cuestión.
A está restricción se la denomina también restricción de dominio, y nos dice que los dominios deben ser atómicos, o sea, deben ser una unidad mínima de información.
Especifica el valor válido para un atributo dado. Es sólo una declaración para que un atributo especifico sea de un tipo particular. Por esta razón, una restricción de atributo puede ser eliminada únicamente mediante la eliminación del propio atributo.
Ningún componente de una clave primaria de una relación puede ser nulo. El valor nulo es un valor indefinido, y la clave primaria de una relación identifica de forma única y mínima a una sola tupla de la relación. Por ende, si algún componente de la clave primaria pudiese ser nulo, entonces dicha clave primaria no sería mínima, porque nos estaría diciendo que ese atributo no es necesario para identificar a una tupla dada. Por está razón, si existieran componentes nulos en una clave primaria, esta no identificaría a ninguna tupla.
Con esta restricción nos referirnos a las relaciones existentes en el modelo relacional. Nos dice que no pueden existir valores de clave ajena (foránea) sin una concordancia, es decir, que dicha clave foránea debe contener un valor que exista en la relación a la cual hace referencia. Además esta restricción nos impone que una clave foránea es o toda no nula o toda nula (con esto se representa la totalidad y la parcialidad de la relación).
Este es un modelo conceptual de datos de alto nivel muy utilizado introducido por Peter Chen en 1976, que sirve para la representación de estructuras de información, no conteniendo un lenguaje para representación de manipulaciones de datos
Este modelo y sus variaciones se emplean a menudo en el diseño conceptual de aplicaciones de bases de datos. El objetivo principal del diseño conceptual es crear un esquema conceptual de alto nivel, independiente del DBMS (Data Base Managment System), partiendo de especificaciones de requerimientos que describan la realidad.
Al igual que otros problemas en la informática, dada la complejidad que se presenta en el diseño de una base de datos, éste se divide en subproblemas o etapas independientes que se pueden resolver por separado usando métodos y técnicas específicas. El diseño de base de datos se divide en tres grandes fases de diseño, con una fase posterior a estas de recolección y análisis de requerimientos.
Diseño conceptual. Una vez recabados y analizados todos los requerimientos, el siguiente paso es crear un esquema conceptual para la base de datos. Éste es una descripción concisa de los requerimientos de información de los usuarios, y contiene descripciones detalladas de los tipos de datos, las relaciones y las restricciones.
Diseño Lógico. Esta fase consiste en implementar una descripción de la estructura de la base de datos que puede procesar el software de DBMS, esto es conocido como esquema lógico.
Diseño Físico. Este es paso final durante el cual se especifican las estructuras de almacenamiento internas y la organización de los archivos de la base de datos. Hay una retroalimentación entre el diseño físico y el lógico, porque las decisiones tomadas durante el diseño físico para mejorar el rendimiento pueden afectar las estructuras del esquema lógico.
Una vez completo el diseño físico de la base de datos, los diseños lógico y físico se expresan mediante un lenguaje de definición de datos (DDL) del DBMS a usar. De esta forma la base de datos puede ser cargada y probada, y más aún, las aplicaciones que usan la base de datos se pueden diseñar y probar completamente.
El modelo ER describe los datos como entidades, relaciones y atributos. Una entidad se puede definir como un elemento del mundo real con existencia independiente. Los atributos son propiedades específicas que describen las entidades. Por último podemos definir las relaciones como conjunto de asociaciones entre entidades.
Junto con el modelo Entidad-Relación, Chen también presentó el concepto de Diagrama de Entidad–Relación (DER). Los Diagramas Entidad–Relación constituyen una técnica para representar la estructura lógica de una base de datos en forma de gráficos. Éstos proporcionan un medio sencillo y de fácil comprensión para comunicar las características sobresalientes de cualquier base de datos dada. A continuación se presentarán algunos conceptos básicos para elaborar un DER (de todas formas, los DER pueden ser representados de diversas maneras).
Las entidades se representarán por medio de rectángulos y los nombres de éstas serán en plural y empezarán siempre con mayúscula. Los atributos se escriben alrededor de la entidad, y la clave primaria va subrayada, como se puede ver en la figura.
 |
Las relaciones se representan por medio de rombos y se unen con las entidades participantes por medio de aristas.
Además, deben ser nombradas significativamente.
Sobre las aristas se indica la cardinalidad de la relación (1-1, 1-N, N-1, N-N).
En la unión entre la arista que une la relación con alguna de las entidades participantes de la relación se puede indicar la totalidad o parcialidad de la relación mediante la existencia o no de un punto (el punto indica totalidad mientras que su ausencia indica parcialidad).
Estos conceptos quedarán más claros luego de ver el ejemplo siguiente:
 |
Cuando decimos que la relación Asignaciones es N-1 queremos decir que "un empleado no puede estar asignado a más de un departamento" y que "un departamento puede contener muchos (N) empleados". Cuando decimos que la relación Asignaciones es total del lado de Empleados queremos decir que "todo empleado tiene asignado por lo menos un departamento".
Para resumir, podemos decir que la cardinalidad de una relación denota el máximo y la totalidad y parcialidad un mínimo.
Vale la pena aclarar que los valores de cardinalidad y totalidad no tienen porque ser N o 1, sino que pueden ser cualquier valor numérico (si se quiere indicar un mínimo específico de totalidad debe ir por debajo de la arista, sino se toma el valor por omisión 1).
Las relaciones también pueden tener atributos y se representan de la misma forma que los atributos de las entidades.
También tenemos un caso particular en el que si R es una relación que relaciona elementos de un conjunto de entidades E a elementos de ese mismo conjunto E, R se denomina relación recursiva. Para que éstas queden más claras, se distingue el rol que cada elemento del conjunto de entidades tiene en la relación agregándoseles un rótulo en cada una de las aristas, como se puede apreciar en el ejemplo.
 |
Consiste en considerar un conjunto de relaciones y sus conjuntos de entidades como un conjunto de entidades "agregando" toda la información, relacionando esa agregación a otro conjunto de entidades.
 |
Como se puede apreciar en la figura, la agregación se representa encerrando la relación y las entidades participantes en un rectángulo.
Existen muchos casos prácticos en que un conjunto de entidades representa elementos del mundo real que se subdividen en categorías con atributos parcialmente distintos.
Las categorizaciones pueden tener nombre y se representan mediante triángulos.
 |
Definiciones sobre ingeniería inversa de base de datos
La Ingeniería Inversa de Bases de Datos:
Es el conjunto de técnicas que permite la obtención de una representación conceptual de un esquema de base de datos a partir de su codificación.
Aplicaciones:
Sus aplicaciones son múltiples. Re-documentar, reconstruir y/o actualizar documentación perdida o inexistente de bases de datos, servir como pivote en un proceso de migración de datos, y ayudar en la exploración y extracción de datos en bases poco documentadas.
Ahora comenzaremos a realizar el análisis por el cual obtendremos -como ya hemos dicho más de una vez- el modelo conceptual de una base de datos a partir de un modelo físico.
Como ya he dicho anteriormente, yo implementé esta aplicación en Delphi, con un Oracle Server 8i Lite, por lo tanto los ejemplos que realizaré serán basándome en que utilizo dichos productos. De todas formas, el análisis es el mismo a seguir independientemente del lenguaje o base de datos que utilicemos.
Obtener información de la estructura de la base de datos
Lo primero que debemos hacer es obtener toda la información posible de la estructura de la base de datos (no de los datos que contiene),es decir, nombre de las tablas, atributos de las tablas, etc.
Dicha información se encuentra almacenada en el catálogo de la base de datos (el cual se consulta fácilmente utilizando SQL). La información que obtendremos a partir del catálogo la debemos almacenar en algún lado (yo en particular me creé una serie de clases que permitían almacenar toda la información y además a dichas clases les agregue cierta funcionalidad que me permitía manejar fácilmente la información almacenada en ellas).
Lo primero que haremos será obtener todas las tablas que componen la base de datos. Para realizar esto debemos efectuar una consulta SQL. En dicha consulta no sólo obtendremos los nombres de las tablas, sino que también obtendremos los atributos que componen dicha tabla con sus características más generales (tipo de dato, y si admite valores nulos), es decir, que aún no sabemos que atributos componen la clave primaria o alguna clave foránea.
La consulta SQL que utilice es la siguiente:
SELECT at.table_name, attc.column_name, attc._data_type, attc.nullable
FROM all_tables at, all_tab_columns attc
WHERE at.table_name = attc.table_nameEl resultado de dicha consulta será el siguiente: por cada fila habrán cuatro columnas. Las columnas significan lo siguiente: nombre de la tabla, nombre del atributo, tipo de dato del atributo y si el atributo puede ser nulo. Por lo tanto, cada tabla tendrá tantas filas en el resultado de la consulta como atributos posea.
Una vez realizada esta consulta procederemos a guardarla en nuestras estructuras de almacenamiento. Una vez hecho esto estamos en condiciones de analizar cuales atributos de las tablas corresponden a la clave primaria, cuales son claves foráneas y cuales son claves únicas (que en el modelo de normalización serían las claves candidatas).
Lo primero que haremos será obtener aquellos atributos que componen la clave primaria de una tabla dada. La siguiente consulta se debería realizar para cada tabla existente en la base de datos, cambiando en la siguiente consulta NombreTabla por el nombre de la tabla que estamos consultando (a partir de este momento, cada vez que se coloque NombreTabla se entenderá que es la tabla que nos encontramos analizando). Aquí va la consulta SQL realizada:
SELECT column_name
FROM all_constraints ac, all_cons_columns acc
WHERE ac.table_name = 'NombreTabla'
AND ac.constraint_type = 'P'
AND ac.constraint_name = acc.constraint_name
ORDER BY acc.position;El resultado de esta consulta es una fila para cada atributo que forma parte de la clave primaria. Dichos atributos se desplegarán en orden ascendente según su posición, para así poder ingresarlos en el orden por el cual fueron definidos.
A está altura ya tenemos cargados los nombre de las tablas y sus atributos, con sus respectivos tipo de datos, e identificados los atributos que conforman a la clave primaria.
A continuación obtendremos los atributos que forman las claves foráneas de una tabla, y las tablas a las cuales hace referencia dicha clave foránea perteneciente a una determinada tabla, que llamaremos nuevamente NombreTabla.
SELECT ac.constraint_name, column_name, r_constraint_name
FROM all_constraints ac, all_cons_columns acc
WHERE ac.table_name = 'NombreTabla'
AND ac.constraint_type = 'R'
AND ac.constraint_name = acc.constraint_name
ORDER BY acc.position;Como resultado obtenemos una fila por cada atributo que compone a una clave foránea. Cada constraint (clave foránea en este caso) tendrá tantas filas como atributos los compongan. Por cada columna tenemos la siguiente información en el siguiente orden: nombre del constraint, nombre del atributo y nombre del constraint al cual hace referencia.
A partir de los constraints a los cuales se hacen referencia, se puede obtener fácilmente a que tabla pertenecen por medio de la siguiente consulta:
SELECT table_name
FROM all_constraints
WHERE constraint_name = 'NombreConstraint'Con la consulta anterior obtuvimos a que tabla pertenece el constraint NombeConstraint y por lo tanto, si una clave foránea hace referencia al constraint NombreConstraint, entonces ahora sabemos a que tabla hace referencia dicha clave foránea.
Finalmente, para la carga de datos nos queda únicamente averiguar cuales son los atributos que componen a las claves únicas. Para esto realizamos la siguiente consulta:
SELECT ac.constraint_name, column_name
FROM all_constraints ac, all_cons_columns acc
WHERE ac.table_name = 'NombreTabla'
AND ac.constraint_type = 'U'
AND ac.constraint_name = acc.constraint_name
ORDER BY acc.position;La consulta anterior nos devuelve todas las claves únicas que existen en una tabla. Cada clave única tendrá tantas filas en el resultado de la consulta como atributos la compongan. El significado de las columnas es el siguiente: nombre del constraint (o sea, de la clave única en este caso) y nombre del atributo.
A continuación presentaré como determinar que representación conceptual tiene una tabla dada. Es decir, por ejemplo, una tabla puede ser considerada una entidad, una relación binaria, una relación ternaria, una categorización, etc.
El análisis puede ser encarado de dos formas completamente distintas. Una es por medio de consultas SQL, es decir, que se realice una consulta SQL y en función de los resultados determinar por ejemplo si una tabla corresponde a una entidad o a una relación, o por ejemplo determinar si una tabla corresponde a una relación binaria o ternaria, etc.
El problema con esto es que nuestra aplicación será dependiente de la base de datos que estemos utilizando, y por ende debemos escribir todas las consultas SQL para cada uno de los motores de base de datos a los que nuestra aplicación les realice ingeniería inversa.
La otra posibilidad es utilizar la información que tenemos almacenada en nuestras estructuras. Con esta información es posible determinar casi cualquier caso (digo casi porque como veremos más adelante existen algunas circunstancias en las que no podremos determinar a que modelo conceptual corresponde).
Las únicas consultas que deberemos escribir para cada motor de base de datos son aquellas para extraer la información sobre las estructuras, pero esto no es tan complicado debido a que apenas son cinco consultas. En mi análisis utilizaré la segunda opción debido a que obviamente es mucho más flexible que la primera.
Para el análisis, al igual que hicimos a la hora de obtener la información de la estructura de la base de datos, nos crearemos un conjunto de estructuras de almacenamiento en las cuales iremos almacenando los datos que obtendremos del análisis. Yo en particular me creé las siguientes estructuras, nuevamente utilizando clases: entidades, relaciones, agregaciones y categorizaciones. En entidades obviamente almaceno aquellas tablas en que sean entidades, en relaciones almaceno el nombre de la relación, cardinalidades, totalidad y parcialidad y todas las tablas que relaciona, en agregación almaceno el nombre de la agregación junto con todas las estructuras que relaciona y finalmente en categorizaciones almaceno la tabla que corresponde a la categorización y su madre.
En general, el siguiente análisis debe ser realizado por todas las tablas. Como guía, a continuación presento un diagrama de flujo, que es el que se debe seguir a la hora de analizar una tabla. Es decir, a una tabla dada se le realizarán ciertas pruebas y en función de los resultados de dichas pruebas decidiremos que 'camino' del diagrama de flujo seguir.
A continuación presento el diagrama de árbol que me ha servido de ayuda a la hora de realizar el análisis.
 |
Determinar si una tabla corresponde a una entidad o a una relación
Lo primero que debemos realizar en el proceso del análisis es determinar si el modelo conceptual de una tabla corresponde a una entidad o a una relación.
Para realizar dicho análisis, intentaremos probar distintos casos, mediante los cuales podremos ir descartando las diferentes opciones que poseemos.
Determinar si corresponde a una entidad aislada
Tal vez el término 'aislada' no es el más adecuado, debido a que en un modelo relacional bien hecho, muy difícilmente existan tablas completatamente aisladas. En este análisis nos referimos a entidades aisladas cuando una tabla no posee claves foráneas a otras tablas. Mediante el análisis de esta tabla no podemos saber a priori las relaciones en las que participa dicha tabla, pero sí se podrá determinar más adelante en nuestro análisis. Por lo tanto no es una entidad aislada, sino que más bien es una potencial entidad aislada, pero no lo sabremos hasta finalizar el análisis de todas las tablas.
Determinar si una tabla corresponde a un entidad aislada es muy sencillo, lo único que debemos hacer es fijarnos si dicha tabla posee claves foráneas. En el caso de que posea estamos seguros de que no es una entidad aislada y podemos proseguir con el análisis de la tabla, pero si se diera el caso que no posee ninguna clave foránea, entonces estamos seguros que corresponde a una entidad aislada, por lo que podemos agregar dicha tabla a nuestra estructura de almacenamiento entidades y pasar a analizar la siguiente tabla.
Determinar si corresponde a una categorización
Las categorizaciones se caracterizan por lo siguiente: toda la clave primaria de una tabla 'hija' forma una (y solo una) clave foránea a la tabla 'madre'.
Si hemos llegado a este punto sabemos que la tabla posee por lo menos una clave foránea (por dicha razón no es considerada una entidad aislada). Lo primero que debemos hacer es fijarnos si los atributos que componen a la clave primaria de la tabla componen a su vez una clave foránea. Con esto quiero decir que los atributos que componen la clave primaria NO componen a más de una clave foránea (la razón por la cual repito tanto este punto, es porque esto es fundamental a la hora de hablar de categorizaciones). En el caso que los atributos que componen la clave primaria no compongan ninguna clave foránea, o que compongan a más de una clave foránea, estamos seguros que no nos encontramos frente a una categorización (es más, como veremos más adelante, si los atributos que componen la clave primaria de la tabla, componen a su vez a más de una clave foránea, entonces estamos seguros que nos encontramos en un caso en donde la representación conceptual de la tabla es una relación y no una entidad).
A continuación plantearemos un ejemplo sencillo de categorización mediante el uso de tres tablas: empleados, gerentes y secretarias. La estructura física de las tres tablas es la siguiente:
| Empleados | Gerentes | Secretarias |
| Número_Empleado (PK) | Número_Empleado (PKFK) | Número_Empleado(PKFK) |
El atributo Número_Empleado, tanto en la tabla Gerentes como en la tabla Secretarias, forma una clave foránea a la tabla Empleados.
Debido a que tanto la tabla Gerentes como la tabla Secretarias no poseen más claves foráneas, deducimos instantáneamente que no es una tabla que represente una relación, sino que existe una categorización. Por lo tanto, la representación sería la siguiente:
 |
Como dijimos, este es un caso extremadamente sencillo, pero en el análisis de las categorizaciones puede existir un caso particular en el cual no sepamos que quiso representar quien haya diseñado la base de datos.
Imaginemos el siguiente caso:
| Empleados | Gerentes | Secretarias |
| Número_Empleado (PK) | Número_Empleado (PKFK) | Número_Empleado(PKFK) Número_Computadora(FK) |
Hemos agregado un nuevo atributo a la tabla Secretarias. Dicho atributo (Número_Computadora) es una clave foránea a una tabla denominada Computadoras. Si se nos diera este caso debemos realizar un análisis más profundo para poder determinar si la tabla Secretarias pertenece a una categorización, debido a que la representación conceptual de esta tabla podría ser cualquiera de las siguientes:
Caso 1:
 |
Caso 2:
 |
Como notará, son representaciones completamente diferentes. En la primera Secretarias forma parte de una categorización, y en la segunda Secretarias es considerada una relación entre Empleados y Computadoras, con cardinalidades N–1 o 1–1.
En ocasiones es posible poder diferenciar entre los dos casos. La única forma de hacerlo es examinando el atributo Número_Computadora que pertenece a la tabla Secretarias y que hace referencia a la tabla Computadoras: si dicho atributo admite valores nulos, estamos completamente seguros que nos encontramos en el primer caso y no en el segundo. Esto es debido a que si Secretarias fuese una tabla que representa una relación entre Empleados y Computadoras, el atributo Número_Computadora NO podría aceptar valores nulos, debido a que por definición, las relaciones binarias son pares ordenados.
Si se nos plantea el caso de que el atributo Número_Computadora perteneciente a la tabla Secretarias no admite valores nulos, es imposible diferenciar entre los dos casos que planteamos anteriormente, por lo tanto debemos adoptar criterios para poder diferenciar entre ellos (por ejemplo, valores por omisión).
Una vez terminada esta parte del análisis sabemos si la tabla pertenece a una categorización o no, si perteneciera a una categorización la almaceno en mis estructuras de almacenamiento, y analizo el resto de las claves foráneas que posee la tabla como si se tratase de una tabla que representa a una entidad referente.
Determinar si corresponde a una entidad referente
Con entidad referente quiero decir, que es una tabla que hace referencia a otras tablas (son las clásicas relaciones 1–N o 1–1).
Si llegamos a este punto sabemos que no nos encontramos frente a una tabla que representa a una entidad aislada (es decir, que posee por lo menos una clave foránea) y que tampoco corresponde a una categorización (por ende los atributos que componen la clave primaria de la tabla no componen ninguna clave foránea). Por lo tanto, ya estamos seguros que esta tabla es una tabla referente (es decir, que hace referencias a otras tablas), dado que tampoco puede representar una relación debido a que en una tabla que represente una relación los atributos que forman la clave primaria de la tabla deben formar también al menos una clave foránea.
Sabemos que cada clave foránea que posea la tabla representará una relación binaria (debido a que es el único tipo de relación que puede representarse sin utilizar una tabla) entre la tabla que nos encontramos analizando y la tabla a la cual hace referencia la clave foránea. Además sabemos que la cardinalidad de dicha relación es 1–1, o bien 1–N, debido a que si fuese N–N se debería haber representado por medio de una tabla. Finalmente también sabemos que la cardinalidad correspondiente a la tabla que nos encontramos analizando es 1.
A continuación aclararé a que me refiero con referencia 1–1 cruzada.
Nos encontramos en el caso que una tabla que representa una entidad hace referencia a otra (por lo tanto deducimos que la cardinalidad para la tabla que nos encontramos analizando es 1). Ahora, si se diera el caso de que la tabla a la se hace referencia también posee una referencia a la tabla que nos encontramos analizando, entonces nos encontramos en el caso de una relación 1–1 cruzada.
Como es lógico, para resolver este caso (es decir, para decidir si nos encontramos frente a está situación) debemos analizar por un momento la tabla a la cual hace referencia la tabla que nos encontramos analizando: en el caso que la tabla a la cual se hace referencia posea una clave foránea hacia la tabla que nos encontramos analizando, entonces podemos deducir con plena seguridad que nos encontramos en frente de una relación 1–1 cruzada.
Este es en el único caso en el cual podremos deducir la totalidad y parcialidad en ambos lados de la relación. Esto lo haremos de la siguiente forma: en el caso de que los atributos que compongan la clave foránea admita valores nulos quiere decir que es parcial, de lo contrario es total (este análisis se debe realizar en ambas tablas, es decir, en la que nos encontramos analizando y en la que se hace referencia).
A continuación presentaré un ejemplo de una relación 1–1 cruzada. Imagínese el caso hipotético en el cual en una empresa un funcionario puede utilizar una sola computadora, y una computadora puede estar asignada a solamente un usuario.
| Funcionarios | Computadoras |
| Número_Funcionario (PK) Computadora_Asignada (FK) |
Número_Computadora (PK) Funcionario_Asignado (FK) |
En el caso anterior el atributo Computadora_Asignada en la tabla Funcionarios hace referencia a la tabla Computadoras, y el atributo Funcionario_Asignado en el tabla Computadoras hace referencia a la tabla Funcionarios. Aquí nos encontramos en un caso claro de un relación 1–1 cruzada entre las entidades Funcionarios y Computadoras.
Ahora podría darse el caso en el cual se quisiera representar una relación 1–1, pero no utilizando una referencia cruzada, sino que utilizando una referencia simple.
Ahora imagínese el caso que planteamos en la sección anterior, pero con una pequeña variación, que planteamos a continuación:
| Funcionarios | Computadoras |
| Número_Funcionario (PK) Computadora_Asignada (FK) |
Número_Computadora (PK) |
Como se habrá dado cuenta, se eliminó la referencia existente Computadoras–Funcionarios. Por lo tanto se eliminó también la referencia cruzada.
En el caso que quién diseñó la estructura de la base de datos haya querido representar una relación 1–1 no cruzada entre las tablas Funcionarios y Computadoras, debió obligatoriamente (para que no se confundiera con una relación 1–N) definir a los atributos que componen la clave foránea de la relación (en este caso, Computadora_Asignada) como únicos. Esto nos indica que un número de computadora puede aparecer solamente una vez en la tabla Funcionarios, representando de esta manera una relación 1–1.
En este último caso sólo podemos obtener la totalidad y parcialidad del lado de la entidad Funcionarios: en el caso que los atributos que componen la clave foránea admitan valores nulos será parcial y de lo contrario será total.
El análisis de este caso es idéntico al que realizamos en la sección anterior (es decir, en Referencia 1–1), con la salvedad que los atributos que componen la clave foránea no están declarados como únicos.
En este caso, la totalidad y parcialidad de la relación se analiza de la misma forma que el caso anterior.
Con este último caso (referencia 1–N), concluimos el análisis de las entidades. Si se diera el caso que una tabla escapa a todos estos casos, entonces nos encontramos con una tabla que representa una relación.
Análisis de una tabla que representa una relación
Si llegamos a este punto estamos completamente seguros que la tabla que nos encontramos analizando corresponde a una relación, debido a que no es una entidad aislada, no es una entidad referente y tampoco es una categorización. Desde mi punto de vista, el análisis de una relación fue sin duda el más complejo debido a la cantidad de casos diferentes que existen (recuerde que en teoría una tabla podría representar una relación de N entidades, por lo tanto el número de tablas que podría llegar a relacionar es variable e infinito).
En todos los casos en los cuales una tabla representa una relación nos es imposible determinar las totalidades y parcialidades de la relación.
Si probamos que la tabla es una relación colocaremos a esta junto con todos sus datos en nuestras estructuras de almacenamiento de relaciones (por ejemplo, nombre de la relación, tablas que relaciona, cardinalidad, etc.).
Análisis de una relación binaria
Comenzaremos nuestro análisis por un caso particular de relación, la llamada "relación binaria". Este caso se podría tratar junto con las relaciones n–arias (dado que es un caso especial de éstas), pero es tan común ver este tipo de relación que decidí tratarlo especialmente.
En este tipo de relación es imposible que se de una agregación. En realidad, imposible no es, pero no es demasiado lógico hacerlo. Por dicha razón pueden haber únicamente dos claves foráneas, ni más ni menos.
Para explicar este caso plantearemos la siguiente situación: dada las entidades A y B que se relacionan mediante una relación con cardinalidad 1–1, tenemos que dado un elemento de A sólo existe un elemento de B y dado un elemento de B sólo existe un elemento de A. Ahora, para representar dicha situación mediante una tabla sólo existe una forma, y es la siguiente: una de las foráneas debe ser obligatoriamente la clave primaria (digo una porque recordemos que la clave primaria determina de forma única y mínima a cualquier tupla de la relación, y debido a que queremos representar una relación 1–1), con eso representaríamos una de las cardinalidades 1 (por ejemplo, la de A), pero aún nos falta representar la segunda cardinalidad 1 (siguiendo con el ejemplo la de B). Para realizar esto último debemos hacer uso de las claves candidatas, es decir, debemos hacer que la segunda clave foránea sea a su vez clave única (con esto representaríamos que B también posee clave única).
A continuación plantearé un ejemplo para intentar clarificar este punto.
| Vehículos | Matrículas_Vehículos | Matrículas |
| Número_Vehículo(PK) Número_Matrícula(FK) |
Número_Vehículo(PKFK) | Número_Matrícula(PK) |
Obviamente la tabla Matrículas_Vehículos intenta representar una relación entre las entidades Vehículos y Matrículas. Como vemos, Número_Vehículo es una clave foránea y a su vez es clave primaria de la tabla, por lo que deducimos que la cardinalidad de Vehículos es 1. Ahora, si queremos representar una relación binaria 1–1 debemos hacer que los atributos que componen a la otra clave foránea (en este caso Número_Matrícula) además de foráneos sean únicos en la tabla. Con esto último representaríamos que Matrículas también posee cardinalidad 1 en la relación.
A continuación presento un conjunto de tuplas para clarificar la necesidad de poseer la clave única.
| Vehículos | Matrículas_Vehículos | Matrículas | Válido |
| 8946 | Num_Veh: 8946 Num_Mat: SAB 555 |
SAB 555 | Sí |
| 8946 | Num_Veh: 8946 Num_Mat: SAK 430 |
SAK 430 | No |
| 1388 | Num_Veh: 1388 Num_Mat: SAK 430 |
SAK 430 | No |
Como se ve en el ejemplo de tuplas anterior, existe una necesidad de especificar el atributo Número_Matrícula como único.
En teoría deberíamos especificar las dos claves foráneas como únicas, pero debido a que la definición de clave primaria es que es única y no nula, queda implícito que si una clave foránea debe ser a su vez clave primaria, entonces dicha clave foránea también es única. Este comentario es válido para las próximas secciones.
En este caso, y al igual que en el anterior, no es lógico pensar que se pueda llegar a formar una agregación con esta relación, por lo tanto eliminamos la posibilidad de una agregación. También al igual que en el caso anterior, en este tipo de relación solo poseemos dos claves foráneas (por la precisa razón que no pueden existir agregaciones).
Para esta situación, los atributos que componen la clave foránea correspondiente a la entidad que posee cardinalidad N deben formar a su vez la clave primaria de la tabla. A diferencia de el caso anterior, los atributos que forman la clave foránea correspondiente a la otra entidad NO pueden ser declarados como únicos.
A diferencia de los casos anteriores, este tipo de relación sí puede formar agregaciones, y debemos hacer ciertas consideraciones antes de comenzar su análisis.
A diferencia de los casos anteriores, para representar este tipo de relación siempre se debe utilizar una tabla, y los atributos que compongan las claves foráneas correspondientes a las dos tablas que relaciona deben formar a su vez la clave primaria de la tabla.
En el caso que la clave primaria este formada por una sola clave foránea, y que a su vez no todos los atributos de dicha clave foránea formen a la clave primaria, podemos considerar que se quiere representar a una entidad no representada (es decir, que dicha entidad existe en el modelo conceptual, pero no en el físico y lógico).
Recordemos que este tipo de relación puede formar una agregación, por lo tanto puede tener hasta N claves foráneas. Por eso la clave primaria debe estar formada como máximo por dos claves foráneas.
A continuación presentaré un caso sencillo de este tipo de relación.
| Alumnos | Asignaturas_Alumnos | Asignaturas |
| Número_Alumno(PK) Número_Asignatura(PKFK) |
Número_Alumno(PKFK) | Número_Asignatura(PK) |
Como vemos en el ejemplo anterior, la clave primaria de la tabla Asignaturas_Alumnos se encuentra formada por dos claves foráneas.
Debido a que la tabla Asignaturas_Alumnos no posee ninguna clave foránea a excepción de las que componen a la clave primaria, deducimos rápidamente que nos encontramos frente a una relación binaria N–N.
Por lo tanto, la representación conceptual de las tablas anteriores es la siguiente:
 |
El caso de ejemplo que planteamos anteriormente ilustra un caso típico, en el cual podemos deducir sin ambigüedades la cardinalidad y tipo de relación existente, pero imagínese el siguiente caso:
| Alumnos | Asignaturas_Alumnos |
| Número_Alumno(PK) | Número_Alumno (PKFK) Número_Asignatura (PKFK) Número_Semestre (FK) |
| Asignaturas | Semestres |
| Número_Asignatura (PK) | Número_Semestre (PK) |
La tabla Asignaturas_Alumnos posee las siguientes características: la clave primaria de la tabla se compone por dos claves foráneas, pero además posee una clave foránea que no compone a la primaria. Es obvio que nos encontramos frente a una tabla que representa una relación, pero el problema es determinar el tipo de relación existente. Si nos enfrentamos a un caso similar a este, se nos pueden plantear dos posibilidades que mostramos a continuación:
Caso 1:
 |
Caso 2:
 |
Como notará, existe una gran diferencia entre las dos posibilidades, debido a que la primera corresponde a una relación binaria formando una agregación, pero en el segundo caso la relación es ternaria.
Está situación se plantea debido a que si una relación forma una agregación que se relaciona con otra entidad, si dicha relación (entre la agregación y la entidad) posee cardinalidades 1–1 o N–1, existe la posibilidad de que no se represente la relación por medio de una tabla.
Para poder resolver este problema sólo tenemos dos posibilidades. La primera es examinando los atributos que forman la clave foránea que no compone la clave primaria de la tabla que representa la relación y en el caso de que dichos atributos admitan valores nulos, entonces deducimos directamente que nos encontramos frente al caso de una agregación, debido a que si fuese una relación ternaria no debería admitir valores nulos. Si la primer opción falla, tenemos una ultima opción y es intentar probar una relación 1–1 cruzada entre la relación y la entidad (en nuestro ejemplo entre la tabla Asignaturas_Alumnos y Semestres) en el caso de que probemos que existe una relación 1–1 cruzada podemos decir con total seguridad que nos encontramos frente a una agregación.
Si las dos pruebas que explicamos anteriormente fallan, entonces no tenemos forma de determinar frente a que caso nos encontramos.
Análisis de una relación n–aria
Si llegamos a este punto, sabemos con certeza de que la tabla representa una relación, y que a su vez esta relación no es binaria. Por ende, nos queda sólo determinar si es o no una agregación, y en el caso que no lo sea analizar las características de la relación (cardinalidad, entidades que relaciona, etc.).
Para realizar este análisis consideraremos que una relación n–aria es una relación que relaciona N entidades, siendo N un número mayor que dos (esto lo haremos porque las binarias las analizamos en la sección anterior, pero no se debe perder de vista que una relación binaria es un caso particular de una relación n–aria). A pesar de este hecho, existen tres posibilidades bien diferenciadas en una relación n–aria, vinculadas con sus cardinalidades: la primera es que todas sus cardinalidades sean uno, la segunda es que todas sus cardinalidades sean N y finalmente que sus cardinalidades sean una mezcla entre unos y enes.
Relación n–aria con todas sus cardinalidades N
Si una tabla representa una relación n–aria con todas sus cardinalidades N, entonces sabemos con certeza de que dicha tabla no posee claves únicas para representar su cardinalidad (debido a que no necesita esto).
Distinguir este tipo de relación es sumamente fácil, debido a que para representar esta cardinalidad todas las claves foráneas que forman la relación deben formar a su vez la clave primaria de la tabla.
Ahora puede darse el caso de que se quiera representar una relación entre varias entidades, en donde una de estas entidades no se represente tanto en el modelo lógico como en el físico. No tenemos duda que es una relación n–aria con todas sus cardinalidades N, debido a que no posee claves únicas y todas las claves foráneas forman la clave primaria. Pero no todos los atributos que componen a la clave primaria son foráneos, por lo tanto deducimos que existen entidades no representadas en el modelo físico. Aquí se evaluarán dichos atributos según los criterios que nosotros impongamos (por ejemplo, cada atributo es una entidad no representada, preguntar al usuario, etc.).
A continuación planteo un ejemplo, de una relación n–aria con entidades no representadas:
| Tipos_de_Movimientos | Conformes | Tipos_Conformes |
| Número_Tipo (PK) | Número_Conforme (PK) | Número_Tipo (PKFK) Número_Conforme (PKFK) Fecha (PK) |
En las tablas anteriores, notamos que en la tabla Tipos_Conformes el atributo Fecha forma parte de la clave primaria de la tabla, pero no compone ninguna clave foránea, por lo tanto deducimos que es una entidad no representada (no sería lógico tener en el modelo físico una tabla con todas las fechas posibles; en cambio, en el modelo conceptual sí es útil y muchas veces necesario).
La representación conceptual de las tabla anteriormente expuestas es la siguiente:
 |
Los casos planteados anteriormente son los más sencillos a la hora del análisis, pero como siempre se puede complicar un poquito más. Imagínese el caso de una relación ternaria con todas sus cardinalidades N que forma una agregación, que se relaciona a su vez con otra entidad mediante una cardinalidad 1–1 o N–1. A continuación planteo un ejemplo de esta situación:
| Tablas | Usuarios | Permisos |
| Número_Tabla (PK) | Número_Usuario(PK) | Número_Permiso (PK) |
| Tablas_Usuarios_Permisos | Atributos |
| Número_Tabla (PKFK) Número_Usuario (PKFK) Número_Permiso (PKFK) Número_Atributo (FK) |
Número_Atributo (PK) |
Las tablas anteriores poseen dos representaciones conceptuales totalmente válidas. A continuación expongo la primer opción:
 |
O también podría interpretarse de la siguiente forma:
 |
Como vemos en este caso, las dos opciones son diferentes representa. ciones validas para el mismo conjunto de tablas, pero que sean las dos válidas no quiere decir que sea lo que realmente se quiso representar (o sea, no es lo mismo decir que cuatro entidades se relacionan mediante una agregación entre tres de las entidades, y una relación simple con la cuarta entidad, que decir que se relacionan mediante una relación cuaternaria).
Existen dos posibilidades para solucionar este problema. Una es si los atributos que componen a la clave foránea que representa a la entidad cuyos atributos no componen a la clave primaria de la tabla admiten valores nulos, pues entonces estamos en el primer caso (es decir, en el caso de la agregación con una relación simple). Esto es así porque si fuese una relación entre todas las entidades ninguna clave foránea puede aceptar valores nulos.
La segunda opción es estudiar si existe una relación 1–1 cruzada entre la clave foránea que no compone la clave primaria y la entidad que esta relaciona. En el caso de existir una relación 1–1 cruzada entonces estamos completamente seguros que nos encontramos frente a el primer caso.
Si no se puede llegar a probar ninguna de las dos opciones antes planteadas, entonces no podemos deducir nada a partir de los datos de la estructura de la base de datos.
Relación n–aria con todas sus cardinalidades 1
Para determinar si una relación pertenece a este tipo debemos estudiar sus claves candidatas (es decir, sus claves únicas). En este caso sabemos que toda clave foránea que pertenezca a una entidad que esta tabla relaciona se debe encontrar ya sea en la clave primaria o en alguna de sus claves únicas.
A continuación planteamos un ejemplo de este tipo de relación por medio de una relación ternaria:
| Matrículas | Vehículos |
| Número_Matrícula (PK) | Número_Matrícula (PK) |
| Departamentos | Matrículas_Vehículos_Departamentos |
| Número_Departamento (PK) | Número_Matrícula (PKFK) Número_Vehículo (PKFK) Número_Departamento (FK) |
En este caso existirán dos claves únicas además de la clave primaria. Dichas claves únicas son (Número_Matrícula, Número_Departamento) y (Número_Vehículo, Número_Departamento).
Entonces en este caso deducimos que la relación es 1-1-1 debido a que al haber una clave foránea que no es primaria, entonces sabemos que la cardinalidad de la entidad a la cual hace referencia dicha clave foránea es 1. Ahora listamos todas las claves únicas con los atributos que la componen. Para cada caso aquel atributo que no se encuentre en una clave única y que nosotros sepamos que hace referencia a una entidad que relaciona la tabla, entonces sabemos con certeza que tiene cardinalidad 1, es decir, si Número_Matrícula-Número_Departamento componen una clave única sabemos que Número_Vehículo tiene cardinalidad 1.
Ahora pasaremos a justificar lo anterior con un ejemplo, ingresando algunas tuplas a las tablas de la relación antes citada.
| Matrículas_Vehículos_Departamentos | Válido | ||
| Núm_Mat | Núm_Veh | Núm_Dep | |
| SAK 445 | 4670 | 10 | Sí |
| SAK 445 | 890 | 10 | No |
| SSD 320 | 430 | 11 | Sí |
| DFF 440 | 4670 | 10 | No |
Debido a que Número_Matrícula, Número_Vehículo son clave primaria de la tabla, entonces deducimos que la entidad Departamentos tiene cardinalidad 1.
Ahora, a partir del ejemplo que mostramos ingresando tuplas, deducimos que un valor de Número_Vehículo y Número_Departamento estos sólo pueden aparecer juntos en una misma tupla una sola vez en toda la tabla. Por ende estos dos atributos forman una clave única, deduciendo entonces que la entidad Matrículas tiene cardinalidad 1. De la misma forma ocurre con la entidad Vehículos.
Por lo tanto la representación conceptual de este conjunto de tablas es la siguiente:
 |
Relación n–aria con sus cardinalidades mezcladas
Con cardinalidades mezcladas quiero decir que la cardinalidad de la relación no es ni todas uno ni todas enes, sino que la cantidad de cardinalidades unos y enes son variables.
Para resolver este tipo de relación, nuevamente haremos uso de las claves candidatas (claves únicas). El análisis lo haré basándome en un ejemplo.
| Personas | Personas_Garantes |
| Número_Persona (PK) | Número_Persona_Garante (PK) |
| Conformes | Conformes_Personas |
| Número_Conforme (PK) | Número_Persona (PKFK) Número_Conforme (PKFK) Número_Persona_Garante (FK) |
Como podemos observar en los esquemas que describimos anteriormente, la tabla que representa la relación tiene la siguiente clave primaria compuesta: Número_Persona, Número_Conforme; por lo cual deducimos directamente que la cardinalidad de la entidad Personas_Garantes es 1.
Luego tras analizar las claves únicas que posee la tabla deducimos que posee una clave única compuesta por los siguientes atributos: Número_Conforme, Número_Persona_Garante, por lo cual deducimos que la entidad Personas también posee cardinalidad 1. Finalmente al no poseer más claves únicas llegamos a la conclusión de que la cardinalidad de esta relación es 1-1-N.
A continuación presentamos un ejemplo por medio del cual podemos deducir la lógica antes nombrada sobre la utilización de las claves únicas:
| ConformesPersonas | Válido | ||
| Núm_Per | Núm_Con | Núm_Per_Gar | |
| 888 | 456 | 456 | Sí |
| 976 | 321 | 231 | Sí |
| 682 | 321 | 231 | No |
Debido a que Número_Persona_Garante no pertenece a la clave primaria de la relación deduzco que la cardinalidad de la entidad Personas_Garantes es 1.
A partir del ejemplo anterior deducimos que dado cierto valor de Número_Conforme y Número_Persona_Garante estos valores juntos pueden aparecer una sola vez en toda la tabla, por lo tanto deducimos que la cardinalidad de la entidad Personas es 1. Debido a que la entidad Conformes pertenece a todas las claves únicas que hay en la tabla, deduzco que su cardinalidad es N.
Por lo tanto la representación conceptual del conjunto de tablas expuestos anteriormente, es la siguiente:
 |
En el análisis anterior fuimos descubriendo (en algunos casos) aquellas tablas que componían una agregación.
Aquí no trataremos un análisis de como determinar una agregación, debido a que una agregación no es más que una relación especial, ya que la agregación se utiliza para relacionar una relación con una entidad o conjunto de entidades, o con otra agregación o conjunto de agregaciones, o con ambas.
Ahora, a la hora de representar el modelo conceptual nos puede interesar identificar cuando una tabla que representa una relación relaciona a una agregación con otras estructuras. Para identificar esto nos fijamos si alguna de las entidades que relaciona es a su vez una relación, en ese caso deducimos que la relación a la cual se hace referencia es una agregación (debido a que la única forma de relacionar relaciones es por medio de agregaciones).
La aplicación que implementé la realicé en Delphi 6. Mi elección radicó en que desde mi punto de vista Delphi es una herramienta sumamente potente, fácil de aprender, y además es el lenguaje en el que poseo más experiencia. De todas formas mi idea es que tras leer el análisis se pueda realizar en cualquier lenguaje y con cualquier base de datos. En mi caso utilicé a modo de ejemplo un servidor Oracle 8i Lite.
El código fuente completo de la aplicación puede bajarse de http://www.latiumsoftware.com/descarga/iibd.zip
