Capítulo 3: El modelo relacional

(1)

Capítulo 3: El modelo relacional

Estructura de las bases de datos relacionales Álgebra relacional

Operaciones de álgebra relacional extendida Modificación de la base de datos

(2)

Estructura básica

Formalmente, dados los conjuntos D₁, D₂, …. D_n una relación r es un subconjunto de D₁ x D₂ x … x D_n

Por tanto una relación es un conjunto de n-tuplas (a₁, a₂, …, a_n) donde a_i ∈ D_i

Ejemplo: si

nombre-cliente = {Gómez, Pérez, López, García} calle-cliente = {Gran_Vía, Norte, Retiro}

ciudad-cliente = {Huesca, Reus, Pamplona}

Entonces r = { (Gómez, Gran_Vía, Huesca), (Pérez, Norte, Reus),

(López, Norte, Reus),

(García, Retiro, Pamplona)}

(3)

Tipos de atributos

Cada atributo de una relación tiene un nombre

El conjunto de valores permitidos para cada atributo se conoce como el dominio del atributo

Se exige (normalmente) que los valores de los atributos sean

atómicos, esto es, indivisibles

Por ejemplo, los valores de los atributos multivalorados no son atómicos

Por ejemplo, los valores de los atributos compuestos no son atómicos

El valor especial null es un miembro de cada dominio

El valor nulo causa complicaciones en la definición de muchas operaciones

Ignoraremos el efecto de los valores nulos en nuestra presentación principal y consideraremos sus efectos posteriormente

(4)

Esquema de la relación

A₁, A₂, …, A_n son atributos

R = (A₁, A₂, …, A_n ) es un esquema de la relación Por ejemplo, esquema-cliente =

(nombre-cliente, calle-cliente, ciudad-cliente) r(R) es una relación en el esquema de la relación R

(5)

Instancia de relación

Los valores actuales (una instancia de la relación) de una relación se especifican con una tabla

Un elemento t de r es una tupla, representada por una fila en una tabla Gómez Pérez López García nombre-cliente Gran Vía Norte Norte Retiro calle-cliente Huesca Reus Reus Pamplona ciudad-cliente cliente Columnas (atributos) Filas (tuplas)

(6)

Bases de datos

Una base de datos está compuesta por múltiples relaciones

La información sobre una empresa se divide en partes. Cada relación contiene una parte de la información

Por ejemplo:

pedidos: contiene información sobre los pedidos

productos: contiene información sobre los productos

detalles_pedido: productos que contiene aparecen en cada pedido

Almacenar toda la información como una relación simple da como resultado

repetición de información (por ejemplo, dos clientes poseen una cuenta)

la necesidad de valores nulos (por ejemplo, representar un cliente sin cuenta)

La teoría de la normalización explica cómo diseñar esquemas relacionales

(7)

Diagrama E

-

R de Pedidos

ID_Empleado nombre Precio EMPLEADO calle Cantidad . PRODUCTO ID_Producto Existencias (1,1) PEDIDO CLIENTE ID_Cliente calle ciudad ID_Pedido N:M (0,n) (1,1) nombre ciudad N:1 1:N Atender Solicitar Detalles (0,n) (0,n) (1,m) fecha

(8)

Claves

Sea K ⊆ R

K es una superclave de R si los valores de K son suficientes

para identificar una tupla única de cada posible relación r(R) .Por “posible r” entendemos una relación r que pueda darse en la

empresa que tenemos como modelo.

Ejemplo: {nombre-cliente, calle-cliente} y {nombre-cliente}

son ambos superclaves de Cliente, siempre que no haya

posibilidad de que dos clientes puedan tener el mismo nombre. K es una clave candidata si K es mínimal

Ejemplo: {nombre-cliente} es una clave candidata para Cliente, ya que es una superclave (suponiendo que dos clientes no

pueden tener el mismo nombre), y que ninguno de sus subconjuntos es una superclave.

(9)

Determinación de claves desde los

