Algebra relacional 2.

Capítulo 3 Álgebra relacional

1

Álgebra relacional El álgebra relacional es un lenguaje teorético con operadores que trabajan en una o dos relaciones para producir otra relación. Así que ambos operandos y el resultado son tablas.

Hay varias variaciones de sintaxis de comandos de álgebra relacional, aquí se usará una sencilla y se presenta informalmente. Hay también muchas variaciones de las operaciones que están incluidas en el álgebra relacional. C.J. Date originalmente propuso ocho operaciones, pero se han desarrollado otras. Tres operaciones básicas SELECT, PROJECT y JOIN, permiten realizar la mayoría de las operaciones de consulta de datos que nos interesan.

Se usará como ejemplo la base de datos universidad de la figura 1 para ilustrar estas operaciones.

El operador SELECT El operador SELECT trabaja sobre una sola tabla y toma las filas que satisfacen una condición especificada, copiándolas a una nueva tabla. La forma general es: SELECT table-name WHERE condition [GIVING new-table-name] Simbólicamente, la forma es: σpredicado(table-name) Nótese que las operaciones se realizan en la tabla existente, nombrada “table-name”, y produce una nueva tabla la cual es un subconjunto horizontal de la tabla “vieja” y a la cual se le puede nombrar ”new-table-name”. Los paréntesis cuadrados indican que la parte del comando que encierran es opcional. La tabla “vieja” continúa existiendo con su nombre, y ésta y la nueva tabla están disponibles para operaciones adicionales. Si simplemente se desea encontrar y desplegar las filas que satisfacen la condición, pero no se planean operaciones posteriores sobre la nueva tabla, se omite su nombre.

Por ejemplo, si se desea encontrar toda la información en la tabla STUDENT acerca del estudiante S1013, se podría escribir: SELECT STUDENT WHERE STUID = ‘S1013’ GIVING RESULT o simbólicamente: σstuid = ‘S1013’(STUDENT) Bases de datos I

Arturo Ruvalcaba

Capítulo 3 Álgebra relacional

2

La operación produce una nueva tabla a la que se llamará RESULT, la cual es similar a la siguiente: RESULT

STUID S1013

STUNAME McCarthy, Owen

MAJOR Math

CREDITS 0

STUDENT

STUID S1015 S1005 S1001 S1010 S1002 S1013 S1020

STUNAME Jones, Mary Lee, Perry Smith, Tom Burns, Edgard Chin, Ann McCarthy, Owen Rivera, Jane

MAJOR Math History History Art Math Math CSC

CREDITS 42 3 90 63 36 0 15

CLASS

COURSE# ART103A HST205A CSC201A MTH101B CSC203A MTH103C

FACID F101 F115 F105 F110 F105 F110

SCHED MWF9 MWF11 TuThF10 MTuTh9 MThF12 MWF11

ROOM H221 H221 M110 H225 M110 H225

FACULTY

FACID F101 F115 F105 F110 F221

FACNAME Adams Smith Tanaka Byrne Smith

DEP. Art History CSC Math CSC

RANK Professor Associate Instructor Assistant Professor

ENROLLMENT

COURSE# ART103A CSC201A CSC201A ART103A ART103A MTH101B HST205A MTH103C MTH103C

STUID S1001 S1020 S1002 S1010 S1002 S1020 S1001 S1010 S1002

GRADE A B F D A C B

Figura 1. Instancia de la base de datos Universidad

La operación SELECT se puede usar para encontrar más que una fila. Por ejemplo, para obtener todas las clases que se imparten en el salón H225, se podría escribir: SELECT CLASS WHERE ROOM = ´H225’ GIVING ANSWER

Bases de datos I

Arturo Ruvalcaba

Capítulo 3 Álgebra relacional

3

o simbólicamente: σROOM = ‘H225’(CLASS) Esta operación produce la siguiente tabla: CLASS

COURSE# MTH101B MTH103C

FACID F110 F110

SCHED MTuTh9 MWF11

ROOM H225 H225

Se pueden formar condiciones más complicadas usando los siguientes símbolos, que tienen el significado usual: <, <=, >, >=, =, AND, OR, NOT Por ejemplo, para encontrar todas las asignaturas principales de matemáticas que tengan más de 30 créditos se escribe: SELECT STUDENT WHERE MAJOR = ‘Math’ AND CREDITS > 30 o simbólicamente: σMAJOR = ‘Math’ AND CREDITS > 30(STUDENT) Esta instrucción produce la siguiente tabla sin nombre: STUID S1015 S1002

STUNAME Jones, Mary Chin, Ann

MAJOR Math Math

CREDITS 42 36

El operador PROJECT El operador PROJECT también opera en una sola tabla, pero produce un subconjunto vertical de la tabla, extrayendo los valores de las columnas especificadas, eliminando duplicados y colocando los valores en una nueva tabla, su forma es: PROJECT table-name OVER (col-name, . . ., col-name) [GIVING new-table-name] o simbólicamente: ∏col-name, . . . , col-name(table-name) Para ilustrar la proyección sobre una sola columna, se obtendrán todas las diferentes asignaturas principales que los estudiantes han declarado mediante la instrucción: PROJECT STUDENT OVER MAJOR LIVING TEMP o ∏MAJOR(STUDENT)

