Modelo Relacional de Datos - Universidad de Murciadis.um.es/~jfernand/0708/fbd/tema2.pdf · • Construir consultas de bases de datos empleando las sentencias del álgebra ... 3 Modelo

1

Modelo Relacional de Datos

Competencias específicas a adquirir (I)

• Describir la terminología y principios fundamentales del modelo de datos relacional formal.

• Describir los conceptos de integridad de entidad, integridad referencial y nulos en el modelo relacional.

• Construir consultas de bases de datos empleando las sentencias del álgebra relacional y del cálculo relacional de tuplas, así como del lenguaje estándar SQL-92

Tema 2. Modelo relacional de datos

SQL 92.

1

Modelo Relacional de Datos

Competencias específicas a adquirir (I)

• Construir, depurar y ejecutar sentencias SQL de obtención y modificación de información almacenada en una base de datos relacional.

• Construir y ejecutar sentencias SQL de definición de datos, es decir, de creación, alteración y eliminación de los elementos que componen un esquema de bases de datos relacional: esquema, tablas, vistas, restricciones, etc.

• Construir depurar y ejecutar programas para el acceso y gestión de la


• Construir, depurar y ejecutar programas para el acceso y gestión de la información almacenada en una base de datos (procedimientos, funciones, disparadores, SQL embebido, entornos de cuarta generación, etc

• Acceder a las bases de datos relacionales desde un lenguaje de programación

2

2

Modelo relacional de datos

ContenidosContenidosPresentación y orígenes del modelo relacionalEstructura de datos relacionalCaracterísticas generales de integridad de datosManipulación de datos: lenguajes relacionales

Á


Álgebra relacionalCálculo relacional de tuplasSQL-92

Programación con SQL3

Modelo relacional de datos

Bibliografía

• [D 2001] capítulos 2 y 9;

• [EN 2007] capítulos 5 a 9;

• [CB 2005] capítulos 3 a 7;


• [SKS 2002] capítulos 3 y 4;

• [ACPT 1999] capítulos 2 y 4;

• [DD 1996] capítulos 7 a 13.4

3

• Introducido por Codd, 1970

1 Presentación y orígenes del MR

• Es un Modelo de Datos Lógico - de Representación -(basado en registros)

• El modelo más usado en las aplicaciones comerciales de procesamiento de datos convencional

• Dividido en 3 partes:


Dividido en 3 partes:1. Estructura de Datos2. Integridad de Datos (características generales)3. Manipulación de Datos

5

Base de Datos = Conjunto de Relaciones

• Relación– Estructura de datos fundamental del modelo

2 Estructura de datos relacional

Estructura de datos fundamental del modelo– Tiene un nombre y representa una entidad genérica– Conjunto de tuplas

• Cada tupla representa una entidad concreta– Compuesta de atributos con nombre (y dominio)

• Cada atributo representa un atributo de la entidad

– Representada mediante una tabla con filas y columnas


• Modelo basado en Teoría matemática– Analogía entre “Relación” (concepto matemático) y “Tabla”– Teoría de Conjuntos y Lógica de Predicados de 1er orden» Sólida Base Formal

6

4


La relación PELICULA

--- --

Ciencia-ficción,Drama,Thriller,

Comedia... --- --

2002, 1997, 1999, 2001,

1994, 1972...

Italia,Argentina, España, EEUU, Francia,Japón..

--- --

omin

ios Títulos Nombres

Géneros Años PaísesTiempo

título director género rodaje nacionalidad duración

Amores Perros A. González Drama 2000 México 145The Matrix A. Wachowsky Ciencia-ficción 1999 EEUU 138Torrente S. Segura Comedia 1997 España 110

N i N Ló P li i 2001 E ñ 118tuplas

inal

idad

---- --

-

---- --

-

, , p ---- --

-

do


Nos miran N. López Policiaco 2001 España 118Amelie J. P. Jeunet Comedia 2001 Francia 122

Los lunes al sol F. León Drama 2002 España 117

card

i

gradoatributos

7

Términos básicos


Modelo Relacional Procesamiento de Ficheros

Formal SQL-92

Relación Tabla Fichero

Tupla Si la tupla t está en la relación R, entonces t∈R Fila Registro concreto

Atributo Debe tener un nombre único dentro de cada relación

cabecera deColumna

Nombre deCampo de registro

di lid d ó


Cardinalidad nº de tuplas en una relación =

Grado nº atributos en una relación =

Dominio colección de valores permitidos para ciertos atributos =

8

5

• Conjunto de valores atómicos del mismo tipo, donde toman su valor los atributos

Definiciones formales: DOMINIO


– La definición de dominios forma parte de la definición de la BD– Cada atributo definido sobre un ÚNICO dominio OBLIGATORIO– Si A, B representan un mismo concepto, A y B con mismo dominio– Dominio D puede contener valores no tomados por ningún atributo

{valores de A} ⊆ Dominio(A)


• Comparaciones Restringidas a Dominio– La comparación de dos atributos sólo tiene sentido si ambos toman

valores del mismo dominio– Si el SGBD soporta dominios, podrá detectar este tipo de errores

9

Una relación R, sobre conjunto de dominios D1, D2 ... Dn

se compone de dos partes:

Definiciones formales: RELACIÓN (1)


p p

• Esquema o CabeceraConjunto de pares Atributo:Dominio

{ (A1:D1), (A2:D2) ... (An:Dn) }– Cada Aj tiene asociado sólo un Dj

– Los Di no tienen por qué ser distintos entre sí


i

• Estado, Cuerpo o Instancia– Conjunto de tuplas que contiene en un instante concreto– tupla = conjunto de pares Atributo:Valor

{ { (A1:vi1), (A2:vi2) ... (An:vin) } }, donde i=1..m

10

6

Un esquema de relación:PELICULA (titulo:Titulos, duracion:Tiempo, director:Nombres, estreno:Fechas)



Un estado de la relación:{ { (titulo:Torrente), (duracion:110), (director:S.Segura), (estreno:1997) }

{ (titulo:The Matrix), (duracion:138), (director:A.Wachowski), (estreno:1999) }... }

• El estado de una relación es variable en el tiempo– nuevas tuplas, modificación o borrado de existentes


• El esquema no suele variarcostoso:

· reescritura de “miles” de tuplas· ¿valores de nuevos atributos para tuplas ya existentes?

– Suele incluir un conjunto de Reglas de Integridad (se verá)

11

• Propiedades de una Relación

1 No e i ten t plas epetidas



1. No existen tuplas repetidas

2. Las tuplas no están ordenadas

3. Los atributos no están ordenadosesquema = conjunto de pares Atributo:Dominio

4 Los valores de atributos son Atómicos

estado = conjunto matemático de tuplas


4. Los valores de atributos son Atómicosdominio = conjunto de valores atómicos

Intersección fila/columna = un solo valor (no lista de valores)Si R cumple esta propiedad, R está en 1FN

12

7

• FORMAS NORMALESR está en <determinada> FN si



R está en determinada FN si

cumple <cierto> conjunto de condiciones o restricciones

necesarias para estar bien diseñada

de acuerdo con el modelo relacional de datos.

• Toda relación ha de estar en 1FN (estructura de datos simple)

Tema 2. Modelo relacional de datos 13

• Relación vs. Tabla– Relación: Representación abstracta de un elemento de datos



Relación: Representación abstracta de un elemento de datos

– Tabla: Representación concreta de tal elemento abstracto

– Ventajas

Representación muy sencilla (tabla) del elemento abstracto básico (relación) del Modelo Relacional

á l d l d


Fácil de utilizar, entender, razonar...

– Inconveniente

Aparente orden entre filas y entre columnas de la tabla

14

8

• Percibida por usuarios como una colección de relaciones– de diversos grados (nº de atributos)

Definiciones formales: BD RELACIONAL (1)


g ( )– que varían con el tiempo (nº de tuplas, estado)

• Las relaciones (tablas) son la estructura lógica de la BD– Niveles externo y conceptual ANSI/X3/SPARC

• Toda BDR cumple el Principio de Información:Todo contenido de información de la BD está representadod ól f l lí it


de una y sólo una forma: como valores explícitos dentro de posiciones de columnas dentro de filas dentro de tablas

• Conexión lógica entre Relaciones (vínculo o interrelación)– Representada mediante valores– No existen punteros (visibles al usuario)

15

• En una BDR distinguimos...– Esquema de base de datos

Definiciones formales: BD RELACIONAL (2)


Esquema de base de datosDescripción de la base de datosConjunto de esquemas de relación

PELICULA ( titulo:Títulos, director:Nombres, género:Géneros, rodaje:Años, nacionalidad:Países, duración:Tiempo )

ACTOR ( nombre:Nombres, nombreArtistico: Nombres, agente:Nombres, cache:Dinero )

DIRECTOR ( b N b i lid d P í P i Tít l )


DIRECTOR ( nombre:Nombres, nacionalidad:Países, operaPrima:Títulos )...

– Estado o instancia de base de datosVisión del contenido de la base de datos en cierto instanteConjunto de estados de relación

16

9

• Qué es una Relación

Recopilando…

• Qué es un Dominio

• Cuáles son las propiedades de una Relación

• Diferencias entre Relación y Tabla


Diferencias entre Relación y Tabla

• Rol de las relaciones en la arquitectura ANSI/SPARC

17

• Todo estado de BD refleja la realidad– es un modelo de una porción del mundo real (minimundo)

3 Características generales de integridad de datos

• Algunas configuraciones de valores NO tienen SENTIDO– pues no representan ningún estado posible del minimundo

2 personas distintas con el mismo DNIUn empleado sin NSSUn alumno con -29 añosUna película sin director


Definición de la BD (esquema) necesita incluir

REGLAS DE INTEGRIDAD

18

10

• Informan al SGBD de restricciones del mundo realA í l SGBD it fi i d d t i ibl


Reglas de integridad

• Así, el SGBD evita configuraciones de datos imposibles• Aumentan la capacidad expresiva del modelo relacional• Cumplen que:

• Forman parte de la base de datos• Se cumplen para cualquier estado de la BD• No varían con el tiempo

• Son específicas de cada BD particular pero el

Tema 2. Modelo relacional de datosClaves Candidatas y Primarias

Claves Ajenas (o foráneas o externas)

• Son específicas de cada BD particular, pero elModelo Relacional incluye...

características generales de integridadimportantes y necesarias en toda BD

19

Sea R una relación R(A1:D1 , A2:D2 ,... An:Dn )


Superclave y Clave de una relación

• Una superclave de R es un subconjunto SK de atributos tal que cumple la restricción de Unicidad:

No existen dos tuplas distintas con la misma combinación de valores para SK

• Una clave de R es una superclave tal que cumple la restricción de Irreductibilidad:


restricción de Irreductibilidad:Ningún subconjunto de CK cumple la r. Unicidad

• Clave Simple (1 atributo) o Compuesta (varios atributos)

• Cada clave es una restricción de integridad20

11

• Claves como restricción de integridadCLIENTE (codCliente nombre ciudad telefono )

3 Características generales de integridad de datosSuperclave y Clave: Ejemplos

CLIENTE (codCliente, nombre, ciudad, telefono,...)¿Qué implicaciones tiene establecer como clave...a) CK = {codCliente, ciudad}b) CK = {codCliente} …?

• Varias claves en una relación«Relación para registrar las visitas de pacientes a sus médicos de familia. Un mismo

i t d i it édi i i dí


paciente puede visitar a su médico varias veces en un mismo día»VISITAMEDICA (nssPaciente, historial, fecha, hora, numVisita, medico, observ)Claves (VISITAMEDICA)={ {nssPaciente, numVisita}, {nssPaciente, fecha, hora},

{historial, numVisita}, {historial, fecha, hora} }

21

• Si R tiene varias claves Claves CandidatasClaves (ACTOR) = { {nombre} {nombreArtistico} }


Clave Candidata, Primaria y Alternativa

Claves (ACTOR) = { {nombre}, {nombreArtistico} }Claves (EMPLEADO) = { {dni}, {nombre, fechaNac}, {nss} }

• La Clave Primaria (Primary Key, PK ) es la clave candidata elegida para identificar las tuplas de RClave Primaria (ACTOR) = {nombreArtistico}Clave Primaria (EMPLEADO) = {nss}


• Las Claves Alternativas (Alternative Keys, AK) son el resto de claves candidatasClaves Alternativas (ACTOR) = {nombre}Claves Alternativas (EMPLEADO) = { {dni}, {nombre, fechaNac} }

22

12

• Conjunto de atributos FK de una relación R2, tal que:

3 Características generales deintegridad de datos

Clave Ajena (Externa o Foránea)

1. Existe otra relación R1 con clave primaria PK , y2. Cada valor de FK en R2 es idéntico al de PK en alguna tupla de R1

Conjunto de atributos de una relación que hace referencia a la clave primaria de otra relación (o la misma)

• PELICULA (título, género, duración, director, ...)DIRECTOR (nombre, nacionalidad, ...)


( )

• EMPLEADO (codEmp, nombre, jefe, nss, ...)

• LIBRO (título, isbn, autor, editorial, edición, año, ...)ESCRITOR (dni, nombre, ...)ARTICULO (título, tema, autor, revista, página, ...)

23

• Cada componente de una FK debe estar definido


Clave Ajena (Externa o Foránea) (2)

sobre el mismo dominio que el correspondiente atributo de la PK a la que referencia

PACIENTE (nss, nombre, dirección, ...)HISTORIAL (nss, especialidad, fechaApert, ...)VISITA (nss, especialidad, numVisita, fecha, ...)

• Clave Ajena Simple o Compuesta


• Clave Ajena Simple o Compuesta• El uso de Claves Ajenas facilita...

– Eliminación de la Redundancia: Integridad entre ficheros– Mecanismo del Modelo Relacional de datos para establecer

VÍNCULOS ENTRE RELACIONES

24

13

CUENTA



número saldo ...Cada cliente sólo puede tener

CLIENTE nombre dirección ciudad cuentaG í A G Ví 6 M i 200

200 35000505 40000821 50000...

Cada cliente sólo puede tener una cuenta a su nombre.

Una cuenta puede tener más de un cliente como titular.

Vínculo Cliente-


García, A Gran Vía, 6 Murcia 200López, B Ronda Norte, 3 Murcia 821Azorín, C Paseo Nuevo, 9 Valencia 505Pérez, C Plaza Mayor, 2 Valencia 505...

Cuenta

25

• Restricción de Integridad Referencial



Todo valor de una FK debe coincidircon un valor en la correspondiente PK

– La BD no debe contener claves ajenas sin correspondencia:

Si una tupla en una relación hace referencia a otra relación, debe referirse a una tupla existente en esa relación

ESCRITORARTICULO FK


• Puede existir algún valor de PK al que NO haga referencia ningún valor de la FK

– ESCRITOR que no haya escrito artículos: ninguna tupla de ARTICULOhará referencia a la tupla correspondiente a dicho escritor

ESCRITORARTICULO

26

14

• Diagrama ReferencialE ió d l i t i d Cl Aj


Clave Ajena (Externa o Foránea) (y 5)

– Expresión de la existencia de Claves Ajenas• Camino Referencial

ESCRITOR dni nombre ... editorial

LIBRO título isbn autor editorial ...

ARTICULO título tema autor revista pág

EDITORIAL nombre dirección ...


ARTICULO título tema autor revista pág ...

• Ciclo Referencial– Camino que empieza y acaba en la misma relación– Caso especial: Autorreferencia EMPLEADO codEmp ... jefe

EMPL codEmp ... dep DEPTO codDep ... dire

27

• Las operaciones que no satisfacen –violan– la Integridad R f i l d j l BD t d i t


Mantenimiento de la Integridad Referencial

Referencial, dejan la BD en un estado incorrectoEjemplo de un Hotel:

– ¿Qué pasaría si se eliminara la tupla (501, D, ...) en HABITACIÓN?– ¿Y si se eliminara la tupla (100, D, ...)?

– ¿Y si se anotara la ocupación de la habitación 900?

OCUPACIÓN codClie habit ...

CLI04 100

HABITACIÓN numHabit tipo ...

115 I


CLI04 100CLI02 420CLI05 115CLI10 100

115 I420 I100 D304 D405 I501 D

28

15

• ¿Cómo evita el SGBD esos estados incorrectos?


Mantenimiento de la Integridad Referencial (2)

El SGBD puede...

Rechazar toda operación que pueda provocar un estado ilegal, o

Aceptar (y ejecutar) tales operaciones, pero

realizar acciones que restauren la integridad de los datos

Di ñ d d l BD d ifi l SGBD


Diseñador de la BD puede especificar al SGBDAcciones de Mantenimientode la Integridad Referencial

para que la BD SIEMPRE alcance un estado final legal

29

R2 ⎯→ R1



