Tutorial. Diseño de bases de datos con MySQL Workbench

A partir del diagrama creado en MySQL Workbench es posible obtener el código SQL para la inicialización de la base de datos. Dicho script podrá ser ejecutado contra un servidor MySQL para crear una base de datos MySQL. Para generar dicho script, seleccionamos la opción File | Export | Forward Engineer SQL CREATE Script…​ del menú principal. A continuación, se nos presentará un asistente en el que podremos seleccionar las opciones de exportación y los objetos a incluir en el script SQL. En la ventana de generación del script SQL podremos elegir un nombre para el archivo y activar distintas opciones, tal y como muestra la figura siguiente. Por ejemplo, en la figura se han marcado las opciones para eliminar las tablas antes de ser creadas siempre que existiesen previamente, de forma que la base de datos será creada o inicializada cuando se ejecute este script.

A la hora de crear el script podremos indicar para qué objetos queremos que se cree código SQL. Entre estos objetos están las tablas, vistas y triggers del modelo Workbench con el que estamos trabajando.

Finalmente, nos aparecerá el código SQL para la creación de los objetos que hayamos seleccionado tal y como muestra la figura siguiente. Dicho script lo podremos utilizar para crear la base de datos en cualquier servidor MySQL al que tengamos acceso.

Si tenemos definida una conexión a un servidor MySQL es posible crear directamente la base de datos en el servidor a partir del modelo definido sin tener que pasar por el proceso de generar el script SQL y ejecutarlo posteriormente en un servidor MySQL. Además, esta opción ofrece la ventaja de que los cambios que realicemos en el modelo en una operación de modificación de esquemas podrán ser trasladados directamente a la base de datos actualizándola de acuerdo con los cambios introducidos en el modelo. Este proceso de realizar cambios en el esquema Workbench y llevarlos directamente a la base de dato es el que se conoce como ingeniería directa.

El proceso de llevar los cambios directamente desde el modelo a la base de datos es similar al proceso indirecto (vía el script SQL). La diferencia es que se añade un paso al inicio en el que hay que indicar los datos de la conexión a la base de datos en la que queremos realizar los cambios. Para ello, seleccionamos la opción Database | Forward Engineer…​ del menú principal. A continuación, se nos presentará un asistente en el que comenzaremos indicando los datos de conexión al servidor MySQL en el que queremos crear la base de datos. La figura siguiente ilustra la pantalla de ese paso en el asistente. Ahí seleccionamos una conexión definida previamente, o bien especificamos los datos de conexión directamente.

Una vez creada la base de datos en el servidor MySQL podríamos ver los objetos creados. Para ello, desde la pestaña de inicio del modelo Workbench hay que establecer una conexión con la base de datos. Una vez abierta la conexión podremos ver los distintos objetos creados. La figura siguiente ilustra una captura de una base de datos generada desde un diagrama Workbench mostrando las distintas tablas creadas.

Otra función interesante de MySQL Workbench es la posibilidad de crear un diagrama Workbench a partir de los objetos existentes en una base de datos MySQL a la que tengamos acceso. Este proceso, conocido como ingeniería inversa, importará tablas y vistas definidas en la base de datos, y podrá crear relaciones entre ellas siempre que haya definidas claves ajenas en las tablas de la base de datos. Una vez importados los objetos del diagrama, sólo tendremos que reorganizarlos en la zona de trabajo. Posteriormente, podremos realizar cambios sobre el modelo y trasladarlos a la base de datos. Al igual que en la ingeniería directa, necesitamos tener los datos de conexión al servidor al que nos vamos a conectar para importar los objetos.

Para realizar la ingeniería inversa, seleccionamos la opción Database | Reverse Engineer…​ del menú principal. A continuación, se nos presentará un asistente en el que comenzaremos indicando los datos de conexión al servidor MySQL en el que queremos importar los objetos. La figura siguiente ilustra la pantalla de ese paso en el asistente. Después de haber especificado los parámetros de conexión, debemos indicar desde qué esquema queremos importar los objetos. La figura siguiente ilustra la selección de un esquema de artículos deportivos.

