UF2176 - Definición y manipulación de datos

Por Antonio Cala Hurtado

La finalidad de esta Unidad Formativa es enseñar a interpretar las estructuras de datos y el diseño de la base de datos, de forma que pueda realizar correctamente las tareas de programación encomendadas, así como manipular el contenido de bases de datos relacionales de forma interactiva y programar módulos de manipulación de la base de datos.

Para ello, se analizarán los lenguajes relacionales y el lenguaje de manipulación de la base de datos.

Tema 1. Lenguajes relacionales.
1.1 Tipos de lenguajes relacionales.
1.2 Operaciones en el modelo relacional.
1.3 Cálculo relacional.
1.4 Lenguajes comerciales: SQL (Structured Query Language), QBE (Query By Example).

Tema 2. El lenguaje de manipulación de la base de datos.
2.1 El lenguaje de definición de datos (DDL).
2.2 El lenguaje de manipulación de datos (DML).
2.3 Cláusulas del lenguaje para la agrupación y ordenación de las consultas.
2.4 Capacidades aritméticas, lógicas y de comparación del lenguaje.
2.5 Funciones agregadas del lenguaje.
2.6 Tratamiento de valores nulos.
2.7 Construcción de consultas anidadas.
2.8 Unión, intersección y diferencia de consultas.
2.9 Consultas de tablas cruzadas.
2.10 Otras cláusulas del lenguaje.
2.11 Extensiones del lenguaje.
2.12 El lenguaje de control de datos (DCL).
2.13 Procesamiento y optimización de consultas.

• Idioma: Español
• Editorial: Editorial Elearning
• Fecha de lanzamiento: 14 ene 2019

Vista previa del libro

1.1. Tipos de lenguajes relacionales

1.2. Operaciones en el modelo relacional

1.2.1. Álgebra relacional

1.2.2. Cálculo relacional

1.3. Lenguajes comerciales: SQL (Structured Query Language), QBE (Query By Example)

1.3.1. Orígenes y evolución del SQL

1.3.2. Características del SQL

1.3.3. Sistemas de Gestión de bases de datos con soporte SQL

1.1.Tipos de lenguajes relacionales

Una base de datos es un conjunto de datos que están organizados de tal forma que pueden ser consultados, actualizados y gestionados de una forma sencilla por parte del usuario. La organización de una base de datos relacional se basa en lo que se denomina modelo de datos relacional, consistiendo este básicamente en que los datos son almacenados de forma lógica en estructuras de tablas, y la relación entre estos datos están asimismo contenidas en estas tablas y representadas por estos datos.

Importante

Una bases de datos relacional es un conjunto de tablas (relaciones), cada tabla está formada por un conjunto de columnas (atributos), cada columna (atributo) puede tomar una serie de valores (dominio), cada fila (tupla) en la tabla contiene los datos que se deseen, los datos de una fila están relacionados.

Base de datos relacional. Imagen propia

Definición

Dado los conjuntos C1 , C2, ........, Cn, se dice que R es una relación sobre estos conjuntos si R es un conjunto de n-tuplas en donde cada una de estas tuplas el primer elemento pertenece a C1 , el segundo pertenece a C2 y así sucesivamente hasta Cn. El conjunto Ci representa el i-esimo dominio de R, esto es, el conjunto de valores que puede tomar una tupla en el elemento i-esimo. Cada conjunto Ci de una relación R tiene asociado un nombre de atributo Aique identifica la columna i-esimade forma única. Se habla del dominio del atributo Ai. Al número de atributos se le llama cardinalidad u orden de la relación.

Esquema de una relación R

La expresión R(A) se puede leer como la relación R sobre el conjunto de atributos A, donde A={A1,...,An}.

Ejemplo

La empresa El cisne blanco tiene centros comerciales distribuidos por diferentes ciudades que ofertan todo tipo de productos de vestir, zapatería, complementos, y perfumería. Esta base de datos tiene el nombre de El cisne. Se requiere representar los datos relativos al esquema de la relación centro comercial. Esta base de datos tiene el nombre de el_cisne.

Para más información, consulta El cisne blanco en el anexo al final del libro.