Operación: Eliminar una tupla t de R1 que es referenciada por otras de R2

Ejemplo: Eliminar la tupla (100, D, ...) de HABITACIÓNAcciones posibles:

1. Rechazar la operación (acción por defecto) Sólo permite borrar t si ninguna otra tupla hace referencia a t


Sólo permite borrar t si ninguna otra tupla hace referencia a t2. Cascada. Propagar la eliminación

1º Borrar todas las tuplas de R2 que referencian a t2º Eliminar t

3. Establecer nulos – (* se verá después *)

30

16

R2 ⎯→ R1



Operación: Modificar el valor de una FK a un valor no existente en la PK de R1

Ejemplo: Modificar (CLI02, 420,...) a (CLI02, 900,...) en OCUPACIÓNAcción:

1. Rechazar la operación (SIEMPRE)


Intento de violación de la restricción de Integridad Referencial

31

Operación: Modificar el valor de la PK de una tupla t de R1 f i d t t l d R2



que es referenciada por otras tuplas de R2Ejemplo: Modificar la tupla (100, D,...) a (130, D,...) en HABITACIÓNAcciones posibles:

1. Rechazar la operación (acción por defecto) Sólo permite modificar la PK de t si ninguna tupla referencia a t

2. Cascada. Propagar la modificación


p g- Toda tupla de R2 que referencia a t seguirá haciendolo:

modificar su valor de FK al nuevo valor de la PK de t- Modificar el valor de la clave primaria de t

3. Establecer nulos – (* se verá después *)

32

17

R2 ⎯→ R1



Operación: Inserción de una tupla t en R2 cuyo valor de FK no se corresponde con ningún valor de la PK en ninguna tupla de R1

Ejemplo: Insertar una tupla (CLI03, 555, ...) en OCUPACIÓNAcciones posibles:

- Rechazar la operación (SIEMPRE)


ec a a a ope ac ó (S )

Intento de violación de la restricción de Integridad Referencial

33

• Encadenamiento de eliminaciones (análogo para Modificación)


Mantenimiento de la Integridad Referencial (y 7)

R3 R2 R1R2 → R1, Acción de Eliminación en CascadaR3 → R2, Acción de Eliminación XX- Eliminar una tupla de R1 eliminar tuplas de R2 que la referencian- Pero existen tuplas en R3 que referencian esas tuplas de R2...¿cómo afecta la Acción de Eliminación XX en esta operación?

Si X X = en CASCADA, no-problemo! eliminar esas tuplas de R3 Si X X = RECHAZAR La operación completa fallará

R3 → R2 → R1


• Las operaciones de actualización en una BD son siempre atómicas: se realiza “TODO o NADA”PROFESOR → ÁREA → DEPARTAMENTOASIGNATURA → TITULACIÓN → UNIVERSIDAD

34

18

• En el mundo real existe...– información perdida fechaNacimiento desconocida


Nulos

– información perdida fechaNacimiento desconocida– ausencia de información ¿tiene teléfono?– valores no aplicables a ciertos atributos fechJubilac a empleado activo

• Para representar estas situaciones en los sistemas de BD se utiliza el NULO (null)– Si una tupla tiene un atributo que contiene un nulo,

significa que el valor real de tal atributo es desconocido


– Es posible especificar si un atributo puede o no contener nulo

nulo no es un valor en sí mismo,sino un indicador de ausencia de información

No hay dos nulos iguales (num_telefono NULL ≠ edad NULL)

35

• Nulo y Claves Primarias


Implicaciones de los nulos en la integridad

Restricción de Integridad de Entidad:Ningún atributo componente de unaclave primaria puede contener nulo

EMPLEADO (codEmp, nss, nombre, telefono, depto, jefe...)¿Qué pasaría si codEmp pudiera contener NULO?

• Nulo y Claves Ajenas


• Nulo y Claves AjenasEl Modelo Relacional permite nulocomo valor de clave ajena

depto = null empleados no asignados a ningún departamentojefe = null empleados sin jefe

36

19

• Hemos de extender la definición de clave ajena


Implicaciones de los nulos en la integridad (2)

Sea R2 una relación. FK es una clave ajena en R2 si es un subconjunto de sus atributos tal que:

1. Existe otra relación R1 con clave primaria PK y2. En todo momento, cada valor de FK en R2

a) es NULO, ob) es idéntico a un valor de PK en alguna tupla de R1


• Restricción de Integridad ReferencialLa Base de Datos no debe contener valores no nulosde clave ajena sin correspondencia

37

• Hay que extender algunas acciones de mantenimiento de l I t id d R f i l R2 R1


Implicaciones de los nulos en la integridad (3)

la Integridad Referencial: R2 ⎯→ R1Operación: Eliminar una tupla t de R1 que es referenciada

por otras de R2Acciones posibles:

1. Rechazar la operación (acción por defecto) 2. Cascada. Propagar la eliminación


3. Establecer nulosSólo si la FK de R2 permite NULO- Toda tupla de R2 que referencia a t pasa a contener NULL en FK- Eliminar la tupla t

38

20

R2 ⎯→ R1


Implicaciones de los nulos en la integridad (y 4)

Operación: Modificar el valor de la PK de una tupla t de R1que es referenciada por otras tuplas de R2

Acciones posibles:1. Rechazar la operación (acción por defecto) 2. Cascada. Propagar la modificación

3. Establecer nulos


3. Establecer nulosSólo si la FK de R2 permite NULO

- Toda tupla de R2 que referencia a t pasa a contener NULL en FK- Modificar el valor de la PK de t

39

• Comprobar las claves candidatas (primaria y alternativas):No existen dos tuplas distintas con igual valor para una clave


Resumiendo, el SGBD se encarga de...

No existen dos tuplas distintas con igual valor para una claveDefinición de BD : indicar los Atributos Componentes de las Claves Candidatas

• Comprobar la restricción de Integridad de entidadNingún atributo componente de una clave primaria contiene nulo

Definición de BD : indicar los Atributos Componentes de la Clave Primaria• Comprobar la restricción de Integridad Referencial...

El valor de la clave ajena en cualquier tupla o es nulo o coincide con


El valor de la clave ajena en cualquier tupla, o es nulo, o coincide con un valor de clave primaria de alguna tupla en la relación referenciada

Definición de BD : indicar los Atributos Componentes de las Claves Ajenas• ... y mantenerla frente operaciones que puedan violar la integridad

Definición de BD : indicar Acciones de Mantenimiento de la Integridad Referencial

40

21

• Definir la restricción de integridad de entidad

Recopilando…

• Definir la restricción de integridad referencial

• Explicar el papel desempeñado por los valores nulos y sus implicaciones en la integridad


Dominio Definición del DominioCODPEL enteros(3)CODGUI enteros(3)CODDIR enteros(3)CODDIS enteros(2)CODACT enteros(4)CODAGE enteros(2)SEXOS { M, F }TEXTO cadena caracteres variable (500)PORCENT enteros (2)DINERO enteros(9)NIF cadena caracteres fija (12)TITULOS cadena caracteres variable (120)GENEROS {comedia,drama,terror,suspense,accion,romantica,gore,pulp,roadmovie}PAISES {españa,francia,gran_bretaña,eeuu,australia,alemania,la_india,argentina}AÑOS AÑO


AÑOS AÑOFECHAS FECHANOMBRES cadena caracteres variable (35)APELLIDOS cadena caracteres variable (80)DOMICILIOS cadena caracteres variable (50)TELEFONOS cadena caracteres variable (15)TIPO_PAPEL {protagonista, secundario, reparto, figuracion}

42

22

Esquema “PRODUCTORA”

PELICULA (codP:CODPEL, titulo:TITULOS, año:AÑO, genero:GENEROS, guion:CODGUI, director:CODDIR, directorFotog:CODDIR, distrib:CODDIS, nacio:PAISES, estreno:FECHA, numOscar:enteros(2), taquilla:DINERO)

DIRECTOR (codDir:CODDIR, nombre:NOMBRES, apellidos:APELLIDOS, nacio:PAISES, fechaNacim:FECHA operaPrima:CODPEL)fechaNacim:FECHA, operaPrima:CODPEL)

DIREC_FOTOG (codDF:CODDIR, nombre:NOMBRES, apellidos:APELLIDOS, nacionalidad:PAISES, fechaNacim:FECHA, ultTrabajo:CODPEL)

GUION (codG:CODGUI, titulo: TITULOS, resumen: TEXTO, nomAutorPpal:NOMBRES, fechaFin:FECHA, fechaEntrega:FECHA)

DISTRIBUIDORA (codDis:CODDIS, nombre:NOMBRES, cif:NIF, direccion:DOMICILIO, telefono:TELEFONOS, porcentaje:PORCENT)


ACTOR (codA:CODACT, nombre:NOMBRES, nomReal:NOMBRES, nacionalidad:PAISES, fechaNacim:FECHA, sexo:SEXOS, agencia:CODAGE, cache:DINERO)

AGENCIA (codAg:CODAGE, nombre:NOMBRES, direccion:DOMICILIO, telefono:TELEFONOS)

ACTUA_EN (actor:CODACT, film:CODPEL, papel:TIPO_PAPEL, paga:DINERO)

43

• Definida por Codd, 1972• Colección de operadores que toman relaciones como

operandos y devuelven relaciones como resultado

4 Manipulación de datos4.1. Álgebra Relacional

operandos y devuelven relaciones como resultado

– Operadores tradicionales sobre conjuntosunióninterseccióndiferenciaproducto cartesianoLos operandos son relaciones, y NO conjuntos arbitrarios


operaciones adaptadas a relaciones (tipo especial de conjuntos)

– Operadores relacionales especialesrestricciónproyecciónreunión ( join )división

44

23

El resultado de cualquier operación del álgebrarelacional es otra relación

Álgebra RelacionalClausura relacional

☺ la salida de una operación puede ser entrada (operando) de otra

Expresiones AnidadasSus operandos son otras expresiones del álgebra


(en lugar de nombres de relación)

45

• En matemáticas, A∪B = { e / e∈A y-o e∈B }• Relación = conjunto de tuplas

ibl h l ió d d l i R S

Álgebra RelacionalCompatibilidad de tipos (o con la unión)

es posible hacer la unión de dos relaciones R y S• R∪S = { t / t∈R y-o t∈S }

– Conjunto de todas las tuplas que están en R y/o en S– Sin embargo...

PELICULA ∪ DIRECTOR es un conjunto, pero no es una relación

Las relaciones deben ser homogéneas: no pueden


Las relaciones deben ser homogéneas: no pueden contener mezcla de tuplas de distintos tipos– Ha de mantenerse la Propiedad de Clausura:

el resultado de la operación DEBE ser una relación» Las relaciones de entrada deben ser de tipos compatibles

46

24

Sean R ( r1, r2,..., rn ), S ( s1, s2, ..., sn )• Relaciones R y S compatibles en tipo si tienen el

“ i ” d i

Álgebra RelacionalCompatibilidad de tipos (y 2)

“mismo” esquema, es decir:1. Igual número de atributos:

grado(R) = grado(S) = n2. Atributos correspondientes definidos sobre el mismo dominio:

dom(ri) = dom(si) ,, i = 1, 2, ..., n

Ejemplo DIRECTOR DIR FOTOG son de tipos compatibles


Ejemplo: DIRECTOR y DIR_FOTOG son de tipos compatibles

UNIÓN, INTERSECCIÓN, DIFERENCIA necesitan operandos compatibles en tipoPRODUCTO CARTESIANO no necesita compatibilidad de tipo en sus operandos

47

R∪S, con R y S compatibles en tipo, es una relación tal que:Esquema: el de R (o S)

Álgebra RelacionalUnión de relaciones

Estado: conjunto de tuplas que están en R, en S o en ambas

Las tuplas repetidas se eliminan (por definición)Ejemplo: DIRECTOR ∪ DIR_FOTOG

R∩S, con R y S compatibles en tipo, es una relación tal que:

Intersección de relaciones


, y p p , qEsquema: el de R (o S)

Estado: conjunto de tuplas que están a la vez en R y en S

Ejemplo: DIRECTOR ∩ DIR_FOTOG

48

25

R―S, con R y S compatibles en tipo, es una relación tal que:Esquema: el de R (o S)

á R S

Álgebra RelacionalDiferencia entre relaciones

Estado: conjunto de tuplas que están en R, pero NO en Soperación con «cierta direccionalidad», como la resta aritmética

Ejemplo: DIRECTOR ― DIR_FOTOG

• La propiedad de clausura relacional permite aplicar una operación tras otra

Secuencias de operaciones


operación tras otraSean R, S, T relaciones de tipos compatibles,

– Única expresión: expresiones anidadas R ∩ ( S ∪ T )– Varias expresiones: relaciones intermedias con nombre

A ← S ∪ TB ← R ∩ A

49

Álgebra Relacional

• Por defecto, los atributos de la relación resultado de una operación heredan los nombres de los del operando más

Renombramiento de atributos

operación heredan los nombres de los del operando más a la izquierda

DIR ← DIRECTOR ∪ DIR_FOTOGLos atributos de DIR tienen los mismos nombres que los de DIRECTOR

• Se puede indicar una lista con nuevos nombres para los atributos de la relación resultado:

DIR(codDir nomDir apeDir nacDir fechaNac pelic) ← DIRECTOR ∪ DIR FOTOG


DIR(codDir,nomDir,apeDir,nacDir,fechaNac,pelic) ← DIRECTOR ∪ DIR_FOTOG

50

26

• En matemáticas, A Χ B = { (a,b) / a∈A y b∈B }• Relación = conjunto de tuplas,

ibl l d t t i t l i R S

Álgebra RelacionalProducto Cartesiano entre relaciones

es posible el producto cartesiano entre relaciones R y S

• R Χ S = { (tR,tS) / tR∈R y tS∈S }– Conjunto de pares ordenados de tuplas de R y S– Pero ha de conservarse la Propiedad de Clausura:

» El resultado debe ser un conjunto de tuplas (no de pares de)


Producto Cartesiano Ampliado, pues cada par ordenado es sustituido por la tupla resultantede la combinación de las dos tuplas origen

51

• R Χ S, con R y S cualesquiera, es una relación tal que:Esquema: combinación (unión) de los esquemas de R y S

Álgebra RelacionalProducto Cartesiano entre relaciones (2)

Estado: conjunto de todas las tuplas formadas por las posibles

combinaciones de cada tupla de R con cada tupla de S

Ejemplo: PELICULA Χ DIRECTORObtiene un conjunto de tuplas tales que cada una es la combinación de una tupla de

PELICULA y otra de DIRECTOR


• Operación sin demasiada importancia práctica– No se tiene más información a la salida que a la entrada

– pero es necesaria para definir la operación REUNIÓN (JOIN)

52

27

• El esquema de la relación resultante de R Χ Sdebe estar bien formado (nombres de atributos únicos)

Álgebra RelacionalProducto Cartesiano entre relaciones (y 3)

• Si R y S tienen atributos con igual nombre, R Χ S tendría ¡dos atributos nombrados igual! ko!

ACTOR Χ AGENCIA “colisión” de nombres en atributo “nombre”• Soluciones posibles:

1. Renombrar atributos de una relación, antes del productoAGENCIA_2(codAge, nomAge, direccion, telefono) ← AGENCIA


RESULTADO ← ACTOR Χ AGENCIA_2

2. Prefijar atributos con el nombre de su tabla, en la tabla resultadoRESULTADO(codA, ACTOR.nombre, nomreal,..., codAg, AGENCIA.nombre, ...) ← ACTOR Χ AGENCIA

53

R, S, T relaciones de tipos compatibles

• Asociativa

Álgebra RelacionalPropiedades de los operadores relacionales

( R ∪ S ) ∪ T ≡ R ∪ ( S ∪ T ) ≡ R ∪ S ∪ T( R ∩ S ) ∩ T ≡ R ∩ ( S ∩ T ) ≡ R ∩ S ∩ T( R Χ S ) Χ T ≡ R Χ ( S Χ T) ≡ R Χ S Χ T

• ConmutativaR ∪ S ≡ S ∪ RR ∩ S ≡ S ∩ R


R Χ S ≡ S Χ R

• La diferencia no cumple ninguna de estas propiedades

El producto cartesiano “normal” no las cumple, pero sí el “ampliado”

54

28

• Obtener un subconjunto de las tuplas de una relaciónpara las cuales se satisface una condición de selección

Álgebra RelacionalRestricción de una relación σ

σ<condición> (<relación>)

• Resultado: Relación (conjunto de tuplas) con atributos de <relación>• <condición> es una expresión booleana…

– Especificada en términos de atributos de <relación>– Compuesta por una o más cláusulas, del tipo:


p p , p<nomAtrib> <opComp> <cte> o bien <nomAtrib> <opComp> <nomAtrib>

