Tema 4 SQL by uzd19483

VIEWS: 0 PAGES: 85

									                           Tema 4: SQL

         n Lenguaje de definición de datos (DDL)
         n Consultas: estructura básica
         n Operaciones de conjuntos
         n Funciones agregadas
         n Valores nulos
         n Subconsultas anidadas
         n Relaciones derivadas
         n Vistas
         n Modificaciones de Bases de Datos
         n Relaciones unidas (joined)
         n SQL embebido, ODBC y JDBC



Bases de datos                                     1
       Esquema utilizado en los ejemplos


 sucursal          cuenta                     depositante            cliente
 nombre-sucursal   numero-cuenta              nombre-cliente         nombre-cliente
 ciudad-sucursal   nombre-sucursal            numero-cuenta          calle-cliente
 activos           saldo                                             ciudad-cliente




                            prestamo                           prestatario
                            numero-prestamo                    nombre-cliente
                            nombre-sucursal                    numero-prestamo
                            cantidad




Bases de datos                                                                        2
  Lenguaje de definición de datos (DDL)

Permite la especificación de un conjunto de relaciones y
además de información sobre cada una de las relaciones,
incluyendo:
    n El esquema de cada relación.
    n El dominio de los datos asociados a cada atributo.
    n Restricciones de integridad.
    n El conjunto de índices que se debe mantener para
      cada relación.
    n Información de seguridad y autorización para cada
      relación.
    n La estructura de almacenamiento físico de cada
      relación en disco.



Bases de datos                                             3
                  Tipos de dominio en SQL
n char(n). Cadena de caracteres de longitud fija n indicada por el usuario.
n varchar(n). Cadena de caracteres de longitud variable, con una longitud
  máxima n indicada por el usuario.
n int. Entero (un subconjunto finito de enteros dependiente de la máquina).
n smallint. Entero corto (un subconjunto del dominio entero cuyo tamaño es
  dependiente de la máquina).
n numeric(p,d). Número en formato de punto fijo, con una precisión
  indicada por el usuario de p digitos, con n digitos a la derecha del punto
  decimal.
n real, double precision. Numeros en formato de punto flotante y punto
  flotante de doble precisión, con precisión dependiente de la máquina.
n float(n). Númeor en punto flotante, conuna precisión indicada por el
  usuario de al menos n digitos.
n En todos los tipos de dominios se permiten valores nulos. Si un atributose
  declara not null, se prohiben los valores nulos para ese atributo.
n La construcción create domain de SQL-92 permite crear dominios
  definidos por el usuario:
           create domain nombre-persona char(20) not null
 Bases de datos                                                           4
      Tipos de Fecha/Hora en SQL (Cont.)

n date. Fechas, conteniendo un año (4 dígitos) , mes y día.
     H P.e. date ‘2001-7-27’
n time. Horas, minutos y segundos.
     H P.e. time ’09:00:30’       time ’09:00:30.75’
n timestamp: Fecha y hora.
     H P.e. timestamp ‘2001-7-27 09:00:30.75’
n Interval: periodo de tiempo
     H P.e. Interval ‘1’ día
     H Restar un valor date/time/timestamp de otro nos da un valor de tipo intervalo
     H Valores de tipo intervalo se pueden sumar a valores date/time/timestamp
n Podemos extraer valores de campos de date/time/timestamp
     H P.e. extract (year from r.fechainicio)
n Podemos transformar tipos cadena de caracteres a
    date/time/timestamp
     H P.e. cast <expresion-tipo-string> as date


 Bases de datos                                                                        5
                       Comando Create Table

     n Una relación SQL se define mediante el comando
         create table:
                 create table r (A1 D1 RI1, A2 D2 RI2, ..., An Dn RIn,
                                (restriccion-integridad1),
                                ...,
                                (restriccion-integridadk))
           H r es el nombre de la relación
           H cada Ai es un nombre de atributo en el esquema de la
             relación r
           H Di es el tipo de datos de los valores del dominio del
             atributo Ai
     n Ejemplo:
                        create table sucursal
                            (nombre-sucursal          char(15) not null,
                            ciudad-sucursal           char(30),
Bases de datos              activos                   integer)             6
  Restricciones de integridad en Create Table
 n    not null
 n    primary key (A1, ..., A n)
 n    check (P), donde P es un predicado
 n    foreign key (A1, ..., A n) references r (B1, ..., B n)



Ejemplo: Declarar nombre-sucursal como la clave
primaria de sucursal y asegurar que el valor de activos no
es negativo.
             create table sucursal
               (nombre-sucursal         char(15),
               ciudad-sucursal          char(30)
               activos                  integer,
               primary key (nombre-sucursal),
               check (activos >= 0))




Bases de datos                                                 7
             Comandos Drop y Alter Table

     n El comando drop table borra toda la información referente a
         la relación eliminada de la base de datos.
     n El comando alter table se utiliza para añadir atributos a una
         relación existente.
                 alter table r add A D
         donde A es el nombre del atributo a añadir a la relación r y
         D es el dominio de A.
           H A todas las tuplas de la relación se les asigna null como valor
             del nuevo atributo.
     n El comando alter table también se puede utilizar para
         eliminar atributos de una relación
                           alter table r drop A
         donde A es el nombre de un atributo de la relación r
           H Algunos SGBD no soportan la eliminación de atributos
Bases de datos                                                                 8
                   Integridad referencial

n Asegura que un valor que aparece en una relación para un
   conjunto de atributos determinado también aparece en un conjunto
   de atributos de otra relación.
    H Ejemplo: Si “Vigo” es un nombre de sucursal que aparece en una de
      las tuplas de la relación cuentas, entonces existe una tupla en la
      relación sucursales para la sucursal “Vigo”.
n Definición formal
    H Dadas las relaciones r1(R1) y r2(R2) con claves primarias K1 y K2
      respectivamente.
    H El subconjunto α de R2 es una clave foránea referenciando K1 en la
      relación r1, si para cada t2 en r2 debe haber una tupla t1 en r1 tal que
      t1[K1] = t2[α].
    H Las restricciones de integridad referencial también se denominan
      dependencias de subconjunto ya que se pueden expresar como
          ∏α (r2) ⊆ ∏K1 (r1)



