Usando MySQL Workbench Para Diseñar Y Crear Una Base De Datos Archivado en: mysql — Etiquetas: bases de datos, mysql, workbench — vramirez @ 8:15 pm La compra de MySQL AB por parte de Sun Microsystems a comienzos de este año, es sin lugar a dudas un reconocimiento al buen trabajo hecho por la comunidad de MySQL, para llevar a esta base de datos a ser una de las mejores bases de datos open source que se encuentre en el mercado, y que hoy por hoy es la base de datos predilecta por los desarrolladores, e incluso grandes empresas como Suzuki, Sagem, o la misma Adobe o sitios como Yahoo! Finance, para bases de datos transaccionales o bodegas de datos ( Data Warehousing)).MySQL provee entre sus herramientas, una denominada MySQL Workbench Warehousing la cual nos permite desde una ambiente gráfico, diseñar un modelo E-R de una base de datos y luego crear la base de datos, como tal en una base de datos MySQL. Para este tutorial es necesario tener instalado:
MySQL 5.x o superior
MySQL Workbench 5.x.x
NOTA: En esta demo, solo se mostrará el funcionamiento de MySQL Workbench, mas no la instalación de éste ni la de MySQL Server. El servidor mysql puede estar instalado en cualquier equipo y/o sistema operativo soportado (Windows, Linux, Aix, etc). MySQL Workbench solo se encuentra disponible para Windows. En el ejemplo crearemos una base de datos muy sencilla, la cual no representa un ejemplo real. Simplemente es para probar las características. Primero creemos el esquema “test”, haciendo click en el símbolo “+”. Abajo se abre una pestaña en que nos pregunta el nombre del esquema (test) y el idioma del esquema, el cual se recomienda dejar por default como se muestra en la figura. Si la aplicación pregunta si desean renombrar todos los objetos, seleccionen “Si”.
Ya creado el esquema, procedemos a crear las tablas. Existen dos formas, aunque muy similares de crearlas. La primera es desde la pantalla en la que estamos, la otra es mediante la ventana visual, en la cual podremos además, realizar las relaciones entre las tablas.
Pero
primero
lo
primero;
las
tablas.
Verifica que estes en el esquema “test” y luego haz doble click en el icono de “Add Table”.
Al hacer click en esta opción, se despliega al lado un icono representando la nueva tabla, mientras que debajo se abre una nueva pestaña, en la cual podemos editar todo al respecto de las tablas haciendo (Columnas, llaves, triggers, etc) click en cada una de las pestañas ubicadas en la parte inferior de esta nueva pestaña. Nota: los triggers solo estan disponibles a partir de MySQL 5. Cualquier tabla con triggers generada en Workbench arrojará un error cuando se corra en una versión i nferior a MySQL 5.0 Agregaremos un par de columnas a la tabla:
Al hacer click en la pestaña “Columns”, podemos editar todas las columnas dela tabla indicada. Podemos alterar el nombre, el tipo de dato, si es nulo o no (NN) y si es Auto incremental (AI), obviamente este último es sólo para datos de tipo numérico, De igual forma podemos especificar un valor por default para la columna. En la parte derecha, inmediatamente podemos indicar si la columna seleccionada hace parte de la llave primaria (PRIMARY KEY), si es UNSIGNED o ZEROFILL para las variables numéricas y si la variable es de tipo caracter, podemos especificar si es BINARY.
Ahora pasemos a crear una tabla por medio de un diagrama E-R. En la parte superior observamos la opción “Add Diagram”. Hacemos doble click sobre ella.
Luego se abre una nueva pestaña en la cual encontramos un grid. A la izquierda, ecnontramos una barra de herramientas, desde la cual podremos crear tablas y vistas, además de relaciones (1:1,1:n,n:m…) y a la derecha un árbol con los objetos de nuestro esquema (tablas, vistas, etc). De esa ventana, seleccionamos la tabla que creamos y la arrastramos hasta el grid. Luego hacemos click derecho y seleccionamos “Edit table”, abajo nos muestra la pestaña para editar la tabla y crear columnas, ll aves, etc.
Buscamos a la izquierda el ícono que nos permite crear una nuev a tabla. Hacemos un click en el ícono y luego un click en el grid.
Editamos las segunda tabla y ahora haremos una relación much os a uno.Seleccionamos el icono, y luego cada una de las tablas (primero la de muchos y luego la de uno). En mi caso, la tabla de “muchos” es
table1.
Quedaría algo asi:
Como ven el Workbench se apega a la teoría relacional, donde en este caso la llave de a relación “uno” pasa a la tabla de “muchos”. Igualmente si creamos una relacion M.N (muchos a muchos) generará una relación entre las dos tablas. A continuación viene la mejor parte de esta herramienta: la generación del script SQL.
Como ven, podemos exportar el diseño, incluso como una imagen en pdf, o en png, entre otros. La primera pantalla, nos muestra las opciones con las que queremos que se genere el script DDL. Luego seleccionamos la ubicacion destino del archivo que se creará.
Luego nos muestra un resumen de lo que creará; usuarios, tablas, vis tas, rutinas y triggers. Incluso si seleccionamos el botón “Detailed Selection” de cada sección, podemos especificar aún más, por ejemplo, qué tablas deseo realmente exportar.
Luego finalizar, y en la ruta que le hallamos indicado estará un archivo .sql, con nuestro script. Como se podrán dar cuenta Workbench, resuelve algunas limitantes en la comunidad open source de un buen software front-end para MySQL, y sobre todo de una herramienta que provea Forward Engineering, de una manera tan sencilla y bien ilustrada. Digamos que la única limitante de Worlbench es su ausen cia en otras plataformas, o sea, en Linux :p.
¿Qué es MySQL WorkBench? MySQL Workbench es una aplicación para el diseño y documentación de bases de datos (sucesora de la aplicación DBDesigner4) pensada para ser usada con el sistema de gestión de bases de datos MySQL (recién adquirido por Sun Microsystems). Existen dos versiones del producto, una es open source y la otra es una versión comercial. Evidentemente, la versión comercial proporciona algunas funcionalidades que pueden resultar de interés en algún ámbito, aunque la versión open source es más que suficiente para la realización de la práctica.
¿Cómo conseguir la aplicación? Existen versiones para Window, Linux y Mac. Hay que tener en cuenta que para los dos últimos sistemas las versiones son Betas. Los links de descarga son los siguientes: Windows y Linux: http://dev.mysql.com/downloads/workbench/5.1.html Mac: http://dev.mysql.com/workbench/?p=182
¿Para qué la usaremos ? Lo que se pretende con el uso de la aplicación es facilitar la realización de la segunda práctica de la asignatura y, por este motivo, este mini-tutorial solo se centra en las caracterísicas necesarias para la realización de la misma. La herramienta podría usarse para realizar un diagrama EER, y esa es su principal función: primero diseñar el diagrama EER, implementándolo sobre la herramienta y a partir de él obtener el diagrama del esquema relacional y también las sentencias de creación de tablas, vistas e índices de manera automática. Como tiene varias limitaciones para representar un diagrama EER completo (no contempla el modelado de categorías, jerarquías, retículas...), y el diagrama EER completo ya se ha realizado en la primera práctica, se utilizará para representar el diagrama del esquema relacional que acompaña a la segunda práctica, o sea, las tablas y sus columnas, incluyendo las claves primarias, las claves foráneas y a quienes referencian, y todo ello mediante una interface gráfica que permitirá además generar las sentencias DDL de creación de tablas e índices, que, con algunas modificaciones, podrán adaptarse a lo solicitado en la práctica. Las posibilidades de utilizar diferentes notaciones nos permitirán obtener un diagrama del esquema relacional habitual en cualquier trabajo profesional, aunque para ello debemos ajustar la herramienta a nuestras necesidades, como se indica a continuación y sobre todo en el punto Normas para la entrega de la práctica, al final de este documento. Debemos tener presente en todo momento que vamos a añadir tablas y sus propiedades, y obtener un diagrama del esquema relacional, y para ello usamos una herramienta que realmente está orientada a crear diagramas ER limitados, o sea, forzamos un poco las cosas, pero la herramienta lo permite. Para ello debemos prescindir de los conceptos del modelado conceptual y centrarnos en los del modelo relacional, aunque en la herramienta
trabajemos sobre el panel 'EER Diagrams'. Así en el modelo relacional la vinculación entre dos tablas suele ser 1 a N y en algún caso 1 a 1, pero nunca N a N.
¿Cómo crear un diagrama del esquema relacional? Una vez abierta la aplicación, veremos algo similar a esto:
Figura 1 Para crear el diagrama del esquema relacional necesario en la práctica, debemos hacer doble click sobre el icono 'Add Diagram', como si lo que creásemos fuera un nuevo diagrama ER. Esto nos conducirá al siguiente interfaz:
Figura 2
¿Cómo crear una tabla (una relación en el modelo relacional)? 1.- Click sobre el incono Insertar Tabla. 2.- Click en la posición del lienzo en la que queremos ver la tabla. Obtenemos lo siguiente:
Figura 3 3.- Haciendo doble click sobre la tabla se desplegará un menú en la parte inferior del interfaz.
Figura 4 En la pestaña 'Table', en el campo 'Name' se indicará el nombre de la tabla. Los campos 'Collation' y 'Engine' son relativos a MySQL y pueden obviarse.
¿Cómo crear los atributos de una relación? Partiendo de la Figura 4, desplegamos la pestaña Columns:
Figura 5
Column Name: nombre del atributo. Datatype: tipo de dato del atributo. Como la aplicación está pensada para MySQL y la práctica se realizará sobre Oracle, puede que existan tipos de datos válidos para Oracle que no lo son para el otro gestor. En este caso se eligirá un tipo de dato similar y se cambiará a mano en el script con las sentencias. Se recomienda ser lo más coherente posible al realizar esta tarea, es decir, para cada tipo de dato no disponible en MySQL, elegir siempre el mismo tipo de dato de sustitución. NN: añade la restricción NOT NULL para ese atributo. AI: Auto Increment. No se debe marcar ya no es una caracterísitca de Oracle. Default: valor por defecto para el atributo. ColumnDetails.Flags: se utiliza para añadir la restricción de clave primaria (PRIMARY KEY).
Para añadir una nueva columna solo es necesario hacer doble click en la fila que va a continuación de la última añadida (señalada con un punto rojo en la imagen).
¿Cómo indicar que uno o varios atributos son clave primaria? 1.- Hacer doble click sobre la tabla en cuestión. 2.- Abrir la pestaña 'Columns'. 3.- Seleccionar la columna que se desea utilizar como clave primaria. 4.- Marcar la opción PRIMARY KEY. 5.- Si se desea definir una clave primaria sobre más de un atributo volver al punto 3.- . NOTA: la aplicación no permite darle un nombre a la restricción de clave primaria. Esto debe hacerse manualmente sobre los scripts generados ( si así se desea).
¿Cómo se pueden declarar las vinculaciones de clave foránea?
A continuación se muestra el menú para crear los tipos de relación (1:1, 1:N y N:M) en un ER. En la práctica se usarán solo los 1:N y 1:N, pues son las vinculaciones que hay entre tablas en el modelo relacional:
Figura 6 El calificativo 'identificadora' indica si los atributos que forman parte de la clave foránea (lado N de la relación) deben formar parte también de la clave primaria de dicha entidad, lo que ocurre si una tabla proviene de un tipo de entidad débil o en el caso de atributos de tablas que provienen de tipos de relación N:M. Existen, al menos, dos formas diferentes de crear relaciones entre tablas: a través del menú de tabla o usando el el menú antes propuesto. a)A través del menú de tabla (recomendado): 1.- Doble click sobre la entidad del lado N de la relación. 2.- Crear los atributos que van a hacer la función de clave foránea ( si no están definidos ya). 3.- Comprobar que existen los atributos en la tabla referenciada por la clave foránea. Si no existen deben crearse antes de continuar. 4.- En el menú de tabla , desplegar la pestaña 'Foreing Keys'. Obtendremos lo siguiente:
Figura 7 Foreing Key Name: nombre de la restricción de clave foránea. Referenced Table: tabla referenciada por la clave foránea. Column: columna o columnas que van a formar parte de la clave foránea. Referenced Column: columna o columnas que van a ser referenciadas por la clave foránea. Foreing Key Options: útil para definir las acciones referenciales. On Update: acciones referenciales para la actualización. On Delete: acciones referenciales para el borrado.
b)Usando el menú: 1.- Las tablas deben estar creadas. 2.- Se elige en el menú de la izquierda el tipo de relación que se desea. 3.- Click en la tabla que representa el lado N de la relación y luego sobre la del lado 1 (esto puede ser al revés dependiendo del sistema operativo).
4.- Los retoques que se deseen hacer sobre la clave foránea se hacen siguiendo el apartado 4.- del punto a).
¿Cómo se gestionan los índices? Para crear un índice haremos doble click sobre la tabla que contiene los atributos y, una vez en el menú de tabla, desplegaramos la pestaña 'Indexes'.
Figura 8 Index Name: nombre del índice. Type: en el menú desplegable elegiremos 'INDEX'. Column: marcamos las columnas que formaran parte del índice. Order: índice ascendente o descendente. Index Options. Storage Type: podemos elegir entre BTREE, RTREE, HASH o ninguno de los anteriores. NOTA: la aplicación crea índices automáticamente tanto para las claves primarias como para las foráneas.
¿Cómo se crea una vista?
En la Figura 2 se indica el botón sobre el que hay que hacer click para insertar una vista. Una vez colocada en el lienzo, si hacemos doble click sobre ella obtenemos lo siguiente:
Figura 9 Name: nombre de la vista. DDL: sentencia SQL para la creación de la vista.
¿Cómo se insertan los datos en las tablas? 1. 2. 3. 4.
Doble click sobre la tabla en cuestión. Desplegar la pestaña 'Inserts' del menú de tabla. Click sobre 'Open Editor...' Añadir tantas filas como se deseen.
¿Cómo se generan las sentencias SQL? Para generar las sentencias SQL (motivación principal para el uso de la herramienta) realizar los siguientes pasos: 1.- Elegir la opción 'Forward Engineer SQL CREATE Script...'
Figura 10 2.- Marcar las siguientes opciones (Generate INSERT si queremos que genere los INSERT) y elegir un fichero para volcar las sentencias (debe ser un script de SQL, es decir, con las extensión .sqll).
Figura 11 3.- En la siguiente pantalla seleccionar 'Finish' . 4.- Abrir el fichero *.sql generado y retocar las sentencias SQL para adaptarlas al sistema de gestión de base de datos usado en el laboratorio de prácticas. Ejemplo práctico Con este ejemplo se pretende hacer una pequeña demostración del uso de la herramienta sobre un caso práctico y de sobra conocido: la base de datos que se utiliza en clase de prácticas. Las sentencias de creación de las tablas, es decir, el reflejo relacional del diagrama entidad relación, son las siguientes (nótese que el uso que le queremos dar a la herramienta es precisamente obtener las sentencias de creación de tablas pero, para realizar el ejemplo, debemos partir de un esquema relacional (en vuestro caso será el que hayáis propuesto en la primera entrega de la práctica)): CREATE TABLE DEPT ( DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY, DNAME VARCHAR2(14) , LOC VARCHAR2(13) ) ;
CREATE
TABLE EMP (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
Lo primero que vamos a hacer es crear la table DEPT. Para ello haremos lo siguiente: 1.- Click en el icono señalado con la flecha (inserción tabla) y luego click sobre el lienzo. Para editar las propiedades de la tabla hacer doble click sobre la misma.
Figura 12 2.- Añadir los atributos a la tabla. 2.1.- En la pestaña 'Table' cambiamos 'table1' por el nombre 'DEPT'. 2.2.- En la pestaña 'Columns' añadimos una a una las columnas de la tabla:
Figura 13 Nótese que hemos indicado que la columna DEPTO es clave primaria (al indicar que es clave primaria el checkbox de NN (Not Null) se marca automáticamente). Como dijimos con anterioridad, la aplicación no permite nombrar las restricciones de clave primaria, esto ha de hacer se con posterioridad, sobre el script de creación de tablas y a mano. Además, como la aplicación no contempla la representación del tipo de dato NUMBER, hemos elegido el INT como tipo de dato de sustitución. Es necesario acordarse de los tipos de sustitución utilizados para cambiarlos posteriormente en el script generado. A continuación repetiremos el proceso pero para crear la tabla EMP. 1.- Creamos la tabla, añadimos las columnas e indicamos la restricción de clave primaria. El resultado es el siguiente:
Figura 14
2.- Para añadir la restricción de clave foránea en la tabla EMP tenemos dos opciones: Opción A: 2.1.- Añadir una columna más a la tabla con el nombre de DEPT. 2.2.- Haciendo doble click sobre la tabla EMP y seleccionando la pestaña 'Foreing keys'. Indicaremos el nombre de la restricción (FK_DEPTNO), la tabla a la cual hace referencia dicha clave (DEPT).
Figura 15
2.3.- Indicamos cual es la/las columnas que forman la clave marcando los checkboxes necesarios en Column (en este caso DEPT) e indicaremos la columna de la tabla EMP al a cual referenciamos (DEPTNO).
Figura 16 En la sentencias de creación de tablas del ejemplo no se indican las acciones referenciales para la clave foránea. Opción B: 2.1.- Seleccionar en el menú de la izquierda lo que se indica con una flecha en el dibujo y hacer click, primero sobre la tabla EMP y luego sobre la DEPTO. El resultado es el que sigue:
Figura 17 2.2.- Continuamos en el punto 2.3 de la Opción A eligiendo los nombres adecuados para cada columna (cambiar DEPT_DEPTO por DEPT).
El resultado, para ambos casos es el siguiente:
Figura 18
Normas para la entrega de la práctica El ejemplo anterior se realizó con las opciones defectivas de la herramienta, por ejemplo con la notación gráfica conocida como 'Crow's foot' o la generación autoática del nombre del atributo que será clave foránea. Para una mejor presentación de la práctica, se realizán los siguientes ajustes: 1. Elegir 'Model > Object Notation > Workbench (Simplified)' para que no se incluyan los índices en el diagrama. 2. Elegir 'Model > Relationship Notation > Connect to Columns' para que las conexiones se representen claramente entre los atributos que las poseen. 3. Para que no aparezcan los tipos de datos en el diagrama, debe irse a 'Model > Model Options', desactivar 'Use Global Settings' y en 'Diagrams' desactivar 'Show Columns Types'. En una visualización posterior, el diagrama ya apareceá sin los tipos de datos. 4. Debe entenderse que lo que aparece en el panel ''EER Diagrams' son dibujos activos, pero que una cosa es que en un diagrama del esquema relacional no aparezca una tabla y que la tabla exista en la base de datos, lo que la herramienta denomina 'Catalog', situado en el panel de la derecha. Por ello, al eliminar una tabla de un diagrama con 'Botón derecho > Delete', pregunta si se desea eliminar el correspondiente objeto de la base de datos al eliminar la figura. Puede eliminarse una figura y mantener la tabla que representaba. Una tabla existente en 'Catalog' puede incorporarse a un diagrama seleccionándola y arrastrándola al diagrama. De esta forma el diagrama quedaría semejante a:
Figura 19
Esta es la manera en la que hay que entregar el diagrama del esquema relacional en la práctica, en formato PDF. Obsérvese que están diferenciadas las claves primarias (una llave), las foráneas (rombo rojo), los atributos normales (rombo azul) y los atributos no nulos (rombo relleno de color azul). Asimismo está diferenciadas las vinculaciones de clave foránea normales (líneas discontinuas), de las que la clave foránea forma parte de la clave primaria (líneas continuas). Trabajando sobre Ubuntu 8.10 (existe un paquete deb que instala la versión 5.1.9 Beta) se han detectado algunos comportamientos de la herramienta que se indican por orientación y para evitar pérdida de tiempo (no se comprobó su funcionamiento en otros sistemas operativos): 1. Al abrir un diagrama previamente almacenado, pueden aparecer todas las tablas 'pegadas' a la fila superior y sin posibilidad de ubicarlas más abajo. Este comportamiento resulta molesto porque desfigura el dibujo y no se soluciona intentando ajustar con 'Arrange > Autolayout'. Hay que ir a 'Model > Diagram Properties' y fijar la anchura y altura a una página o al menos cambiar el cero que
aparece en ellas. Después de esto ya se pueden mover las figuras o utilizar 'Arrange > Autolayout'. 2. En ocasiones se ha comprobado que la herramienta acaba su ejecución de manera sorpresiva y anómala. Es recomendable guardar el trabajo con 'Ctrl+S' o 'File > Save' a medida que se va realizando, y no llevar una sorpresa desagradable posteriormente. 3. La primera opción para definir claves foráneas ha funcionado en Ubuntu mejor que la segunda.
Para saber más En la web de MySQL hay diversa información sobre sus productos, entre ellos MySQL Workbench. En particular hay un manual en línea, que también puedes encontrar en formato PDF, en local.
