Curso de diseño de bases de datos relacionales

Con la última entrada se completa un curso básico de diseño de Bases de Datos relacionales, los primeros pasos son teóricos y los últimos son prácticos y se ejecutan en SQL Server.

El curso queda del siguiente modo:

1-     Explicación del modelo ER con un ejemplo práctico de cómo adaptar un enunciado para convertirlo en un modelo Entidad-Relacción.

2-     Cómo pasar del modelo ER a un modelo relacional para que sea más fácil adaptarlo a Bases de Datos Relacionales como SQL Server.

3-     Teoría de la normalización y formas normales explica cómo optimizar  y normalizar el diseño de la base de datos para evitar problemas de referencias entre otros, en Bases de Datos mal diseñadas.  Y se explican las diferentes formas normales. Continuando con el ejemplo sencillo del primer capítulo se expone cómo  adaptarlo a las formas normales.

4-     Finalmente se explica de forma práctica cómo crear las tablas y sus respectivas Claves y Foreing Keys a través de los menús de la aplicación SQL Management Studio de SQL Server o directamente a través de un script. 

Crear una tabla en SQL Server 2005

Esto es un pequeño manual de cómo crear en SQL Server  Tablas y asignarle las Claves y las Foreing Keys correspondientes, al final viene un script para crear una tabla mediante código.

Para generar la tabla.

 
 Y luego elegimos del nombre de la tabla

 Para poner claves.

 Elegimos el campo clave y pulsamos

 Con lo que queda

 Si queremos que la clave sean varios campos, selecionamos los campos deseados y pulsamos.

 Para crear una foreing Key simple.

 Nos abre el diálogo

 Pulsamos Add y luego el botón
 Con lo que sale

Pulsamos OK y ya tenemos nuestra Foreing Key, cerramos con close.

 Para crear una foreing Key complejo.

Aparecen dos colunnas, si intentamos dar al OK nos da un mensaje de error del tipo

Para evitar esto, hay que elegir la columna que se quiere relacionar y poner  <none> en las que no corresponda la relación

Ahora si deja crear la relación.

Generación mediante scripts

Para generar las tablas, sus claves y foreing keys se puede hacer por código del siguiente modo:

USE [model]

GO