En la relación Centro_comercial se tienen los atributos ID_Centro_comercial que es un número que identifica al centro comercial de forma única, Ciudad donde se localiza el centro comercial, la Dirección del centro comercial y el Nombre del centro comercial.

Añadiendo algunas tupla:

En el modelo relacional se tiene una vista u organización lógica de los datos que es independiente de como realmente se almacenen estos físicamente. Además, este modelo supone la base para la derivabilidad, redundancia y consistencia de las relaciones:

–Derivabilidad: se dice que una relación es derivable o se puede obtener a partir de la manipulación de otras relaciones.

–Redundancia: se dice que una relación de la base de datos es redundante cuando puede ser obtenida a partir de otras relaciones de la base de datos. En general y en lo posible se intenta evitar la redundancia en el diseño de la base de datos ya que puede llevar a inconsistencias.

–Consistencia: se habla de que el conjunto de relaciones en una base de datos es consistente o inconsistente si cumple o no una serie de restricciones.

Importante

El modelo relacional proporciona la base para gestionar una base de datos mediante relaciones (en forma de tablas) que el usuario puede manipular de forma sencilla, independientemente del medio en que los datos sean almacenado físicamente. Se dice que la representación lógica de los datos y su almacenamiento físico son independientes.

El término relación es un concepto matemático que está basado en la teoría de conjuntos. Los operadores usados en el modelo relacional son los operadores que se usan para manipular conjuntos, como pueden ser por ejemplo el operador de unión o el de intersección, y que tienen como operandos y resultados conjuntos de relaciones.

Sabías que

