Sistemas de Información II Tema 6. Álgebra relacional

1 Sistemas de Información II Tema 6. Álgebra relacional Carlos Castillo UPF – 2008 Bibliografía: Elmasri y Navathe: “Fundamentos de Sistemas de Bases ...

57 downloads 264 Views 270KB Size
Sistemas de Información II

Tema 6. Álgebra relacional Bibliografía: Elmasri y Navathe: “Fundamentos de Sistemas de Bases de Datos” 3ª edición, 2002 (Capítulo 7). Garcia-Molina, Ullman y Widom: “Database systems: the complete book”. Prentice-Hall (Capítulo 5). Carlos Castillo UPF – 2008 1

Operaciones Proyectar () Seleccionar () Producto cartesiano (×) Join ó Reunir ( ) Operaciones de conjuntos Unir (∪) Intersectar (∩) Restar (–) 2

Proyección () Selecciona el valor de ciertos atributos de todas las tuplas de una relación A1,A2,...,An(R) = { t[A1,A2,...,An] : t ∈ R }

Selecciona columnas completas

3

Proyección () ejemplos Película ID_Película 1 2 3 4

Nombre

La guerra de las galaxias El señor de los anillos 1 Mar Adentro El viaje de Chihiro

Año

1977 2001 2004 2001

Actor ID_Actor 1 2 3 4

Nombre

Mark Cristopher Javier Hugo

Apellido Hamill Lee Bardem Weaving

Año(Película) = {<1977>,<2001>,<2004>,<2001>} ID_Película,Año(Película) = {<1,1977>,<2,2001>,<3,2004>,<4,2001>} Nombre(Actor) = {,,,} 4

Proyección () en SQL

A1,A2,...,An(R)

SELECT A1,A2,...,An FROM R

5

Selección () Selecciona el valor de ciertas tuplas condición(R) = { t∈R : condición(t) es cierto}

Selecciona filas completas

6

Selección () ejemplos Película ID_Película 1 2 3 4

Nombre

La guerra de las galaxias La comunidad del anillo Mar Adentro El viaje de Chihiro

Año

1977 2001 2004 2001

Actor ID_Actor 1 2 3 4

Nombre

Mark Cristopher Javier Hugo

Apellido Hamill Lee Bardem Weaving

Apellido=Lee(Actor) = {<2,Cristopher,Lee>} Año>2000(Película) = {<2,La comunidad del anillo,2001>, <4,El viaje de Chihiro,2001>}

7

Selección () en SQL

condición(R)

SELECT * FROM R WHERE condición

8

Composición de selección y proyección , Película ID_Película 1 2 3 4

Nombre

La guerra de las galaxias La comunidad del anillo Mar Adentro El viaje de Chihiro

Año

1977 2001 2004 2001

Actor ID_Actor 1 2 3 4

Nombre

Mark Cristopher Javier Hugo

Apellido Hamill Lee Bardem Weaving

Nombre(Apellido=Lee(Actor)) = {} Nombre(Año>2000(Película)) = {, }

9

Composición ( y ) en SQL

A1,A2,...,An(condición(R))

SELECT A1,A2,...,An FROM R WHERE condición

10

Eliminar duplicados () Elimina tuplas duplicadas en una relación Película ID_Película 1 2 3 4

Nombre

La guerra de las galaxias La comunidad del anillo Mar adentro El viaje de Chihiro

(R)

Año

1977 2001 2004 2001

ID_Estudio 3 2 4 1