Una vez seleccionada la base de datos, podremos continuar y tras un informe del progreso nos permitirá seleccionar qué objetos queremos importar. De forma predeterminada, se importarán todos los objetos existentes en la base de datos. No obstante, se puede indicar al asistente que excluya algunos objetos de la importación. La figura siguiente ilustra una captura del asistente en el que se pueden excluir algunos objetos de importación.

La figura siguiente ilustra los objetos importados al diagrama. Si las tablas importadas de la base de datos tienen claves ajenas definidas, éstas se representarán en el diagrama mediante relaciones de identificación o no identificación, según corresponda.

Siguiendo estas buenas prácticas, se puede diseñar una base de datos eficiente, fácil de mantener para satisfacer las necesidades de una aplicación.

Nos han encargado crear una base de datos relacional para gestionar los atletas y sus resultados en las competiciones oficiales de atletismo. Contamos con los datos siguientes:

A partir de la descripción del problema, obtenemos lo siguiente:

A partir de esta descripción, podemos crear un diagrama en MySQL Workbench que represente la estructura de la base de datos.

Para ilustrar algunos de los puntos importantes, mostraremos algunas capturas de pantalla del proceso de diseño de la base de datos en MySQL Workbench.

Comenzamos creando una tabla para el conjunto de entidades Atleta con todos sus atributos. Como el atributo sponsor es multivaluado, lo almacenamos en una tabla independiente y creamos una relación 1:M de identificación entre Atleta y Sponsor. La relación será de identificación porque la clave primaria de Atleta se trasladará a Sponsor y formará parte de la clave primaria de Sponsor. En cuanto a la cardinalidad mínima, la establecemos a 0 para permitir que un atleta no tenga sponsors. Para cambiar esa cardinalidad y hacer que sea mínima, editamos la relación y desactivamos la casilla Mandatory. Para crear la relación 1:M de identificación, primero seleccionamos la tabla Sponsor (parte M) y después la tabla Atleta (parte 1). La figura siguiente muestra la creación de la tabla Atleta y la relación con la tabla Sponsor.

A continuación creamos la tabla para País y establecemos las relaciones con Atleta. Inicialmente, crearemos dos relaciones de no identificación, una para la relación NacidoEn y otra para la relación FederadoEn. Se trata de relaciones de no identificación porque no queremos que la clave primaria de País forme parte de la clave primaria de Atleta. Para crear la relación de no identificación, primero seleccionamos la tabla Atleta (parte M) y después la tabla País (parte 1). En cuanto a la cardinalidad mínima de la relación, por un lado la establecemos a 1 con País para indicar que un atleta debe estar federado en un país y haber nacido en un país. Por otro lado, la establecemos a 0 con Atleta para permitir que un país no tenga atletas federados o nacidos en él. La figura siguiente muestra la creación de la tabla País y las relaciones con la tabla Atleta.

Otra parte importante de la creación de este diagrama es la relacionada con el conjunto de entidades débiles Record. En este caso, la relación entre Record y País es de identificación, ya que la clave primaria de País se trasladará a Record y formará parte de la clave primaria de Record. Para la creación de la relación 1:M de identificación, primero seleccionamos la tabla Record (conjunto de entidades débiles al que le llegará la clave y está en el extremo M) y después la tabla País (conjunto de entidades fuertes del que depende y está en el extremo 1). En cuanto a la cardinalidad mínima, la establecemos a 1 con País para indicar que un record debe estar asociado a un país y a 0 con Record para permitir que un país no tenga records asociados. La figura siguiente muestra la creación de la tabla Record y la relación con la tabla País.

