Tema 4: SQL

n Lenguaje de definicin de datos (DDL) n Consultas: estructura bsica 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

Esquema utilizado en los ejemplos

sucursal nombre-sucursal ciudad-sucursal activos

cuenta numero-cuenta nombre-sucursal saldo

depositante nombre-cliente numero-cuenta

cliente nombre-cliente calle-cliente ciudad-cliente

prestamo numero-prestamo nombre-sucursal cantidad

prestatario nombre-cliente numero-prestamo

Bases de datos

Lenguaje de definicin de datos (DDL)

Permite la especificacin de un conjunto de relaciones y adems de informacin sobre cada una de las relaciones, incluyendo:
n El esquema de cada relacin. 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 relacin. n Informacin de seguridad y autorizacin para cada relacin. n La estructura de almacenamiento fsico de cada relacin en disco.

Bases de datos

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

mxima n indicada por el usuario. n int. Entero (un subconjunto finito de enteros dependiente de la mquina). n smallint. Entero corto (un subconjunto del dominio entero cuyo tamao es dependiente de la mquina). n numeric(p,d). Nmero en formato de punto fijo, con una precisin 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 precisin, con precisin dependiente de la mquina. n float(n). Nmeor en punto flotante, conuna precisin 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 construccin 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 ao (4 dgitos) , mes y da.

H P.e. date 2001-7-27

n time. Horas, minutos y segundos.

H P.e. time [Link]

n timestamp : Fecha y hora.

time [Link].75

H P.e. timestamp 2001-7-27 [Link].75

n Interval : periodo de tiempo

H P.e. Interval 1 da 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 [Link])

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 relacin 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 relacin H cada Ai es un nombre de atributo en el esquema de la relacin r H Di es el tipo de datos de los valores del dominio del atributo Ai
n Ejemplo:

Bases de datos

create table sucursal (nombre-sucursal ciudad-sucursal activos

char(15) not null, char(30), integer)

Restricciones de integridad en Create Table

n n n n

not null primary key (A1, ..., A n) check (P), donde P es un predicado 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

Comandos Drop y Alter Table

n El comando drop table borra toda la informacin referente a

la relacin eliminada de la base de datos.

n El comando alter table se utiliza para aadir atributos a una

relacin existente. alter table r add A D donde A es el nombre del atributo a aadir a la relacin r y D es el dominio de A.
H A todas las tuplas de la relacin se les asigna null como valor del nuevo atributo.
n El comando alter table tambin se puede utilizar para

eliminar atributos de una relacin alter table r drop A donde A es el nombre de un atributo de la relacin r
H Algunos SGBD no soportan la eliminacin de atributos
Bases de datos 8

Integridad referencial
n Asegura que un valor que aparece en una relacin para un

conjunto de atributos determinado tambin aparece en un conjunto de atributos de otra relacin.

H Ejemplo: Si Vigo es un nombre de sucursal que aparece en una de las tuplas de la relacin cuentas, entonces existe una tupla en la relacin sucursales para la sucursal Vigo.
n Definicin 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 fornea referenciando K1 en la relacin 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 tambin se denominan dependencias de subconjunto ya que se pueden expresar como (r2) K1 (r1)

Bases de datos

Integridad referencial en el modelo E-A

n Consideremos el conjunto asociacin 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 forneas 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 asociacin n Los jerarquas de especializacin/generalizacin tambin dan lugar a restricciones de integridad referencial. n Los conjuntos entidad dbiles tambin dan lugar a restricciones de integridad referencial. H El esquema de relacin de un conjunto entidad dbil debe incluir los
atributos que forman la clave primaria del conjunto entidad del que depende

Bases de datos

10

Comprobacin de integridad referencial durante una modificacin

n Se deben realizar las siguientes comprobaciones con el fin de

preservar la siguiente restriccin 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 vaco
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 relacin r2 y la actualizacin modifica los valores

4

de la clave fornea ,entonces se debe hacer un test similar al caso de insercin: 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 actualizacin modifica el valor de la clave