conjuntos E

-

R

Conjunto de entidades fuertes. La clave primaria del conjunto de

entidades se convierte en la clave primaria de la relación.

Conjunto de entidades débiles. La clave primaria de la relación

consiste en la unión de la clave primaria de un conjunto de entidades fuertes y el discriminante del conjunto de entidades débiles.

Conjunto de relaciones. La unión de las claves primarias de los

conjuntos de entidades relacionadas se convierte en una superclave de la relación.

Para conjuntos de relaciones binarias varios-a-uno, la clave primaria de la entidad “varios” se convierte en la clave primaria de la relación.

Para los conjuntos de relaciones uno-a-uno la clave primaria de la relación puede ser la de cualquiera de las que componen el conjunto.

Para conjuntos de relaciones varios-a-varios, la unión de las claves primarias se convierte en la clave primaria de la relación

(10)

Lenguajes de consulta

Lenguajes en los que los usuarios solicitan información de la base de datos.

Categorías de los lenguajes

procedimental

no-procedimental

Lenguajes “puros”:

Álgebra relacional

Cálculo relacional de tuplas

Cálculo relacional de dominios

Los lenguajes puros forman las bases subyacentes de los lenguajes de consulta comerciales.

(11)

Álgebra relacional

Lenguaje procedimental Seis operaciones básicas

selección proyección unión diferencia de conjuntos producto cartesiano renombramiento

Las operaciones toman dos o más relaciones como entrada y producen como resultado una nueva relación.

(12)

Operación selección

Notación:

σ

_p

(r)

p se denomina predicado de la selección

Definido como:

σ

_p

(

r) = {t | t ∈ r y p(t)}

Donde p es una fórmula de cálculo proposicional

compuesta por términos conectados por: ∧ (y), ∨ (o), ¬ (no) Cada término es uno de:

<atributo> op <atributo> o <constante> donde op es uno de: =, ≠, >, ≥. <. ≤

(13)

Operación selección

–

Ejemplo

•

Relación r A B C D α α β β α β β β 1 5 12 23 7 7 3 10

• σ

_{A=B ^ D > 5}

(r)

A B C D α β α β 1 23 7 10

(14)

Operación proyección

Notación:

∏_{A1, A2, …, Ak} (r)

donde A₁, A₂ son nombres de atributos y r es un nombre de relación.

El resultado se define como la relación de k columnas obtenidas borrando las columnas que no aparecen

Las filas duplicadas se eliminan del resultado, ya que las relaciones son conjuntos

Por ejemplo, Para eliminar el atributo calle de clientes

(15)

Operación proyección

–

Ejemplo

Relación r: A B C α α β β 10 20 30 40 1 1 1 2 A C α α β β 1 1 1 2 = A C α β β 1 1 2 ∏_A,C (r)

(16)

Operación unión

Notación: r ∪ s

Definido como:

r ∪ s = {t | t ∈ r o t ∈ s}

Para que r ∪ s sea válido.

1. r, s deben tener la misma aridad (el mismo número de atributos)

2. El atributo dominio debe ser compatible (por ejemplo, 2ª columna de r maneja los mismos tipos de valores que la 2ª columna de s)

Por ejemplo, para encontrar todos los clientes con una cuenta o con un préstamo

(17)

Operación unión

–

Ejemplo

Relaciones r, s: r ∪ s: A B α α β 1 2 1 A B α β 2 3 r s A B α α β β 1 2 1 3

(18)

Operación diferencia de conjuntos

Notación r – s Definido como:

r – s = {t | t ∈ r y t ∉ s}

Las diferencias de conjuntos deben realizarse entre relaciones

compatibles.

r y s deben tener la misma aridad

(19)

Operación diferencia de conjuntos

–

Ejemplo

Relaciones r, s: r – s: A B α α β 1 2 1 A B α β 2 3 r s A B α β 1 1

(20)

Operación producto cartesiano

Notación r x s Definido como:

r x s = {t q | t ∈ r y q ∈ s}

