manejo de sentencias de recuperación

BASE DE DATOS I

S4L

GRUPO #1

MANEJO DE SENTENCIAS DE RECUPERACIÓN

01/02/2016

INTEGRANTES:

VEGA CASTILLO LUIS

CORTEZ MERCADO JULIO

TORRES CÓRDOVA JOHANNA

TORRES GARCÍA SERGIO

MANEJO DE SENTENCIAS DE RECUPERACIÓN 1

MANEJO DE SENTENCIAS DE RECUPERACIÓN

Presentado por:

TORRES CÓRDOVA JOHANNA

VEGA CASTILLO LUIS

CORTEZ MERCADO JULIO

TORRES GARCÍA SERGIO

Docente:

ING. ROSSANA LUCIN

Curso:

S4L

UNIVERSIDAD DE GUAYAQUIL

FACULTAD MATEMÁTICAS Y FÍSICAS

INGENIERÍA EN SISTEMAS COMPUTACIONALES

EXPOSICIÓN 2DO PARCIAL

GUAYAQUIL

2016


Contenido

1. Manejo de Sentencias de Recuperación...........................................................3

1.2 Conversión de operaciones de algebra relacional........................................3

1.3 Operaciones de Recuperación......................................................................3

1.4 Operaciones de Columnas............................................................................4

1.5 Operaciones de Tablas..................................................................................4

1.6 Operaciones de Selección.............................................................................5

1.6.1 Selección (Select)..................................................................................5

1.6.2 Criterios de Selección............................................................................5

1.6.3 Funciones de Agregado.........................................................................8

1.6.4 Consultas de selección...........................................................................8

1.7 Tipos de JOIN............................................................................................10

INNER JOIN..............................................................................................10

OUTER.......................................................................................................11

o LEFT JOIN.............................................................................................11

o RIGHT JOIN...........................................................................................11

o FULL JOIN.............................................................................................11

CROSS JOIN..............................................................................................12

1.8 Expresiones de restricción especial............................................................12

1.9 Subconsultas...............................................................................................13

1.10 Ejercicios....................................................................................................14

2. Conclusión.....................................................................................................19

Referencias...........................................................................................................20


1. Manejo de Sentencias de Recuperación

1.2 Conversión de operaciones de algebra relacional

El álgebra relacional se inspira en la teoría de conjuntos para especificar consultas en una base de datos relacional.Para especificar una consulta en álgebra relacional, es preciso definir uno o más pasos que sirven para ir construyendo, mediante operaciones de álgebra relacional, una nueva relación que contenga los datos que responden a la consulta a partir de las relaciones almacenadas. Los lenguajes basados en el álgebra relacional son procedimentales, dado que los pasos que forman la consulta describen un procedimiento.Una característica destacable de todas las operaciones del álgebra relacional es que tanto los operandos como el resultado son relaciones. Esta propiedad se denomina cierre relacional.

Las operaciones del álgebra relacional han sido clasificadas según distintos criterios; de todos ellos indicamos los tres siguientes: 1) Según se pueden expresar o no en términos de otras operaciones.

a) Operaciones primitivas: son aquellas operaciones a partir de las cuales podemos definir el resto. Estas operaciones son la unión, la diferencia, el producto cartesiano, la selección y la proyección. b) Operaciones no primitivas: el resto de las operaciones del álgebra relacional que no son estrictamente necesarias, porque se pueden expresar en términos de las primitivas; sin embargo, las operaciones no primitivas permiten formular algunas consultas de forma más cómoda. Existen distintas versiones del álgebra relacional, según las operaciones no primitivas que se incluyen. Nosotros estudiaremos las operaciones no primitivas que se utilizan con mayor frecuencia: la intersección y la combinación.

2) Según el número de relaciones que tienen como operandos: a) Operaciones binarias: son las que tienen dos relaciones como operandos. Son binarias todas las operaciones, excepto la selección y la proyección.b) Operaciones unarias: son las que tienen una sola relación como operando. La selección y la proyección son unarias.

3) Según se parecen o no a las operaciones de la teoría de conjuntos: a) Operaciones conjuntistas: son las que se parecen a las de la teoría de conjuntos. Se trata de la unión, la intersección, la diferencia y el producto cartesiano. b) Operaciones específicamente relacionales: son el resto de las operaciones; es decir, la selección, la proyección y la combinación.