primaria (K), entonces se debe realizar un test similar a la del caso de eliminacin:
1. El sistema debe calcular

= t1[K] (r2)
utilizando el valor anterior de t1 (el valor antes de hacer la actualizacin).
2. Si el conjunto no es vaco

1. la actualizacin se puede rechazar como un error, o 2. La actualizacin 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 bsica

n SQL est basado en operaciones sobre relaciones y sobre

conjuntos con algunas modificaciones y mejoras

n Una consulta tpica 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 expresin de lgebra

relacional: A1, A2, ..., An(P (r1 x r2 x ... x rm))

n El resultado de una consulta SQL es una relacin.
Bases de datos 13

La clusula select
n La clusula select lista los atributos que queremos en el

resultado de la consulta
H Corresponde a la operacin de proyeccin del lgebra de relaciones
n P.e. encontrar los nombres de todas las sucursales de la relacin

prestamo select nombre-sucursal from prestamo

n En la sintaxis del lgebra relacional pura, la consulta sera:

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 eliminacin de duplicados en los resultados

utilizamos distinct despus de select.

n Encontrar los nombres de todas las sucursales en la relacin

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 aritmticas con

las operaciones +, , , y /, y operar sobre constantes o atributos de tuplas. select numero-prestamo, nombre-sucursal, cantidad 100 from prestamo devolver una relacin igual a la relacin prestamo, excepto que el atributo cantidad estar multiplicado por 100.

n La consulta:

Bases de datos

16

La clausula where
n La clausula where especifica condiciones que debe satisfacer el

resultado
H Corresponden al predicado de seleccin del lgebra relacional.
n Encontrar todos los nmeros de prstamo 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 lgicos se pueden combinar con las conectivas

lgicas and, or y not.

n Las comparaciones se pueden aplicar al resultado de

expresiones aritmticas.

Bases de datos

17

La clausula where (Cont.)

n SQL incluye un operador de comparacin between n P.e. Encontrar los nmeros de prtamo de aquellos prstamos

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, nmero de prstamo y cantidad prestada de

todos los clientes con un prstamo en la sucursal de Vigo. select nombre-cliente, [Link]-prestamo, cantidad from prestatario, prestamo where [Link]-prestamo = [Link]-prestamo and nombre-sucursal = Vigo

Bases de datos

19

La operacin de renombrado
n SQL permite renombrar relaciones y atributos mediante la clusula

as : nombre-antiguo as nombre-nuevo
n Encontrar el nombre, nmero de prstamo y cantidad prestada de

todos los clientes; renombrar la columna numero-prestamo como id-prestamo. select nombre-cliente, [Link]-prestamo as id-prestamo, cantidad from prestatario, prestamo where [Link]-prestamo = [Link]-prestamo

Bases de datos

20

Variables de tupla
n Las variables de tupla se definen en la clusula from mediante el uso

de la clusula as.
n Encontrar los nombres de cliente y sus nmeros de prstamo para

todos los clientes que tengan un prstamo en alguna sucursal.

select nombre-cliente, [Link]-prestamo, [Link] from prestatario as T, prestamo as S where [Link]-prestamo = [Link]-prestamo
n

Encontrar los nombres de todas las oficinas que tienen unos activos mayores que alguna sucursal de Barcelona. select distinct [Link]-sucursal from sucursal as T, sucursal as S where [Link] > [Link] and [Link]-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 caractr. 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 concatenacin (utilizando ||) H convertir de maysculas a minsculas (y viceversa) H calcular la longitud, extraer subcadenas, etc.
Bases de datos 22

Ordenar las tuplas obtenidas

n Listar en orden alfabtico los nombres de todos los clientes que

tenganun prstamo en la sucursal de Vigo select distinct nombre-cliente from prestatario, prestamo where [Link]-prestamo = [Link]-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 unin (union), interseccin