• <opComp> operador de comparación ∈ {=, <, ≤, >, ≥, ≠}• <cte> valor constante ∈ dominio del atributo <nomAtrib>• Cláusulas conectadas por operadores booleanos AND, OR, NOT

55

• Ejemplos:* Tuplas de actores representados por la agencia número 2

Álgebra RelacionalRestricción de una relación (2)

σagencia=2 (ACTOR)

* Actores cuyo caché rebasa los 30.000€σcache>30000 (ACTOR)

* Actores representados por la agencia número 2 cuyo cache no llega


Actores representados por la agencia número 2, cuyo cache no llega a los 22.000€, o bien por la agencia 4 y con caché superior a 32.000€σ(agencia=2 AND cache<25000) OR (agencia=4 AND cache>35000) (ACTOR)

56

29

• Mecanismo de selección del sistema– Aplica <condición> a cada tupla individual de <relación>, sustituyendo

d t ib t l l t l

Álgebra RelacionalRestricción de una relación (3)

cada atributo por su valor en la tupla– Si <condición> es TRUE, la tupla se selecciona para el resultado

• Operador Restricción: Unario– Sólo se aplica a UNA relación

Nunca puede seleccionar tuplas de más de una relación– Se aplica a UNA sola tupla a la vez


<condición> nunca se refiere a más de una tupla• Grado(Relación Resultado) = Grado(Relación Origen)

– Tienen los mismos atributos

• Nº Tuplas(Relación Resultado) ≤ Nº Tuplas(Relación Origen)

57

• La operación restricción es conmutativa

σ (σ (R) )≡ σ (σ (R) )

Álgebra RelacionalRestricción de una relación (y 4)

σcond1(σ cond2 (R) )≡ σcond2(σcond1 (R) )Esto permite …

Secuencia de restricciones (selecciones) en cualquier orden Combinación de una secuencia de restricciones en una

única restricción con una condición conjuntiva:


σcond1(σcond2(...(σcondn(R))...))≡σcond1 AND cond2 AND...AND condn(R)

58

30

• Sólo interesan algunos atributos de una relación• Se proyecta la relación sobre esos atributos

R t i ió P ió

Álgebra RelacionalProyección de una relación ∏

• Restricción vs. Proyección :– σ selecciona algunas tuplas de la relación y desecha otras– ∏ selecciona ciertos atributos y desecha los demás

∏<listAtrib>(<relación>)• Resultado: Relación (conjunto de tuplas) cuyos atributos son

ó


sólo los de <listAtrib> y en ese orden• <listAtrib> lista de nombres de atributos de <relación>

* Obtener el código, nombre y el caché de todos los actores∏codA, nombre, cache(ACTOR)

59

• Si <listAtrib> no contiene atributos clave ¡tuplas repetidas!

* Obt l i l i lid d d t d l t

Álgebra RelacionalProyección de una relación (2)

* Obtener la agencia y la nacionalidad de todos los actores

∏agencia, nacionalidad(ACTOR)» Eliminación implícita de duplicados

– Resultado ≡ relación válida


• Grado(Relación Resultado) = Nº atributos(<listAtrib>)

• Nº Tuplas(Relación Resultado) ≤ Nº Tuplas(Relación Origen)y es igual (=) si <listAtrib> contiene una clave candidata

60

31

• La operación proyección no es conmutativa

∏ (∏ (R) ) ∏ (∏ (R) )

Álgebra RelacionalProyección de una relación (y 3)

∏lista1(∏ lista2 (R) )≠ ∏lista2 (∏lista1 (R) )• Además, siempre que lista1 ⊆ lista2, entonces...

∏lista1(∏ lista2 (R) )= ∏lista1 (R)


Álgebra RelacionalEjercicios


32

• Combina las tuplas relacionadas de dos relacionesen una sola tupla

Álgebra RelacionalReunión o Join entre dos relaciones

• Permite procesar vínculos entre relaciones

* Datos de películas junto con los de su director correspondiente– Es necesario combinar cada tupla de PELÍCULA, p, con la tupla

DIRECTOR, d, tal que el valor de codDir en d coincida con el de director en p


– Se consigue aplicando la operación REUNIÓN a las dos relaciones

R1 ← PELICULA director=codDir DIRECTOR

63

PELICULA ( codP, título, año, genero, guión, director, directorFotog, distrib, nacio, estreno, numOscar, taquilla )

DIRECTOR ( codDir, nombre, apellido, nacio, fechaNacim, óperaPrima )

Álgebra RelacionalReunión o Join entre dos relaciones (2)

* Títulos de películas junto con nombre y apellido de su director– Se consigue aplicando la operación REUNIÓN a las dos relaciones

– Y proyectando el resultado sobre los atributos requeridos

R2←∏titulo,nombre,apellido (PELICULA director=codDir DIRECTOR)


R2 titulo nombre apellidoLa caja 507 Enrique UrbizuMensaka Salvador Gª RuizEl viaje de Carol Imanol UribeAirbag Juanma Bajo Ulloa

64

33

• Forma General para relaciones A(a1, a2, ... an) y B(b1, b2, ... bm):

A < di ió d ió >B


A <condición de reunión>B

• Resultado: Relación con n+m atributos (a1, a2, ... an, b1, b2, ... bm)Esquema: unión de las cabeceras de A y BEstado: conjunto de tuplas, una por cada combinación de tuplas

(una de A y otra de B) que satisface <condición de reunión>


• Reunión vs. Producto Cartesiano– En el Producto Cartesiano aparecen todas las combinaciones

posibles de tuplas de A y de B

65

• <condición de reunión>– Expresión booleana especificada en términos de atributos de A y B


– Evaluada para cada combinación (par) de tuplas:Si la cumplen, forman una nueva tupla de la relación resultado

– Es de la forma:

<condición> AND <condición> AND... AND <condición>donde:

<condición> tiene la forma ai θ bj (condición de reunión general), y


<condición> tiene la forma ai θ bj (condición de reunión general), y · ai es un atributo de A; bj es un atributo de B,· Dominio(ai) = Dominio(bj),· θ (theta) cumple que θ ∈ {=, <, ≤, >, ≥, ≠}

• Reunión con condición de reunión general ≡ REUNIÓN THETA66

34

• La reunión más común es la que implica comparación de igualdad ( θ ≡ = ) EQUI-REUNIÓN (o REUNIÓN, a secas)

* A t i l t


* Actores y agencias que los representanACTOR agencia=codAg AGENCIA

• Problema: colisión de nombres de atributos– Existen atributos nombrados igual en ACTOR y AGENCIA– Resultado con varios atributos de igual nombre ko!– Dos soluciones alternativas posibles:


1. Previo renombramiento de atributos de una relaciónAGENC(codAg, nomAg, dirAg, tel) ← AGENCIAR←∏nombre, nomAg (ACTOR agencia=codAgAGENC)

2. Prefijar atributos con el nombre de su tablaR←∏ACTOR.nombre,AGENCIA.nombre(ACTOR agencia=codAgAGENCIA)

67

• Las tuplas cuyos atributos de reunión son nulos,NO aparecen en la relación resultado


– Los actores que se auto-representan tienen NULL en atributo agencia– Sus tuplas no aparecen en ACTOR agencia=codAg AGENC

• Las tuplas de una relación que no encuentran correspondencia en la otra, tampoco aparecen en la relación resultado


relación resultado– Los actores que no han actuado en ninguna película, no aparecen

en ninguna tupla de la tabla ACTUA_EN– Sus tuplas no aparecen en ACTOR codA=actor ACTUA_EN

68

35

• En general, sea A con nA tuplas y B con nB tuplas, entonces

R ← A <condición de reunión> B cumple que 0 ≤ nR ≤ nA *nB

Álgebra RelacionalReunión o Join entre dos relaciones (y 7)

<condición de reunión> p q R A B

• Si ninguna combinación de tuplas de A y B cumple la <condición de reunión>, entonces– Relación Resultado = Relación vacía (cero tuplas)

• Si NO se especifica <condición de reunión> entonces


• Si NO se especifica <condición de reunión>, entonces– la <condición de reunión> es TRUE para todas las tuplas, y

– ≡ Χ (REUNIÓN ≡ PROD. CARTESIANO ≡ REUNIÓN CRUZADA)

69

A B• Caso particular de reunión, quizá el más importante

Álgebra RelacionalReunión natural entre relaciones

• No «necesita» especificar condición de reunión, pues...• ... iguala todos los pares de atributos con igual

nombre en A y B– Es una EQUI-REUNIÓN + eliminación de atributos superfluos

Sólo conserva un atributo de reunión– La definición estándar de reunión natural exige que los


La definición estándar de reunión natural exige que los atributos de reunión deben tener nombre idéntico en ambas relaciones operando

– Si no es así, aplicar antes un renombramiento de atributos– deben tener el mismo dominio

70

36

R(a, b, c) S(b, d)

Álgebra RelacionalReunión natural entre relaciones (2)

S b d3 -41

R a b c10 1 10020 3 100

T1 ← R R.b=S.b S, tiene el esquema T1 ( a, R.b, c, S.b, d )

1 -5

T1 a R.b c S.b d10 1 100 1 -520 3 100 3 4

20 3 10030 5 300


T2 ← R S, tiene el esquema T2 ( a, b, c, d )

20 3 100 3 -4

T2 a b c d10 1 100 -520 3 100 -4

71

• Ejemplos:1. Título de todas las películas junto con el título y resumen de su guión

GUIO(guion, titGuion, resumen, nomAutorPpal, fechaFin, fechaEntrega) ← GUION

Álgebra RelacionalReunión natural entre relaciones (y 3)

(g , , , p , , g )

RESUMEN ←∏titulo, titGuion, resumen (PELICULA GUIO)

2. Títulos de películas junto con el nombre y apellidos de su directorDIREC(director, nombre, apellidos, nacio, fechaNacim, operaPrima) ← DIRECTOR

PELI_DIRE ←∏titulo, nombre, apellidos (PELICULA DIREC)


3. Nombre de actores y de las agencias que los representanAGENC(agencia, nomAg, direccion, telefono) ← AGENCIA

ACT_AGEN ←∏nombre, nomAg (ACTOR AGENC)

¿A qué se debe el renombramiento en cada caso?

72

37

Sean las relaciones A(a1, a2, ... an, b1, b2, ... bm) y B(b1, b2, ...bm)• A÷B es una relación tal que:

Álgebra RelacionalDivisión entre relaciones ÷

qEsquema: Relación con los atributos no comunes R( a1, a2, ... an )Estado: Conjunto de tuplas { (ai1, ai2, ... ain) } tal que existe

en A una tupla (ai1, ai2, ... ain, bj1, bj2, ... bjm) paraTODAS las tuplas (bj1, bj2, ... bjm ) de B

• Poco común Útil para consultas especiales ocasionales


• Poco común. Útil para consultas especiales ocasionalesNombres de los actores que trabajan en todas las películas dirigidas por los

hermanos Cohen• Para que una tupla t aparezca en el resultado, los valores

de t deben aparecer en A en combinación con todas las tuplas de B

73

A a b y1 x1

B b x1

Álgebra RelacionalDivisión entre relaciones (y 2)

y1, y4 aparecen en A en combinación con las 3 tuplas de B, por eso están en el

y1 x1 y1 x2 y1 x3 y1 x4 y2 x1 y2 x3 y3 x2

3 3

x2 x3

R a y1 y4


p , presultado R = A ÷ B

El resto de valores de y en A, no aparecen con todas las tuplas de B y no son seleccionadas: y2 no aparece con x2, e y3 no aparece con x1

y3 x3 y3 x4 y4 x1 y4 x2 y4 x3

74

38

• Algunas consultas comunes no pueden expresarse con las operaciones estándar del Álgebra Relacional

A li ió d d i i di i l

Álgebra RelacionalOtras operaciones del Álgebra Relacional

– Ampliación de su poder expresivo con operaciones adicionales– Incluidas en la mayoría de los lenguajes de consulta relacionales

comerciales

• Funciones de agregados– Funciones matemáticas de agregados sobre colecciones de valores de

la base de datos


Valor medio del caché de todos los actoresNúmero de películas (almacenadas en la BD)Máximo porcentaje de comisión de las distribuidoras de películasMínima recaudación en taquillaCantidad total pagada a los actores de cierta película

75

• Funciones aplicadas a un conjunto de tuplas– SUMA– PROMEDIO

Álgebra RelacionalFunciones de agregados

PROMEDIO– MÁXIMO– MÍNIMO– CUENTA (número de tuplas en una relación)

• Agrupación de tuplas según valor de ciertos atributos– Puede aplicarse una función agregada a cada grupo por separado* Media del caché de los actores agrupados por agencias ¿Solución?


g p p gAgrupar actores según su agencia representante (valor de atributo agencia)

» Cada grupo incluye tuplas de actores representados por la misma agenciaCálculo del caché medio de cada grupo (función PROMEDIO)

• El resultado es una relación R(agencia, PROMEDIO_caché)

76

39

Álgebra RelacionalFunciones de agregados (2)

ACTORa9AG1 AG8

R PROMEDIO_cacheMedia del cache de a1... a10

a1

a10

a2

a3a4

a9a5

a8a7

a6AG2

AG3

R agencia PROMEDIO cache


R agencia PROMEDIO_cacheAG8 Media del cache de a9 y a2AG3 Media del cache de a5, a6 y a3AG1 Media del cache de a7, a1 y a4AG2 Media del cache de a8 y a10

77

<atributos de agrupación> F <lista funciones>(<relación>)


• <atributos de agrupación> – Lista de nombres de atributos de <relación>– Indican atributos con los que construir los grupos– Puede estar vacía la relación es un (único) grupo

• <lista funciones> – Lista de pares <función> <atributo>

donde <función> ∈ {SUMA, PROMEDIO, MÁXIMO, MÍNIMO, CUENTA}


y <atributo> es uno de los atributos de <relación>• Resultado: una relación R, tal que

Esquema: atributos de <atributos de agrupación> +un atributo por cada elemento de <lista funciones>

Cuerpo: conjunto de tuplas tal que existe una por cada grupo

78

40

• Ejemplos:1. Códigos de Películas, número de actores en cada película y su paga mediaR(codpeli numActores pagaMedia)← F (ACTUA EN)


R(codpeli, numActores, pagaMedia)← film F CUENTA actor,PROMEDIO paga(ACTUA_EN)2. Códigos de agencias, número de actores en cada agencia y caché medioR(codAg, numActores, cacheMedio)←agencia F CUENTA codA,PROMEDIO cache(ACTOR)

• Si no se indican nombres para los atributos de la relación resultado R, dicha relación incluirá...– un atributo por cada atributo incluido en <atributos de agrupación>,

con el mismo nombre y


con el mismo nombre, y– un atributo por cada función incluida en <lista funciones>,

denominado FUNCIÓN_atributoLos esquemas de las relaciones resultado de los ejemplos anteriores serían:1. R(film,CUENTA_actor, PROMEDIO_paga)2. R(agencia, CUENTA_codA, PROMEDIO_cache)

79

• Si no se especifican atributos de agrupación– Toda la relación es un único grupo

L f i li t d l t l

Álgebra RelacionalFunciones de agregados (y 5)

– Las funciones se aplican a todas las tuplas– La relación resultado tendrá una sola tupla

* Número de películas y recaudación mediaF CUENTA codP,PROMEDIO taquilla(PELICULA)

• El resultado de aplicar una función agregada siempre es


El resultado de aplicar una función agregada siempre es una relación, no un nº escalar, aunque tenga un único valor* Recaudación máxima obtenidaF MÁXIMO taquilla(PELICULA)

R MAXIMO_taquilla232.850

80

41

• No pueden expresarse en el Álgebra Relacional• Se aplican a una referencia recursiva entre tuplas del mismo

ti ( l d j f l l ió EMPLEADO)

Álgebra RelacionalOperaciones de cierre recursivo

tipo (empleado y jefe en la relación EMPLEADO)

* Códigos de los empleados que tienen como superior a “e”, en todos los niveles

_________________Nivel 1

e

e11 e12 ... e1n


_________________Nivel 2

_________________Nivel 3

etc.

e21... e2 m ... ... ... e2p

e31... e3q ... e3r ... ... ... e3t...

81

• En Álgebra Relacional es sencillo especificar empleados cuyo jefe es “e” en cierto nivel conocido, pero no en todos los niveles

Álgebra RelacionalOperaciones de cierre recursivo (y 2)

los niveles

Ejemplo para el nivel 2: código de los empleados cuyo jefe directo es “e” o bien su jefe es un empleado cuyo jefe es “e”

EMP_JEF(codE, codJ) ←∏codemp, codjefe (EMPLEADO)

EMP 1(cod) ←∏ dE (σ dJ “ ” (EMP JEF)) Empleados de nivel 1