El modelo de datos relacional fue ideado por el investigado de IBM Edgar Frank Codd en su artículo RelationalModel of Data forLargeShared Data Banks (Un modelo de datos relacional para grandes bancos de datos compartidos. Nació en Inglaterra en 1923 y murió en EEUU en 2003. En el año 1981 ganó el premio ACM TuringAward por su contribución a la teoría y práctica en la gestión de los sistemas de bases de datos. Como dato curioso durante la segunda guerra mundial interrumpió sus estudios de matemáticas y química para convertirse en piloto de la fuerza aérea británica.

Con la finalidad de llevar a cabo la interacción entre usuario y una base de datos, y reflejar la dinámica en los cambios de estados de los datos, es necesario el uso de lo que se denomina un lenguaje relacional. El lenguaje relacional posibilita la realización de las operaciones de consultas, actualizaciones o gestión de los datos. Existen actualmente dos tipo de lenguajes formales relacionales, el álgebra relacional y el cálculo relacional.

–Álgebra relacional: en este tipo de lenguaje se indica cómo se va a obtener el resultado. Esto se lleva a cabo mediante diversos operadores descritos inicialmente por Codd en su modelo relacional. Los lenguajes comerciales basados en el álgebra relacional se llaman lenguajes procedimentales porque describen que pasos hay que seguir para obtener el resultado deseado.

–Cálculo relacional: en este tipo de lenguaje se indica que resultado se quiere obtener. Los lenguajes basados en el cálculo relacional no indican un procedimiento de cómo obtener el resultado sino que resultado se obtiene, por este motivo a este tipo de lenguajes se les denomina lenguajes declarativos.

Basándose en estos dos lenguajes formales existen diversos lenguajes relacionales comerciales como puede ser SQL (StructuredQueryLanguage) o QBE (QuerybyExample).

1.2.Operaciones en el modelo relacional

Mediante las operaciones del modelo relacional se puede gestionar el funcionamiento de una base de datos. Estas operaciones manipulan las relaciones de una base de datos, de forma que se pueden actualizar o se puede consultar la base de datos tomando una o varias de estas relaciones, y aplicándoles una o varias operaciones, obteniendo como resultado una nueva relación.

Ejemplo

La empresa Espacio3i tiene una red de franquicias inmobiliarias que alquila o vende inmuebles por toda España. En la base de datos central de la red tienen que ser registrados datos relativos a los inmuebles, empleados, inmobiliarias y las ventas realizadas. Esta base de datos tiene el nombre de espacio3i.

Esta base de datos tiene las siguientes relaciones:

–Empleado: relación donde se recogen los datos de los empleados de Espacio3i.

–Inmueble: relación con datos de inmuebles y características.

–Inmobiliaria: relación con datos de cada franquicia.

–Ciudad: relación donde se registran las ciudades donde trabaja la red de franquicias.

–Zona: relación donde se guardan datos de las zonas de cada ciudad.

–Venta: relación donde se registran datos de la venta de inmuebles.

–Cliente: relación donde se registran datos de los clientes.

Las operaciones básica del modelo relacional son las de: inserción, modificación, borrado y consulta:

–Inserción: usada para añadir una ó más tuplas a una relación.

Ejemplo

En la tabla de Empleado de la base de datos espacio3i se inserta dos nuevas tuplas.

–Modificación: mediante esta operación se pueden cambiar los valores de una o más tuplasasi como uno o más atributos de una relación.

Ejemplo

En la tabla de Empleado de la base de datos espacio3i se modifica la última tupla introducida ya que el identificador de la inmobiliaria es erróneo en vez del número 1 es el número 2.

–Borrado: con esta operación se pueden eliminar una o más tuplas de una relación.

Ejemplo

En la relación Inmueble se conservan los inmuebles vendidos o alquilados con una antigüedad máxima de 2 años, de forma que pasado este plazo hay un borrado de las tuplas cuyos inmuebles tengan más de este tiempo. Naturalmente, como fue una venta realizada se borrará asimismo de la relación Venta.

–Consulta: se puede extraer un conjunto de tuplas a partir de las relaciones y las tuplas ya existente en una base de datos.

Ejemplo

Se quiere consultar los empleados que trabajan en la franquicia de Almería. Esta franquicia tiene como identificador el número 12. Para realizar esta consulta se tendría que contar en la operación de consulta con las relaciones de Empleado, Ciudad e Inmobiliaria.

1.2.1.Álgebra relacional

Un álgebra es un sistema matemático que está formado por:

–Operandos: son valores que pueden venir dados por constantes o variables.

–Operadores: son operaciones que tienen como argumentos los operandos y como resultado nuevos valores.

Ejemplo

En el álgebra elemental se tienen como operadores las operaciones aritméticas elementales de suma, resta, multiplicación y división. Como operandos se tienen número o variables.

La operación a+5 está formado por dos operando (a y 5) y el operador +.

Importante

Un álgebra relacional tiene como operandos valores que pueden ser relaciones o variables que representan relaciones. Los operadores del álgebra relacional tienen como argumentos relaciones y producen relaciones. Codd añadió el álgebra relacional a su modelo relacional para la manipulación de relaciones.

Ejemplo

En una operación de consulta de una base de datos se tienen una o más relaciones como operandos y como resultado se tiene una nueva relación. En la base de datos el_cisne para obtener los empleados que trabajan en los centros comerciales ubicados en Málaga se requieren las relaciones de Empleado y Centro_comercial.

Operación con relaciones. Imagen propia.

Operación que tiene dos relaciones como operandos, y como resultado se obtiene una nueva relación.

El álgebra relacional se basa en la teoría de conjuntos. Es un lenguaje matemático formal y sobre este se basan la mayoría de los lenguajes de gestión de bases de datos relacionales.

Recuerda

Conceptos básicos de la teoría de conjuntos

Se dice que una serie de elementos pertenecen a un mismo grupo o conjunto, cumpliendo quizás una serie de requisitos comunes. Un conjunto es la reunión de estos elementos.

Si tenemos los elementos a, b, c, y se quiere representar que el conjunto A está formado por estos elementos se haría de la siguiente forma:

A={a,b,c}

Un operador básico en la teoría de conjunto es el de pertenencia o no a un determinado conjunto.

Recuerda

Se dice que el elemento apertenece al conjunto A:

a∈ A

Se dice que el elemento z no pertenece al conjunto A:

z∉ A

Mediante los operadores de intersección y unión se pueden realizar ambas operaciones usando dos conjuntos como operandos.

Si se tienen los conjuntos A={a,b,c} y B={c,e,f}, se pueden realizar las operaciones de unión e intersección:

–Unión: la operación de unión sobre dos conjuntos,denotado comoAÈ B, da como resultado otro conjunto formado por todos los elemento de los dos conjuntos.

A∪ B = {a,b,c,e,f}

–Intersección: la operación de intersección sobre dos conjuntos,denotado comoAÇ B, da como resultado otro conjunto formado por los elementos comunes presentes en ambos conjuntos.

A∩ B ={ c}

Igualmente son importantes las operaciones de diferencia y producto cartesiano.

Recuerda

Diferencia: la operación de diferencia sobre dos conjuntos,denotado como A – B, da como resultado otro conjunto formado por los elementos del primer conjunto menos los elementos del segundo conjunto.

A - B ={a,b}

Producto cartesiano: la operación de producto cartesiano sobre dos conjuntos,denotado comoA x B, da como resultado otro conjunto que está formado por pares ordenados de elementos, donde el primer elemento pertenece a A y el segundo elemento pertenece a B.

A - B = { (a,c),(a,e) ),(a,f),(b,c),(b,e) ),(b,f),(c,c),(c,e),(c,f)}

