En la unidad anterior dejamos a Vindio y Noiba diseñando un modelo lógico para implantar un sistema de bases de datos en el taller mecánico. Una vez establecido el modelo lógico, el siguiente paso consiste en establecer el diseño físico, compuesto de tablas y relaciones. Pasando de lo abstracto a lo concreto.
Para conseguir esto, la mayoría de los SGBD disponen de herramientas gráficas que facilitan el paso entre el diseño de modelos de datos Entidad/Relación y el diseño de tablas concretas del modelo relacional.
Veremos que hay muchas similitudes entre ambas representaciones y, si además hemos comprendido el paso del modelo E/R al modelo relacional, no nos costará mucho entender como están estructuradas las tablas y las relaciones entre ellas de nuestra base de datos.
Vamos, pues, a aprender a realizar el diseño físico de bases de datos utilizando asistentes, herramientas gráficas y el lenguaje de definición de datos del SGBD.
En la unidad anterior has visto cómo realizar el diseño conceptual y lógico de una base de datos relacional, esto es:
Diseño conceptual mediante diagramas Entidad/Relación.
Diseño lógico, transformando el diagrama E/R al modelo o esquema Relacional.
Normalización del modelo lógico relacional, eliminando posibles inconsistencias del modelo obtenido anteriormente.
En esta unidad, verás cómo obtener, a partir del modelo lógico, el diseño físico de una base de datos relacional (BDR) e implantarla en un sistema gestor de bases de datos relacional (SGBDR), utilizando el lenguaje SQL, en particular el sublenguaje DDL (Lenguaje de Descripción o Definición de Datos, en inglés Data Definition Language).
Vindio y Noiba tienen que implementar físicamentela base de datos que han diseñado en un SGBD que incorpore las características específicas que mejor se adaptan a su proyecto. Bajo la propuesta de Juan, se estuvieron documentado bien y realizaron un estudio sobre diferentes SGBD existentes en el mercado. Ahora pueden revisar de nuevo su decisión, antes de implementar la base de datos.
Para implementar la base de datos física vas a necesitar un Sistema Gestor de Bases de Datos Relacional (SGBDR), como por ejemplo es MySQL, cuya instalación en modo local viste como realizarla en la unidad 1.
Te recordamos brevemente las razones por las que decidimos utilizar MySQL como SGBD para las prácticas.
Es una copia de un repositorio o proyecto, ubicada en algún servicio de alojamiento, para proponer e incluir algunos cambios respecto del proyecto original.
MySQL se ha convertido en uno de los SGBDR más utilizados en la actualidad, sobre todo en servidores de páginas web, y además dispone de una versión con licencia GPL General Public License, la versión Community Server, que es la versión con la que trabajaremos en modo local.
El SGBD MySQL es multiusuario y está basado en arquitectura cliente/servidor. Se caracteriza por su rapidez, robustez, facilidad de uso, y estabilidad.
Desde la versión 8.0.x, MySQL ofrece nuevas mejoras para la administración del SGBD así como para el desarrollo de aplicaciones web, aplicaciones móviles, y aplicaciones integradas en la nube (Cloud / SaaS / PaaS / DBaaS). Además, permite trabajar tanto con tablas relacionales a través del lenguaje SQL como con colecciones de documentos JSON, como una base de datos NoSQL, lo cual es una opción muy interesante para la futura escalabilidad de la base de datos y las aplicaciones que hagan uso de ella.
Tras la instalación de MySQL, ¿Qué directorios importantes se han creado para gestionar el sistema y las bases de datos?
Recuerda que tras la instalación de MySQL, independientemente del sistema operativo en el que hayas instalado MySQL, se crearon los directorios cuya ruta se guarda en las variables denominadas datadir y basedir:
<span lang="en"><strong>datadir</strong></span>. es el directorio de los datos, apunta al directorio físico \data estructurado en subcarpetas o subdirectorios.
En el directorio físico \<strong></strong><span lang="en"><strong>data</strong></span> se creará un subdirectorio para cada base de datos creada con MySQL. Y tras la instalación se crean automáticamente otras bases de datos de uso propio del servidor. Entre ellas, mysql en la que se guardan los usuarios, passwords, permisos, etc. Otra base de datos importante es information_schema, en ella se almacenan metadatos.
<strong><span lang="en">basedir</span></strong>: es el directorio base y apunta al directorio físico de la instalación de MySQL. El directorio físico, estructurado en subdirectorios contiene, entre otros: el directorio<strong> \bin,</strong> en el que se almacenan los ejecutables o comando de línea asociados a MySQL.
En Windows <span lang="en"><strong>basedir</strong></span> apunta, por defecto, a la siguiente ruta C:\Archivos de programa\MySQL\MySQL Server x.x. y <span lang="en"><strong>datadir</strong></span> a esta otra ruta C:\ProgramData\MySQL\MySQL Server x.x\data.
Un archivo muy importante es el archivo de configuración de MySQL que en Windows se llama my.ini, y en Linux <strong>my.cnf</strong>, este archivo se encuentra en una de esas carpetas.
Esas rutas físicas las podemos obtener siempre, para cualquier sistema operativo en el que se haya instalado MySQL, ejecutando los siguientes comandos desde el shell de mysql:
SHOW VARIABLES LIKE 'datadir';
SHOW VARIABLES LIKE 'basedir';
Significa que permite proveer servicio y procesamiento a múltiples usuarios simultáneamente.
La arquitectura cliente-servidor es un modelo de diseño de software en el que las tareas se reparten entre los proveedores de recursos o servicios, llamados servidores, y los demandantes de servicios, llamados clientes. Un cliente realiza peticiones a un servidor, quien le da respuesta.
Desde el siguiente enlace puedes acceder y ver las nuevas posibilidades que ofrece MySQL incluyendo servicios de 'machine learning', como es el caso de MySQL HeatWave.
Noiba y Vindio han instalado MySQL Server, y ahora van a recordar los mecanismos de interacción con el servidor.
Noiba sabe que es posible acceder al servidor desde un cliente de línea de comandos o bien a través de un cliente gráfico, como Workbench, con el que ya estuvieron practicando anteriormente y que ahora lo van a utilizar mayoritariamente para guardar las sentencias SQL que escriban y que envíen al servidor para crear nuevas bases de datos.
Recuerda que para interactuar con el servidor MySQL necesitamos de un software cliente, bien una herramienta gráfica o bien el cliente en modo texto que incluye MySQL.
Aunque existen muchos clientes o herramientas gráficas que permiten trabajar con MySQL (PhpMyAdmin, HeidiSQL, Navicat, etc.) nosotros nos decantamos por la línea de comandos y por la herramienta Workbench, que es el cliente gráfico oficial de MySQL y es muy fácil de utilizar.
Podemos trabajar con MySQL, tanto en modo local (instalándolo en nuestro equipo) o en modo remoto (con MySQL instalado por ejemplo en la nube, utilizando servicios como AWS). En cualquiera de los casos tendremos a MySQL como servidor, y como cliente usaremos Workbench, la herramienta gráfica que proporciona Oracle para trabajar con MySQL. También podemos utilizar el cliente en modo texto de MySQL.
Noiba debe decidir entre los distintos motores de almacenamiento que utiliza un SGBDD. Dependiendo de los motores de almacenamiento soportados veremos los tipos de tablas que se pueden crear y sus limitaciones. Noiba sabe que la elección del tipo de tabla puede afectar mucho al rendimiento de la base de datos del taller. Por ejemplo, cuando debe primar la seguridad en las operaciones de modificación sobre el contenido de las tablas, se debe optar por las tablas de transacción segura (InnoDB). Si se desea que prime la velocidad en el acceso a los datos, por ejemplo cuando se accede a ellos a través de Internet, debe optarse por tablas MyISAM. Veamos que significa todo esto.
MySQL soporta varios motores de almacenamiento. Algunos motores de almacenamiento tratan con tablas transaccionales y otros no. Por tanto definiremos previamente el concepto de tabla transaccional o tabla de transacción segura.
Una transacción en un Sistema de Gestión de Bases de Datos (SGBD), es un conjunto de órdenes que se ejecutan formando una unidad de trabajo, es decir, en forma indivisible o atómica. Un SGBD se dice transaccional si es capaz de mantener la integridad de los datos, haciendo que estas transacciones no puedan finalizar en un estado intermedio. Cuando por alguna causa el sistema debe cancelar la transacción, empieza a deshacer las órdenes ejecutadas hasta dejar la base de datos en su estado inicial (llamado punto de integridad), como si la orden de la transacción nunca se hubiese realizado. Para esto, el lenguaje de consulta de datos SQL(Structured Query Language), provee los mecanismos para especificar que un conjunto de acciones deben constituir una transacción. Una tabla se dice transaccional si es capaz de soportar transacciones.
Conjunto de acciones que se ejecutan como una unidad. Si la transacción se cancela, las órdenes se deshacen.
Lenguaje de consulta estructurado. Lenguaje de consulta empleado para comunicarse con las bases de datos relacionales.
A la hora de elegir el motor de almacenamiento tendremos que saber que algunos de ellos serán de uso obligatorio si queremos tener ciertas opciones disponibles. Por ejemplo, el soporte para claves ajenas o foráneas sólo está disponible para el motor InnoDB. Los motores de almacenamiento más utilizados son:
BerkeleyDB o BDB: tablas de transacción segura con bloqueo de página.
HEAP o MEMORY: tablas almacenadas en memoria.
InnoDB: tablas de transacción segura con bloqueo de fila y claves foráneas.
MERGE o MRG_MyISAM: una colección de tablas MyISAM usadas como una única tabla.
MyISAM: el nuevo motor binario de almacenamiento portable que reemplaza a ISAM. No soporta transacciones ni claves foráneas.
Para todos los tipos de tablas, excepto las HEAP, se crean varios archivos que contienen información sobre su estructura y sobre su contenido. Parte de estos archivos quedan almacenados en la carpeta o directorio de la base de datos correspondiente.
Como MyISAM, los motores de almacenamiento MEMORY y MERGE tratan tablas no transaccionales. Los motores de almacenamiento que soportan transacciones son InnoDB y BDB. Generalmente usaremos tablas MyISAM o tablas InnoDB. A veces, cuando se requiera una gran optimización, crearemos tablas temporales en memoria (MEMORY).
Comenzamos viendo los motores más importantes para el tipo de tablas no transaccionales.
Motor de almacenamiento MyISAM.
Este motor trata tablas no transaccionales. Son tablas de acceso secuencial indexado, los índices indican la posición relativa en el fichero de datos; por tanto, son tablas portables de un equipo a otro. Se consideran óptimas cuando las tablas se usan preferentemente para consultas, ya que proporcionan almacenamiento y recuperación de datos rápida, sin embargo, para realizar inserciones necesitan más recursos. Se soporta en todas las configuraciones MySQL, y es el motor de almacenamiento por defecto. Cada tabla se almacena en disco en 3 ficheros que se denominan igual que la tabla pero con las extensiones.
MySAM
Extensión del fichero
Contenido del fichero
frm
almacena la definición de la tabla
MYD
almacena los datos
MYI
almacena el índice
Motor de almacenamiento MERGE.
Es una colección de tablas MyISAM similares que pueden usarse como una sola. Todas las tablas tienen que tener los mismos tipos de columnas y de índices y estar en el mismo orden. Esto se aplica porque cuando una tabla de tipo MyISAM llega a ser demasiado grande, es aconsejable crear otra tabla MyISAM con la misma estructura, de forma que parte de los datos estén en una tabla y parte en otra porque las operaciones de consulta y modificación se hacen muy lentas cuando las tablas son muy grandes. Pero si partimos la tabla en dos, las consultas tendríamos que realizarlas en dos tablas, con lo cual tampoco sería lo adecuado. Para evitar esto se crea una tabla de tipo MERGE que es el resultado de la unión de las dos tablas. Estas tablas no contienen los datos, sino que es un símbolo de que cuando se haga una consulta sobre la tabla MERGE, la consulta se hace sobre las tablas que componen la tabla MERGE. Sin embargo, nunca se podrán realizar inserciones sobre la tabla MERGE, porque el servidor desconocería en cuál de las dos tablas debería insertarse las filas. Cuando se crea una tabla de este tipo MySQL crea dos ficheros en disco con nombres que comienzan con el nombre de la tabla y las siguientes extensiones:
MERGE
Extensión del fichero
Contenido del fichero
frm
almacena la definición de la tabla
MRG
contiene los nombres de las tablas que deben usarse como una
Las tablas no tienen que estar en la misma base de datos que la tabla MERGE misma.
Motor de almacenamiento MEMORY.
El motor de almacenamiento MEMORY crea tablas con contenidos que se almacenan en memoria. Anteriormente, se conocían como HEAP. Estas tablas usan índices hash por defecto, lo que las hace muy rápidas, y muy útiles para crear tablas temporales. Sin embargo, cuando se apaga el servidor, todos los datos almacenados en las tablas se pierden. Sirven para copiar en ellas los datos de una tabla en disco y que los procesos se hagan más rápido al trabajar en la memoria. Para utilizar estas tablas es necesario realizar dos procesos: copiar los datos de la tabla del disco a la tabla MEMORY y después copiar los datos de nuevo a la tabla del disco cuando se han hecho las modificaciones y antes de cerrar la sesión. Cada tabla está asociada con un fichero de disco. El nombre de fichero comienza con el nombre de la tabla y tiene una extensión de .frm
MEMORY
Extensión del fichero
Contenido del fichero
frm
almacena la definición de la tabla
Método común de acceso a disco que almacena datos en forma secuencial, al tiempo que mantiene un índice de campos claves para todos los registros en el archivo para acceso directo.
Permite el acceso a la información almacenada a través de la transformación de su clave.
Uno de los motores de almacenamiento más utilizados y que soporta tablas transaccionales es el motor InnoDB.
Motor de almacenamiento InnoDB.
Este motor de almacenamiento proporciona tablas transaccionales. InnoDB también se incluye por defecto en todas las distribuciones binarias de MySQL 8.0. En otras se puede activar o desactivar. Se utiliza en grandes bases de datos que necesitan alto rendimiento puesto que además de la capacidad de recuperación de fallos, InnoDB gestiona múltiples usuarios simultáneamente. Como característica destacada, soporta también restricciones de clave ajena (FOREIGN KEY) de las cuales hablaremos en esta unidad. Las tablas InnoDB pueden ser de cualquier tamaño. InnoDB almacena tablas e índices en un espacio de tablas que puede consistir en varios ficheros. Por eso no se puede trasladar un fichero InnoDB trasladando el archivo <b>.frm</b>. Cuando se crea una tabla InnoDB se crea un archivo de formato de tabla, con extensión .frm. De forma predeterminada MySQL crea un archivo denominado <b>ibdata1</b> en la carpeta DATA. A este tipo de archivo se le denomina tablespace o espacio de tablas y en él se almacena toda la información relativa a índices y contenidos de todas las tablas de tipo InnoDB. Inicialmente, este tablespace se crea con un tamaño de 10 MB, pero se incrementa automáticamente a medida el contenido de las tablas InnoDB lo requiera. También se puede modificar el espacio de tablas por defecto para todas las tablas.
InnoDB
Extensión del fichero
Contenido del fichero
frm
Ibdata1
almacena la definición y todas las entradas del diccionario de datos
Información relativa a índices y contenidos de todas las tablas InnoDB
Motor de almacenamiento BDB.
El motor de almacenamiento BDB proporciona también tablas transaccionales. Este tipo de tablas no se pueden mover de un directorio a otro. En comparación con las tablas MyISAM la búsqueda secuencial es más lenta y también se trata de tablas más grandes porque suele haber huecos donde pueden insertarse registros. BDB se incluye en la distribución binaria MySQL-Max en aquellos sistemas operativos que la soportan.
En BDB cada tabla se guarda en dos ficheros: un fichero con extensión <b>.frm</b> y otro con extensión <b>.db</b>.
BDB
Extensión del fichero
Contenido del fichero
.frm
almacena la definición de la tabla
.db
Contiene los datos de la tabla e índices
Es un tipo de restricción que establece que la existencia de una columna (denominada clave ajena) de cierta tabla, depende de otra columna, que es asu vez la clave primaria de otra tabla.
Espacio de tablas donde se almacena toda la información relativa a índices y contenidos de las tablas de una base de datos.
Responde si es verdadera o falsa la siguiente afirmación:
Retroalimentación
Falso
Si necesitamos crear una tabla que contenga todos los servicios que ofrecemos en nuestro taller a través de nuestra página Web la opción más apropiada sería una tabla tipo InnoDB
En este apartado se han resumido las características de los principales motores de almacenamiento de MySQL. Si necesitas ampliar esta información puedes consultar el manual correspondiente.
Para definir la estructura de la base de datos Noiba debe empezar por crear las tablas y las relaciones entre ellas, tal y como ha recogido en el esquema. Dentro de cada tabla deberá definir una a una las columnas correspondientes a los atributos que necesita almacenar, y en este punto tendrá que detenerse a pensar qué tipo de datos requiere cada uno de esos atributos.
Vindio recomienda a Noiba que comience por conocer previamente los tipos de datos de que dispone el gestor y después aplicar un criterio profesional, ya que de ello van a depender los procedimientos que luego se lleven a cabo con esos datos.
Por ejemplo, si Noiba necesita un campo para guardar un código compuesto por números, por ejemplo el DNI, quizás sea más interesante utilizar un tipo de datos de caracteres en lugar de numérico, ya que no necesitaremos realizar procedimientos matemáticos con ese atributo y en cambio los datos de caracteres son más rápidos de procesar.
Cada tabla de una base de datos se crea con una o más columnas. En la sentencia de creación de tablas (CREATE TABLE) es necesario especificar el tipo de datos que cada columna puede contener. Para elegir el tipo de columna habrá que tener en cuenta:
Cada base de datos introduce tipos de valores de columna que no necesariamente están presentes en otras. Sin embargo, existe un conjunto de tipos que están representados en la totalidad de estas bases.
Tipos de datos
Tipo de valor
Contenido
Alfanuméricos
Contienen caracteres. Presentan una longitud limitada (255 caracteres).
Numéricos
Existen de varios tipos, principalmente, enteros (sin decimales) y reales (con decimales).
Booleanos
Poseen dos formas: verdadero y falso (Sí o No).
Fecha y hora
Almacenan fechas facilitando posteriormente su explotación. Almacenar fechas de esta forma posibilita ordenar los registros por fechas o calcular los días entre una fecha y otra.
Memos
Son campos alfanuméricos de longitud ilimitada. Presentan el inconveniente de no poder ser indexados (veremos más adelante lo que esto quiere decir).
Autoincrementales
Son campos numéricos enteros que incrementan en una unidad su valor para cada registro incorporado. Su utilidad resulta más que evidente: Servir de identificador ya que resultan exclusivos de un registro.
Tipo de datos cuyos valores ocupan la misma cantidad de espacio.
Tipo de datos en los que la cantidad de espacio necesario depende de del valor que alamcenan.
Pequeño valor TEXT TEXT Normal TEXT Medio TEXT TEXTGrande
L < 28 L < 216 L < 224 L < 232
L+1 bytes L+2 bytes L+3 bytes L+4 bytes
<b>ENUM(“valor1”, “valor2”,...)</b>
Se le asigna un valor del conjunto enunciado
Número máximo de valores: 65.535
1 ó 2 bytes
<b>SET(“valor1”,”valor2”,...) </b>
Se le asigna 0 o más valores del conjunto
Número máximo de valores: 64
1,2,3,4, u 8 bytes
L: longitud que ocupa la cadena. N: número máximo de caracteres que puede tener la cadena.
A continuación se especifica algo más sobre estos tipos:
<b>CHAR Y VARCHAR</b>
Se diferencian en que <b><i>CHAR</i></b> es de longitud fija y <b><i>VARCHAR</i></b> de longitud variable. No pueden mezclarse en la misma tabla columnas de longitud fija y variable, por tanto <b>CHAR</b> no puede mezclarse con columnas <b><i>VARCHAR</i></b>, <b><i>BLOB</i></b> o <b><i>TEXT</i></b>. En este caso se convierten las columnas <b><i>CHAR</i></b> a <b><i>VARCHAR</i></b>. Se pueden mezclar columnas <b><i>CHAR</i></b> menores de cuatro caracteres y <b><i>VARCHAR</i></b>, sin que se cambien todas a <b><i>VARCHAR</i></b>. Si todas las columnas son cortas se convierten todas automáticamente a <b>CHAR</b> para optimizar el almacenamiento. Cuando los valores no varían mucho de longitud es mejor <b><i>CHAR</i></b> porque se procesan de forma más eficiente.
<b>TEXT</b>
Permite definir cadenas de longitud variable. Entre paréntesis se pone la máxima longitud que puede tener la cadena. <b><i>TINYTEXT</i></b> se comporta igual que <b><i>VARCHAR</i></b>, pero el acceso a <b>VARCHAR</b> es más rápido, por eso es más aconsejable.
BLOB
Se trata de un objeto binario largo que puede albergar todo lo que se quiera. Se utilizan para almacenar documentos de procesamiento de textos, imágenes y sonidos, etc.
<b>ENUM y SET</b>
Son tipos de cadena en las que los valores se deben elegir de un conjunto fijo de cadenas. Se diferencian en que los valores <b><i>ENUM</i></b> deben elegir un miembro del conjunto de valores, mientras que los valores <b><i>SET</i></b> pueden estar formados por alguno o todos los miembros del conjunto.
Atributos para columnas de tipo cadena:
<b>BINARY</b>
Se puede especificar para los tipos <b><i>CHAR</i></b> y <b><i>VARCHAR</i></b>, sirve para que los valores de las columnas sean tratados como cadenas binarias.
<b>NULL / NOT NULL</b>
Se puede utilizar en cualquier tipo de cadena. Por defecto es <b><i>NULL. </i></b>El valor NOT NULL es diferente de la cadena vacía. Para evitar una cadena vacía es necesario preverlo en las aplicaciones.
<b>DEFAULT</b>
Sirve para especificar un valor predeterminado o valor por defecto, excepto en <b><i>BLOB</i></b> y <b><i>TEXT</i></b>.
En la siguiente tabla puedes ver los diferentes tipos de datos numéricos en MySQL:
Tipos de datos numéricos
Tipo de datos
Descripción
Rango de valores
Almacenamiento requerido
Con signo
Sin signo
TINYINT[(N)]
Entero muy pequeño
-128 a 127
0 a 255
1 byte
SMALLINT[(N)]
Entero pequeño
-32.768 a 32.767
0 a 65.535
2 bytes
MEDIUMINT[(N)]
Entero mediano
-8.388.608 a 8.388.607
0 a 16.777.215
3 bytes
INT[(N)] INTEGER[(N)]
Entero estándar
-2.147.683.648 a 2.147.483.647
0 a 4.294.967.295
4 bytes
BIGINT[(N)]
Entero largo
-9.223.372.036.854.775.808 a 9.223.372.036.854.775.807
0 a 18.446.744.073.709.551.615
8 bytes
FLOAT[(N,D)]
Número pequeño de simple precisión de coma flotante
Valor mínimo distinto de 0
Valor máximo distinto de 0
4 bytes
±1,175.494.351E-38s
±3,402.823.466E+38s
DOUBLE[(N,D)] REAL[(N,D)]
Número grande de doble de precisión de coma flotante
Valor mínimo distinto de 0
Valor máximo distinto de 0
8 bytes
±2,2.250.738.585.072.014 E-308
±1,7.976.931.348.623.157 E+308
DECIMAL[(N[,D])] NUMERIC[(N[,D])] DEC[(N[,D])]
Número de coma flotante, representado como una cadena
Varios, el rango depende de N y D
N+2 bytes
N : Tamaño máximo de pantalla (precisión) D : Número de decimales (escala)
Atributos para columnas de tipo numérico:
<b>ZEROFILL</b>
Hace que los valores mostrados en pantalla se rellenen con ceros hasta ocupar el ancho asignado a dicho valor. Cuando los valores son más grandes que el ancho de pantalla se muestran en su totalidad, no se recortan.
<b>NULL / NOT NULL</b>
Si no se especifica, el valor predeterminado es <b><i>NULL</i></b>.
<b>DEFAULT</b>
Sirve para especificar el valor predeterminado. Si no se especifica, se elige un valor automáticamente. Para los tipos de columna numérica es <b><i>NULL</i></b>, si no puede contener este valor, el valor por defecto sería 0.
Atributos para columnas de tipo numérico entero:
<b>AUTO_INCREMENT</b>
Para generar identificadores únicos o valores en serie. Normalmente comienzan por 1 y aumentan en 1 por cada fila. Se aplica sólo a columnas de tipo entero.
En la siguiente tabla puedes ver los diferentes tipos de datos en MySQL para almacenar valores de fecha y de hora:
Tipos de datos fecha/hora
Tipo
Descripcion
Rango
Almacenamiento
DATE
Representa una fecha en formato ‘AAAA-MM-DD’
“1000-01-01” a “9999-12-31”
3 bytes
TIME
Representa una hora en formato: ‘HH:MM:SS’
“-838:59:59” a “838:59:59”
3 bytes
DATETIME
Representa una combinación de fecha y hora en formato: ‘AAAA-MM-DD HH:MM:SS’
“1000-01-01 00:00:00” a “9999-12-31 23:59:59”
8 bytes
TIMESTAMP
Representa una combinación de fecha y hora en formato: ‘AAAAMMDD HHMMSS’
19700101000000 a cualquier fecha del año 2037
4 bytes
YEAR[(2|4)]
Representa un año en formato: AAAA
1901 a 2155
1 byte
<b>TIME Y DATETIME</b>
La diferencia entre <b><i>TIME</i></b> y <b><i>DATETIME</i></b> es que en <b><i>DATETIME</i></b> la hora es una hora del día y <b><i>TIME</i></b> representa el tiempo transcurrido. Es necesario especificar los segundos. Las 12:30 son 00:12:30 en lugar de las 12:30:00
<b>TIMESTAMP</b>
Representan valores en formato “AAAAMMDDhhmmss”. Tiene la particularidad de grabar un registro que se crea o se modifica.
<b>YEAR</b>
Se utiliza para almacenar el año en lugar de una fecha completa.
Atributos para columnas de tipo fecha y hora:
No existen atributos específicos. Pueden especificarse: <b><i>NULL, NOT NULL, DEFAULT</i></b>.
Formatos para columnas de tipo fecha y hora:
En la siguiente tabla puedes ver diferentes formatos para estos tipos de dato.
Noiba se plantea empezar a implementar la base de datos en el SGBD elegido buscando la utilidad gráfica que permita crear las tablas y sus relaciones, pero pronto descubre que generalmente es más operativo hacerlo a través de instrucciones. Juan le recuerda que detrás de esas herramientas se encuentra un lenguaje que permite, con instrucciones muy sencillas, crear, consultar, manipular y administrar las bases de datos y que es soportado por todos los SGBD relacionales. Se trata del lenguaje SQL, y conocerlo nos va a resultar muy práctico para comunicarnos con la base de datos. Aunque en un principio estaba pensado como lenguaje para consultar los datos hoy en día incorpora también instrucciones para definir las estructuras que necesitamos.
Para entender mejor lo que es el lenguaje de definición de datos vamos a explicar primero lo que es el SQL de una forma global y luego nos centraremos en el Lenguaje de Definición de Datos (LDD) (o DDL, Data Definition Language en inglés) como una parte del lenguaje SQL.
SQL (Structured Query Language) o Lenguaje de Consultas Estructurado, es un lenguaje que se compone de una serie de comandos que permiten a los usuarios crear las bases de datos, las estructuras de tablas y relaciones, consultar y manipular los datos almacenados y administrar esos datos.
Este lenguaje tiene sentencias que se utilizan en tareas muy variadas. Dependiendo de esas tareas podemos clasificar las sentencias SQL en cuatro grupos:
Sentencias SQL
Grupo de sentencias SQL
Descripción
Sentencias o comandos
LDD o DDL (Lenguaje de Definición de Datos)
Incluye sentencias para gestionar las estructuras.
CREATE
ALTER
DROP
LMD o DML (Lenguaje de Manipulación de Datos)
Incluye sentencias para gestionar los datos.
SELECT
INSERT
UPDATE
DELETE
LCD o DCL (Lenguaje de Control de Datos)
Incluye sentencias para gestionar la seguridad y los permisos.
GRANT
REVOKE
LCT o TCL
(Lenguaje de control de Transacciones)
Incluye sentencias para controlar y gestionar transacciones.
START TRANSACTION
COMMIT
ROLLBACK
SAVEPOINT
Normativas y versiones.
No existe una única versión del lenguaje SQL. Muchos fabricantes de software han desarrollado extensiones del conjunto de comandos básico o variaciones concretas que llamaremos dialectos SQL, pero existen un conjunto de normas básicas que todos deben cumplir, así que la mayoría de ellos son compatibles con la normativa actual.
Las normativas pretenden evitar que cada fabricante saque al mercado sus propias versiones. Estas estandarizaciones están a cargo de la organización ISO(International Standarization Organization). Algunas normativas son las siguientes:
SQL-86: contiene la funcionalidad mínima para que un lenguaje se considere SQL.
SQL-89: añade instrucciones para gestionar las claves ajenas (reglas de integridad referencial).
SQL-92: contiene una gran cantidad de variaciones sobre le original.
SQL:1999: se añaden extensiones hacia la programación orientada objetos.
SQL:2003. Se han introducido características de SQL/ XML.
SQL-2005: Define las maneras en las cuales SQL se puede utilizar conjuntamente con XML. Define maneras de importar y guardar datos XML en una base de datos SQL, manipulándolos dentro de la base de datos y publicando el XML y los datos SQL convencionales en forma XML.
SQL-2008: Permite el uso de la cláusula ORDER BY fuera de las definiciones de los cursores. Incluye los disparadores del tipo INSTEAD OF. Añade la sentencia TRUNCATE.
SQL-2011: Datos temporales (PERIOD FOR). Mejoras en las funciones de ventana y de la cláusula FETCH.
SQL-2016: Permite búsqueda de patrones, funciones de tabla polimórficas y compatibilidad con los ficheros JSON.
(International Standarization Organization). Organismo que se encarga de buscar la estandarización de normas de productos y seguridad para las empresas u organizaciones a nivel internacional. Está subdividido en comités.
En este apartado nos referiremos a los símbolos que se utilizan para describir cada una de las instrucciones que se pueden utilizar en MySQL.
La sintaxis usa los siguientes símbolos:
Símbolos de notación
Símbolo
Descripción
Mayúsculas
Una palabra en mayúsculas refleja una palabra reservada de SQL y por tanto hay que escribirla tal y como aparece en la sintaxis.
Minúsculas
Una palabra en minúsculas es algo variable y hay que sustituirla por un dato concreto.
[ ]
Los corchetes se utilizan para reflejar que las alternativas que contienen son opcionales. Por tanto toda palabra no encerrada entre corchetes debe ser incluida obligatoriamente en la sentencia.
|
La barra vertical sirve para separar opciones alternativas en una lista. Solo se puede usar una de ellas.
[ | ]
Cuando una lista separada con barras se encierra entre corchetes indica que se puede elegir entre estos valores opcionalmente.
{ }
Si una lista se encierra entre llaves, igual que pasa con los corchetes, indica que sus valores se pueden elegir, pero es necesario elegir uno de ellos.
…
Varias palabras separadas por comas y finalizadas con puntos suspensivos significan que se puede usar un número variable de esos datos y siempre separados con comas.
Cuando hablamos de símbolos para describir las instrucciones no significa que esos símbolos formen parte de esas instrucciones ala hora de escribirlas. Por ejemplo, los corchetes se utilizan para reflejar que algo es opcional, pero los corchetes en sí no se escriben como parte de una sentencia SQL. Si los incluyes te mostrará error de sintaxis. Lo mismo ocurre con las llaves. Sin embargo los paréntesis forman parte de la propia sentencia SQL y sí hay que incluirlos.
En el caso de las mayúsculas las utilizamos para reflejar que esa palabra es una palabra reservada de SQL y hay que escribirla así, pero podríamos hacerlo en minúsculas y no nos devolvería error. Lo mismo ocurre con las minúsculas, se utilizan para nombres de columnas, de tablas, o de otros objetos. El lenguaje SQL no es "case sensitive" (no distingue entre mayúsculas y minúsculas), pero por convenio y claridad se recomienda seguir la regla de mayúsculas para palabras reservadas y minúsculas para objetos de la base de datos.
Casi todas las sentencias SQL tienen una forma básica, empiezan por un verbo que es una palabra reservada que describe lo que hace la sentencia (ejemplo SELECT), a continuación le siguen una o más cláusulas que concretan mas detalles acerca de la sentencia y que también empiezan con una palabra clave, (por ejemplo FROM o WHERE). Algunas de estas cláusulas son opcionales y otras obligatorias, como vimos en el apartado anterior.
Libre Office Writter(Elaboración propia)
Hemos visto los tipos de datos que maneja MySQL y sus características, ahora veremos cómo se representan valores correspondientes a los principales tipos de datos:
Un valor de tipo carácter o cadena de caracteres se representa entre comillas simples o dobles. En las comparaciones entre valores cadena de caracteres no se distingue entre mayúsculas y minúsculas.
“SANTANDER”
“Y”
‘n’
‘123’
Los enteros pueden representarse con signo. Son representaciones enteras válidas:
+78
78
-78
Los reales pueden representarse con signo. Como separador entre parte entera y fraccionaria se usa el punto. Puede usarse la representación exponencial. Son representaciones reales válidas:
56
-89.008
3.1089E+24
Las fechas se representan entre comillas y en formato numérico usando como separador el guión. Por ejemplo, el 17 de enero de 2011 se representa:
“2011-1-17”
Las horas (datos de tipos TIME) se representan entre comillas y usando como separador el carácter dos puntos. Por ejemplo, para representar las 4 y 20 de la tarde:
“16:20:00”
Los datos de tipo lógico o booleano sólo admiten dos valores: true y false.
Es una palabra que tiene un significado especial para ese lenguaje y su uso queda restringido.
Tanto para esta unidad como para las unidades siguientes es importante que conozcas una serie de reglas sobre cómo se escriben sentencias en SQL con relación a: valores literales, valores NULL, identificadores, palabras reservadas, comentarios y operadores. Conocer estas reglas te evitará cometer muchos errores a la hora de escribir las instrucciones en MySQL.
Parece que Noiba ya tiene claro lo que necesita saber para enfrentarse al lenguaje SQL. En este apartado presentaremos un subconjunto de ese lenguaje: el lenguaje de definición de datos (LDD). En los siguientes apartados aprenderemos con ella a utilizar los comandos que nos permitirán crear las tablas dentro de una base de datos. Empezaremos por ejemplos sencillos y poco a poco iremos ampliando el modelo.
La primera fase del trabajo con cualquier base de datos comienza con sentencias DDL, puesto que antes de poder almacenar y recuperar información debemos definir las estructuras donde almacenar la información. Las estructuras básicas con las que trabaja SQL son las tablas.
Conocer el Lenguaje de Definición de Datos (DDL) es imprescindible para crear, modificar y eliminar objetos de la base de datos (es decir, los metadatos). En el mercado hay suficientes aplicaciones y asistentes que nos facilitan esta labor, a través de una interfaz visual que nos oculta el lenguaje SQL y en los cuales nos limitamos a poner nombres a los campos, elegir el tipo de datos y activar una serie de propiedades.
Por ejemplo, con Workbench de MySQL podemos:
Realizar el diseño de la base de datos, mediante su herramienta Data Modeling e implantar mediante ingeniería directa la base datos en el Servidor (si lo tenemos en marcha) o bien generar el scriptSQL (con las sentencias SQL para la creación de la base de datos y sus tablas) y cargarlo posteriormente en el servidor.
Trabajar en modo gráfico con esta herramienta, a modo de formularios, y crear la base de datos, sus tablas, etc.
Es cierto que estas herramientas nos facilitan el trabajo, pero resulta imprescindible comprender y conocer en profundidad el lenguaje, ya que nos veremos en muchas situaciones donde necesitaremos crear un objeto, modificarlo o eliminarlo sin depender de esas herramientas visuales.
¿De qué objetos estamos hablando? Éstos podrán ser tablas, vistas, índices u otros objetos relacionados con la definición de la base de datos. ¿Y quién puede crear y manipularlos? En principio el usuario propietario (el que los creó) y los administradores de la base de datos. Más adelante veremos que podemos modificar los privilegios de los objetos para permitir el acceso a otros usuarios.
El lenguaje de definición de datos es la parte del SQL que más varía de un SGBD a otro. Esto se debe a que cada sistema organiza internamente los datos de una forma particular y la función del DDL está estrechamente vinculada a la organización interna de los datos.
El lenguaje de definición de datos es el que se encarga de la creación y modificación de la estructura de los objetos de la base de datos: creación de bases de datos, creación de tablas, modificación de la estructura de tablas, eliminación de tablas, etc.
MySQL dispone de varias sentencias DDL. Las operaciones básicas se realizan con las sentencias: CREATE, ALTER y DROP.
Veamos estas sentencias con un poco más de detalle.
CREATE: Crea un objeto de la base de datos. Estos objetos pueden ser tablas, vistas, índices, trigger, funciones, procedimientos, etc., o la propia base de datos. Las opciones más comunes son:
<b>CREATE DATABASE</b>
<b>CREATE INDEX</b>
<b>CREATE TABLE</b>
<b>CREATE VIEW</b>
ALTER: Permite modificar la estructura de un objeto. Podemos añadir o borrar campos en una tabla, modificar el tipo de un campo, añadir o quitar índices, etc. La sintaxis más frecuente es:
<b>ALTER TABLE</b>
DROP: Elimina un objeto de la base de datos. Puede eliminar una tabla, una vista, un índice, trigger, función, etc., cualquier objeto que soporte la base de datos, y también la base de datos. Presenta varias opciones:
<b>DROP DATABASE</b>
<b>DROP INDEX</b>
<b>DROP TABLE</b>
<b>DROP VIEW</b>
Denominado también disparador o desencadenador, es un procedimiento que se ejecuta cuando se cumple una condición establecida al realizar una operación de inserción, actualización o borrado.
Siguiendo los pasos de Noiba y Vindio hemos aprendido que la base de datos es una estructura compartida e integrada que aloja un conjunto de datos y metadatos para el usuario final y que para un SGBD la estructura de la base de datos es un conjunto de archivos físicos guardados en disco.
Noiba y Vindio se disponen ahora a dar el siguiente paso que consistirá en hacer dos cosas:
Crear una estructura de base de datos que contendrá todas las tablas.
Crear las tablas que guardarán los datos.
En este apartado aprenderemos con ellos a crear la estructura de la base de datos. Dejaremos la creación de las tablas para los apartados siguientes.
Básicamente, la creación de la base de datos consiste en crear las tablas que la componen.
Crear una base de datos implica indicar los archivos y ubicaciones que se van a utilizar además de otras indicaciones técnicas y administrativas. Es obvio que todo esto sólo lo puede realizar si se tiene privilegio de Administrador.
Con el estándar de SQL la instrucción a usar sería CREATE DATABASE, pero cada SGBD tiene su propio procedimiento para crear las bases de datos.
En el caso de MySQL tenemos 3 sentencias a nivel de base de datos:
CREATE DATABASE: para crear bases de datos.
USE DATABASE: para activar una base de datos o ponerla en uso.
DROP DATABASE: para eliminar una base de datos.
Veamos estas sentencias con más detalle a continuación.
CREATE DATABASE
Cuando se crea una base de datos nueva el gestor crea automáticamente las tablas que guardarán los metadatos. Para crear una base de datos nueva escribiremos:
Obligatoriamente se debe poner bien SCHEMA o bien DATABASE, en MySQL es indiferente, las dos formas pueden usarse indistintamente.
Opcionalmente se puede incluir IF NOT EXISTS. Si lo incluimos, al crear la base de datos de nombre NombredemiBaseDatos, si no existe una base dedatos con ese nombre, la crea, si ya existe, no intentará crearla ni dará error.
Automáticamente el servidor crea una carpeta vacía dentro de la carpeta DATA con el mismo nombre de la base de datos.
Por ejemplo, para crear una base de datos de nombre talleres_faber, la sentencia SQL sería:
CREATE DATABASE talleres_faber;
USE DATABASE
Con la sentencia CREATE DATABASE talleres_faber; hemos creado una base de datos denominada talleres_faber, pero eso no significa que esa sea la base de datos activa. Para convertirla en la base de datos activa tendremos que escribir:
Tal y como viste en el apartado de Primeros pasos con MySQL, bien desde el cliente de la línea de comandos o bien desde el cliente gráfico Workbench, te puedes conectar a MySQL con el usuario creado en la instalación y su contraseña, y acceder a la ventana para introducir el código SQL como se ve en las imagénes de arriba.
Noiba ya tiene creada la base de datos TalleresFaber, y también la base de datos campeonato, con la que tú vas a trabajar a a partir de esta unidad, pero hasta el momento no contienen ninguna tabla. La mayoría de los SGBD poseen potentes editores para crear de forma rápida y sencilla cualquier tipo de tabla; sin embargo Noiba decide que es mejor guardar los comandos SQL en un guión (fichero script en inglés) para poder realizar el mantenimiento y actualización de la base de datos de forma fácil. Por esto, y porque muchas veces podemos ahorrarnos quebraderos de cabeza cuando no conocemos bien el editor, aprenderemos con ella a crear, modificar y borrar tablas a partir de sentencias SQL.
Una vez creada nuestra base de datos “campeonato" y "Talleres_Faber" podemos comprobar que existe una carpeta con ese nombre dentro de la carpeta <i><b>data</b></i>, en la siguiente ruta:
C:\ProgramData\MySQL\MySQL Server 8.0\Data\talleres_faber
C:\ProgramData\MySQL\MySQL Server 8.0\Data\campeonato
Aquí es donde MySQL va a guardar nuestras bases de datos.
Una vez creada la base de datos, el siguiente paso sería crear las tablas de esa base de datos.
¿Qué necesitamos para poder guardar los datos? Lo primero será definir los objetos donde vamos a agrupar esos datos. Los objetos básicos con los que trabaja SQL son las tablas, que como ya sabemos es un conjunto de filas y columnas cuya intersección se llama celda. Es ahí donde se almacenarán los elementos de información, los datos que queremos recoger.
Antes de crear la tabla es conveniente planificar algunos detalles:
Qué nombre le vamos a dar a la tabla.
Qué nombre le vamos a dar a cada una de las columnas.
Qué tipo y tamaño de datos vamos a almacenar en cada columna.
Qué restricciones tenemos sobre los datos.
Alguna otra información adicional que necesitemos.
Y debemos tener en cuenta otras reglas que se deben cumplir para los nombres de las tablas:
No podemos tener nombres de tablas duplicados en una misma base de datos (usuario).
Deben comenzar por un carácter alfabético.
Su longitud máxima es de 30 caracteres.
Solo se permiten letras del alfabeto inglés, dígitos o el signo de guión bajo.
No puede coincidir con las palabras reservadas de SQL (por ejemplo, no podemos llamar a una tabla <span lang="en">SELECT</span>).
No se distingue entre mayúsculas y minúsculas.
Como te hemos comentado antes, podrías crear la base de datos y sus tablas utilizando una herramienta gráfica. Aunque aprender a utilizar las herramientas gráficas proporcionadas por el SGBD no es el objetivo de este módulo, ya que lo que pretendemos es conocer el lenguaje SQL, en muchos casos estas herramientas nos pueden ahorrar trabajo. Por tanto veremos cómo se accede a la creación de tablas con Workbench:
Nos conectamos al servidor MySQL mediante el cliente Workbench, con el usuario y contraseña que creamos en la instalación.
Seleccionamos nuestra base de datos en el menú de la izquierda de la pantalla.
La ponemos en uso haciendo doble clic sobre ella.
Aparece un contenedor para tablas. Hacemos clic derecho y seleccionamos 'crear tabla'.
En la ventana siguiente definiremos una a una las características de cada columna.
Observa la siguiente imagen:
Workbench(Elaboración propia)
Como ves, podemos crear de forma gráfica las tablas de la base de datos, pero nuestro objetivo es aprender el lenguaje SQL.
Sentencias SQL para la creación, modificación y eliminación de tablas
Las principales sentencias SQL para manipular tablas en una base de datos son las siguientes:
<b>CREATE TABLE</b>
<b>ALTER TABLE</b>
<b>DROP TABLE</b>
Veremos cada una de ellas en los siguientes apartados.
Esta es la sentencia que sirve para crear tablas. La sintaxis de esta sentencia es muy compleja porque existen muchas opciones diferentes a la hora de crear una tabla. Básicamente para crear una tabla debemos especificar:
El nombre que le queremos asignar a la tabla.
Los nombres de las columnas (atributos o campos) y todas las opciones que admite cada columna.
Otras características a nivel de tabla: como si alguno de esos campos van a ser índices y de qué tipo, etc.
Si nos centramos en MySQL, la sintaxis para crear una tabla sería la siguiente:
CREATE TABLE [IF NOT EXISTS] nombretabla( Columna1 TipoDato [restricciones de columna], Columna2 TipoDato [restricciones de columna],
[restricciones de tabla] ) [ {ENGINE | TYPE} = Tipo_Tabla];
donde:
nombretabla: nombre de la tabla (identificador válido según las reglas soportadas por el gestor de bases de datos).
IF NOT EXISTS: la tabla se creará si no existe ya una con ese nombre.
ColumnaN: nombre de la columna (identificador válido según las reglas soportadas por el gestor de bases de datos).
TipoDato: Tipo de dato para la columna (por ejemplo: INT, VARCHAR, CHAR, DATE, etc.)
ENGINE: indica el tipo de almacenamiento para la tabla mediante Tipo_Tabla. En la misma BD puede haber tablas con diferente tipo de almacenamiento, como por ejemplo son las tablas MyISAM y las tablas InnoDB. Si no se indica, asume el tipo por defecto. (Los tipos de almacenamiento de tablas permitidos en MySQL los podemos ver con la sentencia: SHOW ENGINES;)
Por ejemplo, vamos a crear una tabla de CLIENTES con las opciones básicas:
CREATE TABLE Clientes(
CodCliente INTEGER NOT NULL PRIMARY KEY,
DNI CHAR(10),
Apellidos CHAR(25),
Nombre CHAR(25),
Direccion CHAR(50),
Telefono CHAR(25)
);
¿Qué son las restricciones?
Una restricción es una condición que una o varias columnas deben cumplir obligatoriamente. (En el siguiente apartado las veremos en detalle)
Restricciones de columna: Afectan solo a esa columna (clave primaria, no nulo, etc.)
Restricciones de tabla: Se indican después de especificar todas las columnas, se les puede asignar un nombre y pueden afectar a varias columnas.
Para poder estudiar todas las opciones relativas a estas restricciones vamos a dividirlas en partes:
Restricciones de tipo 1: restricciones o definiciones a nivel de columna.
En el siguiente enlace de la web oficial de MySQL puedes ampliar información y ver el formato completo de la sentencia CREATE TABLE , así como ejemplos de creación de tablas.
Restricciones de columna o de tipo 1. A la hora de definir columnas tenemos más opciones además del nombre y el tipo de datos, como: valor por defecto, si puede contener o no valores nulos, crear la clave primaria, índices, etc. A esas condiciones se les denomina restricciones. Si una misma columna tiene varias restricciones, éstas se separan con espacios (nunca con comas). La coma se utilizará al finalizar la definición de una columna y pasar a la siguiente.
La sintaxis para definir columnas es la siguiente:
Es una limitación que obliga al cumplimiento de ciertas condiciones en la base de datos.
Vamos a ver el significado de cada una de esas restricciones:
NULL | NOT NULL:Sirve para definir si la columna podrá contener o no valores nulos. La opción por defecto es NULL. En el caso de que sean columnas que forme parte de la clave primaria no podrán contener valores nulos, pero hay otros casos en los que esta opción nos podría interesar.
<b>DEFAULT:</b> Sirve para definir el valor que tendrá por defecto una columna. Este se asignará de forma automática cuando no se especifique otro valor al añadir filas. Cuando una columna puede contener nulos y no se especifica valor por defecto este será NULL.
AUTO_INCREMENT: Es una columna autoincrementada que tiene que ser de tipo entero. Si en una columna definida como auto_increment no introducimos ningún valor o introducimos un valor nulo, se añade una unidad al valor anterior. Se usa sobre todo para crear una clave primaria artificial.
<b>PRIMARY KEY</b>: Solo puede existir una clave primaria en cada tabla. Para definirla podemos usar la palabra KEY o PRIMARY KEY. La clave primaria nunca puede tener valores NULL. Si no lo especificamos MySQL lo hace de forma automática pero es aconsejable acostumbrarse a definirlo.
INDEX: Indica que la columna es un índice y por tanto su contenido se almacena en una tabla de índices que agiliza las operaciones de búsqueda sobre las tablas.
UNIQUE: Indica que la columna es un índice que no admite repeticiones en los datos que se introduzcan en esa columna. Por ejemplo: en la tabla Clientes, se ha elegido el CodCliente como PRIMARY KEY, pero el atributo DNI de igual forma no puede repetirse. Para recoger esta restricción lo podemos definir como UNIQUE.
REFERENCES: Se usa para especificar que esa columna es clave ajena que referencia a una columna que es clave primaria en otra tabla. A continuación se puede definir lo que se hará en nuestra tabla en caso de que se borre o modifique la clave primaria de la tabla relacionada. Esto se explicará más despacio en el siguiente apartado.
CHECK: Indica que los valores introducidos en la columna deben cumplir una determinada condición. Como verás más adelante, no tiene efecto en versiones de MySQL anteriores a 8.0.16.
Como ejemplo veamos como crear la tabla CLIENTES con algunas restricciones:
CREATE TABLE Clientes (
CodCliente INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
DNI VARCHAR(9) UNIQUE NOT NULL,
Apellidos VARCHAR(50),
Nombre VARCHAR(25),
Direccion VARCHAR(50),
Población VARCHAR(25) DEFAULT ´Almería´,
Telefono VARCHAR(9)
);
Es un campo o columna añadida a una tabla con la intención de formar una clave para identificar de forma única a cada fila.
Es una restricción que establece que la existencia de una columna de una tabla (la denominada clave ajena) depende de otra columna que es a su vez clave primaria en otra tabla.
Aunque puede hacerse, las restricciones de tipo PRIMARY KEY, REFERENCES, INDEX, UNIQUE no suelen definirse como restricciones a nivel de columna sino como otro tipo de restricciones que veremos a continuación.
Además de la definición de las columnas que acabamos de ver, podemos añadir otras definiciones o restricciones que hemos denominado restricciones de tabla o de tipo 2.
Cuando definimos restricciones a nivel de columna no podemos, por ejemplo, definir una clave compuesta ya que no podríamos asignar la PRIMARY KEY a dos columnas porque se interpretaría como dos claves principales. De la forma que veremos a continuación podemos definir restricciones sobre cualquier columna individual o sobre conjuntos de columnas que se hayan definido en las líneas anteriores. Al ser una sintaxis más general, utilizaremos este tipo de restricciones en muchos casos.
PRIMARY KEY: En el apartado anterior vimos como crear una clave primaria de una sola columna. Tanto si la clave está formada por una sola columna como, obligatoriamente, cuando esté formada por más de una columna (claves compuestas) otra alternativa es especificar la clave principal después de definir las columnas.
KEY e INDEX: Además de la clave primaria tenemos otro tipo de índice que permite definir índices sobre una columna, sobre varias o sobre partes de columnas. Un índice se debe definir para aquellas columnas que se usen frecuentemente. Las opciones KEY o INDEX se usan indistintamente. El nombre del índice es opcional.
UNIQUE: Permite definir índices con valores únicos. Lo importante de este índice es que no admite añadir filas con valores repetidas (excepto NULL, que sí se puede repetir). Se puede parecer a una PRIMARY KEY pero tiene dos diferencias fundamentales:
Sí admite valores NULL.
Puede haber más de un índice UNIQUE en una tabla.
Puede definirse sobre una columna, sobre varias o sobre partes de columnas. Se definen sobre todo sobre columnas que representan claves alternativas.
FULLTEXT: Indica que una o más columnas forman un índice de tipo búsqueda de texto. Sirve para ser utilizado con funciones que permiten buscar palabras dentro de columnas de texto de tipo CHAR, VARCHAR y la familia de tipos <b><i>TEXT</i></b>. Se comporta igual que un índice <b><i>INDEX</i></b> o <b><i>UNIQUE</i></b> pero solo puede aplicarse a esos tipos de columna. Estos índices sólo pueden usarse con tablas MyISAM. Las búsquedas full-text se realizan con las funciones <b><i>MATCH()</i></b> y <b><i>AGAINST()</i></b>.
CHECK: Establece una condición que se debe cumplir para los valores insertados en la tabla. Esta restricción se puede poner al crear las tablas, pero no tiene efecto, es decir, el servidor no valida los datos afectados por esta restricción. Hasta MySQL 8.0.16 se admitía <b><i>CHECK</i></b> por compatibilidad con otros sistemas.
Los índices sirven para optimizar las consultas y las búsquedas de datos. Si recordamos como funciona un fichero con índices, entenderemos que su uso hace que la localización de las filas con determinados valores de columna o seguir un determinado orden a la hora de buscar es mucho más rápido. La alternativa es hacer búsquedas secuenciales, que en tablas grandes requieren mucho tiempo.
<br />FOREIGN KEY: Este tipo de restricción indica que las columnas indicadas entre paréntesis forman una clave ajena relativa a las columnas de otra tabla que se indica tras la palabra <b><i>REFERENCES</i></b>.
Los nombres de la restricción <b><i>símbolo</i></b> y de la clave ajena <b><i>NombreClaveAjena</i></b> son opcionales que se suelen dar para poder utilizarlos en instrucciones de modificación o eliminación de esa restricción.
El nombre de la tabla <b><i>NombreTabla</i></b> es la tabla a la que hace referencia la clave ajena y las opciones <b><i>ON DELETE</i></b> y <b><i>ON UPDATE</i></b> expresan las opciones de borrado y de eliminación sobre las filas de la tabla principal relacionada, es decir qué ocurre en la tabla que estamos creando si se intenta modificar o eliminar filas en la tabla principal que están relacionadas con estas.
Las opciones opcion pueden ser:
CASCADE: Borrado o modificación en cascada.
RESTRICT: Borrado o modificación restringido. Significa que no se puede realizar la acción si hay filas relacionadas en la tabla secundaria.
SET NULL: Borrado o modificación con puesta a nulos.
NO ACTION: Borrado o modificación sin acción. Esta es la opción por defecto.
¿Pero qué significa todo eso realmente?
Al relacionar tablas mediante una columna necesitamos que el dato o valor de la columna que es clave ajena en una tabla (que llamaremos secundaria) previamente haya sido incluido en su tabla de procedencia (tabla primaria) donde es clave primaria. Recuerda que a eso se le llama Integridad Referencial.
Con el propósito de mantener la integridad referencial tenemos las siguienetes opciones:
ON DELETE CASCADE: te permitirá borrar todos los registros cuya clave ajena sea igual a la clave del registro borrado.
ON DELETE SET NULL: colocará el valor NULL en todas las claves ajenas relacionadas con la borrada.
ON DELETE RESTRICT: (igual que NO ACTON en MySQL) no te permitirá borrar el registro principal, si hay registros asociados con ese valor en su clave ajena.
Otras cláusulas, para mantener la Integridad Referencial, que van tras REFERENCE y permiten cambiar de forma automática el valor de las claves ajenas en función de la modificación hecha en el valor de la clave principal son:
ON UPDATE CASCADE: te permitirá modificar el valor de la clave ajena de todos los registros cuya clave ajena sea igual a la clave del registro modificado.
ON UPDATE SET NULL: colocará el valor NULL en todas las claves ajenas relacionadas con la modificada.
ON UPDATE RESTRICT: (igual que NO ACTON en MySQL) no te permitirá modificar la clave primaria el registro principal, si hay registros asociados con ese valor en su clave ajena.
Por defecto, en MySQL, si no se indica nada, las opciones son:
ON DELETE RESTRICT
ON UPDATE RESTRICT
Es la norma que indica que el valor de una clave ajena debe coincidir con uno de los valores de la clave primaria de la tabla padre o bien puede tomar el valor nulo, si se le permite a la columna.
Como habrás deducido el tipo de tablas más conveniente para nuestras bases de datos será el InnoDB, ya que, además de transacciones, nos permite establecer restricciones de clave ajena.
En las últimas versiones de MySQL el tipo predeterminado para las tablas es InnoDB.
Escribe la sentencia SQL que permita crear el siguiente esquema relacional. Elige el tipo de datos que consideres más adecuado. Incluye la actualización en cascada en las claves ajenas.
¿Te has encontrado algún problema en el ejercicio anterior?
Imagina que decides crear la tabla Incluyen antes que la tabla REPARACIONES, entonces cuando intentas definir la segunda clave ajena de la tabla Incluyen te estás refiriendo a la tabla REPARACIONES que aún no has creado, con lo cual MySQL no puede definir esta restricción. Está claro que esto se puede solucionar fácilmente: creando primero las tablas que no lleven claves ajenas y dejando ésta para el final; ahora bien ¿Te imaginas hacer esto en un esquema de, por ejemplo, 10 tablas relacionadas como la base de datos de TalleresFaber?
Te puedes estar preguntando... ¿Qué ocurriría si una vez creadas las tablas, hay que añadir alguna columna que no se tuvo en cuenta inicialmente? ¿Y si se desea añadir una nueva restricción o, por el contrario, eliminarla? Está claro que el esquema de una base de datos puede sufrir algunas modificaciones a lo largo del tiempo. Esto se hace con la sentencia ALTER TABLE.
Vamos a ver cómo se usa.
ALTER TABLE: Permite modificar la estructura de una tabla: añadir una nueva columna, cambiar el tipo de una columna, establecer una clave primaria, eliminar una columna, establecer una clave ajena, etc. La sintaxis general de esta sentencia admite muchas opciones, las más importantes son:
Muchas de las opciones que admite ALTER TABLE las hemos visto al estudiar CREATE TABLE. Veamos ahora las que son nuevas:
CHANGE: Permite modificar el nombre, el tipo y las restricciones de una columna mediante lo indicado en NuevaDefinicionColumna. Si no se quiere cambiar el nombre habrá que repetir dos veces el mismo (el antiguo y el nuevo nombre).
MODIFY: Permite modificar el tipo y las restricciones de la columna indicada en DefinicionColumna por lo indicado en esta definición. Se diferencia de <b><i>CHANGE</i></b> en que con <b><i>MODIFY</i></b> no se puede cambiar el nombre, solo la definición.
Imagina que en la fase de creación de las tablas has creado por error una tabla que no necesitas. ¿Cómo la puedes eliminar? Con la sentencia DROP TABLE.
<span class="destacado_inline">DROP TABLE</span>
Esta sentencia elimina una o más tablas de una base de datos. La sintaxis es:
IF EXISTS: La cláusula IF EXISTS se utiliza para que, en caso de que la tabla no exista, la sentencia no devuelva un código de sentencia con resultado erróneo, (que en un procedimiento o función puede ser muy importante, como veremos más adelante).
Cuando se realiza un DROP TABLE la tabla no es recuperable, por eso tendremos que utilizar esta instrucción con cuidado.
Vamos a ver como ejemplo, la creación del scriptSQL que dará lugar a la base de datos Talleres Faber. En la unidad anterior hemos visto los pasos para diseñar un modelo E/R adecuado y lo hemos trasladado al modelo relacional. En esta unidad hemos seleccionado un SGBD que nos permita crear, manipular y administrar nuestras bases de datos y, por último, hemos conocido la parte del lenguaje SQL que nos va a permitir transformar el modelo relacional obtenido en objetos de nuestra base de datos definiendo sus propiedades.
Aplicando todo lo que has aprendido al caso práctico de talleresFaber habremos obtenido un modelo relacional similar al que ves a continuación:
Nota: Las columnas subrayadas forman parte de las claves primarias, las columnas que aparecen con (fk) forman parte de las claves ajenas.
A continuación necesitamos abrir el editor SQL de nuestro SGBD para escribir las instrucciones que creen esas tablas en un script. En la siguiente imagen verás el editor de SQL en MySQL Workbench:
Workbench(Elaboración propia)
Una vez escritas todas las instrucciones SQL guarda el script en un fichero de extensión sql que podrás editar con el editor de notas.
Para ver el script que genera la base de datos TalleresFaber pulsa en el siguiente enlace:
En este videotutorial verás como realizar el diseño físico de una base de datos relacionaly su implantación en el SGBD MySQL. Veremos como ejemplo práctico la implantación física de la base de datos Veterinaria, cuyo diseño conceptual y diseño lógico se realizó en la unidad anterior.
Los puntos que vamos a tratar son los siguientes:
Revisión de algunos aspectos teóricos a tener en cuenta del modelo relacional y del lenguaje SQL.
Caso práctico de implantación física de la base de datos Veterinaria y posterior modificación en la estructura de algunas tablas con ALTER TABLE. Utilizaremos el cliente gráfico MySQL Workbenchpara redactar las sentencias SQL.
También vas a ver, cómo crear un usuario de base de datos con los permisos necesarios para gestionar esa base de datos
Modelo relacional y creación de la base de datos veterinaria
Para practicar con lo explicado en el videotutorial, puedes descargar el enunciado de Veterinaria así como sus modelos conceptual y lógico, y los scripts SQL de creación del usuario de base de datos, la base de datos y las sentencias ALTER TABLE de modificación de tablas.
Otros ejemplos resueltos: implantación de Películas, Videoteca y Campeonato
En los siguientes apartados vas a ver otros ejemplos de implantación de bases de datos en el SGBD MySQL, cada una de ellas con particularidades diferentes, con las que puedas practicar todo lo aprendido hasta ahora. En concreto verás el diseño físico de las bases de datos cuyo diseño conceptual y lógico realizamos en la unidad anterior: Peliculas, Videoteca y Campeonato.
En la implantación de cada una de estas nuevas bases de datos verás nuevas particularidades, como por ejemplo:
El tipo de dato ENUM() y la restricción AUTO_INCREMENT.
Implementación de claves primarias y foráneas compuestas.
Personalización del nombre de CONSTRAINT o restricciones.
Creación de tablas sin restricción de clave foránea y su posterior implementación de claves foráneas con ALTER TABLE.
Creación de vistas o VIEW para resolver consultas, aunque su estudio lo tienes detallado en un punto posterior de esta unidad.
Uso de Workbench en modo gráfico para creación y modificación de tablas.
Obtención del modelo de una base de datos implantada en el servidor mediante ingeniería inversa.
Ejemplo 1. Implantación física de la base de datos PELÍCULAS.
En el siguiente videotutorial puedes ver cómo se realiza la implantación física de la base de datos Películas en MySQL, que posteriormente viene detallada en las otras secciones de este apartado.
Verás el uso de AUTO_INCREMENT.
Creación de la base de películas y uso de auto_increment
Las especificaciones y requerimientos de la base de datos Películas son los siguientes.
Una película se caracteriza por: código, título, año de estreno y género. 2.- Una película pertenece obligatoriamente a un género y solo a uno. Pero de un determinado género puede haber varias películas o ninguna. 3.- El género de una película se caracteriza por un código, nombre y descripción. 4.- En una película participan uno o varios actores y cada actor puede participar en una o varias películas. 5.- De los actores interesa su código, DNI, nombre, fecha de nacimiento, edad y varios email. 6.- Hay que considerar dos posibles tipos de actores, aunque puede haber otros tipos:
Actores oscarizados: de los que hay que almacenar además, el total de oscars y la fecha del primer oscar.
Actores productores: de los que hay que almacenar además, el numero de películas producidas, aportación mínima, y primer año de producción.
Un actor oscarizado también puede ser productor.
Diagrama ERE y Modelo Relacional
Como resultado del diseño CONCEPTUAL obtuvimos el siguiente diagrama Entidad/Relación Extendido:
Software DIA(Elaboración propia)
Como resultado del diseño LÓGICO obtuvimos el siguiente modelo relacional:
Libre Office(Elaboración propia)
Restricciones para diseño físico
Para realizar el diseño físico y su implantación en MySQL nos dan las siguientes especificaciones y restricciones. Si en alguna tabla no se especifica de forma explícita alguna restricción y/o tipo de dato de ciertas columnas, es porque se debe deducir del propio modelo relacional y/o valor que va a almacenar.
Tabla GENERO.
El código es alfanumérico de tamaño fijo y de 4 caracteres. El nombre como máximo de 20 caracteres. La descripción puede ocupar mas de 300 caracteres.
Tabla PELICULA.
El código es un valor numérico que se incrementa de forma automática. El título tiene como máximo 30 caracteres y es obligatorio (no puede quedar vacío).
Tabla ACTOR.
El código es alfanumérico de tamaño fijo de 5 caracteres. EL <abbr title="Documento Nacional de Identidad">DNI</abbr> es alfanumérico de tamaño fijo de 10 caracteres. (es clave alternativa).
El nombre es de tamaño variable, como máximo de 20 caracteres.
Tabla EMAIL.
El <span lang="en">email </span>es alfanumérico de tamaño variable, máximo 40 caracteres.
Tabla OSCARIZADO.
El total oscar es un entero pequeño.
Tabla PRODUCTOR.
El número de películas es una entero. El importe mínimo puede tener parte decimal.
RESTRICCIONES DE CLAVE AJENA para borrados y modificaciones.
Al modificar la clave primaria de cualquier tabla principal se deben modificar de igual forma las claves ajenas o foráneas relacionadas.
No se puede eliminar un género si tiene películas relacionadas.
No se puede eliminar una película en la que participen actores.
No se puede eliminar un actor que participe en películas.
Si se elimina un actor, se deben eliminar todos sus email relacionados y sus particularidades como oscarizado y/o productor.
Creación de base de datos y tablas en SQL
Redactamos las sentencias SQL para crear la base de datos y cada tabla desde la línea de comandos o bien en la ventana SQL de la herramienta o cliente Workbench.
Es IMPORTANTE el orden de creación de tablas. Podemos seguir dos criterios de creación:
Crear primero las tablas que no tienen claves foráneas (no referencian a otras tablas), pues no se puede crear una tabla T1 que referencia a la tabla T2, si no se ha creado antes la tabla T2.
Crear todas las tablas y después con ALTER añadir la restricción de claves ajenas o foráneas.
En este caso vamos a seguir la estrategia 1.- Crear primero las tablas que no tienen claves foráneas y después ir creando las tablas que referencian con sus claves foráneas a tablas que ya hemos creado previamente.
En el siguiente enlace tienes el script SQL de creación de la base de datos películas.
En el siguiente videotutorial puedes ver cómo se realiza la implantación física de la base de datos VIDEOTECA en MySQL, que posteriormente viene detallada en las otras secciones.
En esta ocasión:
Le vamos a dar un nombre personalizado a las restricciones o CONSTRAINT a nivel de tabla.
Realizamos ciertas modificaciones sobre algunas tablas con ALTER TABLE y añadiendo índices con INDEX.
Crearemos un par de vistas, VIEW, para resolver determinadas consultas.
Creación de la base de datos Videoteca, constraint personalizadas y VIEW
Las especificaciones y requerimientos de la base de datos Videoteca son los siguientes
Una película se caracteriza por un código, título, año de estreno y género. 2.- Una película pertenece obligatoriamente a un género y solo a uno. Pero de un determinado género puede haber varias películas o ninguna. 3.- El género de una película se caracteriza por un código, nombre (que es único) y su descripción. 4.- Todas las películas tienen una o varias copias y pueden ser alquiladas. Una copia es de una y solo una película. Si se descataloga la película no tiene sentido mantener la copia. 5.- Las copias de cada película se identifican con un número sucesivo (1, 2, 3, 4 …) y de ellas se guarda la fecha de compra y estado de conservación. 6.- Las copias de la películas se alquilan a clientes. De los clientes interesa su DNI, nombre, fecha_nacimiento, edad y varios email. 7.- Los clientes puedes ser de dos tipos: 7.1.- Socios: de ellos se guarda además un código, fecha de alta, descuento y cuota. 7.2.- Eventuales: de estos clientes se guarda la fecha de su primer alquiler. 8.- Tanto los clientes socios como los eventuales han alquilado al menos una copia, pudiendo alquilar varias, y cada copia puede haber sido alquilada varias veces o ninguna. 9.- Al alquilar una copia interesa saber la fecha de alquiler y precio de alquiler. 10.- Los clientes eventuales pueden haber sido ser invitados por un único cliente eventual, y un cliente eventual puede invitar a varios cliente eventuales o a ninguno.
Diagrama ERE y Modelo Relacional
Como resultado del diseño CONCEPTUAL obtuvimos el siguiente diagrama Entidad/Relación Extendido:
Software DIA(Elaboración propia)
Como resultado del diseño LÓGICO obtuvimos el siguiente modelo relacional:
Office Libre(Elaboración propia)
Restricciones para diseño físico
Para realizar el diseño físico y su implantación en MySQL nos dan las siguientes especificaciones y restricciones. Si en alguna tabla no se especifica de forma explícita alguna restricción y/o tipo de dato de ciertas columnas, es porque se debe deducir del propio modelo relacional y/o valor que va a almacenar.
Tabla GENERO.
El código es alfanumérico de tamaño fijo y de 4 caracteres. El nombre como máximo de 20 caracteres, y no se puede repetir. La descripción puede ocupar mas de 300 caracteres.
Tabla PELICULA.
El código es alfanumérico de tamaño fijo y de 5 caracteres. El título tiene como máximo 30 caracteres y es obligatorio (no puede quedar vacío).
Tabla COPIA.
El num_copia es un entero. El estado solo puede tomar los siguientes valores ('malo',' regular', 'bueno','excelente').
Tabla CLIENTE.
El DNI es alfanumérico de tamaño fijo. El nombre como máximo de 60 caracteres.
Tabla EMAIL.
El <span lang="en">email </span>es alfanumérico de tamaño variable, máximo 40 caracteres.
Tabla se_alquila.
El importe puede tener parte decimal, no puede ser nulo y por defecto tiene el valor 2,5.
Tabla SOCIO.
El código es alfanumérico de tamaño fijo 5 caracteres, obligatorio y no se puede repetir. El descuento puede tener parte decimal.
La cuota es un valor entero, obligatoria y por defecto tiene el valor 10.
RESTRICCIONES DE CLAVE AJENA para borrados y modificaciones.
Al modificar la clave primaria de cualquier tabla principal se deben modificar de igual forma las claves ajenas o foráneas relacionadas.
No se puede eliminar un género si tiene películas relacionadas.
Si se elimina una película se eliminan sus copias relacionadas.
No se puede eliminar una copia que esté alquilada.
No se puede eliminar un cliente con copias alquiladas.
Si se elimina un cliente, se deben eliminar todos sus email relacionados y sus particularidades como socio y/o eventual.
Si se elimina un eventual que es el que invitó, los invitados por él quedan sin valor en la columna dni_invita.
Creación de base de datos y tablas en SQL.
Redactamos las sentencias SQL para crear la base de datos y cada tabla desde la línea de comandos o bien en la ventana SQL de la herramienta o cliente Workbench.
Es IMPORTANTE el orden de creación de tablas. Podemos seguir dos criterios de creación:
Crear primero las tablas que no tienen claves foráneas (no referencian a otras tablas), pues no se puede crear una tabla T1 que referencia a la tabla T2, si no se ha creado antes la tabla T2.
Crear todas las tablas y después con ALTER añadir la restricción de claves ajenas o foráneas.
En este caso vamos a seguir la estrategia 1.- Crear primero las tablas que no tienen claves foráneas y después ir creando las tablas que referencian con sus claves foráneas a tablas que ya hemos creado previamente.
Además vamos a dar nombre personalizado a las restricciones o CONSTRAINT.
En el siguiente videotutorial puedes ver cómo se realiza la implantación física de la base de datos CAMPEONATO en MySQL, que posteriormente viene detallada en las otras secciones.
En este caso vamos a crear todas las tablas sin ninguna restricción de clave foránea y después con ALTER TABLE añadiremos esas restricciones de FOREIGN KEY.
Veremos como crear y modificar tablas en modo gráfico.
Una vez implantada la base de datos CAMPEONATO obtendremos su modelo mediante ingeniería inversa.
Creación de la base de datos campeonato, añadir después claves foráneas y obtener el modelo con ingeniería inversa
La Asociación Andaluza de Videojuegos o e-Sports necesita gestionar diferentes campeonatos de juegos online mediante una base de datos que almacene información sobre los concursantes, sus equipos y los diferentes juegos en los que participan los concursantes.
Se debe realizar el diseño de una base de datos relacional a partir de las siguientes especificaciones o requisitos que debe cumplir:
De los concursantes se desea almacenar un código, su nombre, sexo, fecha de inscripción y cuota de inscripción. 2.- Cada concursante pertenece obligatoriamente a un equipo y solo a uno. Pero en un equipo puede haber ninguno o varios concursantes. 3.- Cada equipo se caracteriza por un código, nombre, comunidad y año de fundación. 4.- Los equipos, no todos, pueden organizar varios juegos. Cada juego es organizado por uno y solo un equipo. 5.- Los juegos se caracterizan por su código, nombre, nivel de dificultad y número de megusta. 6.- Los concursantes pueden participar en ninguno o varios juegos, independientemente del equipo al que pertenezcan. 7.- En un juego pueden participar ninguno o varios concursantes. 8.- Cuando un concursante participa en un juego, se anota la fecha de comienzo en la que inició ese juego y se guardan también los puntos que va acumulando en ese juego. 9.- Los concursantes pueden designar a otro concursante como su ídolo, de manera que un concursante puede ser ídolo de ninguno o varios concursantes, pero como ídolo solo puede tener a un único concursante o a ninguno.
Diagrama ERE y Modelo Relacional
Como resultado del diseño CONCEPTUAL obtuvimos el siguiente diagrama Entidad/Relación:
Software DIA(Elaboración propia)
Como resultado del diseño LÓGICO obtuvimos el siguiente modelo relacional:
Donde en el modelo textual se ha indicado la restricción de cada clave ajena o foránea en cuanto a los borrados y las modificaciones:
BR: borrado restrictivo, BN: borrado con puesta a nulos, BC: borrado en cascada.
MC: modificación en cascada.
Libre Office(Elaboración propia)
Restricciones para diseño físico
Para realizar el diseño físico y su implantación en MySQL nos dan las siguientes especificaciones y restricciones. Si en alguna tabla no se especifica de forma explícita alguna restricción y/o tipo de dato de ciertas columnas, es porque se debe deducir del propio modelo relacional y/o valor que va a almacenar.
Tabla CONCURSANTE.
El código es alfanumérico de tamaño fijo y de 3 caracteres. El nombre como máximo de 30 caracteres y obligatorio. La cuota de inscripción puede llevar parte decimal.
El sexo solo puede tomar los valores 'H' o 'M' y es obligatorio.
Tabla EQUIPO.
El código es un valor alfanumérico de 2 caracteres. El nombre es obligatorio y de una máximo de 50 caracteres alfanuméricos. La comunidad como máximo de 20 caracteres alfanuméricos.
Tabla JUEGO.
El código es alfanumérico de tamaño fijo de 3 caracteres.
El nombre es de tamaño variable, como máximo de 40 caracteres, obligatorio y no se puede repetir.
La dificultad es obligatoria y solo puede tomar los siguientes valores: 'alta', ',media', 'baja'.
la columna megusta es de tipo entero y no puede ser negativa.
Tabla participa.
Los puntos es una columna numérica, no puede ser negativa, es obligatoria y con valor por defecto 0.
RESTRICCIONES DE CLAVE AJENA para borrados y modificaciones.
Al modificar la clave primaria de cualquier tabla principal se deben modificar de igual forma las claves ajenas o foráneas relacionadas.
No se puede eliminar un equipo si tiene concursantes relacionados.
No se puede eliminar un equipo si tiene juegos relacionados.
Si se elimina un concursante se eliminan las filas relacionadas en la tabla participa.
Si se elimina un concursante que es ídolo, los concursantes que lo tenían como ídolo quedan sin ídolo.
Si se elimina un juego, se eliminan las filas relacionadas en la tabla participa.
Creación de base de datos y tablas en SQL
Redactamos las sentencias SQL para crear la base de datos y cada tabla desde la línea de comandos o bien en la ventana SQL de la herramienta o cliente Workbench.
Es IMPORTANTE el orden de creación de tablas. Podemos seguir dos criterios de creación:
Crear primero las tablas que no tienen claves foráneas (no referencian a otras tablas), pues no se puede crear una tabla T1 que referencia a la tabla T2, si no se ha creado antes la tabla T2.
Crear todas las tablas y después con ALTER TABLE añadir la restricción de claves ajenas o foráneas.
En este caso vamos a seguir la estrategia 2.- Crear todas las tablas, en cualquier orden, y después con ALTER TABLE añadir la restricción de clave ajena o foránea a las tablas que corresponda.
En el siguiente enlace puedes descargar el script SQL de creación de la base de datos campeonato.
Observa que el código de la vista es una consulta, que en SQL se realiza con la sentencia SELECT.
Estudiaremos la sentencia SELECT en la unidad 4, por lo que ahora mismo no es necesario que sepas exactamente lo que hace cada una de las partes de esa SELECT. En la unidad 4 volveremos a a trabajar con vistas.
¿Has probado los ejemplos en tu base de datos CAMPEONATO?
Si pruebas los pruebas, verás que no s e arrojan datos, ya que de momento todas las tablas de la base de datos están vacías.
Una vez finalizado el diseño físico en SQL de la base de datos podemos verificar mediante diversas pruebas que la implementación realizada se ajusta al modelo, así como hacer pruebas de rendimiento de la base de datos..
¿Cómo podemos realizar esa verificación?
Existen herramientas para realizar diferentes tipos de pruebas sobre una base de datos, antes de su despliegue, incluso después de éste. Lo mejor, sería realizar las pruebas antes de poner en producción la base de datos. Los diferentes tipos de pruebas se pueden clasificar en:
Pruebas de la estructura de la base de datos. Consiste en realizar pruebas o testing de tablas y columnas, pruebas de esquemas, pruebas de procedimientos almacenados y vistas, etc.
Pruebas funcionales. Supone comprobar la funcionalidad de la base de datos desde el punto de vista del usuario. El tipo más común de pruebas funcionales son las pruebas de caja blanca y caja negra.
Pruebas no funcionales. Esto supone un conjunto de diferentes pruebas que van enfocadas a verificar el rendimiento de la base de datos.
Verifican la estructura interna de la base de datos y restricciones de las especificaciones que se ocultan a los usuarios, como por ejemplo, probar los disparadores de la base de datos.
Supone verificar la integración de la base de datos para verificar su funcionalidad. Estos casos de prueba son simples y se utilizan para verificar los datos de entrada y salida de la función.
En este punto de la unidad, para que te hagas una idea de algunas de estas pruebas, nosotros vamos a realizar y razonar diferentes comprobaciones sobre la estructura o esquema de la base de datos campeonato, para comprobar que la implementación realizada se ajusta al modelo y que las restricciones de columna y de integridad se cumplen.
¡Vamos a ello!
EJEMPLO.-
Observa el modelo de la base de datos campeonato y las relaciones de la tabla concursante. Así como las restricciones que pusimos en su modelo físico.
MySQL Workbench(Elaboración propia)
La tabla concursante está implementada de esta forma:
MySQL Workbench(Elaboración propia)
Sobre la tabla CONCURSANTE, razonamos si podrían realizarse las siguientes operacionesCRUD, en la secuencia indicada.
Para ello, suponemos ya insertadas las siguientes filas en la tabla equipo:
Insertar las siguientes filas de datos en este orden: ('A22','Benita Naranja','2019-09-23',50, 'M', NULL,'03'), ('B22','Marta Violeta','2019-01-09',45,'M','A22','01'),('A44','Elvira Blanco','2019-04-09',100,NULL,'A22','02'),('A33','Ismael Rojo','2018-03-07',45, 'H','A55','01'), ('C01','Juan Amarillo','2019-02-03',75,'H','A22','04')
Sería correcta la inserción de 'A22', 'B22'
Daría error la inserción de 'A44', pues la columna sexo no admite NULL
Daría error la inserción de 'A33' pues se infringe el valor de clave foránea en la columna cdidolo, ya que no existe en la tabla concursante el valor de código 'A55' en la clave primaria de esa tabla, columna cdconcur.
Daría error la inserción de 'C01' pues se infringe la integridad referencial, ya que la columna cdequipo es clave foránea referenciando a la tabla equipo, y en equipo no existe el valor '04' en la clave primaria de esta tabla, columna cdequipo.
Eliminar al concursante A22. En caso de que sea posible ¿Cómo debe responder el sistema?
Si se puede eliminar, y el sistema pone a NULL la columna cdidolo (clave foránea de la tabla) en todas las filas en las que aparecía el consursante A22.
Eliminar el equipo de código '01'
No se podría eliminar el equipo '01', puesto que la columna cdequipo en concursante es clave foránea referenciando a equipo, y su restricción ON DELETE RESTRICT, no deja eliminar el equipo pues hay filas relacionadas con él en la tabla concursante, el concursante 'B22'.
CRUD hace referencia a un acrónimo en el que se reúnen las primeras letras de las cuatro operaciones fundamentales sobre una base de datos: R ead (Leer registros), W rite (Escribir registros), U pdate (actualizar registros) y D elete (borrar registros).
Supone verificar la integración de la base de datos para verificar su funcionalidad. Estos casos de prueba son simples y se utilizan para verificar los datos de entrada y salida de la función.
Verifican la estructura interna de la base de datos y restricciones de las especificaciones que se ocultan a los usuarios, como por ejemplo probar los disparadores de la base de datos.
Con las sentencias vistas hasta ahora Noiba y Vindio ya conocen todas las instrucciones necesarias para añadir, modificar o eliminar todo tipo de índices. Sin embargo, Juan les aconseja que consulten la documentación del lenguaje SQL, y revisen si es necesario añadir índices a alguna de las tablas con el propósito de optimizar las consultas.
Considera pues que es importante conocer la sentencia que permite crear índices y también aprender algo más sobre la utilidad de los índices en una base de datos.
Puedes probar todos los ejemplos que se expongan en las bases de datos cuyo script de creación te proporcionamos en el apartado 8.4.-, en particular en la base de datos CAMPEONATO
Sabemos que los índices ayudan a la localización más rápida de la información contenida en las tablas. Pero, ¿dónde se almacenan? ¿debemos tener muchos índices en las tablas?
Vamos a verlo:
Almacenamiento: La información sobre los índices se almacena en una tabla de índices de tamaño mucho más reducido que la tabla de datos, ordenados de forma ascendente junto con información relativa a donde están almacenados los datos correspondientes a cada valor del índice.
Acceso: El usuario no puede acceder a la tabla de índices aunque siempre que realiza una operación condicionada a los valores de un índice, el servidor MySQL busca primero en la tabla de índices para, a través de la información almacenada en el índice, acceder de forma directa a la los datos correspondientes de la tabla de datos.
Ventajas y desventajas: Si bien un índice permite consultar de forma más rápida la tabla, las operaciones de añadir filas, modificar filas o eliminar filas consumen más tiempo. Además, los datos de una tabla en la que se definen índices ocupan más espacio que el dedicado exclusivamente a esos datos, puesto que necesita un espacio adicional para almacenar una tabla de índices. Por ello, no es aconsejable definir índices de manera indiscriminada. Lo que se debe hacer es definir índices sobre columnas a partir de las cuales se vayan a hacer consultas frecuentes.
Creación de índices
Veamos ahora la sentencia SQL para crear índices, que es CREATE INDEX.
CREATE INDEX:
Permite añadir índices a tablas existentes. Un índice, al ser creado, puede recibir un nombre. Si no se especifica ese nombre, el índice recibe el nombre de la primera columna que forma ese índice. Un índice se puede definir sobre una columna o sobre un grupo de columnas. Para los índices formados por columnas de tipo CHAR o VARCHAR se puede especificar que sólo los primeros caracteres de esas columnas formen el índice con un número entre paréntesis (longitud).
UNIQUE | FULLTEXT : Si no se especifica UNIQUE o FULLTEXT el índice admite valores duplicados. En caso de que sea UNIQUE los valores del índice no podrán repetirse. El índice de tipo FULLTEXT sirve para buscar palabras dentro del texto contenido en las columnas correspondientes. Un índice FULLTEXT sólo se admite sobre columnas CHAR, VARCHAR o cualquiera de los tipos TEXT.
ASC | DESC: Los índices pueden ordenar los datos de forma ascendente (ASC) o descendente (DESC).
Ejemplo: Crear un índice para la columna nombre de la tabla equipo.
CREATE INDEX ind_nom_equ ON equipo (nombre);
Además de con la sentencia CREATE INDEX, en MySQL también podemos crear índices de la siguientes maneras:
Al mismo tiempo que creamos la tabla con el uso de la opción INDEX.
CREATE TABLE equipo (
cdequipo CHAR(2) NOT NULL,
nombre VARCHAR(50) NOT NULL,
comunidad VARCHAR(20),
anio_funda YEAR,
PRIMARY KEY(cdequipo),
INDEX ind_nom_equ (nombre)
) ENGINE=INNODB;
Con la sentencia ALTER TABLE si es que la tabla ya existe, mediante ADD INDEX.
ALTER TABLE equipo
ADD INDEX ind_nom_equ (nombre);
Eliminación y modificación de índices
Para eliminar los índices se utiliza la sentencia DROP INDEX.
Aunque no se trata de instrucciones propias del LDD, Noiba se plantea que quizá le sea útil en su trabajo con la base de datos TalleresFaber estudiar ahora el funcionamiento de las vistas. Se trata de consultas almacenadas que trataremos igual que si fueran tablas. La ventaja que ofrecen las vistas para Noiba es que se podrá mostrar los datos que el usuario pueda manejar como si fuera una tabla y ocultarle el resto. Esto puede ser importante para que no todos los empleados del taller tengan acceso a toda la información almacenada.
Una vista (VIEW) es sinónimo de una consulta almacenada en MySQL como una tabla virtual, que permite acceder a los datos que se obtienen como resultado de una consulta determinada. Una vez creada una vista, tiene un comportamiento similar al de una tabla, ya que se pueden realizar acciones como consultar datos sobre la vista. El usuario maneja aparentemente datos de la vista, pero realmente está manejando datos de las tablas de cuya consulta se ha obtenido la vista.
Los datos de una vista, como se ha dicho, no existen realmente. Cuando se hace una consulta sobre una vista, el servidor de base de datos realiza la consulta SELECT que crea la vista primero y, sobre los datos obtenidos (la vista) obtiene el resultado de la consulta que se ha hecho.
¿Cuáles son las ventajas del uso de vistas o VIEW.?
Simplifican las consultas. Ya que se puede guardar en una vista la definición de una consulta compleja que requiera acceder a varias tablas, combinándolas, y/o realizando varias operaciones como: agrupamientos, filtros de grupos y subconsultas. Una vez definida la consulta compleja en la VIEW o vista, se podrá ejecutar con sólo lanzar una SELECT básica sobre esa vista (SELECT * FROM nombre_vista;).
Confieren seguridad a la base de datos. Ya que proporcionan un único punto de acceso a la base de datos, la VIEW o vista, sin tener que conocer el nombre las tablas y columnas implicadas en la consulta.
Permiten reutilizar código. Ya que una vez creada la vista, ésta puede ser utilizada por diferentes aplicaciones o directamente contra el servidor de bases de datos, tantas veces como sea necesario.
Las vistas se usan para poder evitar que determinados usuarios accedan a ciertas tablas, dándoles acceso solamente a una parte de esas tablas a través de las vistas.
Analizaremos ahora la primera parte de esta sentencia, ya que la segunda parte incluye una consulta con la sentencia SELECT, que veremos en la siguiente unidad:
Los nombres que le demos a las columnas: NombreColumna1, NombreColumna2, …, no tienen por qué coincidir con los nombres que tenían en las tablas originales, pero sí tiene que haber el mismo número de columnas devueltas en la sentencia <b>SELECT</b> que en la vista. Si no se especifica ninguna columna, las columnas creadas para la vista son las que se obtienen en la consulta.
Las vistas no pueden contener subconsultas ni variables de usuario o del sistema.
La vista no puede tener el mismo nombre que una tabla existente.
Si una vista está basada en una sola tabla, se pueden añadir, modificar o eliminar los datos de la tabla de la que se obtiene la vista añadiendo, modificando o eliminado filas en la vista. La vista realmente no se actualiza porque es virtual.
Modificar Vistas
ALTER VIEW
Modifica la definición de una vista existente. Es una sentencia similar a CREATE VIEW.
Sobre la base de datos CAMPEONATO vamos a crear una vista de nombre vpuntos_juegos que obtenga un listado de todos los juegos, se participe o no en ellos, con su código y nombre, nombre de su equipo, media de puntos del juego y total de concursantes participantes en ese juego. Si no se participa en el juego, la media puntos debe ser 0, al igual que el total de concursantes.
El código de la vista sería el siguiente:
CREATE VIEW vpuntos_juegos (cdjuego, juego, equipo, media_puntos, nconcur)AS
SELECT j.cdjuego, j.nombre,e.nombre,IFNULL(AVG(p.puntos), 0), COUNT(p.cdconcur)
FROM juego j
INNER JOIN equipo e ON e.cdequipo=j.cdequipo
LEFT JOIN participa p ON p.cdjuego=j.cdjuego
GROUP BY p.cdjuego;
Una vez creada la vista, para lanzar una consulta sobre ella, sería:
SELECT * FROM vpuntos_juegos;
Y si queremos una consulta concreta, podemos usar la VIEW como cualquier otra tabla de la base de datos e incluir las cláusulas de la sentencia SELECT que necesitemos, así como combinarla con otras tablas.
Por ejemplo, si nos interesa el listado de sólo los juegos con más de 2 concursantes, y ordenado de más a menos concursantes, sería:
SELECT *
FROM vpuntos_juegos
WHERE nconcur >=2
ORDER BY nconcur DESC;
¿Has probado los ejemplos en tu base de datos CAMPEONATO?
Si pruebas los pruebas, verás que no s e arrojan datos, ya que de momento todas las tablas de la base de datos están vacías.
Observa que el código de la vista es una consulta, que en SQL se realiza con la sentencia SELECT.
Estudiaremos la sentencia SELECT en la unidad 4, por lo que ahora mismo no es necesario que sepas exactamente lo que hace cada una de las partes de esa SELECT. En la unidad 4 volveremos a trabajar con vistas.
En la base de datos campeonato se necesita crear una vista llamada puntos_concursantes<b> </b>que muestre el código y nombre de los concursantes, nombre de su equipo, nombre y puntos de los juegos en los que participa.
Con Vindio y Noiba hemos recorrido todas las sentencias que podemos necesitar para realizar el diseño físico de una base de datos. Pero aún nos queda conocer una serie de sentencias que no corresponden al LDD pero que resultan muy útiles a la hora de trabajar con SQL. Muchas veces resulta más fácil recurrir a ellas que consultar la información desde las opciones que ofrece la herramienta gráfica del SGBD. Veremos a continuación cuáles son esas sentencias y cómo se utilizan.
Finalmente Noiba y Vindio ya tienen el diseño físico de la base de datos que recoge la actividad del taller mecánico. Antes de dar por concluido el trabajo de diseño e implantación, Juan les indica que deben confeccionar el diccionario de datos. En él se hace una descripción detallada de todos los elementos que intervienen en el sistema y nos servirá para tener una visión global de la base de datos, sin ambigüedad. Esta información puede ser útil tanto para Noiba, como para otras personas que se incorporen al proyecto.
Afortunadamente la mayoría de los SGBD incorporan utilidades o herramientas que confeccionan este diccionario automáticamente.
En MySQL esta información se puede obtener desde Workbench y nos muestra en un informe la descripción de cada objeto de la base de datos y sus propiedades. Este informe está formado por las tablas, vistas y sus descripciones, permitiendo saber:
Estructura lógica y física de la base de datos
Los usuarios de la base de datos.
Restricciones de integridad sobre las tablas de la base de datos.
Espacio asociado a cada objeto en la base de datos.
En MySQLla información relativa a los metadatos de la base de datos se encuentra en <b><i>INFORMATION_SCHEMA</i></b>. Se trata de una base de datos que almacena información acerca de todas las otras bases de datos que tengamos en nuestro servidor. Cada usuario tiene derecho a acceder a estas tablas, pero sólo a los registros que se refieren a los objetos a los que tiene permiso de acceso. Se puede acceder a su contenido con <b><i>SELECT</i></b> pero no se puede insertar, actualizar o borrar.
Una vez que Naroadomina bastante bien el lenguaje DDL del SQL, le pregunta a Juan si se podría utilizar alguna herramienta gráfica para mejorar la productividad en el diseño y desarrollo del script SQL de creación de una base de datos. Juan le dice que sí, que existen muchas herramientas gráficas para ello. Entre ellas la herramienta de modelado de Workbench. Esta herramienta permite realizar el diseño en modo gráfico de una base de datos y obtener el código SQL de creación de la base de datos de forma automática, incluso dejarla implenatda en el servidor, si existe una conexión activa al servidor MySQL.
En este vamos a utilizar a utilizar la herramienta de modelado de bases de datos de Workbench y a realizar ingeniería directa.
¿Y qué es la ingeniería directa?
En el contexto de bases de datos, la ingeniería directa permite:
Generar automáticamente el script SQL de creación de una base de datos, a partir del modelo.
Implantar la base de datos en el SGBD, a partir del modelo.
Por tanto, aprenderás a :
Realizar el diseño o modelo gráfico de una base de datos. Este modelo gráfico o esquema de la base de datos, será una mezcla del modelo lógico y el físico, pues tendremos que especificar el tipo de datos de cada columna de las tablas que diseñemos, así como sus restricciones.
Generar, a partir del modelo gráfico, el script SQL de creación de la base de datos, y sus tablas, mediante ingeniería directa.
Dejar implantada la base de datos en el servidor MySQL a partir del modelo. Para ello es necesario que haya conexión con el servidor de bases de datos.
En el siguiente tutorial dispones de un ejemplo práctico sobre cómo realizar con Workbench:
El modelado o diseño de gráfico de una base de datos.
Obtener el script SQL de creación de la base de datos y sus tablas, a partir del modelo, con ingeniería directa.
Dejar implantada la base de datos diseñada en el servidor MySQL, a partir de su modelo, con ingeniería directa. (Se necesita tener conexión con el SGBD)
Materiales desarrollados inicialmente por el Ministerio de Educación, Cultura y Deporte y actualizados por el profesorado de la Junta de Andalucía bajo licencia Creative Commons BY-NC-SA.
Antes de cualquier uso leer detenidamente el siguenteAviso legal
Ubicación: En la Unidad Mejora (tipo 3): * Pasar a la unidad GBD01 lo relativo a la Instalación del SGBD MySQL y primeros pasos con MySQL (Apartados 1. 2. 3.).
* En toda la unidad: cambiar los ejemplos en los que se utiliza una notación deprecated en los tipos de dato numéricos (respecto al formato de impresión).
* A partir del Apartado 9.4 en el que se proporciona al alumnado 4 ejemplos de bases de datos completas en SQL, se deberían sustituir los ejemplos propuestos sobre una base de datos inexistente,
para que se ilustren sobre alguna de las bases de datos que ya ha podido instalar el alumnado.
* Incluir un apartado con ejemplos para verificar mediante un conjunto de datos que la implementación de una base de datos se ajusta al modelo, ya que es un Criterio de Evaluación
y no existe ningún ejemplo al respecto.
* Incluir en el apartado 11, las ventajas que proporciona el uso de vistas, cambiando también los ejemplos para
realizarlos sobre alguna de las bases de datos del apartado 9.4.
* En el apartado 12, cambiar el Ejercicio Resuelto haciendo uso de una de las bases de datos que ya está resuelta y documentada en la unidad.
* En el apartado 13, añadir un videotutorial resumen de cómo crear/añadir tablas a una base de datos en modo gráfico con todas sus restricciones, obtener por ingeniería inversa el modelo de la base de datos
y obtener el diccionario de datos desde Workbench.
* Añadir un apartado nuevo o Anexo en el que se pueda ver en un tutorial cómo realizar paso paso la creación de una base datos diseñando su modelo
mediante la herramienta Workbench y dejándola implantada mediante ingeniería directa.
* Modificar el mapa conceptual según los nuevos contenidos.
* Modificar las orientaciones al Alumnado según los nuevos contenidos.
Ubicación: Mapa Mejora (Mapa conceptual): Actualizado según los nuevos contenidos.
Ubicación: Tabla de contenidos Mejora (Orientaciones del alumnado): Actualizado el índice según los nuevos contenidos.
Versión: 02.00.00
Fecha de actualización: 24/05/21
Autoría: Isabel Cruz Granados
Ubicación: Apartado 9.4 Mejora (tipo 3): En el apartado 9.4 se puede cambiar el nombre del epígrafe para que sea:
9.4.- Casos resueltos, e incluir los mismos ejemplos que se han visto en los tutoriales propuestos de la unidad 2 (punto 4.2 y 6.)
finalizando así las fases del diseño de una base de datos con la implantación fisica de los ejemplos desarrollados anteriormente.