Supone que los atributos de r(R) y s(S) son disjuntos. (Es decir,

R ∩ S = ∅).

Si los atributos de r(R) y s(S) no son disjuntos, se debe utilizar el renombramiento.

(21)

Operación producto cartesiano

-

-Ejemplo

Ejemplo

Relaciones r, s: r x s: A B α β 1 2 A B α α α α β β β β 1 1 1 1 2 2 2 2 C D α β β γ α β β γ 10 19 20 10 10 10 20 10 E a a b b a a b b C D α β β γ 10 10 20 10 E a a b b r s

(22)

Composición de operaciones

Permite obtener expresiones que utilizan operaciones múltiples Ejemplo: σ_A=C(r x s) r x s σA=C(r x s) A B α α α α β β β β 1 1 1 1 2 2 2 2 C D α β β γ α β β γ 10 19 20 10 10 10 20 10 E a a b b a a b b A B C D E α β 12 α β 1020 a a

(23)

Operación renombramiento

Nos permite nombrar y por lo tanto referirnos a los resultados de expresiones de álgebra relacional.

Nos permite referirnos a una relación por más de un nombre. Ejemplo:

ρ

_x (E)

devuelve la expresión E bajo el nombre X

Si una expresión de álgebra relacional E tiene aridad n, entonces

ρ

_x _{(A1, A2, …, An)}(E)

devuelve el resultado de la expresión E bajo el nombre X, y con los atributos renombrados como A1, A2, …., An.

(24)

Definición formal

Una expresión básica en el álgebra relacional se compone de una de las siguientes:

Una relación en la base de datos

Una relación constante

Sean E₁ y E₂ expresiones de álgebra relacional. Todas las siguientes son expresiones del álgebra relacional:

E₁ ∪ E₂

E₁ - E₂

E₁ x E₂

σ_p (E₁), P es un predicado de atributos de E₁

∏_s(E₁), S es una lista que se compone de alguno de los atributos de E₁

(25)

Operaciones adicionales

Definimos operaciones adicionales que no añaden potencia al álgebra relacional, pero que simplifican las consultas habituales. Intersección de conjuntos

Reunión natural División

(26)

Operación intersección de conjuntos

Notación: r ∩ s

Definido como:

r ∩ s ={ t | t ∈ r y t ∈ s }

Suponiendo:

r, s tienen la misma aridad

los atributos de r y s son compatibles

(27)

Operación intersección de conjuntos

-

-Ejemplo

Ejemplo

Relación r, s: r ∩ s A B α α β 1 2 1 A B α β 23 r _s A B α 2

(28)

Operación reunión natural

Notación: r s

Sean r y s relaciones de los esquemas R y S respectivamente. El resultado es una relación del esquema R ∪ S que se obtiene

considerando cada par de tuplas t_r de r y t_s de s.

Si t_r y t_s tienen el mismo valor en cada uno de los atributos de R ∩ S, se añade una tupla t al resultado, donde

t tiene el mismo valor que t_r en r

t tiene el mismo valor que t_s en s

Ejemplo:

R = (A, B, C, D) S = (E, B, D)

Esquema resultante = (A, B, C, D, E)

r s se define como:

(29)

Operación reunión natural

–

Ejemplo

Relaciones r, s: A B α β γ α δ 1 2 4 1 2 C D α γ β γ β a a b a b B 1 3 1 2 3 D a a a b b E α β γ δ ∈ r A B α α α α δ 1 1 1 1 2 C D α α γ γ β a a a a b E α γ α γ δ s r s

(30)

Operación división

Pensada para las consultas que incluyen la frase “para todos”.

Sean r y s relaciones de los esquemas R y S respectivamente, donde

R = (A₁, …, A_m, B₁, …, B_n)

S = (B₁, …, B_n)

El resultado de r ÷ s es una relación del esquema R – S = (A₁, …, A_m)

r ÷ s = { t | t ∈ ∏ _R-S(r) ∧ ∀ u ∈ s ( tu ∈ r ) }