Bases de datos I

Arturo Ruvalcaba

Capítulo 3 Álgebra relacional

4

La tabla resultante TEMP es la siguiente: TEMP

MAJOR Math History Art CSC

Nótese que se obtienen todos los valores que aparecen en la columna MAJOR de la tabla STUDENT, pero se han eliminado los duplicados.

Cuando se proyecta sobre dos o más columnas, se eliminan los duplicados de combinaciones de valores. Por ejemplo, supóngase que se quiere encontrar todos los salones en los que enseñan los miembros de la facultad. Se puede realizar usando el siguiente comando: PROJECT CLASS OVER (FACID, ROOM) o ∏FACID, ROOM(CLASS) Lo cual da los siguientes resultados: FACID F101 F115 F105 F110

ROOM H221 H221 M110 H225

Mientras que se tienen repeticiones en los valores de salón, con H221 apareciendo dos veces, se observa que los valores repetidos aparecen con diferentes valores de FACID. Mientras que la combinación no haya aparecido antes, se agrega a la proyección. Nótese que las dos últimas filas de la tabla CLASS no contribuyen al resultado de la proyección, puesto que su combinación de valores para FACID y ROOM aparecieron previamente.

Se pueden combinar las operaciones SELECT y PROJECT, pero el hacerlo requiere dos pasos. Por ejemplo, supóngase que se desea obtener los nombres e IDs de todas las asignaturas principales de historia.

Puesto que solo se quieren las asignaturas principales de historia, se necesita hacer un SELECT. Sin embargo, dado que se quieren solo ciertas columnas, se requiere hacer un proyección. Se puede expresar la consulta como: SELECT STUDENT WHERE MAJOR = ‘History’ GIVING TEMP PROJECT TEMP OVER (STUNAME, STUID) GIVING RESULT Después de que se ejecuta el primer comando, se tiene la tabla TEMP

Bases de datos I

STUID S1005 S1001

STUNAME Lee, Perry Smith, Tom

MAJOR History History

CREDITS 3 90

Arturo Ruvalcaba

Capítulo 3 Álgebra relacional

5

El segundo comando se realiza sobre esta tabla temporal, y el resultado es: RESULT

STUNAME Lee, Perry Smith, Tom

STUID S1005 S1001