EMP_1(cod) ←∏codE (σcodJ=“e” (EMP_JEF)) Empleados de nivel 1

EMP_2(cod) ←∏codE (EMP_JEF codJ=cod (EMP_1)) Empleados de nivel 2

RESULTADO ← EMP_1 ∪ EMP_2

82

42

• Extensión de la operación REUNIÓN• Permiten conservar todas las tuplas en A o B o ambas,

Álgebra RelacionalReunión externa (Outer-join) entre relaciones

aunque...– No tengan tuplas coincidentes– Contengan nulos en los atributos de reunión

* Nombres de actores y de sus agencias representantes, si tienenAGEN(codAg, nomAg, direccion,telefono) ← AGENCIA


AGEN(codAg, nomAg, direccion,telefono) ← AGENCIA

TEMP ← (ACTOR agencia=codAg AGEN)

RESULTADO ←∏nombre, nomAg (TEMP)

83

• REUNIÓN EXTERNA IZQUIERDA R = A B– Conserva en R todas las tuplas de A

Si no encuentra una tupla coincidente en B cada atributo de R

Álgebra RelacionalReunión externa entre relaciones (2)

– Si no encuentra una tupla coincidente en B, cada atributo de R(correspondiente a B) es NULO

• REUNIÓN EXTERNA DERECHA R = A B– Conserva en R todas las tuplas de B– Si no encuentra una tupla coincidente en A, cada atributo de R

(correspondiente a A) es NULO


• REUNIÓN EXTERNA COMPLETA R = A B– Conserva en R todas las tuplas de A y de B– Cuando no encuentra tuplas coincidentes, rellena con NULO

84

43

Álgebra RelacionalReunión externa entre relaciones (3)

ACTOR nomAc ... agencia ...Carmelo Gómez A23M í P j lt A03

AGENCIA codAg nomAg ...A10 AgeRepA30 R A t

* Nombres de agencias y de los actores a los que representan, incluyendo... 1.- las agencias que no representan a ningún actor

R1= πnomAc, nomAg (ACTOR agencia=codAg AGENCIA)

María Pujalte A03Pere Ponce A10Javier Bardem NULL

A30 ReprActorsA03 ActorsMngrA23 ARA


2.- los actores que no tienen agencia de representaciónR2 = πnomAc, nomAg ( ACTOR agencia=codAg AGENCIA

3.- tanto las agencias que no representan a ningún actor, como los actores que no tienen agencia

R3 = πnomAc, nomAg ( ACTOR agencia=codAg AGENCIA

85

Álgebra RelacionalReunión externa entre relaciones (y 4)

ACTOR AGENCIA nomAc nomAgC l Gó ARACarmelo Gómez ARAMaría Pujalte ActorsMngrPere Ponce AgeRepNULL ReprActors

ACTOR AGENCIA nomAc nomAgCarmelo Gómez ARA

ACTOR AGENCIAnomAc nomAg

Carmelo Gómez ARAMaría Pujalte ActorsMngr


María Pujalte ActorsMngrPere Ponce AgeRepJavier Bardem NULL

Pere Ponce AgeRepJavier Bardem NULLNULL ReprActors

86

44

Álgebra RelacionalEjercicios II


• Lenguaje formal para BD Relacionales• Basado en Cálculo de Predicados de Primer Orden (rama

de Lógica Matemática)

Manipulación de datosCálculo Relacional

de Lógica Matemática)

Cálculo Relacional vs. Álgebra Relacional- Expresiones Declarativas

(lenguaje no procedimental) - Secuencias de Operaciones

» No se indica CÓMO evaluar la consulta sino QUÉ se desea

» Aunque se anidan para formar una sola expresión, siempre se

d lí d


consulta, sino QUÉ se desea obtener

» Describe la información deseada sin dar un procedimiento específico para obtenerla

indica explícitamente cierto ordende las operaciones

» Estrategia parcial de evaluación de la consulta (≈ lenguaje procedimental de alto nivel )

88

45

• Poder expresivo idéntico de álgebra y cálculo relacionalesCualquier obtención de datos especificada en el Álgebra Relacional puede expresarse en el Cálculo Relacional (restringido a expresiones seguras) y viceversa

Cálculo Relacional

e p es o es segu as) y ce e sa

• Definición: Lenguaje Relacionalmente CompletoLenguaje en el que es posible expresar cualquier consulta que pueda especificarse en el Cálculo Relacional– Cálculo Relacional como medida del poder selectivo de lenguajes

relacionales


• Formas de adaptar el Cálculo de Predicados de 1er Ordenpara crear un Lenguaje de Consultas para BDR: Cálculo Relacional de Tuplas (CRT) –– Codd, 1972

Cálculo Relacional de Dominios –– Lacroix y Pirotte, 1977

89

• CRT basado en la especificación de variables de tupla• Toda variable de tupla «abarca» o recorre una relación

Cálculo RelacionalExpresiones de consulta

puede tomar como valor cualquier tupla de esa relación

{ t | COND(t) }• Resultado: conjunto de tuplas t que satisfacen la condición COND(t)• COND(t): expresión condicional en la que interviene la var. de tupla t

* A t hé b l 2 000€


* Actores cuyo caché rebasa los 2.000€{ t | ACTOR(t) and t.cache>2000 }

– ACTOR(t) indica que ACTOR es la Relación de Intervalo que t recorre– t.cache hace referencia al atributo caché de la variable de tupla t

90

46

• Obtención de algunos atributos de las tuplas seleccionadas{ <lista atributos> | COND(t) }

Cálculo RelacionalExpresiones de consulta (y 2)

• Ejemplos:

* Nombre y nacionalidad de los actores cuyo caché rebasa los 2.000€{ t.nombre, t.nacionalidad | ACTOR(t) and t.cache>5000 }


* Fecha de nacimiento y nombre real del actor “Javier Bardem”{ t.fechaNacim, t.nombreReal | ACTOR(t) and t.nombre = “Javier Bardem” }

91

• Expresión General

{ t1.a1, t2.a2, ... , tn.ap | COND(t1, t2, ..., tn, tn+1,tn+2,...tn+m) }

Cálculo RelacionalExpresión del CRT: punto de vista formal

{ t1.a1, t2.a2, ... , tn.ap | COND(t1, t2, ..., tn, tn+1,tn+2,...tn+m) }

donde:

– t1, t2, ..., tn, tn+1, tn+2, ... tn+m son variables de tupla

– ai es un atributo de la relación que tj recorre

– COND(..): fórmula (bien formada) del Cálculo Relacional de Tuplas


( ) ( ) p

constituida por átomos del Cálculo de Predicados

92

47

• Átomos– R(ti) el intervalo de la variable de tupla ti es la relación R

t t b

Cálculo RelacionalExpresión del CRT: punto de vista formal (2)

– ti.a op tj.b ,, op ∈ { =, ≠, <, ≤, >, ≥ }ti y tj variables de tuplaa atributo de la relación que ti abarcab atributo de la relación que tj abarca

– ti.a op c ,, c op tj.b ,, op ∈ { =, ≠, <, ≤, >, ≥ }ti y tj variables de tuplaa t ib t d l l ió t b


a atributo de la relación que ti abarcab atributo de la relación que tj abarcac valor constante

Los átomos están ligados mediante operadores and, or, not y →

93

• Valor lógico de un átomoEvaluación del átomo para una combinación específica de tuplas

TRUE FALSE


valor TRUE o FALSE– R(ti)

TRUE si se asigna una tupla de R a tiSi no, es FALSE

– ti.a op tj.b ,, ti.a op c ,, c op tj.bTRUE si ti y tj se asignan a tuplas tales que los atributos


TRUE si ti y tj se asignan a tuplas tales que los atributos especificados (a y b) satisfacen la condición

Si no es así, será FALSE

94

48

• Fórmula bien formada (fbf)– Definición recursiva


D1. Todo átomo es una fórmula bien formadaR(ti) ,, ti.a op tj.b ,, ti.a op c ,, c op tj.b

D2. Si F1 y F2 son fbf, también lo son...(F1 and F2), (F1 or F2), not(F1), not(F2) y (F1 → F2)y los valores lógicos de estas fórmulas


y los valores lógicos de estas fórmulas se derivan de F1 y F2, según la Lógica Booleana

Nota: (F1 → F2) ≡ (not(F1) or F2)

continuará...

95

• Cuantificadores– Universal ∀ (∀t) (BANCO(t) and not(t.ciudad = ‘Londres’))


– Existencial ∃ (∃t) (BANCO(t) and t.ciudad = ‘Amsterdam’)

• Variable de tupla libre y ligada en una fbfinformal

t está ligada si está cuantificada ( aparece en cláusulas (∀t) o (∃t) )si no está libre


si no, está libre

96

49

• Variable de tupla libre y ligada en una fbfformal

– Si F ≡ átomo cualquier ocurrencia de una variable de tupla t


– Si F ≡ átomo, cualquier ocurrencia de una variable de tupla t, está libre

– En (F1 and F2), (F1 or F2), not(F1), not(F2) y (F1 → F2),una ocurrencia de t está libre o ligada según lo esté en F1 o F2

– Toda ocurrencia libre de t en F está ligada en F’, si F’=(∃t)F o bien F’=(∀t)Fy t estará ligada al cuantificador especificado en F’


F1 : d.nombre = “Carmelo Gómez”F2 : (∃t) (d.agencia = t.codAg)

La variable de tupla d está libre en F1 y en F2t está ligada al cuantificador ∃ en F2

97

• Fórmula bien formada (continuación)

( )F


D3. Si F es una fbf, también lo es (∃t)F, donde t es una variable de tupla

(∃t)F es TRUE si F es TRUE para al menos una tupla asignada a ocurrencias libres de t en Fde lo contrario es FALSE


D4. Si F es una fbf, también lo es (∀t)F, donde t es una variable de tupla

(∀t)F es TRUE si F es TRUE para toda tupla (en el universo)asignada a ocurrencias libres de t en Fde lo contrario es FALSE

98

50

• Fórmula bien formada (continuación)

Si l fó l d (t d i bl li d tifi d )


– Si la fórmula es cerrada (toda variable ligada a cuantificadores), entonces representa una expresión que será TRUE o FALSE

