UNIDAD 5: SQL: Consultas, Programacin y Triggers

1
UNIDAD 5SQL Consultas, Programación y Triggers

• UTN FRRe
• Diseño de Base de Datos
• Para Sistemas de Información

2
Instancias a utilizar

• Para todos los ejemplos usaremos estas instancias
  de las tablas Marineros y Reservas.
• Si la clave para la relación Reservas contiene
  solo los atributos sid y bid, Cómo cambiaría la
  semántica?

3
Consulta SQL Básica

• relation-list nombre de las relaciones que
  intervienen en la consulta.
• target-list nombre de los atributos de las
  relaciones de relation-list a proyectar
• qualification Condicionales (Atrib op Const o
  Atrib op Atrib, donde op puede ser gt,lt, , ltgt,
  etc.). Se puede combinar usando AND, OR o NOT.
• DISTINCT es una palabra reservada opcional para
  indicar que se deben eliminar las tuplas
  duplicadas del resultado.. Por defecto se debe
  recordar que los tuplas duplicadas NO se
  eliminan.

4
Propuesta de Evaluación Conceptual

• Una estrategia para evaluar una consulta sería
• Computar los productos cruzados de la lista de
  relaciones de la cláusula FROM.
• Descartar las tuplas que no califican según los
  condicionales de la cláusula WHERE.
• Borrar los atributos que no están en la lista de
  proyección.
• Si la claúsula DISTINCT esta especificada,
  eliminar las tuplas duplicadas.
• Esta estrategia es probablemente la menos
  eficiente de computar una consulta. Los
  optimizadores encontrarán estrategias más
  eficientes de resolver la misma consulta.

5
Ejemplo de Evaluación Conceptual
CALCULO INTERMEDIO (PRODUCTO CRUZADO)
6
Referenciando Instancias y Atributos

• En realidad es solo necesario cuando aparecen
  nombre duplicados en las cláusulas FROM o en los
  atributos de proyección. Sin embargo es buena
  política utilizarlo.

7
Encuentre los marineros que reservaron al menos
un bote.

• Haría alguna diferencia agregar la cláusula
  DISTINCT?
• Cúal es el efecto de reemplazar S.sid por
  S.sname en la cláusula SELECT?
• Y ahora Haría alguna diferencia agregar la
  cláusula DISTINCT?

8
Expresiones y Strings

• La consulta anterior muestra el uso de
  expresiones matemáticas y operadores de
  comparación de strings.
• AS y son dos maneras de renombrar los
  resultados
• LIKE es usado para comparar cadena de caracteres,
  _ es comodín de una letra y es comodín de
  varias.

9
Encuentre los marineros que reservaron un bote
rojo o uno verde.

• UNION Puede ser usado para calcular la unión de
  dos relaciones, siempre y cuando cumpla con las
  condiciones de unión-compatible.
• Si reemplazamos OR por AND en la primera versión,
  Qué obtendremos?
• EXCEPT(MINUS) Diferencia de conjuntos

10
Encuentre los marineros que reservaron un bote
rojo y uno verde.

• INTERSECTION Puede ser usado para calcular la
  intersección de dos relaciones, siempre y cuando
  cumpla con las condiciones de unión-compatible.
• Incluido en el standard SQL/92, pero algunos
  motores no lo incluyen.

11
Consultas Anidadas

• Encuentre los nombres de los marineros que
  reservaron el bote 103
• Observese la posibilidad que ofrece la cláusula
  WHERE de contener una consulta como parámetro
  comparativo. (así como las cláusulas FROM y
  HAVING)
• Para encontrar los marineros que NO reservaron el
  bote 103, use NOT IN.
• Para entender la semántica de las consultas
  anidadas, piense en la lógica de un bucle Para
  cada tupla de marineros, chequear la condición
  computando la subconsulta.

12
Consultas Anidadas Correlacionadas

• Encuentre los nombres de los marineros que
  reservaron el bote 103
• EXISTS es otro operador de comparación, como IN.
• Otro ejemplo es UNIQUE que chequea filas
  duplicadas. Si reemplazamos EXISTS por UNIQUE y
  el por R.bid, se puede encontrar los marineros
  que reservaron solo una vez el bote 103.