Bases de datos                                                              9
  Integridad referencial en el modelo E-A

n Consideremos el conjunto asociación R entre los conjuntos entidad E1 y
    E2. El esquema relacional de R incluye las claves primarias K1 de E1 y
    K2 de E2.
    Entonces K1 y K2 son claves foráneas sobre los esquemas relacionales
    de E1 y E2 respectivamente.

                               E1          R          E2


n Las asociaciones 1:N se pueden resolver mediante restricciones de
  integridad, en vez de mediante una nueva asociación
n Los jerarquías de especialización/generalización también dan lugar a
  restricciones de integridad referencial.
n Los conjuntos entidad débiles también dan lugar a restricciones de
  integridad referencial.
    H El esquema de relación de un conjunto entidad débil debe incluir los
         atributos que forman la clave primaria del conjunto entidad del que depende


 Bases de datos                                                                    10
        Comprobación de integridad referencial
             durante una modificación
 n Se deben realizar las siguientes comprobaciones con el fin de
     preservar la siguiente restricción de integridad referencial:
                  ∏α (r2) ⊆ ∏K (r1)
 n Insertar. Si una tupla t2 se inserta en r2, el sistema se debe
   asegurar de que hay una tupla t1 en r1 tal que t1[K] = t2[α]. Es
   decir
                     t2 [α] ∈ ∏K (r1)
 n Eliminar. Si se elimina una tupla t1 de r1, el sistema debe
   hallar el conjunto de tuplas de r2 que referencian t1:
                         σα = t1[K] (r2)
     Si el conjunto no es vacío
       H o bien se rechaza el comando como un error,
       H o bien se deben eliminar las tuplas que referencian a t1
         (se permiten eliminaciones en cascada)

Bases de datos                                                        11
        Modificaciones de la base de datos (Cont.)
n Actualizaciones. Hay dos casos:
   H Si se actualiza una tupla t2 en la relación r2 y la actualización modifica los valores
       de la clave foránea α,entonces se debe hacer un test similar al caso de inserción:
        4    Si t2’ denota el nuevo valor de la tupla t2, el sistema se debe asegurar de que
                              t2’[α] ∈ ∏K(r1)
   H Si se actualiza una tupla t1 en r1, y la actualización modifica el valor de la clave
       primaria (K), entonces se debe realizar un test similar a la del caso de eliminación:
        1. El sistema debe calcular
                σα = t1[K] (r2)
             utilizando el valor anterior de t1 (el valor antes de hacer la actualización).
        2. Si el conjunto no es vacío
             1. la actualización se puede rechazar como un error, o
             2. La actualización se puede hacer en cascada sobre las tuplas del conjunto,
                o
             3. Las tuplas del conjunto se pueden eliminar.



    Bases de datos                                                                            12
                 Consultas: Estructura básica

n SQL está basado en operaciones sobre relaciones y sobre
    conjuntos con algunas modificaciones y mejoras
n Una consulta típica en SQL tiene la siguiente forma:
                     select A1, A2, ..., An
                     from r1, r2, ..., rm
                     where P
      H Ais repesentan atributos
      H ris representan relaciones
      H P es un predicado.

n Esta consulta es equivalente a la expresión de álgebra
    relacional:

                     ∏A1, A2, ..., An(σP (r1 x r2 x ... x rm))
n El resultado de una consulta SQL es una relación.

Bases de datos                                                   13
                      La cláusula select

n La cláusula select lista los atributos que queremos en el
    resultado de la consulta
     H Corresponde a la operación de proyección del álgebra de relaciones
n P.e. encontrar los nombres de todas las sucursales de la relación
    prestamo
                          select nombre-sucursal
                          from prestamo
n En la sintaxis del álgebra relacional “pura”, la consulta sería:
                          ∏nombre-sucursal(prestamo)




 Bases de datos                                                        14
                  La clausula select (Cont.)

n SQL permite duplicados tanto en relaciones (tablas) como en los
    resultados de las consultas.
n Para forzar la eliminación de duplicados en los resultados
    utilizamos distinct después de select.
n Encontrar los nombres de todas las sucursales en la relación
    prestamo eliminando duplicados
                    select distinct nombre-sucursal
                    from prestamo
n La palabra reservada all indica que no se eliminen los
    duplicados (comportamiento por defecto).
                    select all nombre-sucursal
                    from prestamo



 Bases de datos                                                  15
                  La clausula select (Cont.)

n Un asterisco en la clausula select indica “todos los atributos”
                           select *
                           from prestamo
n La clausula select puede contener expresiones aritméticas con
    las operaciones +, –, ∗, y /, y operar sobre constantes o atributos
    de tuplas.
n La consulta:
             select numero-prestamo, nombre-sucursal, cantidad ∗ 100
             from prestamo
    devolverá una relación igual a la relación prestamo, excepto que
    el atributo cantidad estará multiplicado por 100.




 Bases de datos                                                       16
                     La clausula where

n La clausula where especifica condiciones que debe satisfacer el
   resultado
    H Corresponden al predicado de selección del álgebra relacional.
n Encontrar todos los números de préstamo hechso en la sucursal
   de Vigo con cantidades prestadas mayores de 1200 euros.
           select numero-prestamo
           from prestamo
           where nombre-sucursal = ‘Vigo’ and cantidad > 1200
n Los resultados lógicos se pueden combinar con las conectivas
   lógicas and, or y not.
n Las comparaciones se pueden aplicar al resultado de
   expresiones aritméticas.




Bases de datos                                                         17
                 La clausula where (Cont.)

n SQL incluye un operador de comparación between
n P.e. Encontrar los números de prétamo de aquellos préstamos
   cuya cantidad esté entre 90,000 y 100,000 euros (es decir,
   ≥90,000 y ≤100,000)
        select numero-prestamo
               from prestamo
               where cantidad between 90000 and 100000




Bases de datos                                                  18
                           La clausula from

n La clausula from lista las relaciones involucradas en la consulta
    H corresponde al producto cartesiano del álgebra relacional.
n Encontrar el producto cartesiano prestatario x prestamo
                        select ∗
                        from prestatario, prestamo

n Encontrar el nombre, número de préstamo y cantidad prestada de
   todos los clientes con un préstamo en la sucursal de Vigo.