F3 : (∃a) (ACTOR(a) and a.nombre = “Javier Cámara”)F4 : (∀p) (PELICULA(p) (∃d) (DIRECTOR(d) and d.coddir=p.director)

– Si la fórmula es abierta (tiene variables libres), entonces representa una consulta cuya evaluación devolverá los valores


p yde sus variables libres que hacen TRUE la fórmula

F5 : ACTOR(a) and a.fechaNacim > 31/12/1971sirve para preguntar por los actores/actrices que nacieron en 1972 o después

99

• Expresión segura– Su resultado es un número finito de tuplas– Al usar cuantificadores (∃ ∀) o negación (not) la expresión ha de tener

Cálculo RelacionalExpresión del CRT: punto de vista formal (y 9)

– Al usar cuantificadores (∃,∀) o negación (not), la expresión ha de tener sentido: ser segura y no generar una relación infinita

E= {t | not(ACTOR(t))} tuplas del universo que NO son de ACTOR ¡¡∞!!

• Dominio de una expresión del CRT– Valores constantes en la expresión o que existen en cualquier

tupla de las relaciones a las que se referencia en la expresiónDominio ( E={t | not(ACTOR(t))} )= todos los valores de atributos de tuplas ACTOR


Dominio ( E={t | not(ACTOR(t))} )= todos los valores de atributos de tuplas ACTOR

Una expresión es segura si todo valor del resultado ∈ dominio de la expresiónE es insegura, ya que el resultado incluye tuplas (y, por tanto, valores) que no están en la relación ACTOR (es decir, que ∉ su dominio)

100

51

Cálculo RelacionalEjercicios


Structured Query Language (lenguaje estructurado de consulta)• Primer lenguaje de BD de alto nivel. Años 70.

Manipulación de datosEl lenguaje relacional SQL-92

– Diseñado e implementado en el IBM’s Research Laboratory (San José -California), para el SGBD Relacional experimental System R

• Definición de un lenguaje estándar para SGBDRANSI (American National Standards Institute)

+ ISO (International Standardization Organization)

– SQL1 (ANSI 1986), extendido en 1989 (SQL-89)– SQL-92 (SQL2) y


– SQL-92 (SQL2), y– SQL:1999 (extensiones de Orientación a Objetos, disparadores, …)– SQL:2003 (incluye XML y otros conceptos recientes)

• Primeras implementaciones:

– ORACLE (finales 70) y poco después INGRES

102

52

• Lenguaje de bases de datos completo (no sólo «de consulta»)– Definición y Manipulación de Datos (LDD + LMD)– Definición y destrucción de Vistas (LDV)– Creación y destrucción de índices (aunque en SQL-92 «ya no existen»)

SQL-92

– Incorporación de SQL dentro de código escrito con un Lenguaje de Programación de propósito general (Pascal, C, etc.)

• Los proveedores de SGBDR comerciales (Oracle) implementan variaciones de SQL

– Algunas incluyen características que no están estandarizadas(triggers /reglas activas incluidos en la versión SQL:1999)


• Niveles de compatibilidad con el estándar de SQL– Entry SQL– Intermediate SQL– Full SQL

103

• Consultas o Selección de datos

• Modificación de datos

SQL-92Lo que vamos a estudiar...

Modificación de datos

• Vistas

• Definición y Alteración de datos

–Esquemas, Dominios, Tablas• Restricciones de Integridad Generales (Asertos)


Restricciones de Integridad Generales (Asertos)

• Seguridad y Control de Acceso

104

53

• SQL-92 vs. Modelo Relacional Formal– No utiliza los términos formales relación, atributo, tupla…,

sino tabla, columna, fila…

SQL-92Consultas básicas (4)

sino tabla, columna, fila…– Permite que las tablas tengan 2 o más filas idénticas en todos los

valores de sus columnasEn general, tabla SQL ≠ conjunto de filas, sino que

Tabla SQL = Multiconjunto de filas (saco, bag)• Es posible forzar que las tablas SQL sean conjuntos de filas:

- con restricciones de clave o- mediante opción DISTINCT en una SELECT (*se verá*)


mediante opción DISTINCT en una SELECT ( se verá )

– Las columnas de una tabla están ordenadas, y es posible indicar un orden de visualización de las filas

– Una clave ajena puede referenciar a una clave candidata

105

SQL-92Esquema de base de datos COMPAÑÍA

NOMBRE APELLIDO NSS NIF FECHAN DIRECCION SEXO SALARIO NSSJEFE NDEMPLEADO

NOMBRED NUMEROD NSSDIRE FECHAINICDIRE

NUMEROD OFICINA

DEPARTAMENTO

OFICINA_DEPTO

PROYECTO


NOMBREP NUMEROP LUGARP NUMEROD

NSSE NUMP HORAS

NSSE NOMBRE_FAMILIAR SEXO FECHAN PARENTESCO

PROYECTO

TRABAJA_EN

FAMILIAR

54

• Orden SELECT: Instrucción básica de obtención de

SELECT <lista columnas>FROM <lista tablas>WHERE <condición>

SQL-92Consultas básicas

donde:<lista columnas> columnas cuyos valores va a obtener la consulta<lista tablas> tablas necesarias para realizar la consulta<condición> expresión booleana para identificar filas que obtendrá la consulta

(expresión de reunión y/o de selección)

Fecha de nacimiento y dirección del empleado llamado José SilvaSELECT fechan, direccionFROM E l d

información WHERE <condición>


FROM EmpleadoWHERE nombre = ‘José’ AND apellido = ‘Silva’;

• La consulta selecciona las filas de <lista tablas>que satisfacen <condición> yproyecta el resultado sobre las columnas de <lista columnas>

107

• La orden SELECT ... FROM ... WHERE...– No es igual a la operación restricción σ del Álgebra Relacional– SELECT de SQL tiene muchas más opciones y matices


Q p y– En caso de una única tabla T en <lista tablas>

SELECT <lista columnas>FROM TWHERE <condición>

es equivalente a...

Π <lista columnas>(σ<condición> ( T ))


* Nombre, apellido y dirección de los empleados del departamento de InvestigaciónSELECT nombre, apellido, direccionFROM Empleado, Departamento → reunión o join de tablas

WHERE nombred=‘Investigación’ → condición de selección

AND numerod=nd; → condición de reunión entre tablas

108

55

• Cualquier nº de condiciones selección/reunión en SELECT

* Para cada proyecto ubicado en Santiago, obtener el nº del proyecto, nº del departamento que lo controla y el llid di ió f h d i i t d l t d d t t


apellido, dirección y fecha de nacimiento del gerente de ese departamento

SELECT numerop, numd, apellido, direccion, fechanFROM Proyecto, Departamento, EmpleadoWHERE numd=numerod AND nssdire=nss AND lugarp=‘Santiago’;

• Una SELECT puede obtener filas repetidas* Salario de los empleados de los departamentos de Administración y de Investigación


* Salario de los empleados de los departamentos de Administración y de Investigación

SELECT salarioFROM Empleado, DepartamentoWHERE (nombred = ‘Administración’ OR nombred = ‘Investigación’)

AND numerod=nd;109

• Obtención de los valores de todas las columnas de las filas seleccionadas

–No es necesario listar todos los nombres tras cláusula

SQL-92Consultas básicas (5): uso de *

cláusula SELECT

–Uso del símbolo * (todas las columnas)

SELECT *FROM Empleado WHERE nd=5;

SELECT *


FROM DepartamentoWHERE nombred=‘Investigación’;

SELECT *FROM Empleado, DepartamentoWHERE nombred=‘Investigación’ AND nd=númerod;

110

56

• Selección incondicional

–Equivale a una condición TRUE para todas las filas

l ió d t d l fil d

SQL-92Consultas básicas (6): omisión de WHERE

selección de todas las filas de...• una tabla (si la cláusula FROM sólo contiene una tabla), o• el producto cartesiano entre varias tablas (si FROM incluye más de

una)

* Seleccionar todos los nss de empleadosSELECT nss FROM Empleado;


FROM Empleado;

* Obtener todas las combinaciones de nss de empleados y nombres de departamentos

SELECT nss, nombredFROM Empleado, Departamento;

111

• Operador LIKE– Comparación de cadenas de caracteres– Caracteres reservados: ‘%’ y ‘ ’ (comodines)

SQL-92Consultas básicas (7): cadenas de caracteres

Caracteres reservados: % y _ (comodines)

*Nombres y apellidos de los empleados cuya dirección esté en Higueras, estado de MéxicoSELECT nombre, apellidoFROM EmpleadoWHERE direccion LIKE ‘%Higueras, MX%’ ;

• Operador ||– Concatenación de cadenas de caracteres

* N b l t l l d l d di ió Hi


* Nombres completos en una sola columna de empleados con dirección en Higueras (México)

SELECT nombre || ‘ ’ || apellidoFROM EmpleadoWHERE direccion LIKE ‘%Higueras, MX%’ ;

112

57

• Operaciones aritméticas– Aplicación de operadores aritméticos ( + - * / ) sobre valores numéricos

* Salarios de los empleados que trabajan en el proyecto ProductoX tras un aumento del 10%

SQL-92Consultas básicas (8): aritmética y tiempo

Salarios de los empleados que trabajan en el proyecto ProductoX, tras un aumento del 10%

SELECT apellido, nombre, 1.1*salarioFROM Empleado, Trabaja_en, ProyectoWHERE nss=nsse AND nump=numerop AND nombrep=‘ProductoX’ ;

el valor real de los salarios en la tabla EMPLEADO no cambia

• Operaciones con fechas, horas, marcas de tiempo e intervalosEspecificación del valor de un INTERVAL


– Especificación del valor de un INTERVAL como diferencia de dos valores DATE, TIME o TIMESTAMP

– Incremento y Decremento de valores de columnas de tipo DATE, TIME, TIMESTAMP en un intervalo compatible con el tipo

113

• En SQL los nombres de las columnas deben ser únicos dentro decada tabla

• Consulta que referencia a varias columnas de igual nombre, pero de

SQL-92Consultas básicas (9): calificación

Consulta que referencia a varias columnas de igual nombre, pero de tablas distintas...

AMBIGÜEDAD --------------Solución: CALIFICACIÓN

El esquema COMPAÑÍA incluye las siguientes tablas:

DEPARTAMENTO (nombred, numerod, nssdire, fechainicdire)OFICINA_DEPTO (numerod, oficina)


* Código, nombre y lugares de los departamentos de Marketing y de Investigación

SELECT Departamento.numerod, nombred, oficinaFROM Departamento, Oficina_deptoWHERE Departamento.nombred IN (‘Marketing’, ‘Investigación’)

AND Departamento.numerod = Oficina_depto.numerod;114

58

• Puede utilizarse seudónimos para acortar nombres de tabla dentro de las consultas con calificación:

SQL-92Consultas básicas (10): seudónimos

SELECT nombred, D.numerod, oficinaFROM Departamento AS D, Oficina_depto L ← ‘AS’ es opcionalWHERE D.nombred IN (‘Marketing’, ‘Investigación’) AND D.numerod = L.numerod;

• Consulta que se refiere dos veces a la misma tablaAMBIGÜEDAD Solución: SEUDÓNIMOS


AMBIGÜEDAD ------------------- Solución: SEUDÓNIMOS

* Obtener nombre y apellido de cada empleado y de su supervisor inmediato

SELECT E.nombre, E.apellido, S.nombre, S.apellidoFROM Empleado E, Empleado SWHERE E.nssjefe=S.nss;

115

• En el resultado de evaluar la consulta

* Nombres de cada empleado y su supervisor, cambiando al mismo tiempo los nombres de las columnas resultantes a ‘nom empleado’ y ‘nom supervisor’

SQL-92Consultas básicas (11): renombrar columnas

resultantes a nom_empleado y nom_supervisor

SELECT E.nombre AS nom_empleado, S.nombre AS nom_supervisor

FROM Empleado E, Empleado SWHERE E.nssjefe = S.nss;

Nueva cabecera para la tabla resultado


• Seudónimos de columnas (y/o tablas) en cláusula FROMSELECT nom, num, oficina

FROM Departamento D(nom, num, dire, inidire), Oficina_depto WHERE nom IN (‘Marketing’, ‘Investigación’) AND num = numerod;

116

59

• SQL permite presentar las filas resultado de una consulta de forma ordenada: Cláusula ORDER BY

– Ordenación según valores de una o varias columnas

SQL-92Consultas básicas (y 12): orden de presentación

Ordenación según valores de una o varias columnas– Ascendente ASC (por defecto) o Descendente DESC– Suele ser una operación muy costosa

las filas no se ordenan en disco: se ven ordenadas, pero no lo están

*Nombre y apellido de los empleados, y proyectos en los que trabajan, en orden descendente por departamentos y, dentro de cada departamento, en orden


alfabético ascendente por apellido y nombre

SELECT nombred, apellido, nombre, nombreFROM Departamento, Empleado, Trabaja_en, ProyectoWHERE numerod=nd AND nss=nsse AND nump=numeropORDER BY nombred DESC, apellido ASC, nombre ASC;

117

• SQL no elimina filas repetidas del resultado de una consulta, porque...– Eliminación de duplicados costosa (ordenar+recorrer+eliminar)– El usuario puede desear ver las filas repetidas en el resultado

SQL-92Tablas como conjuntos

– Si se aplica una función agregada a filas, rara vez deben eliminarse las duplicadas

• Operador DISTINCT:– Para eliminar duplicados del resultado de una consulta SQL»Resultado = Relación del Modelo Relacional Formal (conjunto de filas)

* Salarios de todos los empleados


SELECT salario FROM Empleado;* Salarios distintos de empleados, sin importar cuántos perciban cada cantidadSELECT DISTINCT salarioFROM Empleado;

118

60

• Operaciones de conjuntos– UNION( ∪ ), INTERSECT( ∩ ), EXCEPT ( — ) (minus en ORACLE)– Resultado: conjunto de filas las filas repetidas se eliminan

L bl d h d ibl i

Tablas como conjuntos (2)

SQL-92

– Las tablas operando han de ser compatibles en tipo:– igual nº de columnas, y – columnas “correspondientes” con el mismo dominio

* Nombres de los proyectos en que participa el empleado de apellido ‘Silva’, ya sea como trabajador o como gerente del departamento que controla el proyecto( SELECT nombrep FROM Proyecto, Trabaja_en, Empleado

WHERE numerop=nump AND nsse=nss AND apellido=‘Silva’ )UNION


( SELECT nombrep FROM Proyecto, Departamento, EmpleadoWHERE numd=numerod AND nssdire=nss AND apellido=‘Silva’ );

• Para no eliminar duplicados... – UNION ALL, INTERSECT ALL, EXCEPT ALL

119

• Un conjunto explícito de valores es una lista de valores encerrada entre paréntesis

• Puede aparecer en la cláusula WHERE

Tablas como conjuntos (3): conjuntos explícitos

SQL-92

uede apa ece e a c áusu a

* nss de los empleados que trabajan en los proyectos 1, 2 ó 3SELECT DISTINCT nsse FROM Trabaja_enWHERE nump IN (1, 2, 3);

• Operador INv IN V


–Indica si el valor v pertenece al conjunto de valores V–Devuelve TRUE si algún elemento e de V cumple que v = e* nss de los empleados que trabajan en algún proyecto que no sea el 4 ni el 6SELECT DISTINCT nsse FROM Trabaja_enWHERE nump NOT IN (4, 6);

120

61

• Operador ANY (o SOME)v <op> ANY V o v <op> SOME V ,, <op> ∈ { >, ≥, <, ≤, <>, = }– Compara un valor individual v con los elementos de un conjunto V

D l TRUE i l ú l t d V l

Tablas como conjuntos (4): conjuntos explícitos

SQL-92

– Devuelve TRUE si algún elemento e de V cumple que v <op> e* nss de los empleados que trabajan en alguno de los proyectos 1, 2 ó 3SELECT DISTINCT nsse FROM Trabaja_enWHERE nump = ANY (1, 2, 3);

• Operador ALLv <op> ALL V,, <op> ∈ { >, ≥, <, ≤, <>, = }– Compara un valor v con los elementos de un conjunto V


– Devuelve TRUE si para todo elemento e de V se cumple v <op> e* nss de los empleados que no trabajan en ninguno de los proyectos 1, 2 y 3SELECT DISTINCT nsse FROM Trabaja_enWHERE nump <> ALL (1, 2, 3);

121

• Es una consulta SELECT completa, dentro de cláusula WHERE de otra consulta (consulta exterior)

• Obtiene valores de la BD que se usan en la condición de otra consulta,

Consultas anidadas

SQL-92

q ,para obtener otros datos* Números de los proyectos en que participa el empleado de apellido ‘Silva’, sea como trabajador o como gerente del departamento que controla el proyecto

SELECT DISTINCT numerop FROM PROYECTOWHERE numerop IN ( SELECT nump

FROM Trabaja_en, EmpleadoWHERE nsse=nss AND apellido=‘Silva’ )


OR numerop IN ( SELECT numerop FROM Proyecto, Departamento, EmpleadoWHERE numd=númerod

AND nssdire=nss AND apellido=‘Silva’ ) ;• Es posible tener varios niveles de consultas anidadas

122

62

• Operador IN (otro uso del mismo operador)t IN S– indica si la fila t pertenece al conjunto de filas S (subconsulta)

Consultas anidadas (2): comparar conjuntos

SQL-92

indica si la fila t pertenece al conjunto de filas S (subconsulta)

* Nombre y dirección de los empleados que trabajan en algún proyecto.

SELECT nombre, dirección FROM EmpleadoWHERE nss IN ( SELECT nsse FROM TRABAJA_EN );

* Números de seguridad social de aquellos empleados que trabajan en algún proyecto en el que trabaje el empleado ‘José B. Silva’, de forma que ambos tengan la misma combinación (proyecto, horas); es decir, todo empleado que trabaje las mismas horas que ‘José B. Silva’, en cada proyecto en el que trabajen ambos. El nss de ‘José B. Silva’ es ‘123456789’.


SELECT DISTINCT nsse FROM Trabaja_enWHERE (númp, horas) IN ( SELECT númp, horas

FROM Trabaja_enWHERE nsse=‘123456789’);

123

• Operador ANY o SOME (otro uso del mismo operador)t <op> ANY S o t <op> SOME S,, <op> ∈ { >, ≥, <, ≤, ≠, = }– Compara una fila t con las filas resultado de una consulta anidada S

Consultas anidadas (3): comparar conjuntos

SQL-92

Compara una fila t con las filas resultado de una consulta anidada S– Devuelve TRUE si alguna fila e de S cumple que t <op> e

• Operador ALL (otro uso del mismo operador)t <op> ALL S,, <op> ∈ { >, ≥, <, ≤, ≠, = }– Compara una fila t con filas resultado de una consulta anidada S– Devuelve TRUE si para toda fila e de S se cumple que t <op> e* Nombres y apellidos de los empleados cuyo salario es menor que el de todos los empleados del departamento 5


* Nombres y apellidos de los empleados cuyo salario es menor que el de todos los empleados del departamento 5

SELECT nombre, apellido FROM EmpleadoWHERE salario < ALL ( SELECT salario

FROM EmpleadoWHERE nd=5 );

¿”Mejor” con DISTINCTen la subconsulta?

124

63

• Coincidencia de nombres de columnas en las consultas exterior y anidada Ambigüedad* Nombre y apellidos de cada empleado con familiares de igual nombre y sexo que él

Consultas anidadas (4): columnas ambiguas

SQL-92

Nombre y apellidos de cada empleado con familiares de igual nombre y sexo que élSELECT nombre, apellido FROM EmpleadoWHERE nss IN ( SELECT nsse FROM Familiar

WHERE nsse=nss AND nombre_familiar=nombreAND sexo=sexo ); ¿cómo evitar esta ambigüedad?

Regla: Una columna no calificada se refiere a la tabla declarada en la consulta anidada más interiorSi en una consulta anidada es necesario usar columnas de


Si en una consulta anidada es necesario usar columnas de tablas declaradas en una consulta exterior calificar* Nombre y apellidos de cada empleado con familiares de igual nombre y sexo que élSELECT nombre, apellido FROM Empleado EWHERE nss IN ( SELECT nsse FROM Familiar

WHERE nss=nsse AND nombre_familiar=nombre AND sexo= E.sexo );

125

• Una consulta exterior y otra anidada están correlacionadassi una condición de la anidada contiene columnas de una tabla declarada en la consulta exterior

Consultas anidadas (5): correlación

SQL-92

una tabla declarada en la consulta exteriorSELECT nombre, apellido FROM EmpleadoWHERE nss IN ( SELECT nsse FROM Familiar

WHERE nss=nsse AND sexo=‘F’ );

• La consulta anidada se evalúa una vez para cada fila(o combinación de filas) de la consulta exterior– Evalúa la consulta anidada para cada fila de EMPLEADO, – Si el valor de nss de la fila EMPLEADO está en el resultado de la consulta


– Si el valor de nss de la fila EMPLEADO está en el resultado de la consulta anidada, selecciona la fila EMPLEADO para el resultado final

• Una consulta anidada que use el operador = o IN siempre puede expresarse como una reunión (join)

SELECT E.nombre, E.apellido FROM Empleado, Familiar DWHERE nss=nsse AND D.sexo=‘F’;

126

64

• Operador EXISTS (S): comprobación de tablas vacías –Devuelve TRUE si la tabla S contiene al menos una fila–Devuelve FALSE si S es una tabla vacía (sin filas)

Consultas anidadas (6): EXISTS

SQL-92

–Devuelve FALSE si S es una tabla vacía (sin filas)S suele ser una consulta anidada correlacionada

* Nombre y apellido de cada empleado con familiares de igual nombre y sexo que élSELECT E.nombre, E.apellido FROM Empleado EWHERE EXISTS ( SELECT * FROM Familiar

WHERE nsse=nss AND nombre_familiar=nombreAND sexo=E.sexo );


AND sexo E.sexo );

* Nombres de empleados sin familiaresSELECT nombre, apellido FROM Empleado EWHERE NOT EXISTS (SELECT * FROM Familiar WHERE nsse=nss);

127

• Operador UNIQUE (S): Comprobación de filas duplicadas

– Devuelve TRUE si NO hay filas repetidas en SS suele ser una consulta anidada correlacionada

Consultas anidadas (y 7): UNIQUE

SQL-92

S suele ser una consulta anidada correlacionada

* Nombres y apellidos de los empleados que trabajan en un único proyecto

SELECT nombre, apellido FROM EmpleadoWHERE UNIQUE ( SELECT nsse

FROM Trabaja_enWHERE nsse = nss );

* Nombres apellidos y salario de los empleados con un solo familiar


* Nombres, apellidos y salario de los empleados con un solo familiar

SELECT nombre, apellido, salario FROM EmpleadoWHERE UNIQUE ( SELECT *

FROM FamiliarWHERE nsse = nss );

128

65

• Null – Ausencia o desconocimiento de información– Comparar NULL con cualquier cosa da FALSE

Nulos

SQL-92

– Comparar NULL con cualquier cosa da FALSE

• Operador IS NULL ,, IS NOT NULLv IS NULL– es TRUE si v es NULLv IS NOT NULL– es TRUE si v es un valor no NULL


* Nombres de empleados sin supervisores