Para ilustrar la creación de una relación M:N ilustraremos la relación entre Atleta y Campeonato. En este caso, la relación es de M:N porque un atleta puede estar inscrito en varios campeonatos y un campeonato puede tener varios atletas inscritos. Para crear la relación M:N, el orden en este caso no es importante. Como consecuencia de la relación M:N, se creará una tabla de relación InscritoEn que contendrá las claves primarias de Atleta y Campeonato. Como esta relación además tiene atributos descriptivos (atributos de relación), en este caso el dorsal, éste se añadirá como columna en la tabla de relación. Desde el punto de vista de la cardinalidad mínima a la tabla de relación, la establecemos a 0 indicando que un atleta no tiene por qué estar inscrito en un campeonato y un campeonato no tiene por qué tener atletas inscritos. La figura siguiente muestra la creación de la relación M:N entre las tablas Atleta y Campeonato.

Así, continuamos creando las tablas y relaciones necesarias para el resto de conjuntos de entidades y relaciones. La figura siguiente muestra el diagrama completo obtenido en MySQL Workbench.

Una vez creado el diagrama en MySQL Workbench, podemos generar el script SQL para la creación de la base de datos. Otra opción es crear directamente la base de datos en un servidor MySQL a partir del diagrama. Sin embargo, antes de continuar debemos hacer un par de operaciones.

En primer lugar, hay que cambiar el nombre del esquema, ya que por defecto es mydb. Para cambiar el nombre del esquema, nos situamos en la pestaña MySQL Model, y en la sección Physical Schemas cambiamos el nombre del esquema haciendo doble clic sobre el nombre de esquema actual (mydb). El nuevo nombre del esquema será CompeticionesAtletismo. La figura siguiente muestra el cambio de nombre del esquema. Si no se cambia el nombre del esquema, el script SQL generará la base de datos con el nombre mydb en lugar de CompeticionesAtletismo.

Otra operación a realizar es sobre la configuración de MySQL Workbench, ya que pueden producirse problemas en la generación del código SQL o en la operacion de creación directa de la base de datos. El problema tiene su origen en que el código SQL que genera (y que intenta ejecutar en el servidor MySQL si nos decantamos por esta opción) incluye una opción en los índices relativos a su visibilidad. Esta opción no es soportada por versiones de MySQL anteriores a la 8.0, ni por MariaDB. Para evitar este tipo de problemas, indicaremos que la versión de MySQL de destino es la 5.7, una de las versiones más populares. Esto hará que el código generado sea compatible con versiones anteriores de MySQL y con MariaDB. Para ello, seleccionamos la opción Edit | Preferences del menú principal. A continuación, seleccionamos la opción Modeling y en la sección MySQL cambiamos la versión de MySQL a 5.7. La figura siguiente muestra la configuración de MySQL Workbench para la versión 5.7.

A partir del diagrama de MySQL Workbench, podemos generar el script SQL para la creación de la base de datos. Para ello, seleccionamos la opción File | Export | Forward Engineer SQL CREATE Script…​ del menú principal. A continuación, seleccionamos las opciones de exportación (p.e. si se quiere eliminar el esquema previo, si se desea omitir la creación de claves ajenas, …​). La figura siguiente ilustra estas opciones y muestra que no activamos ninguna en este ejemplo. Especificaremos un nombre de archivo con las instrucciones SQL (p.e. CompeticionesAtletismo.sql).

En el paso siguiente seleccionamos los objetos a incluir en el script SQL. En este caso, seleccionamos todas las tablas del diagrama. La figura siguiente muestra la selección de los objetos a incluir en el script SQL.

Finalmente, obtenemos el script SQL que podemos ejecutar en un servidor MySQL para crear la base de datos.

Si tenemos una conexión a un servidor MySQL, podemos crear directamente la base de datos a partir del diagrama en MySQL Workbench. Para ello, y tras haber cambiado el nombre del esquema a CompeticionesAtletismo (ver principio de la sección), seleccionamos la opción Database → Forward Engineer…​ del menú principal. A continuación, seleccionamos la conexión a la base de datos y las opciones de exportación. Aparecerá el script SQL que se ejecutará en el servidor MySQL si continuamos adelante. Finalmente, MySQL Workbench creará la base de datos en el servidor MySQL con los objetos definidos en el diagrama. La figura siguiente muestra el paso de selección de la conexión a la base de datos.

