Modelo Físico

SEMANA 6

MODELO FÍSICO

IX Diseño físico de bases de datos 

En este capítulo se describe la metodología de diseño físico para bases de datos relacionales. En esta etapa, se parte del esquema lógico global obtenido durante el diseño lógico y se obtiene una descripción de la implementación de la base de datos en memoria secundaria. Esta descripción es completamente dependiente del SGBD específico que se vaya a utilizar. En este capítulo se dan una serie de directrices para escoger las estructuras de almacenamiento de las relaciones base, decidir cuándo crear índices y cuándo des normalizar el esquema lógico e introducir redundancias.

 Introducción

El diseño de una base de datos se descompone en tres etapas: diseño conceptual, lógico y físico. La etapa del diseño lógico es independiente de los detalles de implementación y dependiente del tipo de SGBD que se vaya a utilizar. La salida de esta etapa es el esquema lógico global y la documentación que lo describe. Todo ello es la entrada para la etapa que viene a continuación, el diseño físico.

Mientras que en el diseño lógico se especifica qué se guarda, en el diseño físico se especifica cómo se guarda. Para ello, el diseñador debe conocer muy bien toda la funcionalidad del SGBD concreto que se vaya a utilizar y también el sistema informático sobre el que éste va a trabajar. El diseño físico no es una etapa aislada, ya que algunas decisiones que se tomen durante su desarrollo, por ejemplo para mejorar las prestaciones, pueden provocar una reestructuración del esquema lógico.

Metodología de diseño físico para bases de datos relacionales

El objetivo de esta etapa es producir una descripción de la implementación de la base de datos en memoria secundaria. Esta descripción incluye las estructuras de almacenamiento y los métodos de acceso que se utilizarán para conseguir un acceso eficiente a los datos.

El diseño físico se divide de cuatro fases, cada una de ellas compuesta por una serie de pasos:

Traducir el esquema lógico global para el SGBD específico. Diseñar las relaciones base para el SGBD específico. Diseñar las reglas de negocio para el SGBD específico. Diseñar la representación física. Analizar las transacciones. Escoger las organizaciones de ficheros. Escoger los índices secundarios. Considerar la introducción de redundancias controladas. Estimar la necesidad de espacio en disco. Diseñar los mecanismos de seguridad. Diseñar las vistas de los usuarios. Diseñar las reglas de acceso. Monitorizar y afinar el sistema. Traducir el esquema lógico global

La primera fase del diseño lógico consiste en traducir el esquema lógico global en un esquema que se pueda implementar en el SGBD escogido. Para ello, es necesario conocer toda la funcionalidad que éste ofrece. Por ejemplo, el diseñador deberá saber:

Si el sistema soporta la definición de claves primarias, claves ajenas y claves alternativas. Si el sistema soporta la definición de datos requeridos (es decir, si se pueden definir atributos como no nulos). Si el sistema soporta la definición de dominios. Si el sistema soporta la definición de reglas de negocio. Cómo se crean las relaciones base.

1. Diseñar las relaciones base para el SGBD específico

Las relaciones base se definen mediante el lenguaje de definición de datos del SGBD. Para ello, se utiliza la información producida durante el diseño lógico: el esquema lógico global y el diccionario de datos. El esquema lógico consta de un conjunto de relaciones y, para cada una de ellas, se tiene:

El nombre de la relación. La lista de atributos entre paréntesis. La clave primaria y las claves ajenas, si las tiene. Las reglas de integridad de las claves ajenas.

En el diccionario de datos se describen los atributos y, para cada uno de ellos, se tiene:

Su dominio: tipo de datos, longitud y restricciones de dominio. El valor por defecto, que es opcional. Si admite nulos. Si es derivado y, en caso de serlo, cómo se calcula su valor.

A continuación, se muestra un ejemplo de la definición de la relación INMUEBLE con el estándar SQL2.