1.3 Operaciones de Recuperación

Las operaciones de copias de seguridad y restauración se producen dentro del contexto del modelo de recuperación de la base de datos. Los modelos de recuperación se han diseñado para controlar el mantenimiento del registro de transacciones. Un modelo de recuperación es una propiedad de base de datos que controla la forma en que se registran las transacciones, si el registro de transacciones


requiere que se realice la copia de seguridad y si lo permite, y qué tipos de operaciones de restauración hay disponibles.

Existen tres modelos de recuperación: simple, completa y por medio de registros de operaciones masivas. Normalmente, en las bases de datos se usa el modelo de recuperación completa o el modelo de recuperación simple. Una base de datos se puede cambiar a otro modelo de recuperación en cualquier momento.

1.4 Operaciones de Columnas

Dentro de las operaciones básicas tenemos las operaciones unarias (se utiliza una sola tabla) y las operaciones binarias (se utilizan dos tablas).

Operaciones unarias

Selección: Mediante esta operación se obtiene un conjunto de filas con todas las columnas de la tabla. Se seleccionan determinadas filas incluyendo una condición. Se utilizan los operadores booleanos "and" (Y)," or" (O), "not" (NO).

La representación sería la siguiente:

Proyección : esta operación nos devuelve una nueva tabla con las columnas seleccionadas de otra.

La representación sería la siguiente:

1.5 Operaciones de Tablas

Los tipos de consultas básicas que se pueden hacer sobre tablas de una base de datos.

Las operaciones que vamos a ver están basadas en el álgebra relacional. Los operandos de cada operación lo constituyen una o varias tablas y el resultado es una nueva tabla.

Operaciones binarias Unión: Esta operación solo se puede hacer si las tablas tienes las mismas columnas, es decir, por ejemplo si tuviéramos una tabla llamada empleados2 con las mismas columnas tan solo tendríamos que añadir las filas de ambas tablas en una única tabla.

Su representación sería la siguiente: Tabla1 u Tabla2

Diferencia: de la misma forma que la unión la diferencia tan solo se puede realizar si las dos tablas tienen las mismas columnas. Su representación sería la siguiente: Tabla1 - Tabla2

Producto cartesiano: se realiza con dos tablas distintas pero relacionadas por alguna columna, siempre y cuando el nombre de la columna no sea el mismo.

Su representación sería la siguiente: Tabla1 x Tabla2


1.6 Operaciones de Selección1.6.1 Selección (Select)

Utilizado para consultar registros de la base de datos que satisfagan un criterio determinado.

1.6.2 Criterios de Selección

Operadores Lógicos y de Comparación

Operadores Lógicos Uso

AND

Es el "y" lógico. Evalúa dos condiciones y devuelve un valor de verdad sólo si ambas son ciertas. Su tabla de verdad es:

OR

Es el "o" lógico. Evalúa dos condiciones y devuelve un valor de verdad si alguna de las dos es cierta. Su tabla de verdad es:

NOT

Negación lógica. Devuelve el valor contrario de la expresión. Su tabla de verdad es:

Los operadores lógicos que se utilizan, también llamados operadores relacionales, nos proporcionan un resultado a partir de que se cumpla o no una cierta condición.


Los operadores de comparación, como su nombre lo indica, permiten comparar dos valores.

Operador Uso

< Menor que

> Mayor que

<> Distinto de

<= Menor ó Igual que

>= Mayor ó Igual que

= Igual que

BETWEEN Utilizado para especificar un intervalo de valores.

LIKE Utilizado en la comparación de un modelo

In Utilizado para especificar registros de una base de datos

Intervalos de Valores

Para recuperar registro según el intervalo de valores de un campo se emplea Between cuya sintaxis:

(La consulta devuelve los registros que contengan en “campo” un valor inlcuido en el intervalo valor1, valor2, (ambos inclusive). Si anteponemos Not devolverá aquelos valores no incluidos en el intervalo.)

Ejemplo:

El Operador Like


Se utiliza para comparar una expresión de cadena con un modelo en una expresión SQL.

Sintaxis: expresión Like modelo

Se puede utilizar el operador Like para encontrar valores en los campos que coincidan con el modelo especificado.