(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

automticamente; 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 prstamo, 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 prstamo (select nombre-cliente from depositante) intersect (select nombre-cliente from borrower)
n Encontrar todos los clientes que tengan una cuenta pero no un

prstamo. (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 relacin y devuelven un valor avg: valor medio min: valor mnimo max: valor mximo sum: suma de valores count: nmero 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 nmero de tuplas de la relacin cliente.

select count (*) from cliente

n Encontrar el nmero de depositantes del banco.

select count (distinct nombre-cliente) from depositante

Bases de datos

27

Funciones agregadas Group By

n Encontrar el nmero de depositantes de cada sucursal.

select nombre-sucursal, count (distinct nombre-cliente) from depositante, cuenta where [Link]-cuenta = [Link]-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 clusula Having

n Encontrar los nombres de todas las sucursales donde el saldo

medio de las cuentas sea de ms 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 nmeros de prstamo que aparecen en la relacin prestamo con un valor nulo en cantidad.

select numero-prestamo from prestamo where cantidad is null

n El resultado de cualquier expresin aritmtica 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 lgica tri-valorada

n Cualquier comparacin con null devuelve desconocido

H P.e. 5 < null o null <> null

null = null

n Lgica 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 clusula 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 prstamos

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 expresin 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 prstamo 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 prstamo 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

prstamo en la sucursal de Vigo select distinct nombre-cliente from prestatario, prestamo where [Link]-prestamo = [Link]-prestamo and nombre-sucursal = Vigo and (nombre-sucursal, nombre-cliente) in (select nombre-sucursal, nombre-cliente from depositante, cuenta where [Link]-cuenta = [Link]-cuenta)

Bases de datos

35

Comparacin de conjuntos
n Encontrar todas las sucursales que tienen unos activos mayores

que alguna sucursal de Madrid. select distinct [Link]-sucursal from sucursal as T, sucursal as S where [Link] > [Link] and [Link]-sucursal = Madrid
n La misma consulta utilizando la clusula > some

select nombre-sucursal from sucursal where activos > some (select activos from sucursal where ciudad-sucursal = Madrid)
Bases de datos 36

Definicin de la clusula Some

n F <comp> some r t r que cumple (F <comp> t)

Donde <comp> puede ser: <, , >, =,

(5< some

0 5 6 0 5 0 5

) = true (leer: 5 < some tupla de la relacin)

(5< some (5 = some

) = false ) = true

0 (5 some 5 ) = true (dado que 0 5) (= some) in Sin embargo, ( some) not in

Bases de datos 37

Definicin de la clusula All

n F <comp> all r t r (F <comp> t)

(5< all

0 5 6 6 10 4 5

) = false

(5< all (5 = all

) = true ) = 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

Comprobacin de relaciones vacas

n La construccin exists devuelve el valor true si la subconsulta

argumento no est vaca.

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 [Link]-cliente from depositante as S where not exists ( (select nombre-sucursal from sucursal where ciudad-sucursal = Madrid) except (select [Link]-sucursal from depositante as T, cuenta as R where [Link]-cuenta = [Link]-cuenta and [Link]-cliente = [Link]-cliente))
nNotar que X Y = X Y n

Nota: Esta consulta no se puede escribir con = all y sus variantes

41

Bases de datos

Comprobacin de ausencia de tuplas duplicadas

n La construccin 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 [Link]-cliente from depositante as T where unique ( select [Link]-cliente from cuenta, depositante as R where [Link]-cliente = [Link]-cliente and [Link]-cuenta = [Link]-cuenta and [Link]-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 [Link]-cliente from depositante T where not unique ( select [Link]-cliente from cuenta, depositante as R where [Link]-cliente = [Link]-cliente and [Link]-cuenta = [Link]-cuenta and [Link]-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 expresin 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 [Link]-cuenta = [Link]-cuenta) union (select nombre-sucursal, nombre-cliente from prestatario, prestamo where [Link]-prestamo = [Link]-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 clusula having dado que calculamos una relacin temporal (vista) resultado en la clusula from, y los atributos de resultado se pueden utilizar directamente en la clusula where.

Bases de datos

46

Clusula With
n La clusula With definir vistas locales a una consulta, en vez de

globalmente.
n Encontrar todas las cuentas con el saldo mximo

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

Bases de datos

47

Consultas complejas con la clusula 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 [Link] >= [Link]

Bases de datos

48

Modificacin 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 [Link]-sucursal = [Link])
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 Solucin 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

Modificacin de Datos - Insercin

n Aadir 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 Aadir una nueva tupla a cuenta con saldo puesto a nulo

insert into cuenta values (A-777,Vigo, null)

Bases de datos

51

Modificacin de datos Insercin

n Dar como premio a todos los clientes con prstamo en la sucursal

de Vigo una nueva cuenta de ahorro con 200 euros de saldo. El nmero de prstamo servir como nmeor 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 [Link]-cuenta = [Link] n La sentencia select-from-where se evala totalmente antes de insertar ninguno de sus resultados en la relacin (si no, consultas como insert into tabla1 select * from tabla1 causaran problemas
Bases de datos 52

Modificacin de datos Actualizaciones

n Incrementar todas las cuentas con ms 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 ms de 10,000 euros un 6% y las dems un 5%. update cuenta set saldo = case when saldo <= 10000 then saldo *1.05 else saldo * 1.06 end

Bases de datos

54

Actualizacin a travs de vistas

n Crear una vista de todos los datos sobre prstamos en la relacin

prestamo, ocultando el atributo cantidad create view prestamo-sucursal as select nombre-sucursal, numero-prestamo from prestamo n Aadir una nueva tupla a prestamo-sucursal insert into prestamo-sucursal values (Vigo, L-307) Esta insercin se debe transformar en la insercin de la tupla (L-307, Vigo, null) en la relacin prestamo n En vistas ms complejas las actualizaciones pueden ser ms difciles o imposibles de transformar y no estn, por tanto, permitidas. n La mayora de implementacines SQL slo permiten actualizaciones a travs de vistas simples (sin agregaciones) definidas sobre una sola relacin.

Bases de datos

55

Transacciones
n Una transaccin es una secuencia de sentencias (normalmente de

consulta y actualizacin) que se ejecutan como una sola unidad. H Las transacciones se inician de manera implcita 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 transaccin.

n Ejemplo:

H La transferencia de dinero de una cuenta a otra supone dos pasos:

4

quitarlo de una cuenta y aadirlo 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 transaccin falla, todo el trabajo realizado por la

transaccin se puede desahcer mediante rollback work.

n El rollback de las transacciones incompletas se hace automticamente en

caso de fallos del sistema.

Bases de datos 56

Transacciones (Cont.)
n En la mayora de SGBD, cada sentencia SQL que se ejecuta

correctamente se confirma (commit) automticamente.

H En este caso, cada transaccin debe consistir en una sola sentencia. H Normalmente se puede deshabilitar la confirmacin automtica, permitiendo transacciones multi-sentencia, pero cmo se hace esto es dependiente del SGBD. H Otra opcin, 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

relacin.
n Estas operaciones normalmente se utilizan como subconsultas

en la clusula from.
n Condicin del join define qu tuplas de las dos relaciones

coinciden, y qu atributos aparecern en el resustado del join.

n Tipo de join define como tratar aquellas tuplas de cada

relacin que no coinciden con ninguna tupla de la otra relacin (en base a la condicin de join.
Tipos de join inner join left outer join right outer join full outer join
Bases de datos

Condicin de join natural on <predicado> using (A1, A2, ..., An)

58

Relaciones unidas Datos para los ejemplos

n Relacin prestamo numero-prestamo nombre-sucursal L-170 L-230 L-260 Lugo Ourense Vigo cantidad 3000 4000 1700

n Relacin prestatario
nombre-cliente numero-prestamo Fernndez Surez Lpez L-170 L-230 L-155

n Nota: falta la informacin del prstatario del prstamo L-260 y la informacin del prstamo L-155
Bases de datos 59

Relaciones unidas - Ejemplos

n prestamo inner join prestatario on

[Link]-prestamo = [Link]-prestamo
numero-prestamo nombre-sucursal cantidad nombre-cliente numero-prestamo

L-170 L-230

Lugo Ourense

3000 4000

Fernndez Surez

L-170 L-230

n prestamo left outer join prestatario on [Link]-prestamo = [Link]-prestamo

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

L-170 L-230 L-260

Bases de datos

Lugo Ourense Vigo

3000 4000 1700

Fernndez Surez null

L-170 L-230 null

60

Relaciones unidas - Ejemplos

n prestamo natural inner join prestatario
numero-prestamo nombre-sucursal cantidad nombre-cliente

L-170 L-230

Lugo Ourense

3000 4000

Fernndez Surez

n prestamo natural right outer join prestatario

numero-prestamo nombre-sucursal cantidad nombre-cliente

L-170 L-230 L-155

Lugo Ourense null

3000 4000 null

Fernndez Surez Lpez

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 L-230 L-260 L-155

Lugo Ourense Vigo null

3000 4000 1700 null

Fernndez Surez null Lpez

n Encontrar todos los clientes que tengan o bien una cuenta o bien un prstamo (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 estndar SQL define la inclusin de SQL en diversos lengujes

de programacin como Pascal, PL/I, Fortran, C, y Cobol.

n EL lenguaje en el que se incluyen sentencias SQL se denomina

lenguaje anfitrin (host), y las estructuras SQL permitidas en el lenguaje anfitrin se denominan SQL embebido (embedded).
n La forma bsica 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 anfitrin, encontrar los nombres y ciudades con alguna cuenta que tenga ms 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 [Link]-cliente = [Link]-cliente and [Link]-cuenta = [Link]-cuenta and [Link] > :cantidad END-EXEC

Bases de datos

64

SQL embebido (Cont.)

n La sentencia open hace que se evale la consulta

EXEC SQL open c END-EXEC

n La sentencia fetch sita en variables del lenguaje anfitrin 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

comunicacin de SQL (SQLCA) se pone a 02000 para indicar que no hay ms datos
n La sentencia close hace que el SGBD elimine la relacin

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 posicin actual del cursor:

update cuenta set saldo = saldo + 100 where current of c

Bases de datos

66

SQL dinmico
n Permite a los programas construir y ejecutar sentencias SQL en

tiempo de ejecucin. n Ejemplo de uso de SQL dinmico 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 dinmico contiene un ?, que es un hueco para el valor que se proporciona cuando se ejecuta el programa SQL.

Bases de datos

67

ODBC
n Estndar Open DataBase Connectivity (ODBC)

H Estndar para programar comunicaciones (accesos) a un servidor de bases de datos desde aplicaciones. H API para
4 4 4

abrir una conexin con una base de datos, enviar sentencias, recibir resultados.

Bases de datos

68

ODBC (Cont.)
n Cada SGBD que soporta ODBC proporciona una librera "driver" que se n

n n

debe enlazar con el programa cliente. Cuando el programa cliente hace una llamada al API ODBC, el cdigo de la librera se comunica con el servidor para realizar la accin solicitada y obtener los resultados. El programa ODBC primero asigna un entorno SQL y, a continuacn, un manejador de conexin a base de datos. Abre una conexin a la base de datos utilizando SQLConnect(). SQLConnect toma como parmetros: H el manejador de la conexin, H el servidor a que conectar, H el identificador del usuario, H su password Tambin 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

Cdigo ODBC
n int ejemploODBC()

{ RETCODE error; HENV env; /* entorno */ HDBC conn; /* conexion a base de datos */ SQLAllocEnv(&env); SQLAllocConnect(env, &conn); SQLConnect(conn, [Link]", SQL_NTS, mramos", SQL_NTS, mramospass", SQL_NTS); { . Realizamos el trabajo } SQLDisconnect(conn); SQLFreeConnect(conn); SQLFreeEnv(env); }
Bases de datos 70

Cdigo ODBC (Cont.)

n Los programas envan 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

automticamente a las variables C correspondientes.

4 Argumentos do SQLBindCol()

Variable ODBC stmt, Posicin del atributo en el resultado de la consulta El tipo de conversin de SQL a C. La direccin de la variable. Para tipos de longitud variable, como cadenas de caracteres, La longitud mxima de la variable. Lugar para almacenar la longitud actual cuando se acceda a la tupla.
Bases de datos 71

Cdigo 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

Ms caractersticas 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 relacin de la base de datos.
n Por defecto, cada sentencia SQL se trata como una transaccin, es

decir, se confirma automticamente.

H Se puede desactivar la confirmacin automtica en una conexin 4 SQLSetConnectOption(conn, SQL_AUTOCOMMIT, 0)} H Y las transacciones de deben confirmar o anular entonces explcitamente 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 estndar.

H Core H Nivel 1: requiere soporte de consulta de metadatos H Nivel 2: requiere capacidad para enviar y obtener cadenas de valores de parmetros e informacin de catlogo ms detallada.
n El estndar CLI (SQL Call Level Interface) es similar al interfaz

ODBC, con pequeas diferencias.

Bases de datos

74

JDBC
n JDBC es una API Java para comunicarse con SGBD que

soportan SQL
n JDBC soporta diversas caractersticas para consultar y

actualizar datos y para obtener los resultados de consultas

n JDBC tambin soporta la obtencin 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 conexin H Crear un objeto sentencia H Ejecutar consultas utilizando el objeto Sentencia para enviar consultas y obtener resultados H Mecanismos de excepcin para gestionar errores

Bases de datos

75

Cdigo JDBC
public static void EjemploJDBC(String idusuario, String passwd)

{
try { [Link] ("[Link]"); Connection conn = [Link]( "jdbc:oracle:thin:@[Link]:bdbanco", idusuario, passwd); Statement stmt = [Link](); Realizar trabajo . [Link](); [Link](); } catch (SQLException sqle) { [Link](ExcepcinSQL : " + sqle); }

}
Bases de datos 76

Cdigo JDBC (Cont.)

n Actualizar la base de datos try { [Link]( "insert into cuentas values ('A-9732', Vigo', 1200)"); } catch (SQLException sqle) { [Link](No se pudo introducir la tupla. " + sqle); } n Ejecutar una consulta y extraer e imprimir los resultados ResultSet rset = [Link]( "select nombre_sucursal, avg(saldo) from cuenta group by nombre_sucursal"); while ([Link]()) { [Link]( [Link](nombre_sucursal") + " " + [Link](2)); }
Bases de datos 77

Detalles del cdigo JDBC

n Obtener campos del resultado:

H [Link](nombresucursal) y [Link](1) son equivalentes si nombresucursal es el primer argumento del resultado del select.
n Tratamiento de valores Null int a = [Link](a); if ([Link]()) [Link](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 = [Link]( insert into cuenta values(?,?,?)); [Link](1, "A-9732"); [Link](2, Vigo"); [Link](3, 1200); [Link](); [Link](1, "A-9733"); [Link]();

Bases de datos

79

Arquitecturas de la aplicacin
n La aplicaciones se pueden construir siguiendo una de las dos

arquitecturas siguientes:
H Modelo de dos capas
4

El programa de aplicacin en la mquina del usuario utiliza directamente JDBC/ODBC para comunicarse con la base de datos Los usuarios/programas ejecutndose en la mquina del usuario se comunica con una aplicacin del servidor. La aplicacin del servidor a su vez se comunica con la base de datos

H Modelo de tres capas

4

Bases de datos

80

Modelo de dos capas

n P.e. cdigo Java se ejecuta en la mquina 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 estn disponibles en la mquina cliente; se permiten todas las operaciones sobre la base de datos H Ms cdigo 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 Aplicaciones/HTTP

Servlets

JDBC

Servidor de BD

HTTP/Protocolo especfico de la aplicacin

Red

Cliente
Bases de datos

Cliente

Cliente
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 enva peticiones va http o un protocolo especfico de

la aplicacin
n La aplicacin o el servidor Web recibe la peticin n La peticin la gestiona un programa CGI program o servlets n La seguridad la gestiona la aplicacin en el servidor

H Mayor seguridad H Seguridad de grado fino

n Cliente simple, pero slo 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