create domain pnum as varchar(5); create domain enum as varchar(5); create domain onum as varchar(3); create domain inum as varchar(5); create domain calle as varchar(25); create domain area as varchar(15); create domain poblacion as varchar(15); create domain tipo as varchar(1) check(value in (`a',`c',`d',`p',`v')); create domain hab as smallint check(value between 1 and 15); create domain alquiler as decimal(6,2) check(value between 0 and 9999); create table inmueble ( inum not null, calle not null, area , poblacion  not null, tipo not null default `p', hab hab not null default 4, alquiler alquiler not null default 350, pnum not null, enum, onum not null, primary key (inum), foreign key (pnum) references propietario on delete no action on update cascade, foreign key (enum) references plantilla on delete set null on update cascade, foreign key (onum) references oficina on delete no action on update cascade );

2. Diseñar las reglas de negocio para el SGBD específico

Las actualizaciones que se realizan sobre las relaciones de la base de datos deben observar ciertas restricciones que imponen las reglas de negocio de la empresa. Algunos SGBD proporcionan mecanismos que permiten definir estas restricciones y vigilan que no se violen.

Por ejemplo, si no se quiere que un empleado tenga más de diez inmuebles asignados, se puede definir una restricción en la sentencia CREATE TABLE de la relación INMUEBLE:

CONSTRAINT inmuebles_por_empleado CHECK (NOT EXISTS (SELECT enum FROM inmueble GROUP BY enum HAVING COUNT (*)>10))

Otro modo de definir esta restricción es mediante un disparador (trigger):

CREATE TRIGGER inmuebles_por_empleado ON inmueble FOR INSERT, UPDATE AS IF ((SELECT COUNT (*) FROM inmueble i WHERE i. Inum=INSERTED.inum)>10) BEGIN PRINT "Este empleado ya tine 10 inmuebles asignados" ROLLBACK TRANSACTION END

Hay algunas restricciones que no las pueden manejar los SGBD, como por ejemplo `a las 20:30 del último día laborable de cada año archivar los inmuebles vendidos y borrarlos'. Para estas restricciones habrá que escribir programas de aplicación específicos. Por otro lado, hay SGBD que no permiten la definición de restricciones, por lo que éstas deberán incluirse en los programas de aplicación.

Todas las restricciones que se definan deben estar documentadas. Si hay varias opciones posibles para implementarlas, hay que explicar porqué se ha escogido la opción implementada.

Diseñar la representación física

Uno de los objetivos principales del diseño físico es almacenar los datos de modo eficiente. Para medir la eficiencia hay varios factores que se deben tener en cuenta:

Productividad de transacciones. Es el número de transacciones que se quiere procesar en un intervalo de tiempo. Tiempo de respuesta. Es el tiempo que tarda en ejecutarse una transacción. Desde el punto de vista del usuario, este tiempo debería ser el mínimo posible. Espacio en disco. Es la cantidad de espacio en disco que hace falta para los ficheros de la base de datos. Normalmente, el diseñador querrá minimizar este espacio.

Lo que suele suceder, es que todos estos factores no se pueden satisfacer a la vez. Por ejemplo, para conseguir un tiempo de respuesta mínimo, puede ser necesario aumentar la cantidad de datos almacenados, ocupando más espacio en disco. Por lo tanto, el diseñador deberá ir ajustando estos factores para conseguir un equilibrio razonable. El diseño físico inicial no será el definitivo, sino que habrá que ir monitorizándolo para observar sus prestaciones e ir ajustándolo como sea oportuno. Muchos SGBD proporcionan herramientas para monitorizar y afinar el sistema.

Hay algunas estructuras de almacenamiento que son muy eficientes para cargar grandes cantidades de datos en la base de datos, pero no son eficientes para el resto de operaciones, por lo que se puede escoger dicha estructura de almacenamiento para inicializar la base de datos y cambiarla, a continuación, para su posterior operación. Los tipos de organizaciones de ficheros disponibles varían en cada SGBD. Algunos sistemas proporcionan más estructuras de almacenamiento que otros. Es muy importante que el diseñador del esquema físico sepa qué estructuras de almacenamiento le proporciona el SGBD y cómo las utiliza.

Para mejorar las prestaciones, el diseñador del esquema físico debe saber cómo interactúan los dispositivos involucrados y cómo esto afecta a las prestaciones:

Memoria principal. Los accesos a memoria principal son mucho más rápidos que los accesos a memoria secundaria (decenas o centenas de miles de veces más rápidos). Generalmente, cuanta más memoria principal se tenga, más rápidas serán las aplicaciones. Sin embargo, es aconsejable tener al menos un 5% de la memoria disponible, pero no más de un 10%. Si no hay bastante memoria disponible para todos los procesos, el sistema operativo debe transferir páginas a disco para liberar memoria . Cuando estas páginas se vuelven a necesitar, hay que volver a traerlas desde el disco (faltas de página). A veces, es necesario llevar procesos enteros a disco (swapping) para liberar memoria. El hacer estas transferencias con demasiada frecuencia empeora las prestaciones. CPU. La CPU controla los recursos del sistema y ejecuta los procesos de usuario. El principal objetivo con este dispositivo es lograr que no haya bloqueos de procesos para conseguirla. Si el sistema operativo, o los procesos de los usuarios, hacen muchas demandas de CPU, ésta se convierte en un cuello de botella. Esto suele ocurrir cuando hay muchas faltas de página o se realiza mucho swapping. Entrada/salida a disco. Los discos tienen una velocidad de entrada/salida. Cuando se requieren datos a una velocidad mayor que ésta, el disco se convierte en un cuello de botella. Dependiendo de cómo se organicen los datos en el disco, se conseguirá reducir la probabilidad de empeorar las prestaciones. Los principios básicos que se deberían seguir para repartir los datos en los discos son los siguientes: Los ficheros del sistema operativo deben estar separados de los ficheros de la base de datos. Los ficheros de datos deben estar separados de los ficheros de índices Los ficheros con los diarios de operaciones deben estar separados del resto de los ficheros de la base de datos. Red. La red se convierte en un cuello de botella cuando tiene mucho tráfico y cuando hay muchas colisiones.

Cada uno de estos recursos afecta a los demás, de modo que una mejora en alguno de ellos puede provocar mejoras en otros.

3. Analizar las transacciones

Para realizar un buen diseño físico es necesario conocer las consultas y las transacciones que se van a ejecutar sobre la base de datos. Esto incluye tanto información cualitativa, como cuantitativa. Para cada transacción, hay que especificar:

La frecuencia con que se va a ejecutar. Las relaciones y los atributos a los que accede la transacción, y el tipo de acceso: consulta, inserción, modificación o eliminación. Los atributos que se modifican no son buenos candidatos para construir estructuras de acceso. Los atributos que se utilizan en los predicados del WHERE de las sentencias SQL. Estos atributos pueden ser candidatos para construir estructuras de acceso dependiendo del tipo de predicado que se utilice. Si es una consulta, los atributos involucrados en el join de dos o más relaciones. Estos atributos pueden ser candidatos para construir estructuras de acceso. Las restricciones temporales impuestas sobre la transacción. Los atributos utilizados en los predicados de la transacción pueden ser candidatos para construir estructuras de acceso.

4. Escoger las organizaciones de ficheros

El objetivo de este paso es escoger la organización de ficheros óptima para cada relación. Por ejemplo, un fichero desordenado es una buena estructura cuando se va a cargar gran cantidad de datos en una relación al inicializarla, cuando la relación tiene pocas tuplas, también cuando en cada acceso se deben obtener todas las tuplas de la relación, o cuando la relación tiene una estructura de acceso adicional, como puede ser un índice. Por otra parte, los ficheros dispersos (hashing) son apropiados cuando se accede a las tuplas a través de los valores exactos de alguno de sus campos (condición de igualdad en el WHERE). Si la condición de búsqueda es distinta de la igualdad (búsqueda por rango, por patrón, etc.), la dispersión no es una buena opción. Hay otras organizaciones, como la ISAM o los árboles B+.

Las organizaciones de ficheros elegidas deben documentarse, justificando en cada caso la opción escogida.

5. Escoger los índices secundarios

Los índices secundarios permiten especificar caminos de acceso adicionales para las relaciones base. Por ejemplo, la relación INMUEBLE se puede haber almacenado en un fichero disperso a través del atributo inum. Si se accede a menudo a esta relación a través del atributo alquiler, se puede plantear la creación de un índice sobre dicho atributo para favorecer estos accesos. Pero hay que tener en cuenta que estos índices conllevan un coste de mantenimiento que hay que sopesar frente a la ganancia en prestaciones. A la hora de seleccionar los índices, se pueden seguir las siguientes indicaciones:

Construir un índice sobre la clave primaria de cada relación base. No crear índices sobre relaciones pequeñas. Añadir un índice sobre los atributos que se utilizan para acceder con mucha frecuencia. Añadir un índice sobre las claves ajenas que se utilicen con frecuencia para hacer joins. Evitar los índices sobre atributos que se modifican a menudo. Evitar los índices sobre atributos poco selectivos (aquellos en los que la consulta selecciona una porción significativa de la relación). Evitar los índices sobre atributos formados por tiras de caracteres largas.

Los índices creados se deben documentar, explicando las razones de su elección.

6. Considerar la introducción de redundancias controladas

En ocasiones puede ser conveniente relajar las reglas de normalización introduciendo redundancias de forma controlada, con objeto de mejorar las prestaciones del sistema. En la etapa del diseño lógico se recomienda llegar, al menos, hasta la tercera forma normal para obtener un esquema con una estructura consistente y sin redundancias. Pero, a menudo, sucede que las bases de datos así normalizadas no proporcionan la máxima eficiencia, con lo que es necesario volver atrás y desnormalizar algunas relaciones, sacrificando los beneficios de la normalización para mejorar las prestaciones. Es importante hacer notar que la desnormalización sólo debe realizarse cuando se estime que el sistema no puede alcanzar las prestaciones deseadas. Y, desde luego, la necesidad de desnormalizar en ocasiones no implica eliminar la normalización del diseño lógico: la normalización obliga al diseñador a entender completamente cada uno de los atributos que se han de representar en la base de datos. Por lo tanto, hay que tener en cuenta los siguientes factores:

La desnormalización hace que la implementación sea más compleja. La desnormalización hace que se sacrifique la flexibilidad. La desnormalización puede hacer que los accesos a datos sean más rápidos, pero ralentiza las actualizaciones.

Por regla general, la desnormalización de una relación puede ser una opción viable cuando las prestaciones que se obtienen no son las deseadas y la relación se actualiza con poca frecuencia, pero se consulta muy a menudo. Las redundancias que se pueden incluir al desnormalizar son de varios tipos: se pueden introducir datos derivados (calculados a partir de otros datos), se pueden duplicar atributos o se pueden hacer joins de relaciones.

El incluir un atributo derivado dependerá del coste adicional de almacenarlo y mantenerlo consistente con los datos de los que se deriva, frente al coste de calcularlo cada vez que se necesita.

No se pueden establecer una serie de reglas que determinen cuándo desnormalizar relaciones, pero hay algunas situaciones muy comunes en donde puede considerarse esta posibilidad:

Combinar relaciones de uno a uno. Cuando hay relaciones (tablas) involucradas en relaciones de uno a uno, se accede a ellas de manera conjunta con frecuencia y casi no se les accede separadamente, se pueden combinar en una sola relación (tabla). Duplicar atributos no clave en relaciones de uno a muchos para reducir los joins. Para evitar operaciones de join, se pueden incluir atributos de la relación (tabla) padre en la relación (tabla) hijo de las relaciones de uno a muchos. Tablas de referencia. Las tablas de referencia (lookup) son listas de valores, cada uno de los cuales tiene un código. Por ejemplo puede haber una tabla de referencia para los tipos de inmueble, con las descripciones de estos tipos y un código asociado. Este tipo de tablas son un caso de relación de uno a muchos. En la relación INMUEBLE habrá una clave ajena a esta tabla para indicar el tipo de inmueble. De este modo, es muy fácil validar los datos, además de que se ahorra espacio escribiendo sólo el código y no la descripción para cada inmueble, además de ahorrar tiempo cuando se actualizan las descripciones. Si las tablas de referencia se utilizan a menudo en consultas críticas, se puede considerar la introducción de la descripción junto con el código en la relación (tabla) hijo, manteniendo la tabla de referencia para validación de datos. Duplicar claves ajenas en relaciones de uno a muchos para reducir los joins. Para evitar operaciones de join, se pueden incluir claves ajenas de una relación (tabla) en otra relación (tabla) con la que se relaciona (habrá que tener en cuenta ciertas restricciones). Duplicar atributos en relaciones de muchos a muchos para reducir los joins. Durante el diseño lógico se eliminan las relaciones de muchos a muchos introduciendo dos relaciones de uno a muchos. Esto hace que aparezca una nueva relación (tabla) intermedia, de modo que si se quiere obtener la información de la relación de muchos a muchos, se tiene que realizar el join de tres relaciones (tablas). Para evitar algunos de estos joins se pueden incluir algunos de los atributos de las relaciones (tablas) originales en la relación (tabla) intermedia. Introducir grupos repetitivos. Los grupos repetitivos se eliminan en el primer paso de la normalización para conseguir la primera forma normal. Estos grupos se eliminan introduciendo una nueva relación (tabla), generando una relación de uno a muchos. A veces, puede ser conveniente reintroducir los grupos repetitivos para mejorar las prestaciones.

Todas las redundancias que se introduzcan en este paso se deben documentar y razonar. El esquema lógico se debe actualizar para reflejar los cambios introducidos.

7. Estimar la necesidad de espacio en disco

En caso de que se tenga que adquirir nuevo equipamiento informático, el diseñador debe estimar el espacio necesario en disco para la base de datos. Esta estimación depende del SGBD que se vaya a utilizar y del hardware. En general, se debe estimar el número de tuplas de cada relación y su tamaño. También se debe estimar el factor de crecimiento de cada relación.

Diseñar los mecanismos de seguridad

Los datos constituyen un recurso esencial para la empresa, por lo tanto su seguridad es de vital importancia. Durante el diseño lógico se habrán especificado los requerimientos en cuanto a seguridad que en esta fase se deben implementar. Para llevar a cabo esta implementación, el diseñador debe conocer las posibilidades que ofrece el SGBD que se vaya a utilizar.

8. Diseñar las vistas de los usuarios

El objetivo de este paso es diseñar las vistas de los usuarios correspondientes a los esquemas lógicos locales. Las vistas, además de preservar la seguridad, mejoran la independencia de datos, reducen la complejidad y permiten que los usuarios vean los datos en el formato deseado.

9. Diseñar las reglas de acceso

El administrador de la base de datos asigna a cada usuario un identificador que tendrá una palabra secreta asociada por motivos de seguridad. Para cada usuario o grupo de usuarios se otorgarán permisos para realizar determinadas acciones sobre determinados objetos de la base de datos. Por ejemplo, los usuarios de un determinado grupo pueden tener permiso para consultar los datos de una relación base concreta y no tener permiso para actualizarlos.

Monitorizar y afinar el sistema

Una vez implementado el esquema físico de la base de datos, se debe poner en marcha para observar sus prestaciones. Si éstas no son las deseadas, el esquema deberá cambiar para intentar satisfacerlas. Una vez afinado el esquema, no permanecerá estático, ya que tendrá que ir cambiando conforme lo requieran los nuevos requisitos de los usuarios. Los SGBD proporcionan herramientas para monitorizar el sistema mientras está en funcionamiento.

Resumen

El diseño físico es el proceso de producir una descripción de la implementación de la base de datos en memoria secundaria. Describe las relaciones base y las estructuras de almacenamiento y métodos de acceso que se utilizarán para acceder a los datos de modo eficiente. El diseño de las relaciones base sólo se puede realizar cuando el diseñador conoce perfectamente toda la funcionalidad que presenta el SGBD que se vaya a utilizar.

El primer paso consiste en traducir el esquema lógico global de modo que pueda ser fácilmente implementado por el SGBD específico. A continuación, se escogen las organizaciones de ficheros más apropiadas para almacenar las relaciones base, y los métodos de acceso, basándose en el análisis de las transacciones que se van a ejecutar sobre la base de datos. Se puede considerar la introducción de redundancias controladas para mejorar las prestaciones. Otra tarea a realizar en este paso es estimar el espacio en disco.

La seguridad de la base de datos es fundamental, por lo que el siguiente paso consiste en diseñar las medidas de seguridad necesarias mediante la creación de vistas y el establecimiento de permisos para los usuarios.

El último paso del diseño físico consiste en monitorizar y afinar el sistema para obtener las mejores prestaciones y satisfacer los cambios que se puedan producir en los requisitos.