(31)

Operación división

–

Ejemplo

Relaciones r, s: r ÷ s: A B α β 1 2 A B α α α β γ δ δ δ ∈ ∈ β 1 2 3 1 1 1 3 4 6 1 2 r s

(32)

Otro ejemplo de división

A B α α α β β γ γ γ a a a a a a a a C D α γ γ γ γ γ γ β a a b a b a b b E 1 1 1 1 3 1 1 1 Relaciones r, s: r ÷ s: D a b E 1 1 A B α γ aa C γ γ r s

(33)

Operación asignación

La operación asignación (←) proporciona una forma

conveniente de expresar consultas complejas, de escribir una consulta como un programa secuencial compuesto de series de asignaciones seguidas por una expresión cuyo valor se muestra como resultado de la consulta.

La asignación siempre debe realizarse a una variable de relación temporal.

Ejemplo: Escribir r ÷ s como

temp1← ∏_R-S (r)

temp2 ← ∏_R-S ((temp1 x s) – ∏_R-S,S(r)) result = temp1 – temp2

El resultado a la derecha de ← se asigna a la variable de relación situada a la izquierda de ←.

(34)

Ejemplo Pedidos

Clientes= (id_cli, nom_cli, calle, ciudad)

Empleados= (Id_emp,nom_emp, calle, ciudad)

Pedidos = (Id_pedido, Id_cli, Id_emp, fecha_pedido) Productos= (Id_pro,nom_pro, existencias, precio) Detalles_pedido=(Id_Pedido, Id_pro, cantidad)

Identificador y Nombre de los clientes de Mieres Nombres de clientes y empleados de Mieres Nombres de los productos vendidos por María Clientes que han pedido Patatas

Clientes que NO han pedido Patatas

Nombre de los productos que han sido pedidos por todos lo clientes

(35)

Operaciones de álgebra relacional

extendida

Proyección generalizada Reunión externa

(36)

Proyección generalizada

Extiende la operación de proyección al permitir que las funciones aritméticas se utilicen en la lista de proyección.

∏ _{F1, F2, …, Fn}(E)

E es cualquier expresión de álgebra relacional

F₁, F₂, …, F_n son expresiones aritméticas que implican constantes y atributos en el esquema de E.

Dada la relación productos(id_producto, nombre,

precio,existencias), averiguar cuánto dinero tengo en almacén

de cada producto:

(37)

Funciones de agregación y operaciones

La función de agregación toma una colección de valores y devuelve como resultado un valor simple.

avg: valor medio min: valor mínimo max: valor máximo sum: suma de valores count: número de valores

La operación de agregación en el álgebra relacional

G1, G2, …, Gn

g

F1( A1), F2( A2),…, Fn( An) (E)

E es cualquier expresión de álgebra relacional

G₁, G₂ …, G_n es una lista de atributos sobre los que se agrupa (puede estar vacía)

Cada F_i es una función agregada

(38)

Reunión externa

Una extensión de la operación de reunión que evita la pérdida de información.

Calcula la reunión y añade las tuplas de una relación que no coinciden con las de la otra relación al resultado de la reunión. Utiliza valores null:

null significa que el valor es desconocido o no existe

Todas las comparaciones que implican nulos son, generalmente hablando, falsas por definición.

Estudiaremos los significados concretos de las comparaciones con nulos más adelante

(39)

Reunión externa

–

Ejemplo

Relación propietario Id_propietario Id_Coche P1 P2 P3 C1 C2 Relación Coche Id_coche C1 C2 C3 O-1701-AD O-1234-BX M-1255-TU nombre Pepe Pepa Carlos matrícula

(40)

Reunión interna propietario Coche

Reunión externa

–

Ejemplo

propietario coche

Reunión externa por la izquierda

Id_Propietario Id_coche P1 P2 C1 C2 matrícula O-1701-AD O-1234-BX nombre Pepa Pepa Id_Propietario Id_coche P1 P2 P3 C1 C2 null matrícula O-1701-AD O-1234-BX null nombre Pepa Pepa Carlos