SELECT nombre, apellido FROM EmpleadoWHERE nssjefe IS NULL;

129

SQLEjercicios I


66

• Función COUNT( ) – Cuenta el número de filas o de valores especificados en una consulta

• Funciones SUM( ), MAX( ), MIN( ), AVG( )S á i í i di it éti ( di )

Funciones agregadas

SQL-92

– Suma, máximo, mínimo y media aritmética (promedio)– Aplicadas a un multiconjunto (saco, bag) de valores numéricos

Pueden aparecer en cláusula SELECT* Suma de los salarios y salario máximo, mínimo y medio de los empleados

SELECT SUM(salario), MAX(salario), MIN(salario), AVG(salario)FROM EMPLEADO;* Suma de los salarios y salario máximo, mínimo y medio de empleados del depto. de Investigación


SELECT SUM(salario), MAX(salario), MIN(salario), AVG(salario)FROM Empleado, DepartamentoWHERE nd=númerod AND nombred=‘Investigación’;

También pueden aparecer en cláusula HAVING (*se verá*)

131

FUNCIONES AGREGADAS

• Uso de ** Número total de empleados de la compañía

SELECT COUNT(*) FROM Empleado ( cuenta filas)

Funciones agregadas (2): uso de * y de DISTINCT

SQL-92

( ) p ( )* Contar el número de empleados de la compañía que tienen un jefe

SELECT COUNT(nssjefe) FROM Empleado; ( cuenta filas con nssjefe no NULL)

* Número de empleados en el departamento de InvestigaciónSELECT COUNT(*) FROM Empleado, DepartamentoWHERE nd=númerod AND nombred=‘Investigación’;

• Uso de DISTINCT


• Uso de DISTINCT* Contar el nº de valores distintos de salario que pueden cobrar los empleados

SELECT COUNT(salario) FROM Empleado;Error: NO se eliminan duplicados, así que COUNT(salario) ≡ COUNT(*)

SELECT COUNT(DISTINCT salario) FROM Empleado; OK !!

132

67

• Es posible que una consulta anidada y correlacionada con otra exterior, incluya una función agregada

Funciones agregadas (y 3) y correlación

SQL-92

* Nombres de los empleados con 2 o más familiares

SELECT apellido, nombre FROM EmpleadoWHERE 2 ≤ ( SELECT COUNT(*)

FROM Familiar


WHERE nss=nsse );

133

• Cláusula GROUP BY– Para formar subgrupos de filas dentro de una tabla– Los grupos se forman según el valor de las columnas de agrupación

Agrupación

SQL-92

Los grupos se forman según el valor de las columnas de agrupación– Las filas de cada grupo tendrán el mismo valor en las columnas

de agrupación• Aplicación de funciones agregadas a grupos de filas

* Para cada departamento, obtener su número, cuántos empleados tiene dicho departamento y el salario medio de los empleados del mismoSELECT nd, COUNT(*), AVG(salario)FROM Empleado


GROUP BY nd ; ← una columna de agrupación

Las columnas de agrupación deben aparecer en la cláusula SELECT, antes de cualquier función agregada, para que su valor (único para cada grupo) aparezca junto al resultado de aplicar la función al grupo

134

68

• Cláusula HAVING– Siempre junto a GROUP BY– Condición que deben cumplir los grupos de filas asociados a

Agrupación (2)

SQL-92

Condición que deben cumplir los grupos de filas asociados a cada valor de las columnas de agrupación

– Un grupo que no cumple la condición, no es seleccionado para el resultado

* Para cada proyecto en el que trabajen más de dos empleados, obtener el número y nombre del proyecto, y el nº de empleados que trabajan en él

SELECT numerop, nombrep, COUNT(*)


FROM Proyecto, Trabaja_enWHERE numerop=numpGROUP BY numerop, nombrepHAVING COUNT(*) > 2 ;

135

• WHERE... se aplica a filas individuales• HAVING... se aplica a grupos de filas

Agrupación (y 3)

SQL-92

* Nº de empleados cuyos salarios superan los 1.800€ en cada departamento, pero sólo en el caso de departamentos en los que trabajen más de 5 empleados

(* Consulta incorrecta ¿por qué? *)

SELECT nombred, COUNT(*)FROM Departamento, EmpleadoWHERE númerod nd

(* Consulta correcta *)

SELECT nombred, COUNT(*)FROM Departamento, EmpleadoWHERE númerod=nd

AND salario>1800


WHERE númerod=ndAND salario>1800

GROUP BY nombredHAVING COUNT(*) > 5 ;

(* pista: orden de ejecución *)

AND salario>1800AND nd IN (SELECT nd

FROM EmpleadoGROUP BY ndHAVING COUNT(*) > 5)

GROUP BY nombred ;

136

69

• Reunión especificada en la cláusula FROM de una consulta

• Hasta ahora la hemos especificado en cláusulas FROM y WHERE

Tablas reunidas

SQL-92

p y* Nombres y dirección de empleados del departamento de Investigación

SELECT nombre, apellido, direccionFROM Empleado, Departamento ← reunión de tablasWHERE nombred=‘Investigacion’

AND nd=numerod; ← condición de reunión

• Consultas más comprensibles: separa condiciones de reunión y de selección


* Nombres y dirección de empleados del departamento de Investigación

SELECT nombre, apellido, direccionFROM (Empleado JOIN Departamento ON nd=numerod) ← tabla reunidaWHERE nombred=‘Investigacion’;

137

• Es posible anidar varias especificaciones de reunión de tablas

Tablas reunidas (2): anidamiento

SQL-92

* Para cada proyecto ubicado en ‘Santiago’, obtener el nº de proyecto, el nº del departamento que lo controla y el apellido, dirección y fecha de nacimiento del gerente de ese departamento

SELECT númerop, númd, apellido, dirección, fechanFROM ( ( Proyecto JOIN Departamento ON númd=númerod


)JOIN Empleado ON nssdire=nss )

WHERE lugarp=‘Santiago’;

138

70

• Es el tipo de reunión “por defecto”SELECT ... FROM ( R1 JOIN R2 ON <condición reunión> )

Reunión Interna de tablas (inner join)SQL-92

FROM ( R1 JOIN R2 ON <condición_reunión> )WHERE ...

• Si existe una fila t1 en R1 y otra fila t2 en R2, tales que cumplen la condición de reunión, la tabla resultado (reunida) incluirá la fila obtenida al combinar t1 y t2

SELECT E.nombre AS nom_empleado, S.nombre AS nom_supervisorFROM (Empleado E JOIN Empleado S ON E nssjefe = S nss);


FROM (Empleado E JOIN Empleado S ON E.nssjefe S.nss);–Son excluidas las filas EMPLEADO con NULL en nssjefe

• También puede especificarse comoR1 INNER JOIN R2 ON <condición_reunión>

139

• Sin condición de reunión explícitaSELECT ... FROM ( R1 NATURAL JOIN R2 )

Reunión Natural de tablas (natural join)

SQL-92

WHERE ...

• Equi-reunión implícita para cada par de columnas con igual nombre en una y otra tabla Sólo se incluye una de estas columnas en el resultado Si no coinciden los nombres de las columnas, es necesario

RENOMBRAR una de ellas mediante AS en la cláusula FROM

SELECT b llid di i


SELECT nombre, apellido, direccionFROM ( Empleado NATURAL JOIN (Departamento AS DEP(nombred, nd, dire, fech)) )WHERE nombred=‘Investigacion’;

140

71

• Útil si en una reunión se necesita obtener las filas… – con valor NULL en las columnas de reunión, o– sin correspondencia en la otra tabla

Reunión Externa de tablas (outer join)

SQL-92

sin correspondencia en la otra tabla

• Tipos de reunión externa:– LEFT [OUTER] JOIN Reunión externa izquierda– RIGHT [OUTER] JOIN Reunión externa derecha– FULL [OUTER] JOIN Reunión externa completa o total


SELECT E.nombre AS nom_empleado, S.nombre AS nom_supervisorFROM (Empleado E LEFT OUTER JOIN Empleado S ON E.nssjefe=S.nss);

Obtiene también los empleados sin supervisor (con NULL en nssjefe)

141

• En una consulta SQL hay un máximo de 6 cláusulas• Sólo son obligatorias SELECT y FROM

Evaluación de consultas

SQL-92

• Orden de especificación de las cláusulas:

SELECT <lista columnas> columnas o funciones que se van a obtener

FROM <lista tablas> tablas necesarias (incluso las reunidas)

WHERE <condición para filas> condiciones para selección de filas


WHERE <condición para filas> condiciones para selección de filas

GROUP BY <lista columnas agrupación> especificación del agrupamiento de filas

HAVING <condición para grupos> condición para selección de grupos de filas

ORDER BY <lista columnas ordenación> orden de presentación del resultado 142

72

• Orden de evaluación de las cláusulas:1) FROM (es decir, la reunión o join de tablas, si se especifica más de una)

Evaluación de consultas (2)

SQL-92

2) WHERE3) GROUP BY4) HAVING5) SELECT6) ORDER BY


• Diversas formas de especificar una misma consultaEjemplo: es posible expresar una consulta utilizando...

a) condiciones de reunión en cláusula WHERE, ob) tablas reunidas en la cláusula FROM, oc) consultas anidadas y el operador de comparación IN ...

Flexibilidad 143

• Ventajas e inconvenientes de esta flexibilidad:☺ – el usuario elige la técnica o enfoque más cómodo

Evaluación de consultas (y 3)

SQL-92

– Confusión del usuario: ¿qué técnica uso?– Algunas técnicas son más eficientes que otras

el usuario debe determinar cuál

• En condiciones ideales...– Usuario: se preocupa sólo de especificar la consulta correctamente– SGBD: se ocupa de ejecutar la consulta de manera eficiente

á í


• Pero en la práctica no suele ser así... conviene saber qué tipos de consulta son más y menos costosos

Recomendación (optimización de consultas):Consultas con mínimo anidamiento correlacionado y mínimo ordenamiento implícito

144

73

• Orden INSERT– Añade una fila completa a una tabla– Incluye nombre de la tabla y lista de valores para las columnas, escritos

i l d l ifi d l d CREATE TABLE

Inserción de datos

SQL-92

en igual orden al especificado en la orden CREATE TABLE

INSERT INTO Empleado VALUES ( 'Ricardo', ‘C’, 'Martínez', '653298653123', ‘987654321’, '30-DIC-52',

'Olmo 98, Cedros, MX', ‘M’, 37000, '987654321321', 4 ) ;

– Si se desea poner los valores de las columnas en cualquier orden, hay que especificar los nombres de las columnas en dicho orden


INSERT INTO Empleado ( nombre, apellido, nss, nif, nd, salario,nssjefe, direccion, fechan, sexo )

VALUES ( 'Ricardo', ‘C’, 'Martínez', '653298653123', ‘987654321’, 4, 37000,'987654321321', 'Olmo 98, Cedros, MX', '30-DIC-52', ‘M’ ) ;

145

• Inserción de varias filas en una sola orden INSERT– Filas separadas por comas

C d fil i t é t i

Inserción (2)

SQL-92

– Cada fila se encierra entre paréntesis

• Especificación explícita de algunas columnas (y no todas)– Omisión de columnas cuyo valor se desconoce– Cada columna no especificada tomará el...

· valor por omisión: valor tomado de su cláusula DEFAULT, o· NULL: si la columna permite nulos y no se definió cláusula


NULL: si la columna permite nulos y no se definió cláusula DEFAULT para la misma

* Inserción de un empleado del que sólo se conoce su nombre, apellidos, nss y nif