Se puede observar que el operador PROJECT permite invertir el orden de las dos columnas en el resultado final. Se podrían haber escrito los comandos simbólicamente como: ∏STUNAME, STUID(σMAJOR = ‘History’(STUDENT) Nótese que se pueden formar las operaciones usando el resultado de la primera como el argumento de la segunda. La tabla intermedia resultante de la operación de selección, la cual se llamó TEMP cuando se usó una sintaxis similar al lenguaje inglés, no necesita un nombre cuando se usa la notación simbólica, debido a que se usa la expresión en su lugar.

Los operadores Producto, Theta Join, Equijoin, Natural Join, Semijoin y Outerjoin Si se tienen dos tablas A y B, se puede formar su producto, escrito A TIMES B o A x B, del mismo modo que se forma el producto cartesiano de conjuntos. A x B es una tabla cuya anchura es la anchura de A más la anchura de B y cuyas columnas son las columnas de A seguidas de las columnas de B. Se puede formar de varios modos. Un método es empezar con la primer fila de A, combinarla con la primera fila de B, luego con la segunda fila de B, continuando de esta manera hasta que se han formado todas las combinaciones de la primera fila de A con todas las filas de B. Después el procedimiento se repite para la segunda fila de A, y luego para la tercera y así sucesivamente. Si A tiene x filas y B tiene y filas, entonces A x B tiene xy filas.

Supóngase que se forma el producto de STUDENT y ENROLLMENT, escrito STUDENT x ENROLLMENT. Esta tabla tendrá siete columnas, pero dos de ellas se llamarán STUID. Para distinguir estas dos, se usan los nombres calificados de las tablas originales, STUDENT.STUID y ENROLLMENT.STUID. El producto tendrá 63 filas y se muestra en la figura 2.

Se pueden definir varias operaciones en el conjunto de tablas. La más general es la llamada THETA JOIN. “Theta” es la letra griega usada con frecuencia por los matemáticos para representar cualquier clase de operador, y fue usada por los desarrolladores de esta teoría para representar cualquiera de los operadores de comparación. La theta join se define como el resultado de realizar una operación de selección usando un operador de comparación theta, el cual puede ser <, <= , etc. en el producto. Por ejemplo, se podría querer solo esas tuplas del producto donde los valores de CREDITS sean mayores de 50. Aquí theta es >, y se podría escribir la consulta como: STUDENT TIMES ENROLLMENT WHERE CREDITS > 50 Esto es equivalente a: STUDENT TIMES ENROLLMENT GIVING TEMP SELECT TEMP WHERE CREDITS > 50 o simbólicamente:

Bases de datos I

σcredits > 50(STUDENT x ENROLLMENT)

Arturo Ruvalcaba

Capítulo 3 Álgebra relacional

STUDENT. STUID S1015 S1015 S1015 S1015 S1015 S1015 S1015 S1015 S1015 S1005 S1005 S1005 S1005 S1005 S1005 S1005 S1005 S1005 S1001 S1001 S1001 S1001 S1001 S1001 S1001 S1001 S1001 S1010 S1010 S1010 S1010 S1010 S1010 S1010 S1010 S1010 S1002 S1002 S1002 S1002 S1002 S1002 S1002 S1002 S1002 S1013 S1013 S1013 S1013 S1013 S1013

STUNAME Jones, Mary Jones, Mary Jones, Mary Jones, Mary Jones, Mary Jones, Mary Jones, Mary Jones, Mary Jones, Mary Lee, Perry Lee, Perry Lee, Perry Lee, Perry Lee, Perry Lee, Perry Lee, Perry Lee, Perry Lee, Perry Smith, Tom Smith, Tom Smith, Tom Smith, Tom Smith, Tom Smith, Tom Smith, Tom Smith, Tom Smith, Tom Burns, Edward Burns, Edward Burns, Edward Burns, Edward Burns, Edward Burns, Edward Burns, Edward Burns, Edward Burns, Edward Chin, Ann Chin, Ann Chin, Ann Chin, Ann Chin, Ann Chin, Ann Chin, Ann Chin, Ann Chin, Ann McCarthy, Owen McCarthy, Owen McCarthy, Owen McCarthy, Owen McCarthy, Owen McCarthy, Owen

Bases de datos I

6

MAJOR Math Math Math Math Math Math Math Math Math History History History History History History History History History History History History History History History History History History Art Art Art Art Art Art Art Art Art Math Math Math Math Math Math Math Math Math Math Math Math Math Math Math

CREDITS 42 42 42 42 42 42 42 42 42 3 3 3 3 3 3 3 3 3 90 90 90 90 90 90 90 90 90 63 63 63 63 63 63 63 63 63 36 36 36 36 36 36 36 36 36 0 0 0 0 0 0

ENROLLMENT. COURSE# STUID ART103A S1001 CSC201A S1020 CSC201A S1002 ART103A S1010 ART103A S1002 MTH101B S1020 HST205A S1001 MTH103C S1010 MTH103C S1002 ART103A S1001 CSC201A S1020 CSC201A S1002 ART103A S1010 ART103A S1002 MTH101B S1020 HST205A S1001 MTH103C S1010 MTH103C S1002 ART103A S1001 CSC201A S1020 CSC201A S1002 ART103A S1010 ART103A S1002 MTH101B S1020 HST205A S1001 MTH103C S1010 MTH103C S1002 ART103A S1001 CSC201A S1020 CSC201A S1002 ART103A S1010 ART103A S1002 MTH101B S1020 HST205A S1001 MTH103C S1010 MTH103C S1002 ART103A S1001 CSC201A S1020 CSC201A S1002 ART103A S1010 ART103A S1002 MTH101B S1020 HST205A S1001 MTH103C S1010 MTH103C S1002 ART103A S1001 CSC201A S1020 CSC201A S1002 ART103A S1010 ART103A S1002 MTH101B S1020

GRADE A B F D A C B A B F D A C B A B F D A C B A B F D A C B A B F D A C B A B F D A

Arturo Ruvalcaba

Capítulo 3 Álgebra relacional

S1013 S1013 S1013 S1020 S1020 S1020 S1020 S1020 S1020 S1020 S1020 S1020

7

McCarthy, Owen McCarthy, Owen McCarthy, Owen Rivera, Jane Rivera, Jane Rivera, Jane Rivera, Jane Rivera, Jane Rivera, Jane Rivera, Jane Rivera, Jane Rivera, Jane

Math Math Math CSC CSC CSC CSC CSC CSC CSC CSC CSC

0 0 0 15 15 15 15 15 15 15 15 15

HST205A MTH103C MTH103C ART103A CSC201A CSC201A ART103A ART103A MTH101B HST205A MTH103C MTH103C

S1001 S1010 S1002 S1001 S1020 S1002 S1010 S1002 S1020 S1001 S1010 S1002

C B A B F D A C B

Figura 2. Student Times Enrollment

El símbolo |x|Ө se usa algunas veces para representar a theta join. Al usarse, el símbolo Ө se remplaza por el predicado actual que se usa para la selección. Se hace notar que para dos relaciones cualesquiera, X y Y, theta join se define simbólicamente como: A|x|Ө B = σ Ө (A x B) Dado que el producto es una operación lenta que requiere, en el ejemplo de arriba, 63 concatenaciones, sería más eficiente realizar la selección primero y luego hacer el producto, previendo que la secuencia de operaciones de el mismo resultado. Una forma más eficiente que requiere solo 18 concatenaciones es: SELECT STUDENT WHERE CREDITS > 50 GIVING TEMP2 TEMP2 TIMES ENROLLMENT o simbólicamente: (σCREDITS > 50(STUDENT)) x ENROLLMENT Uno se podría preguntar porqué se está interesado en formar concatenaciones de filas de STUDENT con filas de ENROLLMENT que tienen diferentes STUID. La respuesta verdadera es que usualmente no se tiene ese interés. Por lo tanto, una operación más común que involucre el producto de tablas es el que se pediría para solo esas filas del producto en las cuales los valores de las columnas comunes son iguales.

STUDENT. STUID S1001 S1001 S1010 S1010 S1002 S1002 S1002 S1020 S1020

STUNAME Smith, Tom Smith, Tom Burns, Edward Burns, Edward Chin, Ann Chin, Ann Chin, Ann Rivera, Jane Rivera, Jane

MAJOR History History Art Art Math Math Math CSC CSC

CREDITS 90 90 63 63 36 36 36 15 15

ENROLLMENT. COURSE# STUID ART103A S1001 HST205A S1001 ART103A S1010 MTH103C S1010 CSC201A S1002 ART103A S1002 MTH103C S1002 CSC201A S1020 MTH101B S1020

GRADE A C F D B B A

Figura 3. Student Equijoin Enrollment

Bases de datos I

Arturo Ruvalcaba

Capítulo 3 Álgebra relacional

8

Cuando el theta es de igualdad, se tiene el EQUIJOIN de tablas. Para formar el equijoin, entonces, se empieza con dos tablas que tienen una columna o columnas comunes. Se compara cada tupla de la primera con cada tupla de la segunda y se eligen solo esas concatenaciones en las cuales los valores en las columnas comunes son iguales. Se formaría el equijoin de STUDENT y ENROLLMENT eligiendo esas tuplas del producto con valores de STUID iguales. La figura 3.3 muestra STUDENT EQUIJOIN ENROLLMENT escrito simbólicamente como STUDENT |x| STUDENT.STUID = ENROLLMENT.STUID ENROLLMENT Nótese que esto es equivalente a: STUDENT TIMES ENROLLMENT GIVING TEMP3 SELECT TEMP3 WHERE STUDENT.STUID = ENROLLMENT.STUID o σ STUDENT.STUID = ENROLLMENT.STUID(STUDENT X ENROLLMENT) Si se tuvieran más de una columna común, ambos conjuntos de valores tendrían que ser iguales.

Se puede notar que, por definición, siempre se tiene al menos dos columnas idénticas en un equijoin. Puesto que parece innecesario incluir las columnas repetidas, se define un NATURAL JOIN como un equijoin en el cual las columnas repetidas se eliminan. Esta es la forma más común de la operación JOIN, tan común, de hecho, que esto es lo que usualmente se entiende por JOIN. Cuando se quiere decir el natural join simplemente se escribe: table-name-1 JOIN table-name-2 [GIVING new-table-name]

o JOIN table-name-1, table-name-2 [GIVING new-table-name]

Se puede usar el símbolo |x| para el natural join como en: table-name-1 |x| table-name-2 Aunque normalmente el JOIN se realiza sobre columnas con los mismos nombres, todo lo que realmente se requiere es que los dominios de los atributos sean los mismos. Entonces, se podría hacer un join en el cual número de créditos de un estudiante se compare con la edad de un miembro de la facultad, puesto que ellos tendrían el mismo dominio. Dado que esto no tiene mucho sentido, usualmente se requiere que las columnas tengan el mismo significado semántico. Si hay alguna confusión sobre las columnas a ser comparadas, se podrían incluir sus nombres, usando una instrucción como la siguiente: JOIN table-name-1, table-name-2 OVER col-1, col-2

o simbólicamente table-name-1 |x| col-1, col-2 table-name-2 Bases de datos I

Arturo Ruvalcaba

Capítulo 3 Álgebra relacional

9

El natural join de STUDENT y ENROLLMENT produciría una tabla idéntica a la de la figura 3, excepto que la segunda columna STUID se eliminaría. Dado que FACULTY y CLASS tienen una columna común, FACID, se podría obtener su natural join. El resultado de la instrucción: JOIN FACULTY, CLASS o FACULTY |x| CLASS se muestra en la figura 4.

FACID F101 F115 F105 F105 F110 F110

FACNAME Adams Smith Tanaka Tanaka Byrne Byrne

DEP. Art History CSC CSC Math Math

RANK Profesor Associate Instructor Instructor Assistant Assistant

COURSE# ART103A HST205A CSC201A CSC201A MTH101B MTH103C

SCHED MWF9 MFW11 TuThF10 MThF12 MTuTh9 MWF11

ROOM H221 H221 M110 M110 H225 H225

Figura 4. Natural Join of Faculty and Class

La tabla resultante da todos los detalles sobre los miembros de la facultad y las clases que ellos enseñan. No se tienen los datos sobre la facultad y las clases en una sola tabla, debido a que se tendrían muchas repeticiones en la tabla. Nótese que los datos sobre Tanaka y Byrne se repiten, debido a que cada uno de ellos imparte dos clases. El join permite recombinar piezas de información sobre una entidad, aunque ellas aparezcan en diferentes tablas.

Consultas más complicadas requieren el uso de los comandos SELECT, PROJECT y JOIN. Por ejemplo, supóngase que se quiere encontrar las clases y las calificaciones de la estudiante Ann Chin. Se observa que la tabla STUDENT contiene los nombres de estudiantes pero no las clases o calificaciones, mientras que la tabla ENROLLMENT contiene las clases y las calificaciones pero no los nombres. Siempre que se necesite usar más de una tabla para responder a una consulta, se debe usar un JOIN. Si el resultado debe contener solo algunas, no todas las columnas de las tablas originales, se necesita un PROJECT. Si no se van a usar todas las filas, se necesita un SELECT. Para decidir que operaciones hacer y en que orden, se examinan las tablas para ver como se respondería a la pregunta “a mano” y entonces intentar formular las operaciones requeridas en términos de operaciones de álgebra relacional. En este caso, se empezaría con la tabla STUDENT y se buscaría el valor del campo STUID de Ann Chin. Después se consultaría la tabla ENROLMENT, buscando los registros con el mismo valor en el campo STUID y se leerían los valores de los cursos y las calificaciones. Un modo de encontrar los datos requeridos es la sigiuente: SELECT STUDENT WHERE STUNAME = ‘Chin, Ann’ GIVING TEMP1 JOIN TEMP1, ENROLLMENT GIVING TEMP2 PROJECT TEMP2 OVER (COURSE#, GRADE) GIVING ANSWER Después de realizarse la primera operación de SELECT se tendrá este resultado:

Bases de datos I

Arturo Ruvalcaba

Capítulo 3 Álgebra relacional

TEMP1

10

STUID S1002

STUNAME Chin, Ann

MAJOR Math

CREDITS 36

Después del JOIN en la segunda instrucción se tendrá el siguiente resultado: TEMP2

STUID S1002 S1002 S1002

STUNAME Chin, Ann Chin, Ann Chin, Ann

MAJOR Math Math Math

CREDITS 36 36 36

COURSE# CSC201A ART103A MTH103C

GRADE F D B

Después de PROJECT en la tercera instrucción, se tendrá este resultado: ANSWER

COURSE# CSC201A ART103A MTH103C

GRADE F D B

Simbólicamente, el query se podría expresar como: ∏COUSE#, GRADE((σ STUNAME=’Chin, Ann’(STUDENT)) |x| ENROLLMENT) Se puede observar que todo lo que realmente se necesitaba de TEMP1 era la columna de STUID, puesto que ésta fue usada para la comparación del join y ninguna de las otras columnas se utilizó después. Por lo tanto si se prefiere, se podría usar un PROJECT en TEMP1 para obtener solo la columna STUID antes de hacer el JOIN. Una manera diferente de realizar el query es: JOIN STUDENT, ENROLLMENT GIVING TEMPA SELECT TEMPA WHERE STUNAME = ‘Chin, Ann’ GIVING TEMPB PROJECT TEMPB OVER (COURSE#, GRADE) o ∏COUSE#, GRADE(σ STUNAME=’Chin, Ann’(STUDENT |x| ENROLLMENT)) Como se puede observar, este método es menos eficiente, puesto que la primera línea requiere 54 comparaciones para formar la tabla resultante del join, una operación relativamente lenta. Al hacer la selección primero se reduce el número de comparaciones a nueve, lo que optimiza el query.

Se pueden hacer joins de tablas resultantes de un join. Supóngase que se desea encontrar los IDs de todos los estudiantes en las clases de profesor Adams. Se necesitarían datos de las tablas FACULTY, CLASS y ENROLLMENT para responder este query. Un método es: SELECT FACULTY WHERE FACNAME = ‘Adams’ LIVING TEMP1 JOIN TEMP1, CLASS GIVING TEMP2 JOIN TEMP2, ENROLLMENT GIVING TEMP3 PROJECT TEMP3 OVER STUID GIVING RESULT Bases de datos I

Arturo Ruvalcaba

Capítulo 3 Álgebra relacional

11

o simbólicamente: ∏STUID(((σ FACNAME=’Adams’(FACULTY)) |x| CLASS) |x| ENROLLMENT) En este ejemplo, TEMP2 tiene siete columnas y TEMP3 tiene nueve columnas. Dado que solo una de las columnas de FACULTY, que es FACID, se utiliza para el join y ninguna de sus otras columnas se utilizará después, se podría hacer un PROJECT antes del primer join.

Alternativamente, dado que solo una de las columnas de TEMP2, COURSE#, se necesita para el join y ninguna de sus otras columnas se usa nuevamente, se podría hacer un PROJECT entre los dos joins.

Hay varios otros tipos de operaciones join que se pueden definir. Una variación es el SEMIJOIN de dos tablas. Si A y B son tablas, entonces el semijoin de A por B se puede encontrar tomando el natural join de A y B y después proyectando el resultado sobre los atributos de A. Para las tablas de STUDENT y ENROLLMENT mostradas en la figura 1, el semijoin de STUDENT por ENROLLMENT, escrito como: STUDENT SEMIJOIN ENROLLMENT o simbólicamente STUDENT |x ENROLLMENT Se muestra en la figura 5. Nótese que el semijoin no es conmutativo. Por ejemplo, el resultado del semijoin anterior es diferente de ENROLLMENT SEMIJOIN STUDENT, el cual sería la proyección sobre las tablas ENROLLMENT del natural join. Sus columnas serían CURSE#, STUID y GRADE. STUID S1001 S1010 S1002 S1020

STUNAME Smith, Tom Burns, Edgard Chin, Ann Rivera, Jane

MAJOR History Art Math CSC

CREDITS 90 63 36 15

Figura 5. Semijoin de Student por Enrollment

Otro tipo de operación join es el OUTERJOIN. Esta operación es una extensión de una operación THETA JOIN, una EQUIJOIN, o una NATURAL JOIN. Cuando se forma un join, cualquier tupla de las tablas originales para la cual no existe tupla en la segunda tabla que permitiera al par de tuplas satisfacer la condición del join, no entra en la tabla resultado.

Una operación outer theta join consiste de todas las filas o registros que aparecen en el usual theta join, más una fila adicional por cada una de las tuplas de las tablas originales que no participe en el theta join. En estas filas, se coloca la tupla original que no iguala con otra y se le agregan valores nulos a los otros atributos. Por ejemplo, en un equijoin para tablas con una columna común, una fila no participará en el resultado a menos que haya una fila en la segunda tabla con el mismo valor para la columna común. Se observa, por ejemplo, que la fila “S1015 Jones, Mary Math 42” de la tabla STUDENT no quedó representada en la tabla STUDENT EQUIJOIN ENROLLMENT mostrada en la figura 3, debido a que no había fila de la tabla ENROLLMENT que tuviera S1015 como valor del atributo STUID. En un outerjoin, esas filas sin “pareja”, aparecen en el resultado con valores nulos en todos los otros atributos.

Bases de datos I

Arturo Ruvalcaba

Capítulo 3 Álgebra relacional

12

Por ejemplo, para formar un outer equijoin de STUDENT y ENROLLMENT se incluyen todos las filas de STUDENT EQUIJOIN ENROLLMENT, y se agregan las filas de STUDENT que no tengan una fila con la que igualen en la tabla ENROLLMENT, colocando valores nulos en las columnas COURSE#, ENROLLMENT.STUID y GRADE. También se deben incluir las filas de la tabla ENROLLMENT para las cuales el valor de STUID no tenga un valor igual con el mismo atributo en la tabla STUDENT, pero se puede ver que no hay tales filas en las tablas mostradas en la figura 1.

STUDENT

STUID S1015 S1005 S1001 S1010 S1002 S1013 S1020

STUNAME Jones, Mary Lee, Perry Smith, Tom Burns, Edgard Chin, Ann McCarthy, Owen Rivera, Jane

MAJOR Math History History Art Math Math CSC

NEWENROLL

COURSE# ART103A CSC201A CSC201A ART103A ART103A MTH101B HST205A MTH103C MTH103C MTH101B CSC203A

STUID S1001 S1020 S1002 S1010 S1002 S1020 S1001 S1010 S1002 S2222 -

GRADE A B F D A C B -

CREDITS 42 3 90 63 36 0 15

Figura 6(a). Tablas para el Outerjoin.

Para ilustrar las posibilidades del outer equijoin, se cambia el ejemplo ligeramente agregando algunas filas a la tabla ENROLLMENT como se muestra en la figura 6(a). Se llamará a la nueva tabla NEWENROLL y se observa que los registros insertados no son correctos. La última fila que tiene un valor nulo en uno de los campos claves, STUID, no tiene integridad de entidad, y la fila arriba de ella, que tiene el valor S2222 en el atributo STUID, no tiene integridad referencial puesto que existe tal valor en la tabla STUDENT. Se ignoran estos defectos en esta ilustración con el propósito de mostrar la operación de outer equijoin en la figura 6(b).

Una variación del outer equijoin es un LEFT OUTER EQUIJOIN, el cual significa que solo las filas emparejadas y las que no tiene pareja de la primera (left) tabla aparecen en el resultado.

El left outer equijoin de STUDENT y NEWENROLL se muestra en la figura 6(c). En un RIGHT OUTER EQUIJOIN se incluyen las filas emparejadas y las que no tienen pareja de la segunda (right) tabla, como se muestra en la figura 6(d).

Bases de datos I

Arturo Ruvalcaba

Capítulo 3 Álgebra relacional

STUDENT. STUID S1015 S1005 S1001 S1001 S1010 S1010 S1002 S1002 S1002 S1013 S1020 S1020 ─ ─

STUNAME Jones, Mary Lee, Perry Smith, Tom Smith, Tom Burns, Edward Burns, Edward Chin, Ann Chin, Ann Chin, Ann McCarth, Owen Rivera, Jane Rivera, Jane ─ ─

13

MAJOR Math History History History Art Art Math Math Math Math CSC CSC ─ ─

CREDITS 42 3 90 90 63 63 36 36 36 0 15 15 ─ ─

NEWENROLL. COURSE# STUID ─ ─ ─ ─ ART103A S1001 HST205A S1001 ART103A S1010 MTH103C S1010 CSC201A S1002 ART103A S1002 MTH103C S1002 ─ ─ CSC201A S1020 MTH101B S1020 MTH101B S2222 CSC203A ─

GRADE ─ ─ A C F D B ─ B A ─ ─

Figura 6(b). Outer Equijoin de Student y Newenroll.

STUDENT. STUID S1015 S1005 S1001 S1001 S1010 S1010 S1002 S1002 S1002 S1013 S1020 S1020

STUNAME Jones, Mary Lee, Perry Smith, Tom Smith, Tom Burns, Edward Burns, Edward Chin, Ann Chin, Ann Chin, Ann McCarth, Owen Rivera, Jane Rivera, Jane

MAJOR Math History History History Art Art Math Math Math Math CSC CSC

CREDITS 42 3 90 90 63 63 36 36 36 0 15 15

NEWENROLL. COURSE# STUID ─ ─ ─ ─ ART103A S1001 HST205A S1001 ART103A S1010 MTH103C S1010 CSC201A S1002 ART103A S1002 MTH103C S1002 ─ ─ CSC201A S1020 MTH101B S1020

GRADE ─ ─ A C F D B ─ B A

Figura 6(c). Left Outer Equijoin de Student y Newenroll.

El outer natural join es similar a el outer equijoin, excepto que se elimina(n) la(s) columna(s) repetida(s). Si las filas unidas tienen un valor igual para una columna repetida, se usa ese valor. Si ambas tienen valores nulos, se usa un valor nulo, y si una tiene un valor nulo y el otro no, se usa el valor no nulo en el resultado.

Bases de datos I

Arturo Ruvalcaba

Capítulo 3 Álgebra relacional

STUDENT. STUID S1001 S1001 S1010 S1010 S1002 S1002 S1002 S1020 S1020 ─ ─

STUNAME Smith, Tom Smith, Tom Burns, Edward Burns, Edward Chin, Ann Chin, Ann Chin, Ann Rivera, Jane Rivera, Jane ─ ─

14

MAJOR History History Art Art Math Math Math CSC CSC ─ ─

CREDITS 90 90 63 63 36 36 36 15 15 ─ ─

NEWENROLL. COURSE# STUID ART103A S1001 HST205A S1001 ART103A S1010 MTH103C S1010 CSC201A S1002 ART103A S1002 MTH103C S1002 CSC201A S1020 MTH101B S1020 MTH101B S2222 CSC203A ─

GRADE A C F D B B A ─ ─

Figura 6(d). Right Outer Equijoin de Student y Newenroll.

Operaciones de conjunto: unión, diferencia, intersección Dado que las relaciones son básicamente conjuntos de n-tuplas, el álgebra relacional incluye una versión de las operaciones de conjunto básicas de unión, intersección y diferencia. Para que estas operaciones binarias sean posibles, las dos relaciones en las cuales ellas se realizan deben ser compatibles en términos de unión. Esto significa que ellas deben tener la misma estructura básica. En particular, ellas deben tener el mismo grado y atributos en la posición correspondiente en ambas y éstos deben de tener los mismos dominios.

El número de columnas en una tabla se llama grado de una relación. El grado de la relación STUDENT es cuatro, ya que la tabla STUDENT tiene cuatro columnas.

Por ejemplo, la tercer columna en la primer tabla debe tener el mismo dominio que la tercer columna en la segunda tabla, aunque los nombres de columna pueden ser diferentes. El resultado de cada una de las operaciones de conjunto es una nueva tabla con la misma estructura que las dos tablas originales. Las cuatro tablas con las que se ha venido trabajando tienen todas estructuras diferentes, así que ningún par de ellas es compatible en términos de unión. Por lo que se usarán las dos tablas en la figura 7(a) para las operaciones de conjunto.

Se asume que la tabla MAINFAC contiene registros de miembros de facultad que enseñan en el campus principal, mientras que la tabla BRANCHFAC contiene registros de los maestros que enseñan en las sucursales de la universidad. Algunos miembros de de la facultad enseñan en ambos lugares.

La unión de dos relaciones es el conjunto de tuplas que se encuentra en una u otra o ambas relaciones. Por ejemplo, se puede encontrar la unión de MAINFAC y BRANCHFAC como sigue: MAINFAC UNION BRANCHFAC o simbólicamente MAINFAC ∪ BRANCHFAC El resultado se muestra en la figura 7(b).

Bases de datos I

Arturo Ruvalcaba

Capítulo 3 Álgebra relacional

15

La intersección de dos relaciones es el conjunto de tuplas en ambas relaciones simultáneamente. La intersección de MAINFAC y BRANCHFAC es: MAINFAC INTERSECTION BRANCHFAC o simbólicamente MAINFAC ∩ BRANCHFAC El resultado se muestra en la figura 7(c).

MAINFAC

BRANCHFAC

FACID

FACNAME

DEP.

RANK

F101 F105 F221

Adams Tanaka Smith

Art CSC CSC

Professor Instructor Professor

FACID

FACNAME

DEP.

RANK

F101 F115 F110 F221

Adams Smith Byrns Smith

Art History Math CSC

Professor Associate Assistant Profesor

Figura 7(a). Relaciones compatibles en términos de unión MAINFAC y BRANCHFAC

FACID

FACNAME

DEP.

RANK

F101 F105 F221 F115 F110

Adams Tanaka Smith Smith Byrns

Art CSC CSC History Math

Professor Instructor Professor Associate Assistant

Figura 7(b). MAINFAC UNION BRANCHFAC

FACID

FACNAME

DEP.

RANK

F101 F221

Adams Smith

Art CSC

Professor Professor

Figura 7(c). MAINFAC INTERSECTION BRANCHFAC

FACID

FACNAME

DEP.

RANK

F105

Tanaka

CSC

Instructor

Figura 7(d). MAINFAC MINUS BRANCHFAC

La diferencia entre dos relaciones es el conjunto de tuplas que pertenece a la primera relación pero no a la segunda. Por lo tanto, Bases de datos I

Arturo Ruvalcaba

Capítulo 3 Álgebra relacional

16

MAINFAC MINUS BRANCHFAC o simbólicamente MAINFAC - BRANCHFAC es la tabla mostrada en la figura 7(d).

División La división es una operación binaria que se puede definir en dos relaciones donde la estructura completa de una (el divisor) es una porción de la estructura de la otra (el dividendo).

Esto nos dice cuales valores en las filas del dividendo aparecen con todas las filas del divisor.

Por ejemplo, si se tiene una tabla S con m + n columnas y otra tabla T con n columnas, y las n columnas de T tienen los mismos dominios que las columnas en las posiciones correspondientes en las últimas n columnas de S, entonces S puede ser dividido por T. El resultado o cociente es una tabla con m columnas representando los atributos de S que no están en T. Las tuplas en las filas del cociente son las columnas de las tuplas en S que no son columnas en T tales que hay una tupla en S para todos los valores de tuplas en T. Para ilustrar esta operación, se muestran tres ejemplos en la figura 8. La operación de división da todas las clases para las cuales todo estudiante en la tabla STU se ha inscrito. Al examinar la figura 8(a), se puede ver que la tabla ENROLL se puede dividir por STU debido a que los nombres de las últimas dos columnas de ENROLL igualan la estructura completa de STU. El cociente tendrá dos columnas, C# y FID. El cociente contendrá valores de C# y FID que aparecen en ENROLL con todas las combinaciones de valores de SID y SNAME en la tabla STU. Puesto que S1001 y S1005 aparecen ambos con ART103A y con CSC201A, estas dos clases aparecerán en la tabla resultado. MTH101B no aparecerá en el resultado, puesto que no aparece en ENROLL, con S1005. Las figuras 8(b) y (c) usan el mismo dividendo, ENROLL, como aparece en la figura 8(a), pero STU tiene diferentes valores. En todos los tres casos se esta encontrando: ENROLL DIVIDEDBY STU el cual puede ser escrito: ENROLL ÷ STU Obsérvese que esta división equivale a las siguientes operaciones: PROJECT ENROLL OVER (C#, FID) LIVING TEMP1 TEMP1 TIMES STU GIVING TEMP2 TEMP2 MINUS ENROLL LIVING TEMP3 PROJECT TEMP3 OVER (C#, FID) GIVING TEMP4 TEMP1 MINUS TEMP4 GIVING QUOTIENT

Bases de datos I

Arturo Ruvalcaba

Capítulo 3 Álgebra relacional

ENROLL C# ART103A ART103A ART103A ART103A CSC201A CSC201A MTH101B MTH101B MTH101B

ENROLL C# ART103A ART103A ART103A ART103A CSC201A CSC201A MTH101B MTH101B MTH101B

ENROLL C# ART103A ART103A ART103A ART103A CSC201A CSC201A MTH101B MTH101B MTH101B

17

FID

SID

SNAME

STU SID

SNAME

F101 S1001 Smith, Tom S1001 Smith, Tom F101 S1002 Chin, Ann S1005 Lee, Perry F101 S1013 McCarthy, Owen F101 S1005 Lee, Perry ENROLL DIVIDED BY STU F105 S1001 Smith, Tom C# FID F105 S1005 Lee, Perry ART103A F101 F110 S1001 Smith, Tom CSC201A F105 F110 S1002 Chin, Ann F110 S1013 McCarthy, Owen Figura 8(a): Ejemplo 1 de ENROLL DIVIDED BY STU

FID

SID

SNAME

STU SID

SNAME

F101 S1001 Smith, Tom S1001 Smith, Tom F101 S1002 Chin, Ann S1002 Chin, Ann F101 S1013 McCarthy, Owen S1013 McCarthy, Owen F101 S1005 Lee, Perry F105 S1001 Smith, Tom ENROLL DIVIDED BY STU F105 S1005 Lee, Perry C# FID F110 S1001 Smith, Tom ART103A F101 F110 S1002 Chin, Ann MTH101B F110 F110 S1013 McCarthy, Owen Figura 8(b): Ejemplo 2 de ENROLL DIVIDEDBY STU

FID

SID

SNAME

STU SID

SNAME

F101 S1001 Smith, Tom S1001 Smith, Tom F101 S1002 Chin, Ann S1002 Chin, Ann F101 S1013 McCarthy, Owen S1013 McCarthy, Owen F101 S1005 Lee, Perry S1005 Lee, Perry F105 S1001 Smith, Tom F105 S1005 Lee, Perry F110 S1001 Smith, Tom ENROLL DIVIDED BY STU F110 S1002 Chin, Ann C# FID F110 S1013 McCarthy, Owen ART103A F101 Figura 8(c): Ejemplo 3 de ENROLL DIVIDEDBY STU Figura 8. Ejemplos de División.

Bases de datos I

Arturo Ruvalcaba