SQL (Structured Query Language) es un lenguaje de programación diseñado para almacenar, manipular y recuperar datos almacenados en bases de datos relacionales. La primera encarnación de SQL apareció en 1974, cuando un grupo de IBM desarrolló el primer prototipo de una base de datos relacional. Relational Software (luego se convirtió en Oracle) lanzó la primera base de datos relacional comercial.
MySQL es una marca registrada por MySQL AB. Parte del material que se expone aquí, concretamente las referencias de funciones del API de MySQL y de la sintaxis de SQL, son traducciones del manual original de MySQL que se puede encontrar en inglés en www.mysql.com.
Ventajas:
- Acceso a las bases de datos de forma simultánea por varios usuarios y/o aplicaciones.
- Seguridad, en forma de permisos y privilegios, determinados usuarios tendrán permiso para consulta o modificación de determinadas tablas. Esto permite compartir datos sin que peligre la integridad de la base de datos o protegiendo determinados contenidos.
- Potencia: SQL es un lenguaje muy potente para consulta de bases de datos, usar un motor nos ahorra una enorme cantidad de trabajo.
- Portabilidad: SQL es también un lenguaje estandarizado, de modo que las consultas hechas usando SQL son fácilmente portables a otros sistemas y plataformas. Esto, unido al uso de C/C++ proporciona una portabilidad enorme.
Definiciones
Base de datos
Podemos considerar que es un conjunto de datos de varios tipos, organizados e interrelacionados. Estos datos deben estar libres de redundancias innecesarias y ser independientes de los programas que los usan.
Dato
Podemos decir que un dato es una información que refleja el valor de una característica de un objeto real, sea concreto o abstracto, o imaginario
SGBD (DBMS)
Son las siglas que significan Sistema de Gestión de Bases de Datos, en inglés DBMS, DataBase Manager System. En este caso, MySQL es un SGBD, o mejor dicho: nuestro SGBD.
Consulta
Es una petición al SGBD para que procese un determinado comando SQL. Esto incluye tanto peticiones de datos como creación de bases de datos, tablas, modificaciones, inserciones, etc.
Redundancia de datos
Decimos que hay redundancia de datos cuando la misma información es almacenada varias veces en la misma base de datos. Esto es siempre algo a evitar, la redundancia dificulta la tarea de modificación de datos, y es el motivo más frecuente de inconsistencia de datos. Además requiere un mayor espacio de almacenamiento, que influye en mayor coste y mayor tiempo de acceso a los datos.
Inconsistencia de datos
Sólo se produce cuando existe redundancia de datos. La inconsistencia consiste en que no todas las copias redundantes contienen la misma información. Así, si existen diferentes modos de obtener la misma información, y esas formas pueden conducir a datos almacenados en distintos sitios. El problema surge al modificar esa información, si lo sólo cambiamos esos valores en algunos de los lugares en que se guardan, las consultas que hagamos más tarde podrán dar como resultado respuestas inconsistentes (es decir, diferentes). Puede darse el caso de que dos aplicaciones diferentes proporcionen resultados distintos para el mismo dato.
Integridad de datos
Cuando se trabaja con bases de datos, generalmente los datos se reparten entre varios ficheros. Si, como pasa con MySQL, la base de datos está disponible para varios usuarios de forma simultánea, deben existir mecanismos que aseguren que las interrelaciones entre registros se mantienen coherentes, que se respetan las dependencias de existencia y que las claves únicas no se repitan.
Modelado de base de datos
El proceso de trasladar un problema del mundo real a un ordenador, usando bases de datos, se denomina modelado.
Para el modelado de bases de datos es necesario seguir un procedimiento determinado. Pero, cuando el problema a modelar es sencillo, con frecuencia estaremos tentados de pasar por alto algunos de los pasos, y crear directamente bases de datos y tablas. En el caso de las bases de datos, como en cualquier otra solución informática, esto es un gran error. Siempre será mejor seguir todos los pasos del diseño, esto nos ahorrará (con toda seguridad) mucho tiempo más adelante. Sobre todo si alguna vez tenemos que modificar la base de datos para corregir errores o para implementar alguna característica nueva, algo que sucede con mucha frecuencia.
Modelo entidad-relación
En esencia, el modelo entidad-relación (en adelante E-R), consiste en buscar las entidades que describan los objetos que intervienen en el problema y las relaciones entre esas entidades.
Todo esto se plasma en un esquema gráfico que tiene por objeto, por una parte, ayudar al programador durante la codificación y por otra, al usuario a comprender el problema y el funcionamiento del programa.
Entidad
Entidad: es una representación de un objeto individual concreto del mundo real.
Conjunto de entidades: es la clase o tipo al que pertenecen entidades con características comunes.
Atributo: cada una de las características que posee una entidad, y que agrupadas permiten distingirla de otras entidades del mismo conjunto.
Dominio: conjunto de valores posibles para un atributo.
Relación
Interrelación: es la asociación o conexión entre conjuntos de entidades.
Grado: número de conjuntos de entidades que intervienen en una interrelación
Clave
Es un conjunto de atributos que identifican de forma unívoca una entidad.
Clave candidata: es cada una de las claves mínimas existente en un conjunto de entidades.
Clave principal: (o primaria), es una clave candidata elegida de forma arbitraria, que usaremos siempre para identificar una entidad.
Generalización: es el proceso según el cual se crea un conjunto de entidades a partir de otros que comparten ciertos atributos.|
La desventaja de la generalización es que se desperdicia espacio de almacenamiento, ya que sólo algunos de los atributos no comunes contienen información en cada entidad, el resto se desperdicia.
La ventaja es que podemos establecer el mismo tipo de interrelación con cualquier entidad del conjunto. En nuestro ejemplo, en lugar de tener que establecer tres interrelaciones de péstamo, o ubicación, bastará con una de cada tipo.
Especialización
Es el proceso inverso al de generalización, en lugar de crear una entidad a partir de varias, descomponemos una entidad en varias más especializadas.
Especialización: es el proceso según el cual se crean varios tipos de entidades a partir de uno. Cada una de los conjuntos de entidades resultantes contendrá sólo algunos de los atributos del conjunto original.
La idea es lógica: si la generalización tiene ventajas e inconvenientes, cuando los inconvenientes superan a las ventajas, será conveniente hacer una especialización.
En realidad, es irrelevante si una entidad en fruto de una generalización o de una especialización, no deja de ser una entidad, y por lo tanto, no afecta al modelo.
Representación de entidades y relaciones: Diagramas
No hay unanimidad total con respecto a la representación de diagramas E-R, he encontrado algunas discrepancias en los distintos documentos que he consultado, dependiendo de la variedad concreta del modelo que usen. Pero a grandes rasgos todos están de acuerdo en lo que expondremos aquí.
Entidad
Las entidades se representan con un rectángulo, y en su interior el nombre de la entidad:
Las entidades débiles pueden representarse mediante dos rectángulos inscritos.
Atributo
Los atributos se representan mediante elipses, y en su interior el nombre del atributo:
Algunas variantes de diagramas E-R usan algunas marcas para indicar que cierto atributo es una clave primaria, como subrayar el nombre del atributo.
Atributo multivaluado: (o multivalorado) se dice del atributo tal que para una misma entidad puede tomar varios valores diferentes, es decir, varios valores del mismo dominio.
Interrelación
Las interrelaciones se representan mediante rombos, y en su interior el nombre de la interrelación:
En los extremos de las líneas que parten del rombo se añaden unos números que indican la cantidad de entidades que intervienten en la interrelación: 1, n. Esto también se suele hacer modificando el extremo de las líneas. Se añade el rol que representa cada entidad:
Dominio
A veces es conveniente añadir información sobre el dominio de un atributo, los dominios se representan mediante hexágonos, con la descripción del dominio en su interior:
Diagrama
Un diagrama E-R consiste en representar mediante estas figuras un modelo completo del problema, proceso o realidad a describir, de forma que se definan tanto las entidades que lo componen, como las interrelaciones que existen entre ellas.
Al final, nuestra mejor arma es la práctica. Cuantos más problemas diferentes modelemos más aprenderemos sobre el proceso y sobre los problemas que pueden surgir.
Construir un modelo E-R
Podemos dividir el proceso de construir un modelo E-R en varias tareas más simples. El proceso completo es iterativo, es decir, una vez terminado debemos volver al comienzo, repasar el modelo obtenido y, probablemente, modificarlo.
Uno de los primeros problemas con que nos encontraremos será decidir qué son entidades y qué atributos.
La regla principal es que una entidad sólo debe contener información sobre un único objeto real.
Proceso
Para crear un diagráma conceptual hay que meditar mucho. No hay un procedimiento claro y universal, aunque sí se pueden dar algunas directrices generales:
- Hablar con el cliente e intentar dejar claros los parámetros y objetivos del problema o proceso a modelar. Por supuesto, tomar buena nota de todo.
- Estudiar el planteamiento del problema para:
- Identificar los conjuntos de entidades útiles para modelar el problema,
- Identificar los conjuntos de interrelaciones y determinar su grado y tipo (1:1, 1:n o m:n).
- Trazar un primer diagrama E-R.
- Identificar atributos y dominios para los conjuntos de entidades e interrelaciones.
- Seleccionar las claves principales para los conjuntos de entidades.
- Verificar que el modelo resultante cumple el planteamiento del problema. Si no es así, se vuelve a repasar el proceso desde principio.
- Seguir con los siguientes pasos: traducir el diagrama a un modelo lógico, etc.
Extensiones
Existen varias extensiones al modelo E-R que hemos visto, aunque la mayor parte de ellas no las vamos a mencionar.
Una de ellas es la cardinalidad de asignación, que se aplica a atributos multivaluados. Consiste en establecer un número mínimo y máximo de posibles valores para atributos multivaluados.
No siempre será posible establecer una cardinalidad. En el ejemplo planteado se la elegido de una forma completamente arbitraria, y probablemente no sea una buena idea. En otros casos sí existirá una cardinalidad clara, por ejemplo, en un automóvil con cinco plazas, las personas que viajen en él tendrán una cardinalidad (1,5), al menos tiene que haber un conductor, y como máximo otros cuatro pasajeros.
Otra posible extensión consiste en algo llamado "entidad compuesta". En realidad se trata de una interrelación como las que hemos visto, pero a la que se añaden más atributos.
Por ejemplo, en la relación de matrimonio entre dos personas, podríamos añadir un atributo para guardar la fecha de matrimonio.
Para crear un diagráma conceptual hay que meditar mucho. No hay un procedimiento claro y universal, aunque sí se pueden dar algunas directrices generales:
- Hablar con el cliente e intentar dejar claros los parámetros y objetivos del problema o proceso a modelar. Por supuesto, tomar buena nota de todo.
- Estudiar el planteamiento del problema para:
- Identificar los conjuntos de entidades útiles para modelar el problema,
- Identificar los conjuntos de interrelaciones y determinar su grado y tipo (1:1, 1:n o m:n).
- Trazar un primer diagrama E-R.
- Identificar atributos y dominios para los conjuntos de entidades e interrelaciones.
- Seleccionar las claves principales para los conjuntos de entidades.
- Verificar que el modelo resultante cumple el planteamiento del problema. Si no es así, se vuelve a repasar el proceso desde principio.
- Seguir con los siguientes pasos: traducir el diagrama a un modelo lógico, etc
Extensiones
Existen varias extensiones al modelo E-R que hemos visto, aunque la mayor parte de ellas no las vamos a mencionar.
Una de ellas es la cardinalidad de asignación, que se aplica a atributos multivaluados. Consiste en establecer un número mínimo y máximo de posibles valores para atributos multivaluados.
Paso del modelo E-R al modelo relacional
^
Existen varias reglas para convertir cada uno de los elementos de los diagramas E-R en tablas:
- Para cada conjunto de entidades fuertes se crea una relación con una columna para cada atributo.
- Para cada conjunto de entidades débiles se crea una relación que contiene una columna para los atributos que forman la clave primaria de la entidad fuerte a la que se encuentra subordinada y una columna para cada atributo de la entidad.
- Para cada interrelación se crea una relación que contiene una columna para cada atributo correspondiente a las claves principales de las entidades interrelacionadas.
- Lo mismo para entidades compuestas, añadiendo las columnas necesarias para los atributos añadidos a la interrelación.
Las relaciones se representan mediante sus esquemas, la sintaxis es simple:
<nombre_relación>(<nombre_atributo_i>,...)
El modelo relacional también define el modo en que se pueden manipular las relaciones. Hay que tener en cuenta que este modelo tiene una base matemática muy fuerte. Esto no debe asustarnos, en principio, ya que es lo que le proporciona su potencia y seguridad. Es cierto que también complica su estudio, pero afortunadamente, no tendremos que comprender a fondo la teoría para poder manejar el modelo.
En el modelo relacionar define ciertos operadores. Estos operadores relacionales trabajan con tablas, del mismo modo que los operadores matemáticos trabajan con números. Esto implica que el resultado de las operaciones con relaciones son relaciones, lo cual significa que, como veremos, no necesitaremos implementar bucles.
Selección
Se trata de un operador unitario, es decir, se aplica a una relación y como resultado se obtiene otra relación.
Consiste en seleccionar ciertas tuplas de una relación. Generalmente la selección se limita a las tuplas que cumplan determinadas condiciones.
<relación>[<atributo>='<valor>']
Proyección
Consiste en seleccionar ciertos atributos de una relación.
Esto puede provocar un conflicto. Como la relación resultante puede no incluir ciertos atributos que forman parte de la clave principal, existe la posibilidad de que haya tuplas duplicadas. En ese caso, tales tuplas se eliminan de la relación de salida.
<relación>[<lista de atributos>]
Producto cartesiano
Este es un operador binario, se aplica a dos relaciones y el resultado es otra relación.
El resultado es una relación que contendrá todas las combinaciones de las tuplas de los dos operandos.
Esto es: si partimos de dos relaciones, R y S, cuyos grados son n y m, y cuyas cardinalidades a y b, la relación producto tendrá todos los atributos presentes en ambas relaciones, por lo tanto, el grado será n+m. Además la cardinalidad será el producto de a y b.
Composición natural
Cuando la condición es la igualdad entre atributos de cada tabla, la relación de salida tendrá parejas de columnas con valores iguales, por lo tanto, se podrá eliminar siempre una de esas columnas. Cuando se eliminan, el tipo de composición se denomina composición natural.
El grado, por lo tanto, en una composición natural es n+m-i, siendo i el número de atributos comparados entre ambas relaciones. La cardinalidad de la relación de salida depende de la condición.
Unión
También se trata de un operador binario.
Una unión es una suma. Ya sabemos que para poder sumar, los operandos deben ser del mismo tipo (no podemos sumar peras y limones), es decir, las relaciones a unir deben tener el mismo número de atributos, y además deben ser de dominios compatibles. El grado de la relación resultante es el mismo que el de las relaciones a unir, y la cardinalidad es la suma de las cardinalidades de las relaciones.
<relación1> U <relación2>
Intersección
El operador de intersección también es binario.
Para que dos relaciones se puedan interseccionar deben cumplir las mismas condiciones que para que se puedan unir. El resultado es una relación que contendrá sólo las tuplas presentes en ambas relaciones.
<relación1> intersección <relación2>
Por ejemplo, tengamos estas tablas:
tabla1(id, prenda, color)
tabla2(id, prenda, color)
tabla1
id prenda color
10 Jersey Blanco
20 Jersey Azul
30 Pantalón Verde
40 Falda Roja
50 Falda Naranja
Es posible obtener la intersección de ambas relaciones, ya que tienen el mismo número y tipo de atributos:
tabla1 intersección tabla2
id prenda color
20 Jersey Azul
40 Falda Roja
Diferencia
Otro operador binario más.
Los operandos también deben cumplir las mismas condiciones que para la unión o la intersección. El resultado es una relación que contiene las tuplas de la primera relación que no estén presentes en la segunda.
<relación1> - <relación2>
Por ejemplo, tengamos estas tablas:
tabla1(id, prenda, color)
tabla2(id, prenda, color)
tabla1
id prenda color
10 Jersey Blanco
20 Jersey Azul
30 Pantalón Verde
40 Falda Roja
50 Falda Naranja
Divicion
La operación inversa al producto cartesiano.
Este tipo de operación es poco frecuente, las relaciones que intervienen como operandos deben cumplir determinadas condiciones, de divisibilidad, que hace difícil encontrar situaciones en que se aplique.
integridad de datos
Es muy importante impedir situaciones que hagan que los datos no sean accesibles, o que existan datos almacenados que no se refieran a objetos o entidades existentes, etc. El modelo relacional también provee mecanismos para mantener la integridad. Podemos dividir estos mecanismos en dos categorías:
- Restricciones estáticas, que se refieren a los estados válidos de datos almacenados.
- Restricciones dinámicas, que definen las acciones a realizar para evitar ciertos efectos secundarios no deseados cuando se realizan operaciones de modificación o borrado de datos.
restricciones sobre claves primarias
En cuanto a las restricciones estáticas, las más importantes son las que afectan a las claves primarias.
Ninguna de las partes que componen una clave primaria puede ser NULL.
Que parte de una clave primaria sea NULL indicaría que, o bien esa parte no es algo absolutamente necesario para definir la entidad, con lo cual no debería formar parte de la clave primaria, o bien no sabemos a qué objeto concreto nos estamos refiriendo, lo que implica que estamos tratando con un grupo de entidades. Esto va en contra de la norma que dice que cada tupla contiene datos sólo de una entidad.
Las modificaciones de claves primarias deben estar muy bien controladas.
Dado que una clave primaria identifica de forma unívoca a una tupla en una relación, parece poco lógico que exista necesidad de modificarla, ya que eso implicaría que no estamos definiendo la misma entidad.
Además, hay que tener en cuenta que las claves primarias se usan frecuentemente para establecer interrelaciones, lo cual implica que sus valores se usan en otras relaciones. Si se modifica un valor de una clave primaria hay que ser muy cuidadoso con el efecto que esto puede tener en todas las relaciones en las que se guarden esos valores.
Existen varias maneras de limitar la modificación de claves primarias. Codd apuntó tres posibilidades:
- Que sólo un número limitado de usuarios puedan modificar los valores de claves primarias. Estos usuarios deben ser conscientes de las repercusiones de tales cambios, y deben actuar de modo que se mantenga la integridad.
- La prohibición absoluta de modificar los valores de claves primarias. Modificarlas sigue siendo posible, pero mediante un mecanismo indirecto. Primero hay que eliminar las tuplas cuyas claves se quieren modificar y a continuación darlas de alta con el nuevo valor de clave primaria.
Integridad referencial
La integridad referencial se refiere a las claves foráneas. Recordemos que una clave foránea es un atributo de una relación, cuyos valores se corresponden con los de una clave primaria en otra o en la misma relación. Este mecanismo se usa para establecer interrelaciones.
La integridad referencial consiste en que si un atributo o conjunto de atributos se define como una clave foránea, sus valores deben existir en la tabla en que ese atribito es clave principal.
Las situaciones donde puede violarse la integridad referencial es en el borrado de tuplas o en la modificación de claves principales. Si se elimina una tupla cuya clave primaria se usa como clave foránea en otra relación, las tuplas con esos valores de clave foránea contendrán valores sin referenciar.
Existen varias formas de asegurarse de que se conserva la integridad referencial:
- Restringir operaciones: borrar o modificar tuplas cuya clave primaria es clave foránea en otras tuplas, sólo estará permitido si no existe ninguna tupla con ese valor de clave en ninguna otra relación.
Es decir, si el valor de una clave primaria en una tupla es "clave1", sólo podremos eliminar esa tupla si el valor "clave1" no se usa en ninguna otra tupla, de la misma relación o de otra, como valor de clave foránea.
Propagacion de claves
Se trata de un concepto que se aplica a interrelaciones N:1 ó 1:1, que nos ahorra la creación de una relación. Supongamos las siguientes relaciones, resultado del paso del ejemplo 2 del modelo E-R al modelo relacional:
Libro(ClaveLibro, Título, Idioma, Formato, Categoría)
Editado_por(ClaveLibro, ClaveEditorial)
Editorial(ClaveEditorial, Nombre, Dirección, Teléfono)
Cada libro sólo puede estar editado por una editorial, la interrelación es N:1. En este caso podemos prescindir de la relación Editado_por añadiendo un atributo a la relación Libro, que sea la clave primaria de la editorial:
Libro(ClaveLibro, Título, Idioma, Formato, Categoría, ClaveEditorial)
Editorial(ClaveEditorial, Nombre, Dirección, Teléfono)
A esto se le denomina propagación de la clave de la entidad Editorial a la entidad Libro.
Por supuesto, este mecanismo no es válido para interrelaciones de N:M, como por ejemplo, la que existe entre Libro y Autor.
Ejemplo 1
Ejemplo 2
Este ejemplo es más complicado, de modo que iremos por fases. Para empezar, convertiremos los conjuntos de entidades en relaciones:
Libro(ClaveLibro, Título, Idioma, Formato)
Tema(ClaveTema, Nombre)
Autor(ClaveAutor, Nombre)
Editorial(ClaveEditorial, Nombre, Dirección, Teléfono)
Ejemplar(ClaveEjemplar, ClaveLibro, NúmeroOrden, Edición, Ubicación, Categoría)
Socio(ClaveSocio, Nombre, Dirección, Teléfono, Categoría)