Operación de conjuntos. Imagen propia.

Operación de intersección, unión y diferencia sobre el conjunto A y B.

Para realizar una operación en álgebra relacional se usarán, de forma sucesiva, uno o más operadores relacionales, que actúan sobre relaciones ya existentes en la base de datos, y dan como resultado una nueva relación. Esta propiedad se denomina cierre relacional, esto es, el resultado de operar con relaciones en álgebra relacional es otra relación.

Los lenguajes relacionales basados en el álgebra relacional son lenguajes procedimentales porque describen que pasos hay que seguir para obtener el resultado. Hay otros lenguajes basados en los que se denomina cálculo relacional, que son lenguajes no procedimentales porque únicamente se dice que resultado es el que se quiere obtener.

El conocimiento de las operaciones del álgebra relacional es básico para la gestión de una base de datos relacional.

1.2.1.1.Clasificación de operadores

Los operaciones del álgebra relacional se pueden clasificar atendiendo al conjunto de operaciones básicas necesarias para hacer plenamente operativa el álgebra relacional, esto es, a partir de estas operaciones se puede construir cualquier funcionalidad u operación para gestionar una base de datos. Estas operaciones se denominan operaciones primitivas. A partir de estas se construyen las llamadas operaciones no primitivas y cada una de ellas es una combinación de las primitivas.

Las operaciones también se pueden clasificar en función si pertenecen propiamente o no a la teoría de con juntos, y también atendiendo si una operación necesita uno o dos operandos para funcionar.

Las operaciones relacionales primitivas son seis:

–Unión: dadas dos relaciones, se obtiene una relación que contiene todas las tuplas pertenecientes a aquellas relaciones.

–Producto cartesiano: dadas dos relaciones, se obtiene una relación que contiene todas las posibles combinaciones de tuplas pertenecientes a aquellas relaciones.

–Diferencia: dadas dos relaciones, se obtiene una relación que contiene todas las tuplas pertenecientes a la primera relación menos las tuplas que pertenecen a la segunda.

–Selección: dada una relación se obtiene otra relación que cumple una serie de condiciones.

–Proyección: dada una relación, se obtiene otra relación sólo con los atributos deseados.

–Renombrar: dada una relación, se obtiene la misma relación pero con los nombres de atributos o de la relación cambiados.

Las operaciones relacionales no primitivas son tres:

–Intersección: dadas dos relaciones, se obtiene una relación que contiene las tuplas comunes y pertenecientes a ambas relaciones.

–Join: dadas dos relaciones, se combinan de forma que se obtiene una relación teniendo en cuenta ciertas condiciones en las tuplas.

–División: dada dos relaciones, donde la primera relación contiene todos los atributos de la segunda, se obtiene una nueva relación donde están presentes los atributos de la primera eliminando los de la segunda, y constando de las tuplas de la primera relación que contienen todos los valores de la segunda.

Atendiendo al número de operandos que necesita una operación se habla de:

–Operaciones binarias: necesitan dos relaciones, y son la unión, producto cartesiano, diferencia, intersección, join y división.

–Operaciones unarias: necesitan sólo una relación y son la división y join.

Atendiendo a las operaciones procedentes de la teoría de conjuntos:

Operaciones propiamente de la teoría de conjuntos: unión, intersección, producto cartesiano y diferencia.