• En el ejemplo se ve claramente porque la
  necesidad de computar la subconsulta para cada
  tupla de la tabla marineros.
• Recuerde que tambien son válidas las cláusulas
  NOT EXISTS y NOT UNIQUE.

13
Operaciones de Comparación de Conjuntos

• Además están disponibles los operadores
• Encuentre los marineros cuyos ratings son mayores
  que algún marinero llamado Horatio

14
Reemplazar INTERSECT con IN

• Encuentre los marineros que reservaron un bote
  rojo y uno verde.
• De manera similar EXCEPT, se puede reemplazar con
  NOT IN.

15
La operación de DIVISION en SQL

• Encuentre los marineros que reservaron todos los
  botes
• Con EXCEPT
• Sin EXCEPT

Marineros S tal que .
No hay un bote B sin..
una reserva que nos muestre que S haya reservado
B .
16
Operadores de Agregación

• Extienden las posibilidades por sobre el algebra
  relacional

17
Encuentre el nombre y la edad del marinero mas
viejo

• Atención!. La primer consulta es errónea.
• La tercer consulta es equivalente a la segunda,
  esta soportada por SQL/92, pero no por todos los
  motores actuales.

18
GROUP BY y HAVING

• Hasta ahora hemos visto operadores de agregación
  para todas las filas. Sin embargo en algunas
  ocasiones necesitaremos aplicar las funciones en
  grupos mas pequeños.
• Considere Encuentre la edad del marinero mas
  joven para cada nivel de rating.
• En general, no sabemos cuantos niveles de rating
  hay de antemano, y cuales son sus valores.
• Suponga que los valores van de 1 a 10, podemos
  escribir 10 consultas como esta

19
GROUP BY y HAVING

• La lista de proyección contiene atributos y
  operadores de agregación.
• La lista de atributos debe ser un subconjunto de
  la lista de agrupación (GROUP BY)
• Cada fila de la consulta resultante esta formada
  por esta lista de atributos y el resultado de la
  operación solicitada por grupo. (un grupo es un
  conjunto de filas que contiene los mismo valores
  para los atributos indicados en GROUP BY)

20
Evaluación Conceptual

• Se computa el producto cruzado del FROM, luego se
  descartan las tuplas que no cumplen la condición
  WHERE (en caso de haber alguna), se filtran los
  campos innecesarios (SELECT), las filas
  resultantes se particionan en grupos de acuerdo a
  GROUP BY.
• Luego se aplican las restricciones HAVING. Las
  expresiones condicionales de HAVING deben arrojar
  un solo valor por grupo.
• Se genera una sola tupla por grupo.

21
Encuentre la edad del marinero más joven con edad
gt 18, para cada rating con al menos dos
marineros.

• Solo trae S.rating y S.age, los demás son
  innecesarios.
• La segunda columna no tiene nombre (usar alias)

22
Por cada bote rojo, encuentre el nro. de reservas
para ese bote.

• Ejemplo de agrupamiento luego de una junta de
  tres entidades.
• Qué pasaría si quitamos B.colorred de WHERE y
  la agregamos en HAVING?
• Y si quitamos Sailors y la condición S.sid
  R.sid

23
Encuentre la edad del marinero mas joven con edad
gt 18, para cada rating con al menos 2 marineros
(de cualquier edad)

• HAVING puede contener subconsultas.
• Compare esta consulta con la que solo consideraba
  marineros de mas de 18.
• Qué pasa si se reemplaza por HAVING COUNT() gt 1

24
Encuentre los ratings para los cuales el promedio
de edad es mínimo de todos los rankings

• Las operaciones de agregado no pueden ser
  anidadas (ERROR)
• En SQL/92

25
Valores NULLs

• Algunas veces los valores de los campos pueden
  ser desconocidos (Ej. Un rating no asignado) o
  inaplicables (sin segundo nombre)
• Para estos casos, SQL provee un valor especial
  NULL
• La presencia de NULL complica las consultas.
• Se necesitan operadores especiales que lo
  chequeen.
• Ejemplo rating gt 8 es V o F si rating es NULL?
• Lógica trivaluada (V, F o NULL)
• La construcción de condiciones debe ser hecha
  cuidadosamente teniendo en cuenta lo anterior.