(41)

Reunión externa

–

Ejemplo

Reunión externa por la derecha propietario coche

propietario coche

Reunión externa completa

Id_Propietario Id_coche P1 P2 null C1 C2 C3 matrícula O-1701-AD O-1234-BX M-1255-TU nombre Pepa Pepa null Id_Propietario Id_coche P1 P2 P3 null C1 C2 null C3 matrícula O-1701-AD O-1234-BX null M-1255-TU nombre Pepa Pepa Carlos null

(42)

Equivalencias (I)

1. Cascada de proyecciones Si { A1, ..., An} ⊂ { B1, ..., Bm}

π

_{A1, ..., An}

(

π

_{B1, ..., Bm}

(R) =

π

_{A1, ..., An}

(R)

2. Cascada de selecciones

σ

_P1

(

σ

_P2

(R)) =

σ

_P2

(

σ

_P1

(R)) =

σ

_{P1 ^ P2}

(R)

3. Conmutación de selección y proyección

Si P sólo involucra a los atributos A1, ..., An, entonces:

(43)

Equivalencias (II)

4.- Conmutación de selección y producto cartesiano

Si todos los atributos que aparecen en P son atributos de R1

σ _P (R1 x R2) = σ _P (R1) x R2

Si P es de la forma P1 ^ P2, donde P1 involucra sólo atributos de R1 y P2 sólo atributos de R2:

σ _P (R1 x R2) = σ _P1 (R1) x σ _P2(R2)

Si P es de la forma P1 ^ P2, donde P1 involucra sólo atributos de R1 y P2 atributos de R1 y R2:

(44)

Equivalencias (III)

5.- Conmutación de selección y unión de conjuntos

σ _P (R1 U R2) = σ _P (R1) U σ _P(R2)

6.- Conmutación de selección y diferencia de conjuntos

σ _P (R1 - R2) = σ _P (R1) - σ _P(R2)

7.- Conmutación de proyección y producto cartesiano

Sea {A1, ... , An} un conjunto de atributos de los que los k primeros son de R1 y los restantes de R2

π _{A1, ..., An} ( R1 x R2) = π _{A1, ..., Ak} ( R1) x π _{Ak+1, ..., An} ( R2) 8.- Conmutación de proyección y unión de conjuntos

(45)

Valores nulos

Es posible que las tuplas tengan valores nulos para algunos de sus atributos, denotados como null

null significa valores desconocidos o no existentes.

El resultado de una expresión aritmética (+, -, *, /) que contenga un

null es null.

Las comparaciones con valores nulos devuelven el valor especial cierto desconocido

Si se utiliza falso en lugar de desconocido, entonces no (A < 5) no sería equivalente a A >= 5

(46)

Valores nulos

Tres valores lógicos utilizando el valor cierto desconocido:

O: (desconocido o cierto) = cierto,

(desconocido o falso) = desconocido (desconocido o desconocido) = desconocido

Y: (cierto y desconocido) = desconocido, (falso y desconocido) = falso,

(desconocido y desconocido) = desconocido

NO: (no desconocido) = desconocido

En SQL “P es desconocido” se evalúa como cierto si el predicado P se evalúa como desconocido

(47)

Valores nulos

OPERACIONES

El resultado de la selección (where) de un predicado se trata como falso si el predicado se evalúa como desconocido

La reunión (inner join) es un productos cartesiano más una selección (igual que antes). No se incluyen las tuplas que contengan algún valor nulo en los atributos comunes.

La proyección (select) trata los nulos como otro valor a la hora de eliminar duplicados. ¿desconocido=desconocido es cierto? La unión, intersección y diferencia funcionan igual que la

proyección aunque no parezca demasiado coherente. La proyección generalizada (group by , funciones de

(48)

Valores nulos

OPERACIONES

El resultado de la selección (where) de un predicado se trata como falso si el predicado se evalúa como desconocido

La reunión natural (inner join) es un producto cartesiano más una selección (igual que antes). No se incluyen las tuplas que contengan algún valor nulo en los atributos comunes.

La proyección (select) trata los nulos como otro valor a la hora de eliminar duplicados. ¿desconocido=desconocido es cierto?

La unión, intersección y diferencia funcionan igual que la proyección aunque no parezca demasiado coherente.

(49)

Valores nulos

OPERACIONES

En la proyección generalizada (group by , funciones de

agregación) las tuplas duplicadas se eliminan como en la

proyección. Cuando hay valores nulos en una columna sobre la que aplicamos una operación de agregación, se eliminan antes de aplicar la función. Si todos son nulos el resultado es nulo.

La reunión externa (outer join) funciona igual que la reunión natural y luego se añaden las tuplas que no aparecen en la otra tabla dependiendo de su se trata de un left, right o full,

(50)

Modificación de la base de datos

El contenido de la base de datos puede ser modificado utilizando las siguientes operaciones:

Borrado

Inserción

Actualización

Todas estas operaciones se expresan utilizando la operación de asignación.

(51)

Borrado

Una petición de borrado se expresa de forma similar a una

consulta, salvo que en lugar de mostrar las tuplas al usuario, el borrado elimina las tuplas seleccionadas de la base de datos. Sólo se pueden borrar tuplas completas, no se pueden borrar

valores solo en determinados atributos

Un borrado se expresa en álgebra relacional como:

r ← r – E

donde r es una relación y E es una consulta de álgebra relacional.

Ejemplo: Borrar todos los clientes de Mieres

(52)

Inserción

Para insertar datos en una relación, podemos:

Especificar una tupla para insertar

Escribir una consulta cuyo resultado sea un conjunto de tuplas para insertar

En álgebra relacional, una inserción se expresa como:

r ← r ∪ E

donde r es una relación y E es una expresión de álgebra relacional.

La inserción de una tupla simple se expresa tomando E como una relación constante que contiene una tupla.

(53)

Actualización

Un mecanismo para cambiar un valor en una tupla sin cambiar

todos los valores de la tupla

Utilizar la operación de proyección generalizada para realizar esta tarea

r ← ∏ _{F1, F2, …, FI,} (r)

Cada F, es o el iesimo atributo de r, si el iesimo atributo no está actualizado, o si el atributo tiene que ser actualizado

F_i es una expresión que contiene solo constantes y los atributos de r, que da el nuevo valor para el atributo

Ejemplo

(54)

Cálculo relacional de tuplas

Un lenguaje de consultas no procedimental, donde cada consulta tiene la forma

{t | P (t) }

Es el conjunto de todas las tuplas t tales que el predicado P es cierto para t

t es una variable tupla, t[A] denota el valor de la tupla t en el

atributo A

t ∈ r denota que la tupla t está en la relación r

(55)

Fórmula de cálculo del predicado

1. Conjunto de atributos y constantes

2. Conjunto de operadores de comparación: (por ejemplo, <, ≤, =,

≠, >, ≥)

3. Conjunto de conectadores: y (∧), o (v)‚ no (¬) 4. Conjunto de cuantificadores:

∃ t ∈ r (Q(t)) ≡ ” existe” una tupla en t en la relación r tal que el predicado Q(t) es cierto

(56)

Consultas de ejemplo

Productos que cuestan más de 10 €

{t | t ∈ productos ∧ t [precio] > 1200}

Nombres de los productos que cuestan más de 10 € {t | ∃ s ∈ productos (t[nombre] = s[nombre]

∧ s [precio] > 10}

Averiguar los nombres de todos los clientes que han realizado algún pedido el “13-Jun-05”

{t | ∃c ∈ clientes (t[nombre] = c[nombre])

∧ ∃p ∈ pedidos(c[id_cliente] = p[id_cliente] ∧ p[fecha]=“13-Jun-05”)