Operaciones construidas expresamente en el álgebra relacional: selección, proyección y join.

1.2.1.2.Denominación de atributos

Cada atributo Ai de una relación R tiene un nombre único, de modo que se pueda distinguir de forma univoca del resto de los atributos de la relación, y se pueda referir a esa columna sin ambigüedad.

Ejemplo

La empresa Imagina tour ofrece diferentes tipos de paquetes turísticos especializados en destinos muy variados. Los paquetes pueden ser combinados por el cliente según sus preferencias. Los paquetes pueden ser de tipo cultural, gastronómico, espectáculo, medio ambiente, aventura, y hotelero. En cada destino, la empresa tiene concertado convenios con especialistas en cada uno de estos tipos de paquetes.

La base de datos de la empresa se llama imagina_tour,y contiene una relación llamada Paqueteque tiene los atributos, con nombres únicos,ID_Paquete, Destino, Tipo, Precio.

Si se tiene dos relaciones R1 y R2,donde cada una contiene un atributo que se les asigna la misma etiqueta A, y es necesario hacer referencia a ambos atributos A en una misma relación, resultado de una operación anterior donde se tenían como operandos R1 y R2, se nombra primero la relación y a continuación el nombre del atributo A separado por un punto, esto es, R1.A y R2.A.

Ejemplo

En la base de datos el_cisne se tienen dos relaciones llamadas Empleado y Centro_comercial, la primera contiene un atributo etiquetado como Nombre ,que es el nombre del empleado, y en la segunda otro atributo con la misma etiqueta Nombre pero se refiere al nombre del centro comercial. Si se quiere realizar una consulta para saber en que centro comercial trabaja cada empleado se necesita una operación que utilice como operandos las dos tablas. Se puede distinguir entre ambos atributos denominándolos de la forma Empleado.Nombrey Centro_comercial.Nombre.

Si es necesario cambiar la etiqueta de un atributo se puede realizar esto mediante el operador renombrar.

Ejemplo

En el ejemplo anterior se puede renombrar el atributo Nombre de la relación Empleado como Nombre_empleado y el atributo Nombre de la relación Centro_comercial como Nombre_centro_comercial

1.2.1.3.Relaciones derivadas

Una base de datos contiene una serie de relaciones basealmacenadas de forma permanente en algún dispositivo físico como puede ser un disco duro. Estas relaciones base representan un dominio contextual determinado de los datos almacenados. A partir de estas relaciones base, y haciendo uso del álgebra relacional se pueden obtener otras relaciones que se denominan relaciones derivadas, al mismo tiempo estas relaciones derivadas pueden ser usadas, en combinación o no con las relaciones base, para obtener de forma sucesiva nuevas relaciones derivadas. A estas relaciones se les suele denominar vistas o consultas.

Ejemplo

El dominio contextual puede ser por ejemplo el de los centros comerciales de El cisne blanco.

En la base de datos el_cisne las relaciones base son:

–Centro_comercial: contiene datos acerca de los centros comerciales.

–Empleado: contiene datos de los empleados de la empresa.

–Producto: contiene datos de los productos.

–Venta: aquí se registran los datos referentes a las ventas realizadas.

–Sección: contiene datos de las diferentes secciones de los centros comerciales como zapatería, perfumería, etc.

Los datos referentes a todas estas relaciones están almacenados físicamente.

A partir de estas relaciones se pueden realizar diferentes consultas o vistas como pueden ser:

Consulta 1: obtener todos los productos que están puestos a la venta en la sección de zapatería del centro comercial Cisne Goya. Se obtendría una nueva relación con los atributos de nombre de producto y su precio.

Consulta 2: obtener todos los empleados que hallan vendido más de 1000€ durante la primera semana de enero. Se obtendría una relación con los atributos de nombre de empleado, apellidos, cantidad de venta realizada, el centro comercial y la sección donde trabaja.

Consulta 3: Obtener la sección que realizó más venta durante el año 2013. Se obtiene una relación con el atributo nombre de sección, cantidad de ventas realizadas y centro comercial al que pertenece.

Ejemplo

El dominio contextual también puede ser el de una franquicia de inmobiliarias en el caso de Espacio3i