Tras esta opción se creará la base de datos en el servidor MySQL informando del éxito. La figura siguiente muestra el progreso satisfactorio de la creación de la base de datos.

Si ahora abirmos una conexión a la base de datos desde pantalla de inicio de MySQL Workbench, podremos ver los objetos creados. La figura siguiente muestra una captura de la base de datos creada en el servidor MySQL.

Comenzaremos el proceso de migración de la base de datos creando una entrada DSN para la base de datos de origen. Todo el proceso lo realizaremos mediante el Asistente de migración de MySQL Workbench. Para ello, seleccionamos la opción Database | Migration Wizard …​ del menú principal. Esto iniciará el asistente, que se muestra en la figura siguiente.

En el primer paso crearemos el DSN, invocando al Administrador de origen de datos ODBC (64 bits) de Windows pulsando el botón Open ODBC Administrator en la parte inferior del asistente. Un DSN (Data Source Name) es una entrada de configuración que especifica la información necesaria para conectarse a una base de datos a través de un driver ODBC. En el caso de Microsoft Access, necesitaremos crear una entrada DSN para la base de datos de origen Academica.accdb. Para ello, desde el Administrador de origen de datos ODBC (64 bitd) de Windows de la figura siguiente seleccionamos la pestaña DSN de usuario y hacemos clic en el botón Agregar…​.

A continuación, seleccionamos el driver ODBC de Microsoft Access y hacemos clic en el botón Finalizar.

En la ventana de configuración de la entrada DSN, especificamos un nombre para la entrada DSN (p.e. Academica) y la ruta al archivo de la base de datos de origen, tal y como muestra la figura siguiente.

Tras esto, la entrada DSN para la base de datos de origen estará creada, como muestra la figura siguiente. A partir de ahora, podremos utilizarla para conectarnos a la base de datos de origen desde cualquier aplicación que soporte ODBC. En nuestro caso, la utilizaremos para la migración de la base de datos de origen a MySQL con MySQL Workbench.

Una vez creada la entrada DSN para la base de datos de origen, volvemos al asistente de migración de MySQL Workbench para comenzar con el proceso en sí de migración de la base de datos. Comenzamos seleccionado la configuración de la base de datos de origen. En este caso consiste en la selección del SGBD de origen (Microsoft Access), del método de conexión (ODBC Data Source) y de la entrada DSN que acabamos de crear (Academica), tal y como muestra la figura siguiente.

En el paso siguiente seleccionamos los parámetros de conexión del destino, en este caso un servidor MySQL. Para ello, seleccionamos el método de conexión (TCP/IP), el host (p.e. localhost), el puerto (p.e. 3306), el usuario y la contraseña, tal y como muestra la figura siguiente. Si ya tuviéramos creada previamente una conexión a un servidor MySQL, podríamos seleccionarla en lugar de especificar los parámetros de conexión. La figura siguiente muestra la configuración del destino.

A continuación, el asistente se conectará al sistema de origen, comprobará la conexión al sistema de destino y recuperará los objetos de la base de datos de origen. Si todo es correcto podremos continuar al paso siguiente en el que se obtendrán los objetos del sistema de destino, por si hubiera que realizar alguna adaptación. La figura siguiente muestra el resultado. A pesar de informar de un error, éste no es relevante y podemos continuar.

En el paso siguiente, seleccionamos los objetos que queremos migrar. En este caso, seleccionamos todas las tablas de la base de datos de origen. La figura siguiente muestra la selección de los objetos a migrar.

Finalmente, el asistente generará el script SQL para la creación de la base de datos en MySQL y el asistente informará del proceso (pasos Migration y Manual Editing del asistente). En un paso posterior (Target Creation Options) se nos permitirá seleccionar las opciones de creación de la base de datos en MySQL (p.e. si se quiere crear un esquema nuevo, si se desea generar un archivo SQL, …​). La figura siguiente muestra la opciones de creación de la base de datos en MySQL.