/****** Object:  Table [dbo].[tbAtiende]    Script Date: 08/30/2011 10:55:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tbAtiende](

      [intID] [tinyint] IDENTITY(1,1) NOT NULL,

      [strID_MED] [char](5) NOT NULL,

      [strID_PAC] [char](5) NOT NULL,

 CONSTRAINT [PK_tbAtiende] PRIMARY KEY CLUSTERED

(

      [strID_MED] ASC,

      [strID_PAC] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Formas normales (ejemplo)

EJEMPLO SENCILLO

A Continuación un ejemplo sencillo de aplicación de las formas normales al diseño de una base de Datos relacional. Basado en el esquema de base de datos obtenido en el  punto anterior que era el siguiente:

Ahora vamos a continuar con el desarrollo del modelo adaptándolo para que cumpla las formas normales.

En Rojo la clave primaria.

En Azul las claves  ajenas

Paso a primera forma normal

Tabla: tbMedico (strId_MED, strId_HOS,  strNombre, strEspecialidad,)

Un médico puede pertenecer a varias especialidades o a varios hospitales por lo que

pasaría a descomponerse en :

tbMedico (strId_MED, strNombre)

tbMedHos(strId_MED, strId_HOS)

tbMedEsp(strId_MED, strEspecialidad)

Tabla: tbAtiende(strId_MED, strId_PAC)  está en 1FN

Tabla: tbPaciente(strId_PAC, strID_SAL, strId_HOS, strNombre, strDirección, dat_FecNacimiento)

Si consideramos que un paciente solo tiene una dirección válida y sólo puede tener asignada una sala, esta tabla estaría en 1FN.

Tabla: tbSala (strId_SAL, strId_HOS, intCamas,)  está en 1FN.

Tabla: tbHospital(strId_HOS, strNombre, strDireccion, strTelefono)

Para que el hospital pueda tener diversos números de teléfono se deja como:

tbHospital(strId_HOS, strNombre, strDireccion)

tbHospTel (strId_HOS, strTelefono)  No se contempla un listín telefónico por departamentos.

Paso a segunda forma Normal

En este ejemplo no se da ningún caso que esté en 1FN y no esté en 2FN por lo que podemos reordenar las claves dejándolo así.

tbMedico (strId_MED, strNombre)

tbMedEsp (strId_MED, strEspecialidad)

tbMedHos (strId_MED, strId_HOS)

tbAtiende (strId_MED, strId_PAC) 

 tbSala (strId_SAL, strId_HOS, intCamas,) 

tbPaciente (strId_PAC , strID_SAL, , strId_HOS, strNombre, strDirección, dat_FecNacimiento)

Que al tener sólo una clave primaria, está en 2FN.

tbHospital (strId_HOS, strNombre, strDireccion)

tbHospTel (strId_HOS, strTelefono) 

Paso a tercera forma Normal

El campo strEspecialidad de tbMedEsp depende transitivamente de strID_MED por tanto se sustituye por el código de la especialidad y se crea una nueva tabla tbEspecialidades

tbMedico (strId_MED, strNombre)

tbMedEsp (strId_MED, strId_ESP)

tbEspecialidades (strId_ESP, strEspecialidad)

Como en tbSala (strId_SAL, strId_HOS, intCamas)  strID_HOS no depende de la Sala,

sacamos la relación a una tabla auxiliar. Quedando:

tbSala (strId_SAL, intCamas) 

tbSalaHos (strId_SAL, strId_HOS) 

Repitiendo la misma operación con tbPaciente nos queda

tbPaciente (strId_PAC , strNombre, strDirección, dat_FecNacimiento)

tbPacSala (strId_PAC , strID_SAL)  Con esta información y la de tbSalaHos queda definido el hospital asignado al paciente.

Al no hacer claves complejas, llegados a este punto, también cumplen las FNCB formas normales por lo que finalmente tenemos:

SQL Server: La importancia de elegir la instrucción correcta

Diferentes instrucciones de  SQL Server que aparentemente tienen el mismo resultado en realidad muestran diferentes resultados incluso pueden generar errores. Lo veremos más claro con un ejemplo.

Enunciado  del ejemplo:
Dado un campo1 de tipo smallint, se desea que tome valor 1 para todos los valores del campo2 de tipo varchar que comiencen por 2 excepto para los que comiencen por 23 que tomará valor cero. el resto de ocurrencias es indiferente.

--estas dos select devuelven el mismo resultado

SELECT * FROM tabla WHERE substring(campo2,1,1) = 2
SELECT * FROM tabla WHERE campo2 like '2%'

Estas dos líneas de instrucciones realizan la operación solicitada. 
--Esta solución es correcta, pero si el campo varchar contiene letras dará un error de conversión de tipos.
UPDATE tabla SET campo1 = 1 WHERE substring(campo2,1,1) = 2
UPDATE tabla SET campo1 = 0 WHERE substring(campo2,1,2) = 23

Estas dos líneas de abajo realizan el mismo trabajo que las anteriores pero son más robustas frente a fallos.

--Esta solución es más correcta pues hace lo mismo que la anterior y aunque el campo varchar contenga letras, no falla.
UPDATE tabla SET campo1 = 1 WHERE campo2 like '2%'
UPDATE tabla SET campo1 = 0 WHERE campo2 like '23%'

Esto pone de manifiesto que a la hora de hacer una instrucción de SQL no sólo basta con que sea correcta, hay que tener en cuenta los posibles fallos, en este caso de conversión de tipos de un campo varchar que toma valores numéricos (pero puede que no lo haga) pues al ser varchar es legal que un campo venga como 'C2000' y en ese caso el primer UPDATE falla.

Normalización: (Formas Normales)

La teoría de la normalización es una guía que ayuda a prevenir problemas de diseño de bases de datos como las redundancias y otras anomalías en la inserción, modificación y borrado en las tablas de una base de datos; aunque como contrapartida puede haber problemas en su recuperación.

La normalización es un proceso de diseño que consiste en descomponer los registros en otros de menor tamaño (con menos campos), de forma que cumplan una serie de restricciones que se definen como forma normal.

Noción de las Formas Normales

Primera Forma Normal 1FN

No se permite que un registro tenga grupos repetitivos, el concepto de "grupo repetitivo", puede ser definido de diferentes maneras según el criterio utilizado. Por tanto, no hay un acuerdo universal sobre los requisitos para que una tabla se pueda considerar en 1FN.

Aquí vamos a considerar que todos los campos deben ser únicos. En la Figura 1 se muestran distintas opciones de cómo pasar un registro a primera forma normal. Cada una tiene ventajas e inconvenientes y su implantación dependerá de las características del grupo que se repite. (Por ejemplo, si es fijo y con muy pocas ocurrencias podría ser conveniente contemplar la opción C, si fuera variable y numeroso, podría ser más acertada la opción B).

Segunda Forma Normal

Un registro está en segunda forma normal 2FN. Si además de estar en primera forma normal, todos los campos que no pertenezcan a la clave primaria dependen de toda la clave primaria.

Es decir, los campos que no son clave suministran información acerca de la clave.

Por ejemplo, en el registro.

Suministros(Cod_Objeto, Cod_Almacen, Cantidad, Ubicación_Almacen)

Donde la clave primaria está formada por los campos Cod_Objeto y Cod_Almacen, la ubicación en el almacén (Ubicacion_Almacen) es un hecho sólo acerca de Cod_Almacen no del conjunto de la clave, por lo que este registro viola la segunda forma normal. Para evitar este problema, el registro se puede descomponer del siguiente modo:

Suministros (Cod_Objeto, Cod_Almacen, Cantidad)

Almacenes (Cod_Almacen, Ubicacion_Almacen)

Que ya se encuentran en 2FN.

                                

Tercera Forma Normal 

La tercera forma normal 3FN además de englobar las otras dos anteriores, añade la siguiente restricción: Los campos que no forman parte de la clave primaria deben facilitar información sólo acerca de la(s) claves(s) primarias, y no acerca de otros campos. Es decir, un registro está en tercera forma normal, si sus campos deben ser independientes mutuamente y dependientes completamente de las claves primarias.

El siguiente registro, cuya clave es el código de médico, viola la 3FN, ya que el nombre del departamento es un hecho acerca del código del departamento además de serlo transitivamente de Cod_Medico

Medicos (Cod_Medico, Cod_Especialidad, Nombre_Especialidad)

Para conseguir la 3FN sería conveniente descomponerlo de la siguiente manera:

Medicos (Cod_Medico, Cod_Especialidad)

Especialidad (Cod_Especialidad, Nombre_Especialidad)

Definición de dependencia funcional

Decimos que un descriptor Y (Conjunto de campos) depende funcionalmente del descriptor X, si, y sólo si, cada valor de X tiene asociado en todo momento un único valor de Y, lo que se representa como: X -> Y

Así por ejemplo, podemos decir que:

Cod_Almacen -> Direccion_Almacen

Ya que cada código (suponiendo que no se repiten) existe en una sola dirección, en esta dependencia, se dice que el Cod_Almacen es el implicante y Direccion_Almacen el implicado.

Dependencia funcional completa y 2FN

Si el descriptor X es compuesto X(X1, X2) se dice que Y tiene dependencia funcional completa o plena respecto de X, pero no depende de ningún subconjunto del mismo, esto es: 

X ->Y

X1 -/-> Y

X2 -/-> Y

Así por ejemplo, si suponemos que en un hospital un médico puede trabajar en varias espacialidades, realizando una sola función en cada una de ellas (Traumatología, aparato digestivo, pediatría, etc.), aunque pueda ser distinta según la especialidad que:

(DNI_Medico, Cod_Especialidad) -> Funcion

Es una dependencia completa, ya que ninguno de los elementos del descriptor por separado determina el implicado, al poder tener un médico muchas funciones, lo mismo que una especialidad.

Sin embargo la dependencia:

(Cod_Objeto, Cod_almacen) -> Direccion_Almacen

No es completa, ya que Cod_Almacen -> Direccion _Almacen

Podemos ahora definir la 2FN de manera más rigurosa diciendo que un registro está en 2FN si:

-Está en 1FN

-Todo campo no clave tiene una dependencia funcional completa respecto de la clave primaria.

Por esta razón la relación:

Suministros (Cod_Objeto, Cod_Almacen, Cantidad, Ubicacion_Almacen)

No se encuentra en 2FN, mientras que las relaciones:

Suministros (Cod_Objeto, Cod_Almacen, Cantidad)

Almacenes (Cod_Almacen, Ubicacion_Almacen)

Si, ya que el primer registro, (Cod_Objeto, Cod_Almacen) -> Cantidad es una dependencia completa, (Se puede repetir el código de pieza, si el Cod_Almacen cambia, por eso tiene que ir el Cod_Almacen y decimos que es dependencia completa)

Por ejemplo

Cod_Objeto Cod_Almacen Cantidad

001 001 12

002 001 46

002 002 34

y en el segundo caso Cod_Almacen -> Ubicacion_Almacen también lo es, ya que si la clave es simple (está formada por un solo campo) siempre que se encuentre en 1FN lo estará también en 2FN.

Dependencia funcional transitiva y Tercera Forma Normal

Dado un registro con tres descriptores en el que existen las siguientes dependencias funcionales, como se indica en la Figura 2:

X -> Y

Y -> Z

Y -/-> X

Se dice que Z depende transitivamente respecto de X a través de Y, lo que se representa como X- - > Z

Por ejemplo, si suponemos que se dan las siguientes dependencias:

Cod_Medico -> Cod_Especialidad

Cod_Especialidad -> Nombre_Especialidad

Cod_Especialidad -/-> Cod_Medico

Podemos afirmar que Cod_Medico -> Nombre_Especialidad transitivamente a través de Cod_Especialidad.

Se dice que un registro se encuentra en 3FN si:

- Está en 2FN

- Ningún campo no clave depende transitivamente de ninguna clave.

Es por esto que el registro:

Medicos (Cod_Medico, Cod_Especialidad, Nombre_Especialidad) 

No se encuentra en 3FN, ya que la clave es el Cod_Medico, mientras que Nombre_Especialidad, que no forma parte de la clave, depende transitivamente de ella.

Forma normal de Boyce-Codd (FNBC) 

Si nos encontramos con casos en los que hay varias claves compuestas solapadas (que comparten algún campo), tendremos problemas.

Forma Normal Boyce-Codd

Se dice que un registro se encuentra en FNBC si y solo si, todo determinante es clave, donde por determinante entendemos cualquier conjunto de campos en el que otro campo depende funcionalmente de forma completa.

Dado el siguiente registro:

Suministros(Cod_Objeto, Cod_Almacen, Nombre_Almacen, Cantidad)

Donde los almacenes se identifican unívocamente tanto por el código como por el nombre; pero hay dos claves, la formada por el conjunto (Cod_Objeto Cod_Almacen) y la que componen (Cod_Objeto, Nombre_Almacen). Sin embargo, hay cuatro determinantes; además de las claves anteriores, el campo Cod_Almacen y el campo Nombre_Almacen son determinantes, ya que uno implica al otro y viceversa, por tanto este registro no se encuentra en FNBC, por que no todo determinante es clave (Cod_Almacen y Nombre_Almacen forman parte de la clave, pero no “son” la clave.)

Para cumplir la FNBC hay que descomponer el registro, de la siguiente manera:

Almacenes (Cod_Almacen, Nombre_Almacen)

Suministros (Cod_Objeto, Cod_Almacen, Cantidad)

De tal forma que en el primer registro existen dos claves: Cod_Almacen y Nombre_Almacen, y ambos campos son también determinantes, así que está en forma normal FNBC, mientras que en el segundo registro existe una clave compuesta por (Cod_Objeto, Cod_Almacen) y hay un único determinante formado por los dos campos (Cod_Pieza, Cod_Almacen) que determinan a Cantidad, por lo que también se encuentra en FNBC.

Cuarta Forma Normal

Una tabla está en 4NF si y solo si esta en 3FN o en BNCB (Cualquiera de ambas) y no posee dependencias multivaluadas triviales. (requiere que ciertas tuplas estén presentes en la misma). Una tabla con una dependencia multivaluada es una donde la existencia de dos o más relaciones independientes muchos a muchos causa redundancia; y es esta redundancia la que es suprimida por la cuarta forma normal.

Por ejemplo

Dada una tabla de diferentes hospitales con los médicos que tiene y las especialidades que tratan:

Hospitales (Cod_Hospital, Cod_médico, Cod_especialidad)

Cada fila indica que un hospital dado puede tratar una enfermedad con un médico. Como la tabla tiene una clave única y ningún atributo no-clave, no viola ninguna forma normal hasta el BCNF. Pero debido a que los médicos de cada hospital son independientes de las especialidades tratadas, hay redundancia en la tabla. Esto se ve mejor en una tabla con los campos rellenos:

                     

Por ejemplo, nos dicen tres veces que el hospital del Sur tiene al Dr. Bermúdez, si el hospital del Sur contrata al Dr. Martín necesitaremos agregar múltiples registros, uno para cada especialidad tratada. En términos formales, esto se describe como que cada médico está teniendo una dependencia multivalor en Hospital.

Para satisfacer la 4NF, debemos poner los hospitales con los distintos médicos mostrados en una tabla diferente de los hospitales con sus especialidades.

                   

Análisis orientado a objetos (AOO) IV

Definición de estructuras y jerarquías

Una vez generado el modelo CRC hay que centrarse en la estructura de las clases con su correspondiente jerarquía. Se usa para ello la notación UML para representar gráficamente la jerarquía entre las clases obtenidas del modelo CRC. La flecha representa la dependencia jerárquica. La jerarquía mostrada se ha extraído de las tarjetas CRC generadas anteriormente.

             

Definición de Subsistemas

Un modelo de análisis puede tener cientos de clases, en este caso resulta útil dividir ciertos grupos de clases en subsistemas. Para ello se eligen grupos de clases con un conjunto de responsabilidades similares. En realidad consisten en un escalón más en la abstracción para hacer más manejables grandes proyectos. En UML los subsistemas se llaman paquetes. Un subsistema tratado en su conjunto también contiene un conjunto de responsabilidades y posee sus propios colaboradores externos. En el sistema CRC se puede incluir un índice que defina el subsistema al que pertenece cada tarjeta, añadiendo también las responsabilidades a cumplir (contratos) y otros subsistemas si los hay. Por ejemplo el sistema creado anteriormente para una biblioteca se puede representar en UML como un rectángulo con dos líneas debajo para representar un subsistema de un conjunto más amplio, por ejemplo como perteneciente a una gran empresa.

              

El modelo Objeto-Relación

El siguiente paso al CRC es definir las clases colaboradoras que ayudan a realizar cada responsabilidad de una clase dada. Este paso establecerá las conexiones entre las diferentes clases. Existe una relación, asociación o conexión entre dos clases cualesquiera que estén conectadas. La relación más común de todas en la binaria que se da entre dos clases, esta es direccional, es decir tiene una parte cliente y otra servidora.

Las relaciones se pueden derivar analizando los verbos de los enunciados. Se aíslan verbos que sugieren:

- localizaciones físicas (cerca de, contenido en)

- comunicaciones (obtenido de, transmite)

- propiedad (se compone, incorporado por)

- cumple una condición (coordina, controla, dirige)

Una vez definidas las relaciones se conectan los objetos a través de relaciones con nombres y especificando la cardinalidad.

Para obtener el modelo se usan las tarjetas CRC, Si existe alguna relación se dibujan los objetos conectados por líneas, para evitar ambigüedades se pune una punta de flecha apuntando del cliente al servidor. Finalmente se evalúa la cardinalidad de cada extremo.

El esquema en UML quedaría de este estilo.