Por modelo se puede especificar un valor completo o utilizar caracteres comodín para encontrar un rango de valores.

Like ‘P[A-F]###’ devuelve los datos que comienzan con la letra P seguido de cualquier letra entre A y F y de tres dígitos.

Like ‘[A-D]*’ devuelve los campos cuyo contenido empiece con una letra de la A a la D seguidas de cualquier cadena.

Tabla como utilizar el operador Like

El Operador In

El operador devuelve aquellos registros cuyo campo indicado coincide con alguno de los incluidos en una lista.

La cláusula WHERE


Determina que registros de las tablas enumeradas en la cláusula FROM aparecerán en los resultados de la instrucción SELECT. Si no se emplea esta cláusula, la consulta devolverá todas las filas de la tabla.

Ejemplos:

1.6.3 Funciones de Agregado

Las funciones de agregado se usan dentro de una cláusula SELECT en grupos de registros para devolver un único valor que se aplica a un grupo de registros.

Función Descripción

AVG Utilizada para calcular el promedio de los valores de un campo determinado

COUNT Utilizada para devolver el número de registros de la selección

SUM Utilizada para devolver la suma de todos los valores de un campo determinado

MAX Utilizada para devolver el valor más alto de un campo especificado

MIN Utilizada para devolver el valor más bajo de un campo especificado

1.6.4 Consultas de selección

Consultas Básicas

La sintaxis básica de una consulta de selección es la siguiente:

SELECT Campos FROM Tabla;


En donde campos es la lista de campos que se deseen recuperar y tabla es el origen de los mismos, por ejemplo:

SELECT Nombre, Telefono FROM Clientes;

Esta consulta devuelve un recordset con el campo nombre y teléfono de la tabla clientes.

Ordenar los registros

Adicionalmente se puede especificar el orden en que se desean recuperar los registros de las tablas mediante la cláusula ORDER BY Lista de Campos. En donde Lista de campos representa los campos a ordenar. Ejemplo:

SELECT CodigoPostal, Nombre, Telefono FROM Clientes ORDER BY Nombre;

Esta consulta devuelve los campos CodigoPostal, Nombre, Telefono de la tabla Clientes ordenados por el campo Nombre.

Se pueden ordenar los registros por más de un campo, como por ejemplo:

SELECT CodigoPostal, Nombre, Telefono FROM Clientes ORDER BY CodigoPostal, Nombre;

Incluso se puede especificar el orden de los registros: ascendente mediante la claúsula (ASC -se toma este valor por defecto) ó descendente (DESC)

SELECT CodigoPostal, Nombre, Telefono FROM Clientes ORDER BY CodigoPostal DESC , Nombre ASC;

Consultas con Predicado

El predicado se incluye entre la cláusula y el primer nombre del campo a recuperar, los posibles predicados son:

Predicado Descripción

ALL

Devuelve todos los campos de la tabla. Ejm:

TOPDevuelve un determinado número de registros de la tabla que entran al principio o al final de un rango especifico por una clásula ORDER BY.

DISTINCT Omite los registros cuyos campos seleccionados coincidan totalmente (datos duplicados en los campos seleccionados).


DISTINCTROW

Omite los registros duplicados basándose en la totalidad del registro y no sólo en los campos seleccionados (independientemente de los campos indicados en la cláusula SELECT).

Alias

En determinadas circunstancias es necesario asignar un nombre a alguna columna determinada de un conjunto devuelto, otras veces por simple capricho o por otras circunstancias. Para resolver todas ellas tenemos la palabra reservada AS que se encarga de asignar el nombre que deseamos a la columna deseada. Ej.:

SELECT DISTINCTROW Apellido AS Empleado FROM Empleados;

Bases de Datos Externas(IN)

En ocasiones es necesario la recuperación de información que se encuentra contenida en una tabla que no se encuentra en la base de datos que ejecutará la consulta o que en ese momento no se encuentra abierta, esta situación la podemos salvar con la palabra reservada IN de la siguiente forma:

En donde c:\databases\gestion.mdb es la base de datos que contiene la tabla Empleados.

1.7 Tipos de JOIN

En general, las bases de datos relacionales están normalizadas para eliminar la información duplicada. Con lo cual, para poder combinar información de dos o más tablas, es necesario recurrir a las uniones o JOINs.