select nombre-cliente, prestatario.numero-prestamo, cantidad
        from prestatario, prestamo
        where prestatario.numero-prestamo = prestamo.numero-prestamo
               and nombre-sucursal = ‘Vigo’


    Bases de datos                                                    19
                 La operación de renombrado

n SQL permite renombrar relaciones y atributos mediante la cláusula
   as :
                    nombre-antiguo as nombre-nuevo


n Encontrar el nombre, número de préstamo y cantidad prestada de
  todos los clientes; renombrar la columna numero-prestamo como
  id-prestamo.
  select nombre-cliente, prestatario.numero-prestamo as id-prestamo,
  cantidad
  from prestatario, prestamo
  where prestatario.numero-prestamo = prestamo.numero-prestamo




Bases de datos                                                  20
                         Variables de tupla

n Las variables de tupla se definen en la cláusula from mediante el uso
    de la cláusula as.
n Encontrar los nombres de cliente y sus números de préstamo para
    todos los clientes que tengan un préstamo en alguna sucursal.

            select nombre-cliente, T.numero-prestamo, S.cantidad
                  from prestatario as T, prestamo as S
                  where T.numero-prestamo = S.numero-prestamo
n   Encontrar los nombres de todas las oficinas que tienen unos
    activos mayores que alguna sucursal de Barcelona.

       select distinct T.nombre-sucursal
         from sucursal as T, sucursal as S
         where T.activos > S.activos and S.ciudad-sucursal = ‘Barcelona’’



 Bases de datos                                                           21
     Operaciones sobre cadenas de caracteres

n SQL incluye un operador de coincidencia para comparar cadenas de
  caracteres. Los patrones se describen usando dos caracteres
  especiales:
   H porcentaje (%). El % representa cualquier subcadena de caracteres.
   H subrayado (_). El _ representa cualquier caractér.
n Encontrar los nombres de los clientes cuya calle incluya la subcadena
  “Mayor”.
                   select nombre-cliente
                   from cliente
                   where calle-cliente like ‘%Mayor%’
n Para encontrar “Mayor%”
                        like ‘Mayor\%’ escape ‘\’
n SQL soporta diversas operaciones sobre cadenas de caracteres, como
   H concatenación (utilizando “||”)
   H convertir de mayúsculas a minúsculas (y viceversa)
   H calcular la longitud, extraer subcadenas, etc.

 Bases de datos                                                           22
                  Ordenar las tuplas obtenidas

n Listar en orden alfabético los nombres de todos los clientes que
    tenganun préstamo en la sucursal de Vigo
        select distinct nombre-cliente
        from prestatario, prestamo
        where prestatario.numero-prestamo =
              prestamo.numero-prestamo and
            nombre-sucursal = ‘Vigo’
      order by nombre-cliente
n Podemos especificar desc para orden descendente o asc para
  orden ascendente para cada atributo; el orden por defecto es el
  ascendente.
     H P.e. order by nombre-cliente desc




 Bases de datos                                                      23
                 Operaciones de conjuntos

n Las operaciones de conjuntos unión (union), intersección
   (intersect), y diferencia (except) se pueden aplicar sobre
   relaciones y equivalen a las operaciones ∪, ∩, − del álgebra de
   relaciones.
n Cada una de las operaciones anteriores elimina dupicados
   automáticamente; para conservar los duplicados se debe utilizar
   union all, intersect all and except all.

   Supongamos que una tupla aparece m veces en r y n veces en
   s, entonces aparece:
    H m + n veces en r union all s
    H min(m,n) veces en r intersect all s
    H max(0, m – n) veces en r except all s



Bases de datos                                                       24
                  Operaciones de conjuntos
n Encontrar todos los clientes que tengan un préstamo, una
    cuenta o ambas cosas:
                    (select nombre-cliente from depositante)
                    union
                    (select nombre-cliente from prestatario)
n Encontrar todos los clientes que tienen tanto una cuenta como
   un préstamo
                   (select nombre-cliente from depositante)
                   intersect
                   (select nombre-cliente from borrower)
n Encontrar todos los clientes que tengan una cuenta pero no un
   préstamo.
                   (select nombre-cliente from depositante)
                   except
                   (select nombre-cliente from prestatario)
 Bases de datos                                                   25
                 Funciones agregadas

n Estas funciones operan sobre un conjunto de valores de una
   columna de una relación y devuelven un valor
                  avg: valor medio
                  min: valor mínimo
                  max: valor máximo
                  sum: suma de valores
                  count: número de valores




Bases de datos                                                 26
                 Funciones agregadas (Cont.)

n Encontrar el saldo medio de las cuentas de la sucursal de Vigo.

                 select avg (saldo)
                         from cuenta
                         where nombre-sucursal = ‘Vigo’

n Encontrar el número de tuplas de la relación cliente.

                      select count (*)
                              from cliente

n Encontrar el número de depositantes del banco.

                  select count (distinct nombre-cliente)
                          from depositante



Bases de datos                                                      27
          Funciones agregadas – Group By


 n Encontrar el número de depositantes de cada sucursal.

     select nombre-sucursal, count (distinct nombre-cliente)
             from depositante, cuenta
             where depositante.numero-cuenta = cuenta.numero-cuenta
             group by nombre-sucursal


Nota: Los atributos en la clausula select fuera de las funciones
      agregadas deben aparecer en la lista group by




  Bases de datos                                                   28
Funciones agregadas – cláusula Having

n Encontrar los nombres de todas las sucursales donde el saldo
   medio de las cuentas sea de más de 1.200 euros.

           select nombre-sucursal, avg (saldo)
                   from cuenta
                   group by nombre-sucursal
                   having avg (saldo) > 1200




Bases de datos                                                   29
                     Valores nulos (null)

n Las tuplas pueden tener valores nulos, indicado por null, para
     algunos de sus atributos.
n null significa “valor desconocido” o que ese valor no existe.
n El predicado is null se utiliza para comprobar valores nulos.
      H P.e. Encontrar todos los números de préstamo que aparecen en la
        relación prestamo con un valor nulo en cantidad.
            select numero-prestamo
            from prestamo
            where cantidad is null
n El resultado de cualquier expresión aritmética en la que
     participa null es null
      H P.e. 5 + null devuelve null
n Sin embargo, las funciones agregadas simplemente ignoran los
     nulos
