Vista previa del material en texto
UNIVERSIDAD NACIONAL AUTÓNOMA DE MÉXICO FACULTAD DE INGENIERÍA Lab. de Bases de Datos Grupo: 07 - Semestre: 2022-1 Práctica 9: Álgebra Relacional FECHA DE ENTREGA: 09/11/2021 Profesora: López Pelcastre Martha M.I Alumnos: N.L: 26 Téllez González Jorge Luis N.L: 23 Solano González Felipe de Jesús Facultad de Ingenierı́a Lab. de Bases de Datos Índice 1. Objetivo 3 2. Introducción 3 3. Desarrollo 7 3.1. Selección . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 3.2. Proyección . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 3.3. Unión . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 3.4. Diferencia (Except) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 3.5. Producto Cartesiano (Cross) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 3.6. Intersección (Intersect) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 3.7. Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 4. Conclusiones 16 LATEX 2 Facultad de Ingenierı́a Lab. de Bases de Datos 1. Objetivo El alumno conocerá los operadores del álgebra relacional correspondientes a la unión, intersección, diferencia y producto cartesiano. Aplicará el uso de la instrucción SELECT en su forma básica para realizar consulta de datos. Practi- cará el uso de JOIN. Usará alias. 2. Introducción Álgebra relacional Un lenguaje como SQL es adecuado para el uso humano, pero es poco apropiado para una representación interna en el sistema de la consulta. Ası́, una representación interna más útil está basada en el álgebra re- lacional extendida. El álgebra relacional es un conjunto de operaciones que describen paso a paso cómo computar una respuesta sobre las relaciones, tal y como éstas son definidas en el modelo relacional. Deno- minada de tipo procedimental, a diferencia del Cálculo relacional que es de tipo declarativo. Describe el aspecto de la manipulación de datos. Estas operaciones se usan como una representación intermedia de una consulta a una base de datos y, debido a sus propiedades algebraicas, sirven para obtener una versión más optimizada y eficiente de dicha consulta. La primera acción que el sistema tiene que emprender para procesar una consulta es la traducción de la consulta dada a su formato interno. Este proceso de traducción es similar al trabajo que realiza el analizador de un compilador. Durante la generación del formato interno de una consulta, el analizador comprueba la sintaxis de la consulta del usuario, verifica que los nombres de las relaciones que aparecen en la consulta sean nombres de relaciones en la base de datos, etcétera. Luego se construye un árbol para el análisis de la consulta, que se transformará en una expresión del álgebra relacional. Figura 1: Pasos del procesamiento de una consulta en una Base de Datos Relacional. LATEX 3 Facultad de Ingenierı́a Lab. de Bases de Datos Operaciones básicas Selección Permite seleccionar un subconjunto de tuplas de una relación (R), todas aquellas que cumplan la(s) condición(es) P, su notación es la siguiente: σp (R) (1) La sintaxis en SQL es la siguiente: -- Selección SELECT <columna_1>[,columna_2,...] FROM <nombre_tabla> WHERE <condición>; Nótese que se puede extraer más de una columna de una tabla y se pueden utilizar condiciones. Proyección Permite extraer columnas (atributos) de una relación, dando como resultado un subconjunto vertical de atributos de la relación, su notación es la siguiente: ΠA1,A2,...,An (R) (2) Donde: n representa el número de atributos que tiene una entidad. La sintaxis en SQL es la siguiente: -- Proyección SELECT <columna_1>[,columna_2,...] FROM <nombre_tabla>; Unión La operación retorna el conjunto de tuplas que están en R, o en S, o en ambas. R y S deben ser uniones compatibles. La notación es la siguiente: σp1 ( ΠA1,A2,...,An (R) ) ∪σp2 ( ΠA1,A2,...,An (S) ) (3) La sintaxis en SQL es la siguiente: LATEX 4 Facultad de Ingenierı́a Lab. de Bases de Datos SELECT <columna_1>[,columna_2,...] FROM <tabla_1> WHERE <condición_1> UNION SELECT <columna_1>[,columna_2,...] FROM <tabla_2> WHERE <condición_2>; Diferencia La diferencia de dos relaciones, R y S entrega todas aquellas tuplas que están en R, pero no en S. R y S deben ser uniones compatibles. Su notación es la siguiente: σp1 ( ΠA1,A2,...,An (R) ) −σp2 ( ΠA1,A2,...,An (S) ) (4) La sintaxis en SQL es la siguiente: SELECT <columna_1>[,columna_2,...] FROM <tabla_1> WHERE <condición_1> EXCEPT -- MINUS SELECT <columna_1>[,columna_2,...] FROM <tabla_2> WHERE <condición_2>; Producto cartesiano El producto cartesiano de dos relaciones entrega una relación, cuyo esquema corresponde a una com- binación de todas las tuplas de R con cada una de las tuplas de S, y sus atributos corresponden a los de R seguidos por los de S. Su notación es la siguiente: R×S (5) La sintaxis en SQL es la siguiente: SELECT <columna_1>[,columna_2,...], <columna_1>[,columna_2,...] FROM <tabla_R>, <tabla_S> LATEX 5 Facultad de Ingenierı́a Lab. de Bases de Datos Intersección La intersección de dos relaciones corresponde al conjunto de todas las tuplas que están en R y en S, siendo R y S uniones compatibles. Su notación es la siguiente: ΠA1,A2,...,An (σp1 (R))∩ΠA1,A2,...,An (σp2 (S)) (6) La sintaxis en SQL es la siguiente: SELECT <columna_1>[,columna_2,...] FROM <tabla_R> WHERE <condición_1> EXCEPT -- MINUS SELECT <columna_1>[,columna_2,...] FROM <tabla_S> WHERE <condición_2>; Join La operación unión natural en el álgebra relacional es la que permite reconstruir las tablas originales previas al proceso de normalización. Consiste en combinar las proyección, selección y producto car- tesiano en una sola operación, donde la condición es la igualdad Clave Primaria = Clave Externa (o Foránea), y la proyección elimina la columna duplicada (clave externa). Su notación es la siguiente: R 1 S = ΠA1,A2,...,An (σp (R×S)) (7) La sintaxis en SQL es la siguiente: SELECT <columna_1>[,columna_2,...] FROM <tabla_R> inner join <tabla_S> on <tabla_R>.<llave_primaria> = <tablas_S>.<llave_foránea> LATEX 6 Facultad de Ingenierı́a Lab. de Bases de Datos 3. Desarrollo Para el desarrollo de las actividades de la práctica se utilizará las base de datos de instrumentos musicales que ha sido trabajada previamente y poblada con nuevos registros. Sobre ella, se realizarán una serie de consultas basadas en las operaciones básicas y compuestas del Lenguaje procedimental englobadas dentro del Álgebra Relacional. Se debe destacar que el procedimiento correcto para el desarrollo de las consultas debe colocar en primer lugar la creación de las expresiones algebraicas correspondientes, y posteriormente, se escribe su equivalente en código SQL. 3.1. Selección Se solicita obtener toda la información de los proveedores de México. Por tanto, se requieren todos los registros de proveedores que contengan en el campo paı́s la cadena MEXICO. La expresión de esta consulta en álgebra relacional es la siguiente: σpaı́s=′MEXICO′ (PROV EEDOR) Entonces, de lo anterior se puede establecer que el código que corresponde a la expresión algebraica anterior es el siguiente: SELECT * from PROVEEDOR WHERE paı́s = ’MEXICO’ Figura 2: Ejecución exitosa de la consulta con Selección. LATEX 7 Facultad de Ingenierı́a Lab. de Bases de Datos Se espera de la consulta anterior la visualización de todos los registros de la tabla PROVEEDOR que contengan en su campo paı́s a la cadena MEXICO. El resultado exitoso se puede observar en la figura [2]. 3.2. Proyección Después de lo anterior, se solicita obtener en álgebra relacional y SQL el nombre, calle y colonia de las tiendas registradas en la base de datos. Para esto, la Proyección resulta útil ya que se solicita laextracción de los valores de ciertos atributos contenidos en una tabla. La expresión que satisface tal solicitud es la siguiente: πnombre,calle,colonia(T IENDA) Con lo anterior, se puede escribir el código SQL equivalente para ejecutar la consulta correspon- diente en SQL Server: select nombre, calle colonia from TIENDA; Figura 3: Ejecución exitosa de la consulta con Proyección. Finalmente, como se observa en la figura anterior, el resultado obtenido consiste en el listado de los registros de la tabla TIENDA, pero únicamente considerando los campos nombre, campo y colonia al mostrar los resultados de la consulta. LATEX 8 Facultad de Ingenierı́a Lab. de Bases de Datos 3.3. Unión Ahora, se solicita la obtención del nombre y municipio de las tiendas y proveedores de la delegación Co- yoacán. Debido a que en el diseño original la tabla PROVEEDOR no cuenta con un campo llamado muni- cipio, se realiza un ALTER TABLE para añadirlo junto con otros campos adicionales que, de momento, se quedan declarados como NULL para efectos del ejercicio. ALTER TABLE PROVEEDOR ADD calle [varchar](40) NULL, numero [varchar](20) NULL, colonia [varchar](40) NULL, municipio [varchar](40) NULL, entidad [varchar](30) NULL; Hecho lo anterior, se le asigna el municipio de Coyoacán a las tiendas que tienen el ID 10 y 11: UPDATE PROVEEDOR SET municipio = ’COYOACAN’ WHERE idProveedor IN(10,11); Una vez que ya se tiene el campo municipio en la tabla PROVEEDOR y ya se tienen 2 registros con el campo municipio = ’COYOACAN’, se tienen las condiciones suficientes para satisfacer la consulta anterior que solicita la unión entre TIENDA y PROVEEDOR sobre los registros que tienen en común a Coyoacán como municipio. La expresión algebraica para la consulta anterior es la siguiente: σmunicipio=′COYOACAN′(πnombre,municipio(T IENDA)) ∪ σmunicipio=′COYOACAN′(πrazonSocial,municipio(PROV EEDOR)) Figura 4: Resultado obtenido de la consulta. LATEX 9 Facultad de Ingenierı́a Lab. de Bases de Datos Obsérvese que en ambas consultas unidas, en lugar de usar el tı́pico SELECT *, en lugar del * se colocan los campos que únicamente se desean mostrar de los registros, y además, se especifica la condición de que solo se muestren los registros que tienen a la cadena COYOACAN en su campo municipio. Como resultado, la consulta deberá obtener los registros con municipio=’COYOACAN’. y además, solo deberá mostrar las columnas nombre y municipio; que es el resultado obtenido en la figura [4]. Cabe señalar que, en el resultado de la consulta, los valores del campo razonSocial de los proveedores FENDER AC y STRATUS MUSICAL quedan listados con el registro de la tienda TOP MUSIC bajo la columna nombre. Figura 5: Ejecución exitosa de la consulta con Unión. 3.4. Diferencia (Except) La siguiente solicitud consiste en obtener la descripción, precio de venta, costo y tipo de instrumento de aquellos que hayan costado más de 3200 pero su precio de venta sea mayor a 6800. Para realizar tal consulta, se requiere eliminar de un conjunto inicial de registros recuperados lo ob- tenido en una segunda consulta de registros. Analizando, es posible observar que nuestra primer condición es que costo > 3200 para los registros recuperados, pero además, esto deben tener un precioVenta > 6800, por tanto, resulta razonable realizar una consulta de todos los registros que cumplan con la primer condi- ción, y obtenidos estos registros, eliminar a todos aquellos que no cumplan con la segunda condición, es decir, aquellos con un precio de venta menor a 6800. La expresión algebraica para representar lo anterior es la siguiente: LATEX 10 Facultad de Ingenierı́a Lab. de Bases de Datos σcosto>3200(πdescripcion,precioVenta,costo,tipoInstrumento(INST RUMENTO))− ⇒ σprecioVenta<6800(πdescripcion,precioVenta,costo,tipoInstrumento(INST RUMENTO)) De lo anterior, podemos observar que se solicita generar una consulta sobre la tabla INSTRUMEN- TO pero únicamente incluyendo los atributos descripción, precioVenta, costo, tipoInstrumento, y después, de esos registros se debe verificar que en su campo costo el valor presente sea mayor a 3200, y luego, a esa lista de registros se le debe de eliminar todos los registros obtenidos al realizar otra consulta a INSTRU- MENTO con los mismos campos especificados de la primer consulta, pero ahora verificando la condición precioVenta < 6800. Figura 6: Lista de instrumentos en la base. En la imagen anterior se pueden observar los registros que se tienen presentes en la base de datos de la brigada. Entonces, considerando las aseveraciones anteriores, podemos observar que la siguiente consulta arrojará que los instrumentos TAMBOR, TIMBAL, CELESTA, GUITARRA ELECTROACUSTICA, CITARA, ARPA y TUBA cumplen con la condición especificada: select IT.descripción, IT.precioVenta, IT.costo, IT.tipoInstrumento from INSTRUMENTO IT where IT.costo > 3200 Ahora, si lo que se desea es obtener aquellos instrumentos que incumplen la condición de precioVenta> 6800, lo natural es excluir de la lista de resultados a todos aquellos en los que precioVenta < 6800, enton- ces, con la siguiente consulta se obtiene que los siguientes instrumentos deben ser excluidos de la lista LATEX 11 Facultad de Ingenierı́a Lab. de Bases de Datos final de instrumentos que cumplan ambas condiciones: TAMBOR, CAMAPANA, PANDERETA, FLAUTA, ARMONICA, CLARINETE, VIOLIN, CITARA, ARPA, TUBA y BAJO. select IT.descripción, IT.precioVenta, IT.costo, IT.tipoInstrumento from INSTRUMENTO IT where IT.precioVenta <= 6800 Con lo anterior, se deduce que el resultado final de la consulta deberá excluir a los registros TAM- BOR, CITARA y ARPA. Considerando la consulta inicial, eliminando estos elementos de la lista finalmente se tienen que los instrumentos que cumplen las condiciones solicitadas son: CELESTE, TIMBAL y GUITA- RRA ELECTROACUSTICA. Finalmente, para excluir los registros de la segunda consulta (aquellos que no cumplen con la se- gunda condición) se utiliza la instrucción EXCEPT para indicar que, de la primer consulta realizada, se le eliminen los registros resultantes de la segunda consulta. En la siguiente captura es posible obser- var que, efectivamente, ese es el resultado obtenido al obtenerse 3 registro donde precvioVenta > 6800 y costo > 3200: Figura 7: Lista de instrumentos obtenida al ejecutar la consulta. 3.5. Producto Cartesiano (Cross) En este apartado se solicita obtener toda la información de los proveedores y pedidos. Entonces, es posible utilizar el producto cartesiano para cumplir con tal consulta, ya que permite obtener como resultado una multiplicación entre cada pedido y cada proveedor, es decir, si se tienen 3 proveedores y 6 pedidos, la consulta resultante arrojará 18 renglones en total. La expresión algebraica de tal consulta es la siguiente: (PROV EEDOR) χ (PEDIDO) LATEX 12 Facultad de Ingenierı́a Lab. de Bases de Datos Con lo anterior, se tiene que la lı́nea de código en SQL correspondiente es la siguiente, observando que para realizar el producto cartesiano únicamente se añade la otra tabla separada con una coma: select * from PROVEEDOR PR, PEDIDO PE Con lo anterior, se espera que el resultado de ejecutar la consulta anterior de como resultado un total de 18 registros donde los 6 registros de PEDIDO estarán unidos a cada uno de los registros de PROVEEDOR (3 en total). Una vez ejecutada la consulta, se comprueba que el resultado obtenido es el correcto. Figura 8: Ejecución exitosa de la consulta con Producto Cartesiano. 3.6. Intersección (Intersect) La siguiente solicitud requiere obtener la descripción y precioVenta de los instrumentos de percusión ex- cepto los que tiene un precio de venta mayor a 7500. Represente el texto con teorı́a de conjuntos. Analizando el ejercicio anterior, se observa que se solicitan los campos descripción y precioVenta de los instrumentos de tipo P, excluyendo a aquellos con precioVenta > 7500. Recordando que una intersec- ción de 2 conjuntos se compone delos elementos en común de ambos, es factible proponer una intersección del resultado de consultar aquellos registros que cumplen con ser de tipo P con otra consulta que, con la misma tabla y campos a consultar, considere únicamente aquellos con precio < 7500. De este modo, los registros resultantes serán de percusión, y a su vez, tendrán un precio de venta menor a 7500. LATEX 13 Facultad de Ingenierı́a Lab. de Bases de Datos La expresión algebraica que describe lo anterior es la siguiente: σtipoInstrumento=′P′(πdescripcion,precioVenta(INST RUMENTO)) ∩ ⇒ σprecioVenta<7500(πdescripcion,precioVenta(INST RUMENTO)) La consulta en lenguaje SQL proveniente de la intersección resulta de la siguiente forma, resaltando que se utiliza la palabra intersect para denotar la intersección de los registros en común obtenidos de ambas consultas: select IT.descripción, IT.precioVenta from INSTRUMENTO IT where IT.tipoInstrumento =’P’ intersect select IT.descripción , IT.precioVenta from INSTRUMENTO IT where IT.precioVenta < 7500 Haciendo la primer consulta individualmente, se obtienen los registros TAMBOR, TIMBAL, XILO- FONO, CAMPANA, CELESTA y PANDERETA. Luego, con la segunda consulta se obtiene TAMBOR, XILOFONO, CAMPANA, PANDERETA, FLAUTA, ARMONICA, CLARINETE, VIOLIN, CITA- RA, ARPA, TUBA y BAJO. Con ambos conjuntos, se ubican los elementos en común de ambos conjuntos y se arma el conjunto resultado: TAMBOR, XILOFONO, PANDERETA y CAMPANA. Ahora, se ejecuta la consulta y se verifica el conjunto obtenido: Figura 9: Ejecución exitosa de la consulta con Intersección. LATEX 14 Facultad de Ingenierı́a Lab. de Bases de Datos 3.7. Join La solicitud requiere: Obtener toda la información de los pedidos y los instrumentos solicitados en cada pedido.. Para realizar esta consulta, una buena forma de abstraer lo que la solicitud necesita es observar lo que contiene cada una de las tablas. Se hace una consulta básica que muestra el contenido de las tablas PEDIDO y DETALLE PEDIDO. Figura 10: Selección de tablas PEDIDO y DETALLE PEDIDO Nótese que la tabla DETALLE PEDIDO tiene la llave primaria de PEDIDO como una llave foránea. La consulta pide mostrar las información de DETALLE PEDIDO relacionada con la llave idPedido, por lo que se utiliza un JOIN para poder mostrar dicha consulta, La sintaxis en SQL utilizada es la siguiente: select * from PEDIDO inner join detallePedido on PEDIDO.idPedido=DETALLEPEDIDO.idPedido --En donde se unen --Con alias select * from PEDIDO P inner join detallePedido DP on P.idPedido=dP.idPedido La salida obtenida es la siguiente, observa que para cada valor correspondiente se une con su informacion de PEDIDO: Figura 11: JOIN entre PEDIDO y DETALLE PEDIDO LATEX 15 Facultad de Ingenierı́a Lab. de Bases de Datos 4. Conclusiones Solano González Felipe de Jesús: La práctica resultó ser bastante enriquecedora, se comprendió en su totalidad la manera de hacer consultas de información. Para comenzar a realizar la práctica, se empleo las consultas más básicas y sencillas de comprender, las selecciones, si bien ya se habı́an utilizado antes, ahora se utilizan de una forma más especı́fica donde podemos hacer diferentes mo- dificaciones para obtener diferentes consultas. De manera general, no se presentó ningún problema para comprender como utilizar selecciones y proyecciones, los ejercicios se realizaron de manera satisfactoria y se obtuvieron los resultados esperados. Otro punto importante revisado en la práctica fue el uso de las condiciones, para los operadores básicos no resulta complicado entender su función dentro de estas, sin embargo, en operadores más avanzados, se tiene que verificar la forma en como se implementan. Además, se comprendió como utilizar los operadores avanzados como join, unión, diferencia e intersección, debido a que es muy fácil de abstraer observándolo como teorı́a de conjuntos. En conclusión, se puede mencionar que los objetivos de la práctica se cumplen debido a que se conoció y comprendió los operadores del algebra relacional, además, se utilizó de marea correcta la instrucción SELECT para realizar consulta de datos, también, se utilizó el operador JOIN y se implemento en diferentes ejercicios el uso de alias. Téllez González Jorge Luis: El trabajo presente en esta práctica resultó en una experiencia que permitió complementar más a detalle la elaboración de consultas de información dentro de una base de datos. Como se comentó durante la sesión de laboratorio, al momento de operar con un sistema de bases de datos la creación de consultas puede tener un papel tan importante como el propio desarrollo de la base, ya que permite optimizar en gran medida la recuperación eficiente de la información contenida dentro de ella; especialmente cuando entran en juego sistemas que contienen muy altos volúmenes de información. También, se analizó que estas consultas, en sı́ mismas, no representan más que una selección de elementos dentro de un conjunto, que bien, tiene una correspondencia directa al concepto de con- junto onminipresente en todas las disciplinas computacionales y matemáticas, y su uso representa una aplicación en sı́ misma de sentencias lógicas destinadas a la recuperación de un cierto conjun- to de elementos deseados. Esta correspondencia, por tanto, resulta en una asimilación más sencilla del background detrás del álgebra relacional y su posterior aplicación a la elaboración de consul- tas eficientes que permitan obtener la información deseada de forma precisa y sin admitir errores u omisiones en su recuperación. Finalmente, considerando que durante la sesión se comprendieron que las operaciones algebraicas fundamentales corresponden a las operaciones aplicables en la teorı́a de conjuntos y, además, se relacionaron con sus equivalentes directos en lenguaje SQL para la elaboración de consultas, y por último, se estudió la relevancia que tiene JOIN para el enlazado entre tablas padre e hijas por medio LATEX 16 Facultad de Ingenierı́a Lab. de Bases de Datos de Foreign Keys y se abordó el uso de alias como un método de facilitar la escritura de nombres complicados o largos en determinadas tablas, se puede considerar que los objetivos de la práctica han sido cumplidos con total éxito y, por tanto, la brigada cuenta con los elementos básicos necesarios para desarrollar consultas más precisas que conduzcan a resultados eficaces al operar con bases de datos, no solo de SQL Server, sino de otros manejadores como el de Oracle que es empleado en el desarrollo de las clases teóricas. Referencias [1] Lenguaje de manipulación de datos. Recuperado de: https://es.wikipedia.org/wiki/ Lenguaje_de_manipulaci%C3%B3n_de_datos. Fecha de consulta: 03/11/2021. [2] Silberschatz, A., Korth, H. F., and Sudarshan, S. (2002). Fundamentos de Bases de Datos. McGraw- Hill, 4th edition. Los créditos de las fotografı́as pertenecen a sus respectivos autores. © LATEX 17 https://es.wikipedia.org/wiki/Lenguaje_de_manipulaci%C3%B3n_de_datos https://es.wikipedia.org/wiki/Lenguaje_de_manipulaci%C3%B3n_de_datos Objetivo Introducción Desarrollo Selección Proyección Unión Diferencia (Except) Producto Cartesiano (Cross) Intersección (Intersect) Join Conclusiones