Existen 3 tipos de JOINs:

INNER JOIN

La forma más utilizada de combinación se llama INNER JOIN, y el resultado es el cálculo cruzado de todos los registros. Se combinan los registros de la tabla1 con la tabla2, pero sólo van a permanecer los registros que satisfagan la condición especificada.Podemos graficar este ejemplo con un diagrama de Venn:

Tabla1 Tabla 2


OUTERo LEFT JOIN

El resultado que produce este LEFT JOIN son todas las filas de la tabla que se encuentre a la izquierda, sin importar si coinciden con las filas de la derecha.

Tabla1 Tabla 2

o RIGHT JOINDe la misma forma que podemos obtener todas las filas de la tabla de la izquierda con LEFT JOIN, con RIGHT JOIN obtenemos todas las filas de la tabla derecha, sin importar si coinciden con las de la tabla de la izquierda.

Tabla1 Tabla 2

o FULL JOINEl FULL JOIN se utiliza cuando queremos obtener todas las filas de las dos tablas, sin importarnos que tengan coincidencias. MySQL no soporta este tipo de JOIN, por lo tanto para lograr el mismo resultado, hay que hacer LEFT JOIN + RIGHT JOIN.

Tabla1 Tabla 2


CROSS JOINFinalmente, existe el CROSS JOIN, el cual no puede ser explicado con un diagrama de Venn. Lo que hace, es unir todo con todo, o sea, realiza el producto cartesiano. Este tipo de JOIN puede resultar peligroso en el rendimiento de la base de datos, si lo aplicamos a tablas con muchos datos.

1.8 Expresiones de restricción especial

Cómo: Definir expresiones de restricción CHECK

Puede crear una expresión de restricción sencilla para comprobar una condición sencilla en los datos o puede crear una expresión compleja mediante operadores booleanos, para comprobar varias condiciones en los datos. Por ejemplo, supongamos que la tablaauthors tiene una columna zip que requiere una cadena de caracteres de 5 dígitos. Esta expresión de restricción de ejemplo garantiza que sólo se admitirán números de 5 dígitos.

zip LIKE '[0-9][0-9][0-9][0-9][0-9]'

O supongamos que la tabla sales tiene una columna llamada qty que requiere un valor mayor que 0. Esta restricción de ejemplo garantiza que sólo se permiten valores positivos:

qty > 0

Para definir una expresión de restricción

1. Cree una restricción CHECK nueva. 2. En el cuadro de diálogo Restricciones CHECK, escriba una expresión en el cuadro de

diálogo Expresión de restricción CHECK utilizando la sintaxis siguiente:{constant column_name function (subquery)} [{operator AND OR NOT}{constant column_name function (subquery)}]

Cómo: Asociar una nueva restricción CHECK a una tabla o columna

Pasar asociar una restricción CHECK nueva

1. En el diagrama de la base de datos, haga clic con el botón secundario en la tabla que contendrá la restricción y después seleccione Restricciones CHECK en el menú contextual.-O bien-


Abra el Diseñador de tablas de la tabla que va a contener la restricción; a continuación, haga clic con el botón secundario en el Diseñador de tablas y elija Restricciones CHECK en el menú contextual.

2. Haga clic en Agregar.3. En la cuadrícula, en el campo Expresión, escriba las expresiones SQL para la

restricción CHECK. Por ejemplo, para limitar las entradas de la columna state de la tabla authors a Nueva York, escriba:

state = 'NY'

O bien, para exigir que las entradas que se escriben en la columna zip tengan 5 dígitos, escriba:zip LIKE '[0-9][0-9][0-9][0-9][0-9]'

4. Expanda la categoría Diseñador de tablas para definir cuando debe exigirse la restricción:

o Para probar la restricción en datos existentes antes de crearla, active la opción Comprobar datos existentes al crear o habilitar.

o Para exigir la restricción siempre que se realice una operación de replicación en esta tabla, active la opción Exigir para replicación.

o Para exigir la restricción siempre que se inserte o actualice una fila de esta tabla, active la opción Exigir para INSERTs y UPDATEs.

1.9 Subconsultas

Una subconsulta es una instrucción SELECT anidada dentro de una instrucción SELECT, SELECT...INTO, INSERT...INTO, DELETE, o UPDATE o dentro de otra subconsulta. Puede utilizar tres formas de sintaxis para crear una subconsulta:

comparación [ANY | ALL | SOME] (instrucción sql)

expresión [NOT] IN (instrucción sql)

[NOT] EXISTS (instrucción sql)

En donde:

comparaciónEs una expresión y un operador de comparación que compara la expresión con el resultado de la subconsulta.

expresiónEs una expresión por la que se busca el conjunto resultante de la subconsulta.

instrucción SQL

Es una instrucción SELECT, que sigue el mismo formato y reglas que cualquier otra instrucción SELECT. Debe ir entre paréntesis.

Se puede utilizar una subconsulta en lugar de una expresión en la lista de campos de una instrucción SELECT o en una cláusula WHERE o HAVING. En una subconsulta, se utiliza una instrucción


SELECT para proporcionar un conjunto de uno o más valores especificados para evaluar en la expresión de la cláusula WHERE o HAVING.

Se puede utilizar el predicado ANY o SOME, los cuales son sinónimos, para recuperar registros de la consulta principal, que satisfagan la comparación con cualquier otro registro recuperado en la subconsulta. El ejemplo siguiente devuelve todos los productos cuyo precio unitario es mayor que el de cualquier producto vendido con un descuento igual o mayor al 25 por ciento:

SELECT *FROM Productos WHERE PrecioUnidad ANY ( SELECT PrecioUnidad FROM DetallePedido WHERE Descuento = 0 .25)

El predicado ALL se utiliza para recuperar únicamente aquellos registros de la consulta principal que satisfacen la comparación con todos los registros recuperados en la subconsulta. Si se cambia ANY por ALL en el ejemplo anterior, la consulta devolverá únicamente aquellos productos cuyo precio unitario sea mayor que el de todos los productos vendidos con un descuento igual o mayor al 25 por ciento. Esto es mucho más restrictivo.

El predicado IN se emplea para recuperar únicamente aquellos registros de la consulta principal para los que algunos registros de la subconsulta contienen un valor igual. El ejemplo siguiente devuelve todos los productos vendidos con un descuento igual o mayor al 25 por ciento:

SELECT * FROM Productos WHERE IDProducto IN( SELECT IDProducto FROM DetallePedido WHERE Descuento = 0.25)

Inversamente se puede utilizar NOT IN para recuperar únicamente aquellos registros de la consulta principal para los que no hay ningún registro de la subconsulta que contenga un valor igual.

El predicado EXISTS (con la palabra reservada NOT opcional) se utiliza en comparaciones de verdad/falso para determinar si la subconsulta devuelve algún registro. Supongamos que deseamos recuperar todos aquellos clientes que hayan realizado al menos un pedido:

SELECT Clientes.Compañía, Clientes.Teléfono FROM Clientes WHERE EXISTS ( SELECT FROM Pedidos WHERE Pedidos.IdPedido = Clientes.IdCliente )

Esta consulta es equivalente a esta otra:

SELECT Clientes.Compañía, Clientes.Teléfono FROM Clientes WHERE IdClientes IN ( SELECT Pedidos.IdCliente FROM Pedidos )

1.10 Ejercicios

Ejemplo de selección.

Partimos de la siguiente tabla de empleados:


Realizamos la siguiente operación de selección:

La tabla resultante sería la siguiente:

Ejemplo de proyección:

Sobre la tabla anterior proyectamos la columna salario y

nombre quedando como resultado la siguiente tabla

Con las siguientes tablas aplicar diferencia:

El resultado sería el siguiente:

Ejemplo de producto cartesiano: partimos de las dos tablas siguientes:

Como resultado del producto cartesiano tendríamos la siguiente tabla:


Tenemos una base de datos con una tabla “Estudiantes”, que guarda el Id y Nombre, “Materias” que guarda lo mismo, y una que relaciona las dos mediantes sus respectivos ID:

INNER JOIN: Queremos un SELECT que nos devuelva los nombres de cada estudiante que está estudiando una materia, y qué materias está estudiando.

SELECT E.Nombre, M.Nombre FROM Estudiantes AS EINNER JOIN Estudiantes_Materias AS EMON EM.IdEstudiante = E.IdINNER JOIN Materias AS MON M.Id = EM.IdMateriaORDER BY E.Nombre