(Año(Película) = { 1997, 2001, 2004 }

11

Operación delta en SQL

(R)

SELECT DISTINCT * FROM R

12

Producto cartesiano (×) A × B = {(a,b): a ∈ A ∧ b ∈ B} Ejemplo: A = {s,t} B = {u,v,w} A × B = {s,t} × {u,v,w} { (s,u),(s,v),(s,w),(t,u),(t,v),(t,w) }

La cardinalidad es |A × B| = |A||B| 13

Producto cartesiano (×) ejemplos Película ID_Película 1 2 3 4

Nombre

La guerra de las galaxias La comunidad del anillo Mar adentro El viaje de Chihiro

Año

1977 2001 2004 2001

ID_Estudio 3 2 4 1

Estudio ID_Estudio 1 2 3 4

Nombre

Ghibli New Line Cinema Lucasfilms Sogecine

Película× Estudio = { <1,La guerra de las galaxias,1977,3,1,Ghibli>, <1,La guerra de las galaxias,1977,3,2,New Line Cinema>, <1,La guerra de las galaxias,1977,3,3,Lucasfilms>, <1,La guerra de las galaxias,1977,3,4,Sogecine>, <2,La comunidad del anillo,2001,2,1,Ghibli>, <2,La comunidad del anillo,2001,2,2,New Line Cinema>, <2,La comunidad del anillo,2001,2,3,Lucasfilms>, <2,La comunidad del anillo,2001,2,4,Sogecine>, <3,Mar adentro,2004,4,1,Ghibli>, <3,Mar adentro,2004,4,2,New Line Cinema>, ... }

14

Producto cartesiano (×) en SQL

R1×R2

SELECT * FROM R1,R2

15

Seleccionar combinaciones correctas Película ID_Película 1 2 3 4

Nombre

La guerra de las galaxias La comunidad del anillo Mar adentro El viaje de Chihiro

Año

1977 2001 2004 2001

ID_Estudio 3 2 4 1

Estudio ID_Estudio 1 2 3 4

Nombre

Ghibli New Line Cinema Lucasfilms Sogecine

Película.ID_estudio=Estudio.ID_Estudio(Película× Estudio) = { <1,La guerra de las galaxias,1977,3,3,Lucasfilms>, <2,La comunidad del anillo,2001,2,2,New Line Cinema>, <3,Mar adentro,2004,4,4,Sogecine>, <4,El viaje de Chihiro,2001,1,1,Ghibli> }

16

Seleccionar combinaciones correctas en SQL R1.k=R2.k(R1×R2)

SELECT * FROM R1,R2 WHERE R1.k=R2.k

17

Notación, operación Reunir (JOIN) R1.k=R2.k (R1×R2)

R1

k

R2

18

Operación JOIN en SQL

R1

k

R2

SELECT * FROM R1,R2 WHERE R1.k=R2.k

19

Operación JOIN en MySQL

R1

k

R2

SELECT * FROM R1 JOIN R2 USING(k)

20

JOIN natural R1

R2

Omitir el subíndice significa: Unir según todos los atributos que tengan el mismo nombre en las dos tablas

21

Operación NATURAL JOIN en MySQL R1

R2

SELECT * FROM R1 NATURAL JOIN R2

Nota: esto usa todos los atributos que se llamen de la misma manera, a veces no es lo que nosotros queremos  Comunitat( id_comunitat, nom ) Municipi( id_municipi, id_comunitat, nom ) Queremos unir id_comunitat pero no nom

22

Ejemplo de NATURAL JOIN mysql> select comunitat.nom, municipi.nom, municipi.superficie from comunitat natural join municipi; +---------+---------+------------+ | nom | nom | superficie | +---------+---------+------------+ | Ceuta | Ceuta | 19.52 | | Melilla | Melilla | 13.96 | +---------+---------+------------+ 2 rows in set (0.14 sec) mysql> select comunitat.nom, municipi.nom, municipi.superficie from comunitat join municipi using(ca_id); +-----------+----------+------------+ | nom | nom | superficie | +-----------+----------+------------+ | Andalucía | Abla | 45.28 | | Andalucía | Abrucena | 83.18 | | Andalucía | Adra | 89.98 | ... 23

LEFT JOIN JOIN elimina algunos datos Los que no están en las dos tablas

LEFT JOIN reemplaza los eliminados por valores nulos en la tabla de la izquierda

24

Operación LEFT JOIN en MySQL

R1

k

R2

SELECT * FROM R1 LEFT JOIN R2 USING(k)

25

Ejemplo LEFT JOIN Película ID_Película 1 2 3 4

Nombre

La guerra de las galaxias La comunidad del anillo Mar adentro El viaje de Chihiro

Año

1977 2001 2004 2001

ID_Estudio 3 2 4 1

SELECT count(id_pelicula) AS CNT FROM estudio JOIN pelicula USING (id_estudio)

SELECT count(id_pelicula) AS CNT FROM estudio LEFT JOIN pelicula USING (id_estudio)

Estudio ID_Estudio 1 2 3 4 5

1 1 1 1

1 1 1 1 0

Nombre

Ghibli New Line Cinema Lucasfilms Sogecine Nuevo Estudio

CNT

CNT

Nombre

Ghibli New Line Cinema Lucasfilms Sogecine

Nombre

Ghibli New Line Cinema Lucasfilms Sogecine Nuevo Estudio

26

Otro ejemplo LEFT JOIN Ciudad id_ciudad 1 2 3 4 5

Viaje id_salida Nombre

Barcelona Berlin Roma Paris Budapest

1 1 5 5 5 5 2

SELECT ciudad.nombre,COUNT(viaje.id_salida) FROM ciudad LEFT JOIN viaje ON (ciudad.id_ciudad=viaje.id_salida) GROUP BY ciudad.nombre;

2 4 3 4 2 1 4

id_llegada

Nombre

Barcelona Berlin Budapest Paris Roma

2 1 4 0 0

CNT

27

Ejemplo múltiples JOIN Ciudad id_ciudad 1 2 3 4 5

Viaje id_salida Nombre

Barcelona Berlin Roma Paris Budapest

SELECT cs.nombre, cl.nombre FROM viaje JOIN ciudad AS cs ON (viaje.id_salida=cs.id_ciudad) JOIN ciudad AS cl ON (viaje.id_llegada=cl.id_ciudad);

1 1 5 5 5 5 2

2 4 3 4 2 1 4

id_llegada

Nombre

Barcelona Barcelona Budapest Budapest Budapest Budapest Berlin

Nombre

Berlin Paris Roma Paris Berlin Barcelona Paris

28

Resumen Proyectar (): elegir columnas Seleccionar (): criterio para las filas Producto cartesiano (×): producto tablas Join ó Reunir ( ): combinar tablas

29