En la base de datos espacio3i las relaciones base son:

–Empleado: esta relación contiene datos de los empleados de la empresa.

–Inmobiliaria: datos de las inmobiliarias de la red de franquicias.

–Inmueble: datos de inmuebles en alquiler o a la venta.

–Cliente: datos de los clientes.

–Ciudad: ciudades donde trabaja la red.

–Zona: zonas dentro de las ciudades donde la red trabaja.

–Venta: registra datos de ventas o alquileres de inmuebles.

Algunas consultas sobre estas relaciones base podrían ser:

Consulta 1: Obtener todos los inmuebles en alquiler por debajo de 600€ en la zona centro de Sevilla. Se obtendría una relación con los atributos de identificador del inmueble, dirección, precio de alquiler, nombre del propietario y teléfono.

Consulta 2: Consultar la zona más demandada, durante el mes pasado,de alquileren Madrid. Se obtiene una relación con los atributos nombre de la zona, número de personas que figuran demandando esa zona y el precio medio de alquiler.

Consulta 3: consultar los propietarios que tienen más de un inmueble en alquiler o venta. Se obtiene una relación con los atributos de nombre del propietario, teléfono, número de inmuebles en alquiler, número de inmuebles a la venta.

1.2.1.4.Operaciones primitivas: selección, proyección, producto cartesiano, unión y diferencia

Selección

Dada una relación R y una condición Csobre R, mediante esta operación se obtienen las tuplas que cumplen la condición C.

Esta operación se representa de esta forma:

sC (R)

sC (R) = {tuplas de R que cumplen C}

La condición C es una formula que devuelve verdadero o falso, está formada por una cadena de atributos o constantes, los operadores de comparación <, >, =, > #<, y los operadores lógicos ^ (and), v (or), ¬ (not).

Ejemplo

Se tiene la relación R con los atributos A, B y C:

Se quiere obtener las tuplas de R donde A sea mayor que 2 o B sea igual a C. Esta operación se representa como:

sA>2 ÚB = C (R)

El resultado de esta operación sería una nueva relación S que incluye las tuplas que cumplen la condición C:

Ejemplo

Se quiere consultar los paquetes que ofrece Imagina tour en la ciudad de Plasencia. La condición en esta selección sería la ciudad de Plasencia. Se tiene que usar la relación Paquete. Se representaría de la forma:

sDestinoPlansencia (Paquete)

En forma de tabla la operación tendría este aspectos:

Proyección

Dada una Relación R con n atributos y un conjunto de atributos A sobre R con p elementos, mediante la operación de proyección se obtienen una nueva relación cuyas columnas son los p elementos de A. Las tuplas repetidas obtenidas se eliminan.

Esta operación la representamos de esta forma:

pA (R) dondeA=A1,A2,....Ap.

Ejemplo

Se tiene la relación R con los atributos A, B y C:

Se quiere obtener una nueva relación S con los atributos A y C. Esta operación se representa como:

pA,C(R)

El resultado de esta operación sería una nueva relación S que incluye las tuplas con sólo los atributos A y C eliminado el atributo B.

Ejemplo

Obtener los nombres y apellidos de los empleados de Imagina tour. Esta operación se representa de la siguiente forma:

πNombre,Apellido1,Apellido2(Empleado)

Producto cartesiano

Dada dos relaciones R y S que no tienen ningún atributo en común, el producto cartesiano de ambas relaciones es una nueva relación que consiste en concatenar todas las posibles combinaciones de tuplas de R y tuplas de S.

Esta operación la representamos de esta forma:

RxS

Ejemplo

Se tienen las siguientes relaciones R y S.

El producto cartesiano de R y S es la siguiente relación:

Unión

Dadas dos relaciones R y S, que son compatibles en la unión, esto es, que tienen el mismo número de atributos y hay una correspondencia entre los atributos de R y S que tienen el mismo dominio (se habla de biyección),la unión de ambas relaciones es el resultado de unir ambos conjuntos con las tuplas pertenecientes a R o a S o las tuplas comunes a las dos relaciones.

Esta operación la representamos de esta forma:

R ∪ S

Ejemplo

Se tienen las siguientes relaciones R y S.

La unión de R y S