• Esto posibilita y se necesitan de nuevos
  operadores (juntas externas en particular) para
  resolver estas situaciones.

26
SQL Embebido Lenguaje Procedural

• Los comandos SQL pueden ser ejecutados desde
  cualquier plataforma de programación (C, .NET,
  JavaScript, etc.) (EXEC SQL)
• SQL puede hacer referencia a variables del
  sistema (incluyendo variables especiales para
  retornar valores)
• Debe primero conectarse a la base de datos.
• Las relaciones SQL son un conjunto de registros,
  sin un limite preexistente sobre la cantidad.
  Muchos lenguajes no pueden manejar este tipo de
  estructuras.
• SQL soporta un mecanismo llamado cursor

27
Cursores

• Se puede declarar cursor a una consulta o
  relación.
• Se puede abrir un cursor, y repetidamente acceder
  a él (FETCH), y mover el cursor (MOVE), hasta que
  todas las tuplas hayan sido revisadas.
• Se puede usar ORDER BY, para controlar el orden
  de la tuplas.
• Los campos en ORDER BY deben aparecer en la lista
  de proyección.(no era requisito antes)
• Además de consultar se puede borrar o modificar
  una tupla que se encuentre apuntada por el
  cursor.

28
Ejemplo de cursor en C/C Encuentre los
marineros que reservaron un bote rojo, en orden
alfabético.

• Declarar cursor
• EXEC SQL DECLARE c1 CURSOR FOR SELECT deptnumb,
  deptname FROM org WHERE deptnumb lt 40
• Abrir cursor
• EXEC SQL OPEN c1
• Captar y procesar cursor
• EXEC SQL FETCH c1 INTO deptnumb, deptname
• while (sqlca.sqlcode ! 100) / no es fin de
  archivo /
• printf(" 8d -14s\n", deptnumb, deptname)
• EXEC SQL FETCH c1 INTO deptnumb, deptname
• Cerrar cursor
• EXEC SQL CLOSE c1

29
Repaso Restricciones de Integridad

• Describe las condiciones que deben tener cada
  instancia legal de una base de datos.
• Se impide la ejecución de INSERTs, DELETEs y
  UPDATEs que violen dichas condiciones.
• Pueden ser usados para asegurar semánticas de las
  aplicaciones (sid es una clave), o prevenir
  inconsistencias (sname debe ser una cadena de
  texto y age debe ser un entero positivo.)
• Tipos de RIs Restricciones de Dominio,
  Restricciones de Clave Primaria y Foránea,
  Restricciones Generales.

30
Restricciones Generales

• Útil cuando se necesitan restricciones
  adicionales.
• Se pueden usar consultas para expresar
  restricciones.
• Las restricciones pueden nombrarse

31
Restricciones sobre varias entidades

• Esta consulta es incorrecta Si Sailors esta
  vacía el nro de tuplas en Boats puede ser
  cualquiera!
• ASSERTIONs es la solución correcta.

32
Triggers

• Son procedimientos que se inician automáticamente
  si se producen determinadas acciones sobre la
  base de datos.
• Consta de 3 partes
• Evento (que activa el trigger)
• Condición (testea la condición de ejecución)
• Acción (lo que debe hacer el trigger)

33
Ejemplo de Trigger (SQL/99)
34
Resumen

• El lenguaje SQL posee todas las características
  del algebra relacional y tiene incluso más poder
  computacional.
• Aún cuando las consultas pueden representarse con
  Algebra, con SQL se pueden representar mas
  claramente.
• Hay muchas alternativas para escribir una
  consulta, generalmente el optimizador encontrará
  la mejor por nosotros. (no en todos los casos)
• El valor NULL nos complica la evaluación de
  condiciones en SQL. Tener esto en cuenta siempre.
• SQL embebido permite la ejecución de SQL dentro
  de lenguajes procedurales, siendo el cursor el
  mecanismo para traer un registro por vez y
  procesarlo.
• SQL permite la especificación de restricciones no
  solo simples sino tambien de casi cualquier
  complejidad.
• Si lo anterior aún es insuficiente para controlar
  las restricciones de integridad, podemos usar
  triggers que responden con aciones a cambios
  preestablecidos en la base de datos.