Bases de datos                                                       30
                Valores nulos y lógica tri-valorada
n Cualquier comparación con null devuelve desconocido
    H P.e. 5 < null o null <> null   o   null = null
n Lógica tri-valorada utilizando el valor de verdad desconocido:
    H OR: (desconocido or true) = true, (desconocido or false) = desconocido
          (desconocido or desconocido) = desconocido
    H AND: (desconocido and true) = desconocido, (desconocido and false) = false,
            (desconocido and desconocido) = desconocido
    H NOT: (not desconocido) = desconocido
n Los resultados de los predicados de la cláusula where se tratan como false
   si toman el valor desconocido




      Bases de datos                                                           31
                 Valores nulos y agregados

n Total de cantidades de todos los préstamos
                  select sum (cantidad)
                  from prestamo
    H Esta sentencia ignora las cantidades nulas
    H El resultado es nulo si no hay cantidades no nulas
n Todas las funciones agregadas excepto count(*) ignoran las
   tuplas con valores nulos en los atributos agregados.




Bases de datos                                                 32
                 Subconsultas anidadas

n SQL proporciona un mecanismo para anidar subconsultas.
n Una subconsulta es una expresión select-from-where que está
   anidada en otra consulta.
n Un uso habitual de las subconsultas es realizar comprobaciones
   de pertenencia a un conjunto, comparaciones de conjuntos y de
   cardinalidades de conjuntos.




Bases de datos                                                 33
                                Ejemplo

 n Encontrar todos los clientes que tengan tanto una cuenta como
     un préstamo en el banco.
             select distinct nombre-cliente
                     from prestatario
                     where nombre-cliente in (select nombre-cliente
                                              from depositante)

n Encontrar todos los clientes que tienen un préstamo pero no
   una cuenta en el banco

        select distinct nombre-cliente
                from prestatario
                where nombre-cliente not in (select nombre-cliente
                                             from depositante)


  Bases de datos                                                      34
                              Ejemplo

  n Encontrar todos los clientes que tiene tanto una cuenta como un
      préstamo en la sucursal de Vigo
select distinct nombre-cliente
        from prestatario, prestamo
        where prestatario.numero-prestamo = prestamo.numero-prestamo
          and nombre-sucursal = “Vigo”
          and (nombre-sucursal, nombre-cliente) in
                        (select nombre-sucursal, nombre-cliente
                        from depositante, cuenta
                        where depositante.numero-cuenta =
                                cuenta.numero-cuenta)




   Bases de datos                                                 35
                  Comparación de conjuntos

n Encontrar todas las sucursales que tienen unos activos mayores
   que alguna sucursal de Madrid.
                 select distinct T.nombre-sucursal
                         from sucursal as T, sucursal as S
                         where T.activos > S.activos and
                                 S.ciudad-sucursal = ‘Madrid’

n La misma consulta utilizando la cláusula > some

            select nombre-sucursal
                    from sucursal
                    where activos > some
                           (select activos
                            from sucursal
                                    where ciudad-sucursal = ‘Madrid’)

Bases de datos                                                    36
            Definición de la cláusula Some

n F <comp> some r ⇔ ∃ t ∈ r que cumple (F <comp> t)
   Donde <comp> puede ser: <, ≤, >, =, ≠

                 0
    (5< some     5   ) = true
                             (leer: 5 < some tupla de la relación)
                 6
                 0
    (5< some     5   ) = false

                 0
   (5 = some     5   ) = true

            0
  (5 ≠ some 5 ) = true (dado que 0 ≠ 5)

(= some) ≡ in
Sin embargo, (≠ some) ≡ not in
Bases de datos                                                       37
                  Definición de la cláusula All

n F <comp> all r ⇔ ∀ t ∈ r (F <comp> t)

                           0
                 (5< all   5    ) = false
                           6
                            6
                 (5< all   10   ) = true

                            4
             (5 = all       5   ) = false

                   4
          (5 ≠ all 6 ) = true (dado que 5 ≠ 4 y 5 ≠ 6)

     (≠ all) ≡ not in
     Sin embargo, (= all) ≡ in
Bases de datos                                           38
                    Consulta de ejemplo

n Encontrar los nombres de todas las sucursales que tengan unos
   activos mayores que todas las sucursales de Madrid.

             select nombre-sucursal
                     from sucursales
                     where activos > all
                            (select activos
                            from sucursas
                            where ciudad-sucursal = ‘Madrid’)




Bases de datos                                                  39
      Comprobación de relaciones vacías

n La construcción exists devuelve el valor true si la subconsulta
    argumento no está vacía.
n exists r ⇔ r ≠ Ø
n not exists r ⇔ r = Ø




 Bases de datos                                                     40
                    Consulta de ejemplo

n Encontrar todos los clientes que tengan una cuenta en todas las
    sucursales de Madrid.
    select distinct S.nombre-cliente
             from depositante as S
             where not exists (
                      (select nombre-sucursal
                      from sucursal
                      where ciudad-sucursal = ‘Madrid’)
           except
                      (select R.nombre-sucursal
                      from depositante as T, cuenta as R
                      where T.numero-cuenta = R.numero-cuenta and
                               S.nombre-cliente = T.nombre-cliente))

nNotar que X – Y = Ø ⇔ X ⊆ Y
n   Nota: Esta consulta no se puede escribir con = all y sus variantes