Tras esto, el asistente iniciará el proceso de migración de la base de datos de origen a MySQL. Una vez finalizado, se mostrará un informe con el resultado de la migración. La figura siguiente muestra el informe del progreso.

En el paso siguiente el asistente nos muestra todos los objetos que ha creado en el destino e informa que se han llevado a cabo con éxito. La figura siguiente muestra el resultado de la migración.

Finalmente, mostrará algunos pasos más informando que todo ha ido bien. Tras esto, podremos conectarnos a la base de datos de destino y ver los objetos creados. La figura siguiente muestra una captura de la base de datos creada en el servidor MySQL.

Tras realizar la migración, sería interesante obtener un diagrama de la base de datos de destino. Para ello, seleccionamos la opción Database | Reverse Engineer…​ del menú principal. A continuación, seleccionamos la conexión a la base de datos de destino y las opciones de importación. Se creará un diagrama de la base de datos de destino con todas las tablas que lo forman. Podemos apreciar que las tablas fueron llevadas correctamente de la base de datos MySQL al modelo. Sin embargo, en este caso no se crearon las relaciones entre las tablas. Esto se debe posiblemente a que las tablas de la base de datos de origen no tenían claves ajenas definidas. La figura siguiente muestra el diagrama de la base de datos de destino.

Para crear las relaciones entre las tablas, podemos hacerlo de forma manual utilizando el botón de creación de relaciones a partir de columnas existentes. En este proceso de creación de relaciones, primero seleccionaremos las columnas de la clave ajena y después seleccionaremos las columnas de la clave primaria a la que hacen referencia. Tras realizar este proceso, obtendremos un diagrama de la base de datos de destino con las relaciones entre las tablas. La figura siguiente muestra el diagrama de la base de datos de destino con las relaciones creadas.

Una vez creado el diagrama de la base de datos de destino, sería interesante sincronizarlo con la base de datos de destino para aplicar los cambios realizados en el diagrama a la base de datos. Este proceso es necesario porque hasta ahora se han realizado una serie de cambios en el modelo añadiendo las relaciones entre las tablas, y por tanto añadiendo las restricciones de clave ajena. Sin embargo, esta modificación ha ocurrido en el modelo pero no se ha trasladado a la base de datos de destino. Para ello realizaremos lo que se conoce como sincronización de la base de datos. El proceso de sincronización, como su nombre indica, sincroniza el modelo de la base de datos con la base de datos de destino. Es decir, por un lado, aplica los cambios realizados en el diagrama a la base de datos de destino; y por otro lado, si se han realizado cambios en la base de datos de destino, los aplica al diagrama.

Para ello, con el modelo abierto, seleccionamos la opción Database | Synchronize Model…​ del menú principal. A continuación, seleccionamos la conexión a la base de datos de destino y las opciones de sincronización. Se aplicarán los cambios realizados en el diagrama a la base de datos de destino. La figura siguiente muestra el proceso de sincronización a realizar sobre la base de datos de destino (en concreto, en las tablas Estudia, Imparte y Personal).

Tras la sincronización, se mostrará una previsualización del script SQL generado para aplicar los cambios en la base de datos de destino. El script incluirá las instrucciones SQL para añadir las claves ajenas que faltan en la base de datos de destino. La figura siguiente muestra el script SQL generado.

Finalmente, se aplicarán los cambios en la base de datos de destino. Tras esto, la base de datos de destino estará sincronizada con el diagrama y se habrán aplicado las relaciones entre las tablas. Si ahora comprobamos las propiedades de alguna de las claves ajenas de la base de datos de destino, veremos que se han creado correctamente. La figura siguiente muestra las propiedades de una clave ajena de la base de datos de destino.

Esto finalizaría el proceso de migración de una base de datos de Microsoft Access a MySQL y la sincronización de la base de datos de destino con el diagrama. Ahora se contaría con la base de datos en MySQL, el modelo de la base de datos y la base de datos actualizada con las relaciones entre las tablas. Y tanto el modelo como la base de datos estarían sincronizados.