INSERT INTO Empleado (nombre, apellido, nss, nif)VALUES ( 'Rubén', 'Ripoll', '553298653111‘, ‘11222333R’ ) ;

146

74

• Si SGBD con implementación total de SQL-92- El SGBD maneja e impone toda RI definida en esquema de BD (LDD)

• Si SGBD con implementación de algunas RI

Inserción (3): Restricciones de Integridad

SQL-92

p g- Menor complejidad, mayor eficiencia- SGBD implementa comprobaciones para imponer RI que sí maneja

INSERT INTO Empleado (nombre, apellido, nd) VALUES ( 'Roberto', 'Huertas', 2 ) ;Inserción rechazada: no se incluye valor para nss, que debe ser NOT NULL

- Programador debe asegurar la no violación de las RI no manejadas por el SGBD

Supongamos que no existe departamento con numerod=8


INSERT INTO Empleado (nombre, apellido, nss, nif, nd)VALUES ( 'Roberto', 'Huertas', '980760540222', ‘22333444H’, 8 ) ;Si el SGBD sí maneja la Integridad ReferencialInserción rechazadaSi el SGBD NO soporta la Integridad ReferencialInserción permitida ¡el programador debe asegurar que esto no pase!

147

• Carga de una tabla con información sinóptica de la BDSea una tabla INFO_DEPTOS vacía. En ella queremos almacenar los nombres de cada departamento, su nº de empleados y el salario conjunto de los empleados del mismo.

Inserción (y 4): filas resultado de una consulta

SQL-92

INFO_DEPTOS ( nombre_depto, num_emps, sal_total)INSERT INTO Info_deptos ( nombre_depto, num_emps, sal_total )

SELECT nombred, COUNT(*), SUM(salario)FROM Departamento, EmpleadoWHERE númerod=ndGROUP BY nombred ;

– Es posible hacer SELECT ... FROM Info_deptos ...– INFO DEPTOS puede contener información no actualizada


INFO_DEPTOS puede contener información no actualizadaSi se modifica información en EMPLEADO y/o DEPARTAMENTO, los cambios no se reflejarán en la tabla INFO_DEPTOS

Una vista sí “contiene” siempre los datos más actuales (*se verá*)

148

75

• Orden DELETE– Elimina filas completas de una tabla– Sólo una tabla en cláusula FROM

Eliminación de datos

SQL-92

– Sólo una tabla en cláusula FROM– Cláusula WHERE para seleccionar las filas que eliminar

Si no hay WHERE, se eliminan todas las filas La tabla permanece, pero queda vacía

DELETE FROM Empleado ; todas las filasDELETE FROM Empleado WHERE apellido=‘Bojórquez’; 0 filas

DELETE FROM Empleado WHERE nss=‘123456789012’ ; 1 fila

DELETE FROM E l d


DELETE FROM EmpleadoWHERE nd IN ( SELECT numerod FROM Departamento

WHERE nombre=‘Investigación’) ; 4 filas

• Propagación de eliminaciones – Según acciones de mantenimiento de la Integridad Referencial

especificadas con LDD en los CREATE TABLE (esquema de BD)149

• Orden UPDATE– Modifica valores de columnas en una o más filas de una tabla– Se modifican filas de una sola tabla a la vez

Actualización de datos

SQL-92

– Se modifican filas de una sola tabla a la vez– Cláusula SET especifica columnas que modificar y nuevos valores– Cláusula WHERE para seleccionar filas que actualizar

Si no hay WHERE, se aplica la modificación a todas las filas

* Para el proyecto 10, cambiar el lugar a Belén y el nº de depto controlador al 5UPDATE Proyecto SET lugarp = ‘Belen’, númd = 5WHERE numerop=10 ;


• Propagación de modificaciones– Si cambia un valor de clave candidata, este cambio se propaga a

valores de clave ajena de filas de otras tablas, si así se especificó en las acciones de mantenimiento de la Integridad Referencial en la definición de la tabla con CREATE TABLE

150

76

• Modificación de varias filas a la vez con UPDATE

* Conceder a todo empleado del departamento de Investigación un aumento

Actualización (y 2)

SQL-92

* Conceder a todo empleado del departamento de Investigación un aumento salarial del 10% UPDATE Empleado SET salario = salario*1.1WHERE nd IN (SELECT númerod

FROM DepartamentoWHERE nombred=‘Investigación’) ;


• NULL o DEFAULT como nuevo valor de una columnaUPDATE Empleado SET salario = DEFAULT;UPDATE Empleado SET nssjefe = NULLWHERE ... ;

151

• Esquema de Base de Datos Relacional- Agrupa tablas y otros elementos, de una misma aplicación- 1as versiones de SQL: todas las tablas dentro de un esquema único y

LDD: Definición de datos

SQL-92

q yglobal a todas las aplicaciones que accedían a la BD

• Orden CREATE SCHEMA: definición/creación de esquemasCREATE SCHEMA <nombre de esquema>

AUTHORIZATION <identificador de autorización>• <nombre de esquema> identifica el esquema• <identificador de autorización> usuario/cuenta propietaria del esquema

CREATE SCHEMA Compañía AUTHORIZATION JSILVA ;


– A continuación puede especificarse las definiciones de los elementoscontenidos en dicho esquema

• Elementos del esquema:Tablas, Vistas, Dominios, Autorizaciones, Restricciones, etc.

152

77

• Conjunto nombrado de esquemas de BD en un entorno SQLContiene un esquema especial, INFORMATION_SCHEMA, que almacena datos sobre la definición de todos los elementos

Catálogo de base de datos relacional

SQL-92

almacena datos sobre la definición de todos los elementos de todos los esquemas existentes en el catálogoEl Diccionario de Datos (Data Dictionary) de ORACLE se corresponde con el INFORMATION_SCHEMA del estándar SQL-92

- Es posible compartir elementos (dominios, etc.) entre diferentes esquemas del mismo catálogo

- Sólo pueden definirse restricciones de integridad referencial


entre tablas que existan en esquemas dentro del mismo catálogo

• Concepto incorporado en la versión SQL-92 del estándar

153

• Orden CREATE TABLE

– Define (crea) una tabla: nombre, columnas y restricciones

LDD: definición de tablas

SQL-92

( ) , y

– Nombre único dentro del esquema

– Para cada Columna...• nombre,• tipo de datos (dominio)• restricciones de columna

R t i i d t bl

CREATE TABLE Empleado (nombre ...apellido ...

nss ...nif ...

fechan ...


– Restricciones de tabla...• de clave candidata,• de integridad de entidad, • de integridad referencial, o • restricciones de otro tipo

fechan ...direccion ...

sexo ...salario ...nssjefe ...

nd … );154

78

• Indicación del esquema al que pertenece una tabla– Esquema Explícito

CREATE TABLE Compañia Empleado


SQL-92

CREATE TABLE Compañia.Empleado ...– Esquema Implícito en el contexto

CREATE TABLE Empleado ...

• Ordenamiento de columnas y filas – Columnas ordenadas tal como aparecen en CREATE TABLE

L fil tá d d


– Las filas no están ordenadas

• Las tablas creadas con CREATE TABLE son tablas BASE– El SGBD las almacena físicamente en algún fichero de la BD

155

• Especificación del tipo de datos de una columna

1.Especificar directamente el tipo de datos tras nombre de la l


SQL-92

columnaCREATE TABLE Empleado (

nombre VARCHAR(15) ... ... );

2.Definir un dominio y usar su nombre como tipo de datos– Facilita cambio del tipo de datos usado por muchas columnas


p p– Esquema más comprensible

CREATE DOMAIN Nombres VARCHAR(15);...CREATE TABLE Empleado (

nombre NOMBRES ... ... );

156

79

• Numéricos–Enteros y Reales

· INTEGER (también INT) SMALLINT

LDD: tipos de datos

SQL-92

· INTEGER (también INT), SMALLINT,· FLOAT, REAL, DOUBLE PRECISION

–Con formato· DECIMAL(p,e) ( también DEC(p,e) ó NUMERIC(p,e) )

p: precisión, e: escala. El valor por omisión de la escala es e = 0

• Cadena de caracteres–Longitud fija CHAR(n) ( n: nº caracteres )


–Longitud variable VARCHAR(n) ( n: máximo nº caracteres )

• Cadena de Bits–Longitud fija BIT(n) (n: nº bits)

–Longitud variable BIT VARYING(n) n:máx nº bits. Por omisión n=1

157

• Temporales

· DATE (10 posiciones) = YEAR, MONTH, DAY (yyyy-mm-dd)

LDD: tipos de datos

SQL-92

· TIME (8 posiciones) = HOUR, MINUTE, SECOND (hh:mm:ss)

– Sólo permitidas fechas y horas válidas

· TIMESTAMP (marca de tiempo)

– DATE, TIME, fracciones de segundo y desplazamiento respecto al huso horario estándar (WITH TIME ZONE)

· INTERVALPeríodo de tiempo para incrementar/decrementar el valor actual de una


– Período de tiempo, para incrementar/decrementar el valor actual de una fecha, hora o marca de tiempo

– Se califica con YEAR/MONTH ó DAY/TIME para indicar su naturaleza

158

80

CREATE DOMAIN <nombre dominio> <tipo de datos>[ DEFAULT <valor defecto> ][ <lista de definición de restricciones de dominio> ]

LDD: definición de dominios de datos

SQL-92

[ <lista de definición de restricciones de dominio> ] ;- <tipo de datos>: uno de los proporcionados por el SGBD (built-in)- <valor defecto>: (opcional)

- Especifica el valor por omisión para columnas definidas de este dominio- Será asignado a cada columna con dicho dominio, si no tiene ya su propia

cláusula DEFAULT- <lista de definición de restricciones de dominio>: (opcional)

R t i I t id d li t d l d fi id b l d i i


- Restric. Integridad que se aplican a toda columna definida sobre el dominio - Cada RI puede tener un nombre: cláusula CONSTRAINT <nombre_RI>

* Ejemplo: enumeración de posibles valores componentes del dominioCREATE DOMAIN Color VARCHAR(8) DEFAULT ‘sinColor’CONSTRAINT color_validoCHECK (VALUE IN ( ‘rojo’, ‘amarillo’, ‘azul’, ‘verde’, ‘sinColor’ ) ) ;

159

SQLEjercicios II


81

• Especificación de restricciones de columna–Cláusula NULL o NOT NULL

• Opción de nulo: indica si una columna puede contener o no NULL


SQL-92

• Opción de nulo: indica si una columna puede contener o no NULLCREATE TABLE Empleado (...

nombre VARCHAR(15) NOT NULL, ... );• Por omisión, se asume NULL• La restricción NOT NULL es obligatoria para columnas

componentes de una clave primaria

–Cláusula DEFAULT <valor>V l i ió ( d f t )


• Valor por omisión (o por defecto)CREATE TABLE Empleado ( ...

salario DECIMAL(5,2) DEFAULT 1000 NULL,... );• Si una columna no tiene DEFAULT, su valor por defecto es...

– El de su dominio, si su tipo es un dominio que incluye DEFAULT– NULL en cualquier otro caso, siempre que la columna permita NULL

161

• Especificación de restricciones de tabla–Cláusula PRIMARY KEY (<lista_columnas>)


SQL-92

• Columnas que componen la clave primaria

–Cláusula UNIQUE (<lista_columnas>)• Columnas que forman una clave alternativa

–Cláusula FOREIGN KEY (<lista_columnas>) REFERENCES <tabla>(<lista_columnas>)

• Columnas clave externa (Integridad Referencial)


• SQL-92 permite que una clave externa se refiera a una clave primaria o una clave alternativa

–Cláusula CHECK (<expresión>)• Condición sobre los valores de las columnas que debe cumplir

toda fila de la tabla

162

82

CREATE TABLE Empleado (nombre VARCHAR(15) NOT NULL, apellido VARCHAR(15) NOT NULL,


SQL-92

nss CHAR(12) NOT NULL,nif CHAR(9) NOT NULL,fechan DATE NULL,direccion VARCHAR(30) ,sexo CHAR(1) ,salario DECIMAL(5,2) DEFAULT 1000 NULL,nssjefe CHAR(12) ,nd NUMERIC(2) NOT NULL,


( ) ,PRIMARY KEY ( nss ),UNIQUE ( nif ),CHECK ( nssjefe <> nss ),CHECK ( sexo IN (‘H’, ‘M’) ),FOREIGN KEY (nssjefe) REFERENCES Empleado(nss),FOREIGN KEY (nd) REFERENCES Departamento(numerod)

); 163

• Especificación de restricciones de tabla (cont.)

- Dar nombre a una restricción es opcional, pero muy conveniente


SQL-92

CONSTRAINT <nombre_RI> <restricción>

- El nombre de restricción debe ser único dentro del mismo esquema- Identifica una restricción, por si después debe ser eliminada o

sustituida por otra

CREATE TABLE Empleado (


p (...,CONSTRAINT pk_empleado PRIMARY KEY ( nss ),CONSTRAINT nif_unico UNIQUE ( nif ),CONSTRAINT jefe_ok CHECK ( nssjefe <> nss ),... );

164

83

• Especificación de restricciones de tabla (cont.)

- Si la restricción afecta sólo a una columna, puede especificarse en la definición de dicha columna (en la misma línea)


SQL-92

la definición de dicha columna (en la misma línea)- Por ejemplo, si una clave externa no es compuesta, no se necesita la

cláusula FOREIGN KEY

CREATE TABLE Empleado (nombre VARCHAR(15) NOT NULL, nss CHAR(12) PRIMARY KEY,nif CHAR(9) NOT NULL UNIQUE,


( ) Q ,nssjefe CHAR(12) NULL REFERENCES Empleado(nss),nd NUMERIC(2) NOT NULL REFERENCES Departamento(numerod),...,CONSTRAINT jefe_ok CHECK ( nssjefe <> nss ),... );

165

• Acciones de mantenimiento de la integridad referencialCláusulas ON DELETE <acción>

y ON UPDATE <acción>


SQL-92

<acción> ∈ { NO ACTION, CASCADE, SET NULL, SET DEFAULT }

CREATE TABLE Empleado (...,CONSTRAINT jefe_emp FOREIGN KEY (nssjefe)

REFERENCES Empleado(nss)ON DELETE SET NULLON UPDATE CASCADE


ON UPDATE CASCADE,CONSTRAINT dep_emp FOREIGN KEY (nd)

REFERENCES Departamento(numerod)ON DELETE NO ACTION ON UPDATE CASCADE

);

166

84

• Una vista es una tabla derivada de otras tablas• Son tablas virtuales, pues no necesariamente existen en forma física• Sentencia de definición o creación de una vista

LDD: definición de vistas

SQL-92

Sentencia de definición o creación de una vistaCREATE VIEW <nombre_vista> [ (<lista_nombres_columnas>) ]

AS <consulta_de_definición>• La consulta de definición…

– determina el contenido de la vista– contiene las tablas base: tablas o vistas de las que se deriva la vista

(también llamadas tablas de definición)

CREATE VIEW Familiar de Empleado (empleado familiar


CREATE VIEW Familiar_de_Empleado (empleado, familiar, parentesco)AS SELECT nombre, nombre_familiar, parentesco

FROM Empleado, FamiliarWHERE nss = nsse;

167

• Por defecto, la vista ‘hereda’ los nombres de las columnas...- seleccionadas desde las tablas base- siempre que ninguna columna sea el resultado de una operación aritmética o


SQL-92

siempre que ninguna columna sea el resultado de una operación aritmética o función de agregados

CREATE VIEW Empleado_en_ProyectoAS SELECT nombre, apellido, nombrep, horas

FROM Empleado, Proyecto, Trabaja_enWHERE nss = nsse AND nump = numerop ;

• Definición de nuevos nombres para columnas de la vista

nombres que hereda la vista


CREATE VIEW Info_Depto (nombre_depto, num_de_emps, sal_total)AS SELECT nombred, COUNT(*), SUM(salario)

FROM Departamento, EmpleadoWHERE numerod = ndGROUP BY nombred ;

168

85

• Un estado de la vista EMPLEADO_EN_PROYECTO


SQL-92

Empleado_en_Proyecto nombre apellido nombrep horasJosé Silva ProductoX 32.5

Ramón Nieto ProductoZ 40.0

José Silva ProductoY 07.5

Josefa Barceló ProductoX 20.0

Federico Vizcarra ProductoY 10.0

Federico Vizcarra ProductoZ 10 0


Federico Vizcarra ProductoZ 10.0

Josefa Barceló ProductoY 20.0

... ... ... ...

169

• Las vistas pueden utilizarse como mecanismo de...- Simplificación de consultas- Seguridad (*se verá en el tema de seguridad*)


SQL-92

Seguridad (*se verá en el tema de seguridad*)

- Adaptación de la información a las necesidades de cada usuario o grupo de usuarios

• Característica fundamental de las vistasActualización Permanente- El responsable de esta característica es el SGBD

La ista no se c ea c ando se define sino c ando se


La vista no se crea cuando se define, sino cuando se consultaUna vista no “contiene información”, sino que “deja ver

información” almacenada en sus tablas base

170

86

• El SGBD traduce cualquier sentencia SQL sobre la vista a una expresión equivalente sobre sus tablas base: reemplaza el nombre de la vista por su consulta de definición y ejecuta


SQL-92

nombre de la vista por su consulta de definición y ejecutaCREATE VIEW Veterano AS

SELECT nombre, nif, nss, fechan, nd FROM Empleado WHERE fechan<’01/01/1970’;Sentencia de usuario Traducción

SELECT * FROM VETERANOWHERE nombre LIKE ‘G%’;

SELECT nombre, nif, nss, fechan, nd FROM EMPLEADOWHERE fechan < ‘01/01/1970’ AND nombre LIKE ‘G%’;

INSERT INTO VETERANO INSERT INTO EMPLEADO (nombre, nif, nss, fechan, nd)


VALUES (‘Eva’, ‘12345678E’, ‘123456789012’, ‘14/11/1947’, 4);

( , , , , )VALUES (‘Eva’ ‘12345678E’, ‘123456789012’, ‘14/11/1947’, 4);

UPDATE VETERANO SET nd=1WHERE nd=2;

UPDATE EMPLEADO SET nd=1 WHERE fechan < ‘01/01/1970’ AND nd=2;

DELETE FROM VETERANOWHERE nif = ‘12345678E’;

DELETE FROM EMPLEADO WHERE fechan < ‘01/01/1970’ AND nif = ‘12345678E’;

171

• Las vistas no tienen ninguna limitación en operaciones de consulta• El usuario no distingue si el elemento al que accede es una tabla base o

una vista

LDD: consulta a través de vistas

SQL-92

una vista

* Nombres de los empleados y de sus hijos/as

SELECT empleado, familiar FROM Familiar_de_empleado WHERE parentesco LIKE ‘Hij_’ ;

* Datos del departamento ‘Investigación’

SELECT * FROM Info_Depto WHERE nombre_depto=‘Investigación’ ;


* Nombres y apellidos de los empleados que trabajan en el proyecto 'ProductoX'

SELECT nombre, apellido, nombrepFROM Empleado_en_ProyectoWHERE nombrep=‘ProductoX’ ;

172

87

• La actualización de datos a través de vistas tiene algunas limitaciones

LDD: modificación a través de vistas

SQL-92

- Por un lado, actualizar a través de una vista definida sobre varias tablas base suele dar problemas, pues puede haber ambigüedad

UPDATE Empleado_en_Proyecto SET nombrep = ‘ProductoZ’WHERE apellido=‘Silva’ AND nombre=‘José’

AND nombrep=‘ProductoX’;


Esta modificación puede traducirse a dos actualizaciones distintasde las tablas base de la vista (EMPLEADO, PROYECTO y TRABAJA_EN), como se muestra en la siguiente diapositiva…

173

UPDATE Trabaja_en SET nump = (SELECT numerop FROM ProyectoWHERE nombrep = ‘ProductoZ’)

WHERE nsse (SELECT nss FROM Empleado


SQL-92

WHERE nsse = (SELECT nss FROM EmpleadoWHERE apellido = ‘Silva’ AND nombre = ‘José’)

AND númp = (SELECT numerop FROM ProyectoWHERE nombrep = ‘ProductoX’) ;

☺ Modifica los vínculos en TRABAJA_EN: cada fila que relacionaba lasfilas de ‘José Silva’ en EMPLEADO y de ‘ProductoX’ en PROYECTO, pasaa relacionar tal empleado con la fila ‘ProductoZ’ de PROYECTO

UPDATE P t SET b ‘P d t Z’


UPDATE Proyecto SET nombrep = ‘ProductoZ’WHERE nombrep = ‘ProductoX’ ;

Produce igual efecto que pero modifica nombrep en PROYECTO: al calcular la vista, mostrará ‘ProductoZ’ para todos los que antes

aparecían con ‘ProductoX’

174

88

- Por otro lado, algunas actualizaciones a través de vistas


SQL-92

carecen de sentidoUPDATE Info_deptoSET sal_total = 100000WHERE nombred=‘Investigación’ ;

sal_total se define como la suma de salarios individuales de los empleados y muchas actualizaciones de las tablas base satisfarían esta actualización


Así que no se garantiza que “toda vista sea actualizable”

175

• Una vista sería actualizable si...- Implicara una única actualización posible de las tablas


SQL-92

base, o bien- Hubiera varias actualizaciones posibles, pero

existiera un procedimiento específico de actualización de tablas base, tal que...· El usuario pudiera elegir el procedimiento, especificándolo en la definición de la vista, o bien


· El SGBD pudiera elegir el procedimiento, según la actualización más probable

176

89

• En general...- Una vista con una sola tabla base

SÍ t li bl i l ti l l


SQL-92

SÍ es actualizable si sus columnas contienen la claveprimaria u otra clave candidata de la tabla base

– Pues se establece una correspondencia entre cada fila de la vista y una única fila de la tabla base

- Una vista definida sobre varias tablas mediante reuniones

NO es actualizable


- Una vista definida mediante agrupación y funciones agregadas

NO es actualizable

177

• Opción de verificación de vistasCREATE VIEW Emp_Precario AS SELECT nombre, apellido, nss, nif, salario, nd

FROM Empleado WHERE salario < 900 ;


SQL-92

FROM Empleado WHERE salario < 900 ;*¿Qué pasaría al ejecutar estas sentencias?INSERT INTO Emp_Precario VALUES (‘Dimas’, ‘Pi', ‘111222333444’, ‘12121212D’, 1025,

1);UPDATE Emp_Precario SET salario = 950 WHERE nif=‘12345678E’;

• Cláusula WITH CHECK OPTION- En la definición de toda vista actualizable que se vaya a utilizar para la

modificación de datos- Indica al SGBD que debe comprobar cada INSERT y UPDATE sobre la vista y


- Indica al SGBD que debe comprobar cada INSERT y UPDATE sobre la vista, y rechazarlo si su realización implicara que la fila nueva o modificada no cumpliera la condición de definiciónCREATE VIEW Emp_Precario AS SELECT nombre, apellido, nss, salario, nd

FROM Empleado WHERE salario < 900WITH CHECK OPTION ;

178

90

1. Estrategia de actualización de consultas de definición- Cada consulta sobre la vista se traduce a una consulta sobre las

t bl b

LDD: implementación de vistas

SQL-92

tablas base- La vista se rellena de filas a partir de la ejecución de la consulta- Poco eficiente cuando la <consulta_de_definición> es

compleja, con tiempo de ejecución apreciable, y se aplican muchas consultas sobre la vista en poco tiempo

2. Estrategia de materialización de vistas- 1ª consulta sobre la vista creación de tabla temporal física


p- Se conserva la tabla para posteriores consultas sobre la vista

- Necesaria estrategia para actualización incremental de la tabla temporal tras cualquier modificación sobre las tablas baseactualización permanente

- Si no se hace referencia a la vista tras un tiempo, el sistema la eliminará (y la recalculará en una consulta futura)

179

LDD: Modificación de la estructura (alteración)de los elementos del esquema de base de datos

SQL-92

• Alteración de tablas:ALTER TABLE <nombre_tabla> ... ;

– Adición y Eliminación de Columnas– Modificación de la Definición de Columnas– Adición y Eliminación de Restricciones de Tabla

Alt ió d d i i


• Alteración de dominios:ALTER DOMAIN <nombre_dominio> ... ;

– Eliminación y Adición de valor por defecto– Eliminación y Adición de Restricciones de Dominio

180

91

• Adición de una columna a una tabla ya existenteALTER TABLE <nombre_tabla> ADD <definición_columna> ;

N tá itid NOT NULL l d fi i ió d l

LDD: alteración de tablas

SQL-92

– No está permitido NOT NULL en la definición de una nueva columna(si es necesaria esta restricción, podrá establecerse después)

* Añadir una columna a EMPLEADO para contener el puesto de trabajoALTER TABLE Empleado ADD puesto VARCHAR(12);• Todas las filas de EMPLEADO tendrán puesto a NULL

– Para introducir un valor para la columna, en cada fila existente:• Especificar la cláusula DEFAULT al añadir la columna:


Especificar la cláusula DEFAULT al añadir la columna: ALTER TABLE Empleado

ADD puesto VARCHAR(12) DEFAULT ‘aprendiz’;• Utilizar después una orden UPDATE

181

• Eliminación de una columna de una tabla

ALTER TABLE <nombre_tabla> DROP <nombre_columna> <opción>;


SQL-92

<opción> puede ser...CASCADE: elimina la columna y toda restricción o vista que le hace referencia

RESTRICT: sólo elimina la columna si ninguna vista ni restricción le referencia

* Eliminación de la columna dirección de la tabla EMPLEADO

ALTER TABLE Empleado DROP direccion CASCADE;


p ;ALTER TABLE Departamento DROP numerod <opción>;– Si <opción> = RESTRICT: no elimina la columna ‘numerod’, pues existe una columna ‘EMPLEADO.nd’ que le

hace referencia– Si <opción> = CASCADE: elimina la columna y la restricción de integridad referencial que vincula

‘EMPLEADO.nd’ con DEPARTAMENTO. La columna ‘EMPLEADO.nd’ no es eliminada, pero deja de ser clave ajena

182

92

• Modificación de la definición de una columnaALTER TABLE <nombre_tabla> ALTER <nombre_columna> <acción> ;


SQL-92

<acción> indica la modificación que se desea realizar...

– Eliminación de la cláusula DEFAULT existenteALTER TABLE Departamento ALTER nssdire DROP DEFAULT;

– Definición de un nuevo valor por omisión


ALTER TABLE Departamento ALTER nssdire SET DEFAULT ‘123456789012’;

183

• Modificación de una restricción de tabla

– La restricción que se desea modificar debe tener un nombre


SQL-92

– Eliminación de una restricción de tabla

ALTER TABLE <nombre_tabla> DROP CONSTRAINT <nombre_RI> <opción>;ALTER TABLE Empleado DROP CONSTRAINT jefe_emp CASCADE;

– Adición de una restricción de tabla

ALTER TABLE <nombre_tabla> ADD CONSTRAINT <nombre_RI> <definición_RI>;


ALTER TABLE Empleado ADD CONSTRAINT salario_ok CHECK (salario > 0);

ALTER TABLE Empleado ADD CONSTRAINT puesto_ok CHECK (puesto IS NOT NULL);

184

93

• Orden ALTER DOMAIN <nombre_dominio> <acción>;

<acción> indica la modificación que se desea realizar

LDD: alteración de dominios

SQL-92

<acción> indica la modificación que se desea realizar...

– Eliminación y Reemplazo del valor por omisiónALTER DOMAIN <nombre_dominio> DROP DEFAULT;ALTER DOMAIN <nombre_dominio> SET DEFAULT <valor>;

– Eliminación y Definición de nuevas restricciones de dominioALTER DOMAIN <nombre_dominio>


DROP CONSTRAINT <nombre_RI_dominio>;ALTER DOMAIN <nombre_dominio>

ADD [ CONSTRAINT <nombre_RI_dominio> ] <restricción>;

185

• Eliminación de una vista. Orden DROP VIEW– Destruye una tabla derivada, junto con su definición en el

INFORMATION SCHEMA del catálogo

LDD: eliminación de elementos del esquema

SQL-92

_ gDROP VIEW <nombre_vista> ;

• Eliminación de un dominio. Orden DROP DOMAIN – Destruye un dominio de datos, junto con su definición en el

INFORMATION_SCHEMA del catálogo

DROP DOMAIN <nombre_dominio> <opción> ;<opción> puede ser...

RESTRICT d t l d i i i h i l d fi id b él


RESTRICT: destruye el dominio si no hay ninguna columna definida sobre élCASCADE: se elimina el dominio y toda columna definida sobre él pasa a

tener el tipo de datos sobre el que se había definido el dominio (este aspecto es ampliado en el tema “Integridad en sistemas de bases de

datos relacionales”)

186

94

• Eliminación de una tabla. Orden DROP TABLE– Destruye una tabla base, junto con su definición en el


LDD: eliminación de elementos del esquema

SQL-92

_ gDROP TABLE <nombre_tabla> <opción>;

<opción> puede ser...

RESTRICT: Destruye la tabla sólo si no se le hace referencia desde ningunaotra tabla (clave ajena), ni es tabla base de una vista

CASCADE: Elimina la tabla junto con restricciones y vistas que la referencian

• Eliminación de un esquema. Orden DROP SCHEMA– Destruye un esquema de BD, junto con su definición en el



O O _SC de catá ogoDROP SCHEMA <nombre_esquema> <opción>;

<opción> puede ser...

RESTRICT: Destruye el esquema sólo si no contiene ningún elementoCASCADE: Elimina el esquema y las tablas, dominios y demás elementos

contenidos en el esquema

187

JDBC• Java DataBase Connectivityy• API Java para ejecutar consultas SQL en Bases de Datos

Relacionales.• Independiente del Sistema Gestor Relacional.

– Similar en concepto a ODBC de Windows.• Distribuida en dos paquetes:

– java.sql, dentro de J2SEió d d J2EE– javax.sql extensión dentro de J2EE

• Para acceder a una base de datos es necesario un driver.– Implementación de todas las interfaces del API.


95

JDBC• El API ofrece las clases e interfaces para:p

– Establecer una conexión a una base de datos.– Ejecutar una consulta.– Procesar los resultados.

// Establece la conexiónConnection con = DriverManager.getConnection (

"jdbc:odbc:miBD", ”miLogin", ”miPassword");// Ejecuta la consulta// Ejecuta la consulta

Statement stmt = con.createStatement();ResultSet rs = stmt.executeQuery("SELECT nombre, edad FROM Empleados");

// Procesa los resultadoswhile (rs.next()) {

String nombre = rs.getString(”nombre");int edad = rs.getInt(”edad");

}


Arquitectura

Statement

ResultSet

Statement Statement

JVM

AplicaciónConnection

DriverManagerDriver

Access

Connection

Driver

OracleTema 2. Modelo relacional de datos 190

96

Driver• Conjunto de clases encargadas de implementar las interfaces del API y acceder

a la base de datosa la base de datos.• Tipos:

– Driver Tipo 1:• Utilizan un API nativa estándar• Ejemplo: puente JDBC:ODBC

– Driver Tipo 2:• Utilizan un API nativa de la base de datos• Ejemplo: Oracle OCI

– Driver Tipo 3:• Servidor remoto con un API genérica.Se do e oto co u ge é ca• Útil para aplicaciones en internet.

– Driver Tipo 4:• Totalmente desarrollado en Java• Ejemplo: Oracle Thin.


Driver

• Los drivers Tipo 1 y 2 utilizan código nativo vía JNI.– Son más eficientes.

• Diseño Driver Tipo 3:

– En aplicaciones enterprise favorecen la gestión de las bases de datos, que se realiza en el servidor.

• Carga de un driver: Class.forName(“acme.db.Driver”);• Repositorio de drivers:

– http://industry.java.sun.com/products/jdbc/driversTema 2. Modelo relacional de datos 192

97

Conexión

• Representa una conexión con una base de datos.p• Se obtienen a partir de la clase DriverManager:

– DriverManager.getConnection(“URL”, “login”, “password”)

– Mantiene un registro de todos los drivers cargados en la JVM.

• URL identifica el driver y su tipo, la localización de la base de datos y su nombre:datos y su nombre:– jdbc:oracle:oci:dis.um.es/MiBD

• Alternativa a DriverManager: DataSource


DataSource• Actúa como factoría de conexiones.

– Método getConnection()• Abstrae los detalles de acceso:

– Cargar los drivers, URL, login, etc.– Aplicaciones más portables.

• Suelen mantener un pool de conexiones.• Proporcionado por el contenedor utilizando el API JNDI (Java

Naming and Directory Interface).– Los detalles de acceso se indican en ficheros de configuración.– Acceso a los servicios de nombres y directorios. – Ejemplo: acceso a recursos, LDAP.


98

Consultas SQL• El API JDBC no restringe las sentencias que se envían a la BD.• En principio, todos los drivers deben ser compatibles con ANSI SQL-2

Entry Level.• Tipos de sentencias:

– Statement: para sentencias sencillas en SQL.– PreparedStatement: para consultas preparadas,

• Ejemplo: las que tienen parámetros.– CallableStatement: para ejecutar procedimientos almacenados en

la BD.• El API distingue dos tipos de consultas:

– Consultas: SELECT– Actualizaciones: INSERT, UPDATE, DELETE, sentencias DDL.


Statement

• Son creadas a partir de la conexión:p– Statement stmt = conexion.createStatement();

• Ejecución de una consulta:– stmt.executeQuery(“SELECT * FROM Pedidos”);– Devuelve un objeto ResultSet.

• Ejecución de una actualización:– stmt.executeUpdate(“DELETE FROM Pedidos WHERE

codigo = 15”)– Devuelven un entero indicando los registros actualizados ó 0

si es una consulta DDL.


99

ResultSet

• Es un proxy sobre los registros del resultado de la búsqueda.p y g q– Controla la recuperación de los registros.

• Representa un cursor (iterador) sobre los resultados.– Movimiento: métodos next() y previous()– Inicialmente el cursor está posicionado antes del primer

registro.• Depende del objeto consulta:

– Cada vez que se realice una consulta se pierden los resultados.


ResultSet

• Tenemos dos alternativas para acceder a las columnas del presultado:– rs.getString(“nombre”); ⇒ Nombre de la columna– rs.getString(1); ⇒ Posición en la consulta

• El acceso por posición es útil cuando:– Acceso a una columna derivada, ej. calcular la media– Cuando hay columnas con los mismos nombres (join)

ó• Recuperación de los valores de las columnas:– Métodos de acceso (getXXX).– Es conveniente leer los datos de izquierda a derecha.– Para averiguar si se ha leído un nulo: wasNull()


100

ResultSet – Métodos de Acceso

Tipo de dato SQL Método de Acceso CHAR String getString()

VARCHAR String getString() LONGVARCHAR InputStream

getAsciiStream() ó getUnicodeString()

NUMERIC java.math.BigDecimal getBigDecimal()

DECIMAL java.math.BigDecimal getBigDecimal()

BIT boolean getBoolean() TINYINT byte getByte() SMALLINT short getShort() INTEGER int getInt() BIGINT l tL ()BIGINT long getLong()REAL float getFloat() FLOAT double getDouble() DOUBLE double getDouble() BINARY byte[] getBytes()

VARBINARY InputStream getBinayStream()

DATE java.sql.Date getDate() TIME java.sql.Time getTime()

TIMESTAMP java.sql.TimeStamp getTimeStamp()


PreparedStatement• Problema con Statement:

– Cuando la consulta se realiza dentro de un bucle y varía sólo en unos valores:• stmt.executeQuery(“SELECT * FROM Cliente

WHERE codigo = “ + i);• La base de datos planifica cada consulta.

– Conviene disponer de una consulta con parámetros.• PreparedStatement:• PreparedStatement:

– Especialización de Statement que permite definir consultas parametrizadas.

– La BD sólo planifica la consulta cuando se crea.– Evitan tener que formatear los datos al construir la

cadena de consulta: ‘ ’ para cadenas, fechas y horas.


101

PreparedStatement• También se crean a partir de la conexión:p

– PreparedStatement pstmt = conexion.prepareStatement(“SELECT * FROM Cliente WHERE codigo = ?”)

• Los parámetros de entrada se especifican por posición utilizando métodos setXXX:– psmt.setInt(1, 20);– Misma equivalencia que los getXXX de ResultSet.

L l t j i– Los valores se conservan entre ejecuciones.• Borrar parámetros: clearParameters()

• Ejecución:– Consulta: executeQuery().– Actualización: executeUpdate().


Transacciones

• Ejecución de bloques de consultas SQL manteniendo las j q Qpropiedades ACID (Atomicy-Consistency-Isolation-Durability)

• Una conexión funciona por defecto en modo autocommit:– Cada consulta representa una sola transacción.– Método: conexion.setAutocommit(false);

• Definimos bloques de consultas:– Deshabilitando el modo autocommit.

ó– Finalizamos la transacción ejecutando commit() o rollback() sobre la conexión.


102

Nivel de Aislamiento Transaccional

• Niveles de aislamiento:– TRANSACTION_NONE:

• Sin soporte transaccional.– TRANSACTION_READ_UNCOMMITED:

• Permite lecturas sobre datos no consolidados.– TRANSACTION_READ_COMMITED:

• Permite lecturas sólo sobre datos consolidados.• Nivel por defecto.

– TRANSACTION_REPEATABLE_READ.• Bloquea los datos leídos.

– TRANSACTION_SERIALIZABLE.• Sólo una transacción al mismo tiempo.

• Suelen estar disponibles read commited y serializable.


Concurrencia• Establecer el modo de aislamiento:

– conexion.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

• Consejos de uso:– Bloque con sólo actualizaciones:

• TRANSACTION_READ_COMMITED– Bloque donde leamos varias veces el mismo registro:

• TRANSACTION_REPEATABLE_READ– Bloque en el que leamos un valor para actualizarlo:– Bloque en el que leamos un valor para actualizarlo:

• TRANSACTION_SERIALIZABLE– Bloque donde realicemos varias veces la misma consulta (varios

registros):• TRANSACTION_SERIALIZABLE


103

Resumen

• ¿Qué es ODBC?

• Rol del ResultSet

• ¿Cómo se parametrizan consultas?


• ¿Cómo se controla la concurrencia de transacciones?

205

Documents

Modelo Relacional de Datos - Universidad de Murciadis.um.es/~jfernand/0708/fbd/tema2.pdf · • Construir consultas de bases de datos empleando las sentencias del álgebra ... 3 Modelo