Bases de datos                                                           41
    Comprobación de ausencia de tuplas
               duplicadas
 n La construcción unique comprueba si el resultado de una
     subconsulta tiene tuplas duplicadas.
 n Encontrar todos los clientes que tienen como mucho una cuenta
     en la sucursal de Vigo.
       select T.nombre-cliente
       from depositante as T
       where unique (
           select R.nombre-cliente
           from cuenta, depositante as R
           where T.nombre-cliente = R.nombre-cliente and
                 R.numero-cuenta = cuenta.numero-cuenta and
                 cuenta.nombre-sucursal = ‘Vigo’




Bases de datos                                                42
                  Consulta de ejemplo

n Encontrart todos los clientes que tengan al menos dos cuentas
   en la sucursal de Vigo.

        select distinct T.nombre-cliente
        from depositante T
        where not unique (
                select R.nombre-cliente
                from cuenta, depositante as R
                where T.nombre-cliente = R.nombre-cliente
        and
                R.numero-cuenta = cuenta.numero-cuenta
        and
                cuenta.nombre-sucursal = ‘Vigo’)




Bases de datos                                                    43
                                  Vistas

n Proporcionan un mecanismo para ocultar ciertos datos a ciertos
   usuarios. Para crear una vista se usa el comando:
         create view v as <consulta>

    donde:
          H <consulta> es cualquier expresión legal
          H El nombre de la vista es v




Bases de datos                                                     44
                              Ejemplo

n Una vista consistente en las sucursales y sus clientes

        create view clientes-sucursal as
          (select nombre-sucursal, nombre-cliente
           from depositante, cuenta
           where depositante.numero-cuenta = cuenta.numero-cuenta)
            union
         (select nombre-sucursal, nombre-cliente
          from prestatario, prestamo
          where prestatario.numero-prestamo = prestamo.numero-prestamo)

n Encontrar todos los clientes de la sucursal de Vigo

            select nombre-cliente
                    from clientes-sucursal
                    where nombre-sucursal = ‘Vigo’

 Bases de datos                                                 45
                   Relaciones derivadas

n Encontrar el saldo medio de las cuentas de aquellas sucursales
   donde el saldo medio de las cuentas es mayor de 1200 euros.
             select nombre-sucursal, saldo-medio
             from (select nombre-sucursal, avg (saldo)
                     from cuenta
                     group by nombre-sucursal)
                   as resultado (nombre-sucursal, saldo-medio)
             where saldo-medio > 1200
   No necesitamos utilizar la cláusula having dado que calculamos
   una relación temporal (vista) resultado en la cláusula from, y los
   atributos de resultado se pueden utilizar directamente en la
   cláusula where.




Bases de datos                                                      46
                        Cláusula With

n La cláusula With definir vistas locales a una consulta, en vez de
    globalmente.
n Encontrar todas las cuentas con el saldo máximo

        with saldo-maximo (valor) as
           select max (saldo)
           from cuenta
        select numero-cuenta
        from cuenta, saldo-maximo
        where cuenta.saldo = saldo-maximo.valor




 Bases de datos                                                       47
    Consultas complejas con la cláusula With

n Encontrar todas las sucursales donde el total de sus cuentas es
   mayor que la media del total de cuentas de todas las sucursales.

      with total-sucursal (nombre-sucursal, valor) as
            select nombre-sucursal, sum (saldo)
            from cuenta
            group by nombre-sucursal
      with media-total-sucursal(valor) as
            select avg (valor)
            from total-sucursal
      select nombre-sucursal
      from total-sucursal, media-total-sucursal
      where total-sucursal.valor >= media-total-sucursal.valor




Bases de datos                                                      48
            Modificación de datos – Borrado
  n Borrar todas las cuentas de la sucursal de Vigo
                 delete from cuenta
                 where nombre-sucursal = ‘Vigo’
  n Borrar todas las cuentas de todas las sucursales de Madrid.
      delete from cuenta
      where nombre-sucursal in (select nombre-sucursal
                              from sucursal
                              where ciudad-sucursal = ‘Madrid’)
      delete from depositante
      where numero-cuenta in
                 (select numero-cuenta
                  from sucursal, cuenta
                  where ciudad-sucursal = ‘Madrid’
                    and sucursal.nombre-sucursal = cuenta.nombre-
      sucursal)

Bases de datos                                                    49
                      Ejemplo de borrado

n Borrar todas las cuentas con saldos por debajo de la media del
   banco.

        delete from cuenta
           where saldo < (select avg (saldo)
               from cuenta)

   H Problema: a medida que borramos tuplas, la media cambia
   H Solución utilizada en SQL:
   1.     Primero, calcular avg y encontrar todas las tuplas a borrar
   2. Segundo, borrar todas las tuplas encontradas antes (sin recalcular
   avg ni recomprobar las tuplas)




Bases de datos                                                          50
          Modificación de Datos - Inserción
n Añadir una nueva tupla a cuenta
                 insert into cuenta
                       values (‘A-9732’, ‘Vigo’,1200)

o, como forma alternativa

   insert into cuenta (nombre-sucursal, saldo, numero-cuenta)
           values (‘Vigo’, 1200, ‘A-9732’)
n Añadir una nueva tupla a cuenta con saldo puesto a nulo
                 insert into cuenta
                       values (‘A-777’,‘Vigo’, null)




Bases de datos                                                  51
           Modificación de datos – Inserción
n Dar como premio a todos los clientes con préstamo en la sucursal
  de Vigo una nueva cuenta de ahorro con 200 euros de saldo. El
  número de préstamo servirá como númeor de cuenta para la nueva
  cuenta de ahorro.
     insert into cuenta
       select numero-prestamo, nombre-sucursal, 200
       from prestamo
       where nombre-sucursal = ‘Vigo’
     insert into depositante
       select nombre-cliente, numero-prestamo
       from prestamo, prestatario
       where nombre-sucursal = ‘Vigo’
              and prestamo.numero-cuenta = prestatario.numero-
  cuenta
n La sentencia select-from-where se evalúa totalmente antes de
  insertar ninguno de sus resultados en la relación (si no, consultas
  como
       insert into tabla1 select * from tabla1
  causarían problemas
Bases de datos                                                   52
  Modificación de datos – Actualizaciones
n Incrementar todas las cuentas con más de 10,000 euros un 6% y
   el resto de cuentas un 5%.
    H Escribimos dos sentencias update:
                     update cuentas
                     set saldo = saldo ∗ 1.06
                     where saldo > 10000


                     update cuentas
                     set saldo = saldo ∗ 1.05
                     where saldo ≤ 10000
    H El orden es importante
    H Se puede hacer mejor con la sentencia case




Bases de datos                                                53
                 Sentencia Case para actualizaciones
                            condicionales
n La misma consulta de antes: aumentar los saldos de todas las
   cuentas de más de 10,000 euros un 6% y las demás un 5%.

       update cuenta
       set saldo = case
                       when saldo <= 10000 then saldo *1.05
                       else saldo * 1.06
                     end




Bases de datos                                                   54
           Actualización a través de vistas
n Crear una vista de todos los datos sobre préstamos en la relación
  prestamo, ocultando el atributo cantidad
         create view prestamo-sucursal as
                 select nombre-sucursal, numero-prestamo
                 from prestamo
n Añadir una nueva tupla a prestamo-sucursal
         insert into prestamo-sucursal
                 values (‘Vigo’, ‘L-307’)
  Esta inserción se debe transformar en la inserción de la tupla
                 (‘L-307’, ‘Vigo’, null)
  en la relación prestamo
n En vistas más complejas las actualizaciones pueden ser más difíciles
  o imposibles de transformar y no están, por tanto, permitidas.
n La mayoría de implementaciónes SQL sólo permiten actualizaciones
  a través de vistas simples (sin agregaciones) definidas sobre una sola
  relación.


Bases de datos                                                         55
                                  Transacciones

n Una transacción es una secuencia de sentencias (normalmente de
    consulta y actualización) que se ejecutan como una sola unidad.
     H Las transacciones se inician de manera implícita y se terminan mediante:
          4 commit work: hace permanentes en la base de datos todos los cambios
          4 rollback work: deshace todos los cambios realizados en la transacción.

n Ejemplo:
     H La transferencia de dinero de una cuenta a otra supone dos pasos:
          4       quitarlo de una cuenta y añadirlo a la otra
     H Si se realiza un paso y falla el otro, la base de datos queda en un estado
         inconsistente
     H Por tanto, se deben realizar los dos pasos, o ninguno.
n Si cualquier paso de la transacción falla, todo el trabajo realizado por la
    transacción se puede desahcer mediante rollback work.
n El rollback de las transacciones incompletas se hace automáticamente en
    caso de fallos del sistema.

 Bases de datos                                                                     56
                 Transacciones (Cont.)

n En la mayoría de SGBD, cada sentencia SQL que se ejecuta
   correctamente se confirma (commit) automáticamente.
    H En este caso, cada transacción debe consistir en una sola
      sentencia.
    H Normalmente se puede deshabilitar la confirmación automática,
      permitiendo transacciones multi-sentencia, pero cómo se hace esto
      es dependiente del SGBD.
    H Otra opción, en SQL:1999: rodear las sentencias de:
         begin atomic
           …
         end




Bases de datos                                                        57
                  Relaciones unidas (join)

n Las operaciones join toman dos relaciones y devuelven otra
    relación.
n Estas operaciones normalmente se utilizan como subconsultas
    en la cláusula from.
n Condición del join – define qué tuplas de las dos relaciones
    coinciden, y qué atributos aparecerán en el resustado del join.
n Tipo de join – define como tratar aquellas tuplas de cada
    relación que no coinciden con ninguna tupla de la otra relación
    (en base a la condición de join.

          Tipos de join          Condición de join
          inner join             natural
          left outer join        on <predicado>
          right outer join       using (A1, A2, ..., An)
          full outer join

 Bases de datos                                                       58
          Relaciones unidas – Datos para los
                      ejemplos
  n Relación prestamo

            numero-prestamo nombre-sucursal       cantidad

                   L-170       Lugo                3000
                   L-230       Ourense             4000
                   L-260       Vigo                1700

 n Relación prestatario
                 nombre-cliente numero-prestamo

                 Fernández            L-170
                 Suárez               L-230
                 López                L-155

  n Nota: falta la información del préstatario del préstamo
    L-260 y la información del préstamo L-155

Bases de datos                                                59
                 Relaciones unidas - Ejemplos

       n prestamo inner join prestatario on
           prestamo.numero-prestamo = prestatario.numero-prestamo
numero-prestamo      nombre-sucursal   cantidad   nombre-cliente    numero-prestamo

     L-170           Lugo               3000      Fernández         L-170
     L-230           Ourense            4000      Suárez            L-230

       n prestamo left outer join prestatario on
         prestamo.numero-prestamo = prestatario.numero-prestamo

numero-prestamo      nombre-sucursal   cantidad    nombre-cliente   numero-prestamo

      L-170          Lugo                3000     Fernández              L-170
      L-230          Ourense             4000     Suárez                 L-230
      L-260          Vigo                1700     null                      null

    Bases de datos                                                                 60
             Relaciones unidas - Ejemplos

   n prestamo natural inner join prestatario

         numero-prestamo   nombre-sucursal   cantidad   nombre-cliente

                 L-170     Lugo               3000      Fernández
                 L-230     Ourense            4000      Suárez


   n prestamo natural right outer join prestatario

         numero-prestamo   nombre-sucursal   cantidad   nombre-cliente

                 L-170     Lugo               3000      Fernández
                 L-230     Ourense            4000      Suárez
                 L-155     null                null     López


Bases de datos                                                           61
             Relaciones unidas - Ejemplos

 n prestamo full outer join prestatario using (numero-prestamo)

     numero-prestamo   nombre-sucursal   cantidad    nombre-cliente

            L-170      Lugo               3000      Fernández
            L-230      Ourense            4000      Suárez
            L-260      Vigo               1700      null
            L-155      null                null     López

 n Encontrar todos los clientes que tengan o bien una cuenta o
   bien un préstamo (pero no ambos) en el banco.
      select nombre-cliente
              from (depositante natural full outer join prestatario)
              where numero-cuenta is null or numero-prestamo is null


Bases de datos                                                        62
                        SQL embebido

n El estándar SQL define la inclusión de SQL en diversos lengujes
    de programación como Pascal, PL/I, Fortran, C, y Cobol.
n EL lenguaje en el que se incluyen sentencias SQL se denomina
    lenguaje anfitrión (host), y las estructuras SQL permitidas en el
    lenguaje anfitrión se denominan SQL embebido (embedded).
n La forma básica de estos lenguajes sigue la utilizada en el
    Sistema R para incluir SQL en PL/I.
n La sentencia EXEC SQL se utiliza para identificar solicitudes
    embebidas SQL al preprocesador
         EXEC SQL <sentencia SQL embebida > END-EXEC
    Nota: en algunos lenguajes esto cambia. P.e. en Java se usa
        # SQL { …. } ;



 Bases de datos                                                         63
                 Ejemplo de consulta


Desde un lenguaje anfitrión, encontrar los nombres y ciudades
con alguna cuenta que tenga más euros de la cantidad
almacenada en la variable cantidad.
 n Especificamos la consulta en SQL y declaramos un cursor
   asociado
 EXEC SQL
   declare c cursor for
   select nombre-cliente, ciudad-cliente
   from depositante, cuenta, cantidad
   where depositante.nombre-cliente = cliente.nombre-cliente
        and depositante.numero-cuenta = cuenta.numero-cuenta
        and cuenta.saldo > :cantidad
 END-EXEC


Bases de datos                                                  64
                     SQL embebido (Cont.)

n La sentencia open hace que se evalúe la consulta
                  EXEC SQL open c END-EXEC
n La sentencia fetch sitúa en variables del lenguaje anfitrión los
    valores de una tupla del resultado de la consulta.
       EXEC SQL fetch c into :cn, :cc END-EXEC
    Repetidas llamadas a fetch devuelve tuplas sucesivas del
    resultado de la consulta
n Una variable denominada SQLSTATE en el área de
    comunicación de SQL (SQLCA) se pone a ‘02000’ para indicar
    que no hay más datos
n La sentencia close hace que el SGBD elimine la relación
    temporal que alamcena el resultado de la consulta.
                  EXEC SQL close c END-EXEC

 Bases de datos                                                      65
      Actualizaciones mediante cursores

n Declarando que un cursor es para actualizaciones se puede
     modificar la tupla actual del cursor
        declare c cursor for
          select *
          from cuenta
          where nombre-sucursal = ‘Vigo’
        for update
n Para actualizar la tupla en la posición actual del cursor:
        update cuenta
        set saldo = saldo + 100
        where current of c




Bases de datos                                                 66
                       SQL dinámico

    n Permite a los programas construir y ejecutar sentencias SQL en
      tiempo de ejecución.
    n Ejemplo de uso de SQL dinámico desde un programa C.

      char * consultasql = “update cuenta
                         set saldo = saldo * 1.05
                         where numero-cuenta = ?”
      EXEC SQL prepare consultadin from :consultasql;
      char cuenta [10] = “A-101”;
      EXEC SQL execute consultadin using :cuenta;
    n El programa SQL dinámico contiene un ?, que es un “hueco”
      para el valor que se proporciona cuando se ejecuta el programa
      SQL.




Bases de datos                                                  67
                                   ODBC

n Estándar Open DataBase Connectivity (ODBC)
    H Estándar para programar comunicaciones (accesos) a un servidor
      de bases de datos desde aplicaciones.
    H API para
         4   abrir una conexión con una base de datos,
         4   enviar sentencias,
         4   recibir resultados.




Bases de datos                                                         68
                              ODBC (Cont.)
n Cada SGBD que soporta ODBC proporciona una librería "driver" que se
    debe enlazar con el programa cliente.
n   Cuando el programa cliente hace una llamada al API ODBC, el código
    de la librería se comunica con el servidor para realizar la acción
    solicitada y obtener los resultados.
n   El programa ODBC primero asigna un entorno SQL y, a continuacíón,
    un manejador de conexión a base de datos.
n   Abre una conexión a la base de datos utilizando SQLConnect().
    SQLConnect toma como parámetros:
     H el manejador de la conexión,
     H el servidor a que conectar,
     H el identificador del usuario,
     H su password
n   También debe especificar los tipos de los argumentos:
     H SQL_NTS indica que el argumento anterior es una cadena de carateres
         terminado en nulo.



 Bases de datos                                                              69
                        Código ODBC
n int ejemploODBC()
   {
       RETCODE error;
       HENV env; /* entorno */
       HDBC conn; /* conexion a base de datos */
       SQLAllocEnv(&env);
       SQLAllocConnect(env, &conn);
       SQLConnect(conn, “clave.det.uvigo.es", SQL_NTS, “mramos",
         SQL_NTS, “mramospass", SQL_NTS);
       { …. Realizamos el trabajo … }


       SQLDisconnect(conn);
       SQLFreeConnect(conn);
       SQLFreeEnv(env);
  }

Bases de datos                                                     70
                          Código ODBC (Cont.)

n Los programas envían comandos SQL a la base datos mediante
    SQLExecDirect
n Las tuplas del resultado se acceden mediante SQLFetch()
n SQLBindCol() asocia variables C a atributos del resultado de la consulta.
          4 Cuando se accede a una tupla, sus valores se almacenan
             automáticamente a las variables C correspondientes.
          4 Argumentos do SQLBindCol()
                  – Variable ODBC stmt,
                  – Posición del atributo en el resultado de la consulta
                  – El tipo de conversión de SQL a C.
                  – La dirección de la variable.
                  – Para tipos de longitud variable, como cadenas de caracteres,
                     » La longitud máxima de la variable.
                     » Lugar para almacenar la longitud actual cuando se acceda a la
                       tupla.


 Bases de datos                                                                        71
                      Código ODBC (Cont.)
n Cuerpo principal del programa
        char nombresucursal[80];
       float saldo;
       int longOut1, longOut2;
       HSTMT stmt;
       SQLAllocStmt(conn, &stmt);
       char * consultasql = "select nombre_sucursal, sum (saldo)
                            from cuenta
                            group by nombre_sucursal";
        error = SQLExecDirect(stmt, consultasql, SQL_NTS);
        if (error == SQL_SUCCESS) {
           SQLBindCol(stmt, 1, SQL_C_CHAR, nombresucursal, 80, &longOut1);
           SQLBindCol(stmt, 2, SQL_C_FLOAT, &saldo,         0, &longOut2);
          while (SQLFetch(stmt) >= SQL_SUCCESS) {
               printf (" %s %g\n", nombresucursal, saldo);
           }
       }
       SQLFreeStmt(stmt, SQL_DROP);
   Bases de datos                                                     72
                Más características de ODBC
n Sentencias Preparadas
    H Sentencia SQL preparada: compilada en la base de datos
    H Puede tener “huecos”: P.e.: insert into cuenta values(?,?,?)
    H Se ejecuta varias veces con valores concretos para los “huecos”
n Manejo de Metadatos
    H Encontrar todas las relaciones de la base de datos y
    H encontrar los nombres y tipos de las columnas de un resultado de consulta
      o una relación de la base de datos.
n Por defecto, cada sentencia SQL se trata como una transacción, es
  decir, se confirma automáticamente.
    H Se puede desactivar la confirmación automática en una conexión
       4 SQLSetConnectOption(conn, SQL_AUTOCOMMIT, 0)}
    H Y las transacciones de deben confirmar o anular entonces explícitamente
      mediante
       4 SQLTransact(conn, SQL_COMMIT) o
       4 SQLTransact(conn, SQL_ROLLBACK)
   Bases de datos                                                        73
             Niveles de conformidad ODBC

n Los niveles de conformidad especifican subconjuntos de la
    funcionalidad definida por el estándar.
     H Core
     H Nivel 1: requiere soporte de consulta de metadatos
     H Nivel 2: requiere capacidad para enviar y obtener cadenas de
       valores de parámetros e información de catálogo más detallada.
n El estándar CLI (SQL Call Level Interface) es similar al interfaz
    ODBC, con pequeñas diferencias.




 Bases de datos                                                         74
                                 JDBC

n JDBC es una API Java para comunicarse con SGBD que
   soportan SQL
n JDBC soporta diversas características para consultar y
   actualizar datos y para obtener los resultados de consultas
n JDBC también soporta la obtención de metadatos, tales como
   consultas sobre relaciones de la base de datos y sobre los
   nombres y tipos de los atributos de las relaciones
n Modelo para comunicarse con la base de datos:
    H Abrir una conexión
    H Crear un objeto “sentencia”
    H Ejecutar consultas utilizando el objeto Sentencia para enviar
      consultas y obtener resultados
    H Mecanismos de excepción para gestionar errores


Bases de datos                                                        75
                             Código JDBC
    public static void EjemploJDBC(String idusuario, String passwd)
 {
         try {
            Class.forName ("oracle.jdbc.driver.OracleDriver");
            Connection conn = DriverManager.getConnection(
             "jdbc:oracle:thin:@bd.uvigo.es:2000:bdbanco", idusuario, passwd);
          Statement stmt = conn.createStatement();
            …Realizar trabajo ….
          stmt.close();
          conn.close();
     }
     catch (SQLException sqle) {
          System.out.println(“ExcepciónSQL : " + sqle);
     }
}
Bases de datos                                                               76
                          Código JDBC (Cont.)
n Actualizar la base de datos
    try {
        stmt.executeUpdate( "insert into cuentas values
                               ('A-9732', ‘Vigo', 1200)");
    } catch (SQLException sqle) {
        System.out.println(“No se pudo introducir la tupla. " + sqle);
    }
n Ejecutar una consulta y extraer e imprimir los resultados
    ResultSet rset = stmt.executeQuery( "select nombre_sucursal, avg(saldo)
                                         from cuenta
                                         group by nombre_sucursal");
    while (rset.next()) {
            System.out.println(
                    rset.getString(“nombre_sucursal") + " " + rset.getFloat(2));
    }
   Bases de datos                                                                  77
                 Detalles del código JDBC

n Obtener campos del resultado:
    H rs.getString(“nombresucursal”) y rs.getString(1) son equivalentes si
      nombresucursal es el primer argumento del resultado del select.
n Tratamiento de valores Null
    int a = rs.getInt(“a”);
    if (rs.wasNull()) Systems.out.println(“Obtenido un valor nulo”);




Bases de datos                                                           78
                   Sentencias preparadas

n Las sentencias preparadas permiten que las consultas se compilen
   y ejecuten varias veces con argumentos distintos
        PreparedStatement pStmt = conn.prepareStatement(
                                        “insert into cuenta values(?,?,?)”);
        pStmt.setString(1, "A-9732");
        pStmt.setString(2, “Vigo");
        pStmt.setInt(3, 1200);
        pStmt.executeUpdate();


        pStmt.setString(1, "A-9733");
        pStmt.executeUpdate();




Bases de datos                                                                 79
             Arquitecturas de la aplicación

n La aplicaciones se pueden construir siguiendo una de las dos
   arquitecturas siguientes:
    H Modelo de dos capas
         4   El programa de aplicación en la máquina del usuario utiliza
             directamente JDBC/ODBC para comunicarse con la base de
             datos
    H Modelo de tres capas
         4   Los usuarios/programas ejecutándose en la máquina del usuario
             se comunica con una aplicación del servidor. La aplicación del
             servidor a su vez se comunica con la base de datos




Bases de datos                                                             80
                   Modelo de dos capas

n P.e. código Java se ejecuta en la máquina cliente y utiliza JDBC
   para comunicarse con el servidor
n Beneficios:
    H flexibilidad, no necesita restringirse a consultas predefinidas
n Problemas:
    H Seguridad: las passwords están disponibles en la máquina cliente;
      se permiten todas las operaciones sobre la base de datos
    H Más código en el cliente
    H No apropiado entre organizaciones, o en aquellas grandes como
      universidades




Bases de datos                                                            81
                       Modelo de tres capas

                                    Programa CGI



            Servidor de                             JDBC            Servidor
                                   Servlets                          de BD
        Aplicaciones/HTTP

                       HTTP/Protocolo específico de la aplicación



                            Red




             Cliente            Cliente            Cliente
Bases de datos                                                                 82
                  Modelo de tres capas (Cont.)

n P.e. Cliente Web + Servlet Java utilizando JDBC para
    comunicarse con el servidor de bases de datos
n El cliente envía peticiones vía http o un protocolo específico de
    la aplicación
n La aplicación o el servidor Web recibe la petición
n La petición la gestiona un programa CGI program o servlets
n La seguridad la gestiona la aplicación en el servidor
     H Mayor seguridad
     H Seguridad de grado fino
n Cliente simple, pero sólo puede hacer transacciones
    predefinidas




 Bases de datos                                                       83
           Extensiones Procedimentales y
            Procedimientos Almacenados
n SQL proporciona un lenguaje modular
    H permite definir procedimientos en SQL, con sentencias if-then-else,
      bucles for y while, etc.
n Procedimientos Almacenados
    H Se pueden almacenar procedimientos en la base de datos
    H y ejecutarlos mediante la sentencia call
    H permite a las aplicaciones externas operar sobre la base de datos
      sin saber nada sobre detalles internos




Bases de datos                                                            84
                      Fin




Bases de datos   Manuel Ramos Cabrer   85

								
To top