Es importante notar que Transact SQL (la versión de SQL que usa MS SQL SERVER) tiene la posibilidad de hacer este tipo de consultas de forma más “sencilla” a simple vista con algo como:

SELECT E.Nombre, M.NombreFROM Estudiantes AS E, Materias AS M, Estudiantes_Materias AS EMWHERE E.Id = EM.IdEstudiante AND M.Id = EM.IdMateria

Sin embargo, es más recomendable hacerlo a la manera “ANSI” que es la más estándar, así si cambiáramos de motor de base de datos a Oracle, Postgre o MySQL tendríamos menos dificultad con la sintaxis.

OUTER JOIN: Ahora queremos saber lo mismo, pero además mostrar los estudiantes que no están estudiando ninguna materia:

SELECT E.Nombre, EM.IdMateria

http://picandocodigo.net/wp-content/uploads/2007/09/diagrama1bdjoins.png


FROM Estudiantes_Materias AS EMFULL OUTER JOIN Estudiantes AS EON E.Id = EM.IdEstudiante

Otro ejemplo utilizando left, right y full join de las tablas Empleados y Categorías.

Empleados:

Categorías:

Right join

SELECT e.Id, e.Apellido, c.Id, c.Nombre

FROM Empleados e

RIGHT JOIN Categorías c


ON e.Categoria = c.Id

Left join


FROM Empleados e

LEFT JOIN Categorías c


Full join



FROM Empleados e

FULL JOIN Categorías c


2. Conclusión

En conclusión las operaciones de recuperación es una herramienta productiva y útil, especialmente para los investigadores.

El álgebra relacional permite hacer consultas en forma cómoda, es importante porque nos ayuda a la unión entre conjunto de relaciones y entidades.

En definitiva, las operaciones de columna, relacionan 2 o más atributos los cuales serán consultados a partir de una condición en particular, como finalidad de trabajar con menos datos, pues se propone una condición para consultar datos más específicos.

En las operaciones de tabla involucran 2 o más tablas las cuales se relacionan en base a una condición, la cual produce que todos los valores de tabla son utilizados para realizar la operación propuesta.

Además dentro del manejo de sentencias de recuperación tenemos las operaciones de selección las cuales interviene la cláusula Select que es utilizado para consultar registros de la base de datos que satisfagan un criterio determinado.

Y también los tipos de joins los cuales son tres: Inner Join, Outer Join, Cross Join.

Cuando se asocie una restricción CHECK a una tabla o columna, debe incluir una expresión SQL para desarrollarlo. Algunas bases de datos tienen diferente funcionalidad para las restricciones de comprobación. Consultar la documentación de la base de datos para obtener detalles sobre cómo funciona la base de datos con restricciones de comprobación.

Podemos decir que una subconsulta es una sentencia SELECT que aparece dentro de otra sentencia SELECT. Normalmente se utilizan para filtrar una clausula WHERE o HAVING con el conjunto de resultados de la subconsulta, aunque también pueden utilizarse en la lista de selección.


Referencias

(s.f.). Obtenido de http://csrg.inf.utfsm.cl/~jfuentes/_build/html/lectures/week1/lecture3.html

(s.f.). Obtenido de https://msdn.microsoft.com/es-es/library/58xz3zdd(v=vs.90).aspx

(s.f.). Obtenido de http://personal.lobocom.es/claudio/sql007.htm

Alvarez, S. (25 de Octubre de 2007). desarrolloweb.com. Obtenido de http://www.desarrolloweb.com/articulos/operaciones-basicas-sobre-tablas.html

Costal, D. (s.f.). OperCourseWare. Obtenido de www.uoc.edu

Fundamentos de Base de Datos Henry f. Korth

Introducción a los sistemas de bases de datos (7ª ed.). Prentice-Hall.

Kaplan, G. (04 de Noviembre de 2013). NEBARIS. Obtenido de http://www.nebaris.com/post/77/tipos-de-join-en-sql

Orozco Manotas, C. (s.f.). monografias.com. Obtenido de http://www.monografias.com/trabajos11/manu/manu.shtml#co

picando codigo. (12 de Septiembre de 2007). Obtenido de http://picandocodigo.net/2007/joins-en-sql/

manejo de sentencias de recuperación

Engineering