TEMA: CONSULTAS (ALGEBRA RELACIONAL Y SQL) 1.- OBJETIVOS Al finalizar la práctica el estudiante estará en condiciones de:
-
Identificar las equivalencias del algebra relacional en SQL Utilizar funciones como parte de las sentencias SQL. Utilizar el concepto de modularidad al escribir sentencias SELECT.
2.- TRABAJO PREPARATORIO -
Repasar los operadores del álgebra relacional.
3.- BASE TEORICA COMPLEMENTARIA La sentencia SELECT del SQL implementa también las operaciones del álgebra relacional, por tanto, opera sobre tablas y da como resultado otra tabla. En consecuencia, dado un problema, se puede descomponer en unidades lógicas, donde cada unidad lógica puede dar como resultado una tabla temporal que será utilizado en las subsiguientes unidades lógicas, hasta obtener la tabla final con el resultado pedido, similar a los problemas resueltos con el álgebra relacional.
4.- CONTENIDO DE LA PRACTICA. Para realizar está práctica, primero cargar el analizador de consultas de SQL-SERVER. Luego seleccionar la base de datos CONTROL_ACADEMICO. Carrera_Profesional(Cod_CP, Carrera_Profesional(Cod_CP , Nombre_CP) Alumno(Cod_Alumno, Paterno, Materno, Nombres, Cod_CP) Docente(Cod_Docente, Paterno, Materno, Nombres, Categoria, Regimen) Asignatura(Cod_Asignatura, Asignatura(Cod_Asignatura , Cod_CP, Nombre_Asignatura, Categoría, Creditos) Catalogo(Semestre, Cod_Asignatura, Cod_CP, Grupo, Cod_Docente) Matricula(Semestre, Cod_Asignatura, Cod_CP, Grupo, Cod_Alumno, Nota)
PARTE 1.- Operaciones del Álgebra Relacional en SQL. 4.1 PROYECCIÓN 1
Proyectar los atributos: CodAlumno, ApellidoPaterno, ApellidoMaterno y Nombres de la tabla ALUMNO.
Solución: En álgebra relacional se tiene: π
Cod_Alumno, Paterno, Materno, Nombres
(ALUMNO)
El equivalente en SQL es: SELECT Cod_Alumno, Paterno, Materno, Nombres FROM ALUMNO
La operación de proyección del álgebra relacional se realiza, escribiendo los atributos que se desea proyectar a continuación de la sentencia SELECT. La cláusula FROM se utiliza para indicar la tabla con la que se ha de operar. Al ejecutar la sentencia SQL, se debe mostrar un resultado similar a:
4.2 SELECCIÓN 2
Seleccionar los alumnos de la carrera profesional de Ingeniería Informática y de Sistemas.
Solución: En álgebra relacional se tiene: σ
Cod_ CP = ‘IN’
(ALUMNO)
El equivalente en SQL es: SELECT * FROM ALUMNO WHERE Cod_CP = 'IN'
La operación de Selección se implementa en SQL mediante la cláusula WHERE. En este caso, seleccionará todas las tuplas cuyo atributo Cod_CP sea igual a'IN'.
La particularidad en el SQL, es que la operación de selección siempre va acompañada de la operación de Proyección. El * al costado de la sentencia SELECT significa: Proyectar todos los atributos Al ejecutar la sentencia SQL, se debe mostrar un resultado similar a:
4.3 UNION 3
Determinar los alumnos de la carrera profesional de Ingeniería Informática y de Sistemas y los alumnos de la carrera profesional de Ingeniería Electrónica.
Solución: En álgebra relacional se tiene: (σ
Cod_ CP = ‘IN’
(ALUMNO))
(σ
Cod_ CP = ‘IL’
(ALUMNO))
El equivalente en SQL es: SELECT * FROM ALUMNO WHERE Cod_CP = 'IN' UNION SELECT * FROM ALUMNO WHERE Cod_CP = 'IL'
La operación UNION se implementa en SQL mediante la cláusula UNION. En este caso, hará la operación de Unión de conjuntos con los resultados de las dos tablas. Recordar que la operación UNION opera sobre tablas con la misma estructura Al ejecutar la sentencia SQL, se debe mostrar un resultado similar a:
4.4 DIFERENCIA 4
Determinar las asignaturas que se dictaron en el semestre ‘2007 -II’ y no se dictaron en el semestre ‘2008 -I’ .
Solución: En álgebra relacional se tiene: πCod_Asignatura, πCod_Asignatura,
Cod_CP(σ Semestre = ‘2007-II’ Cod_CP(σ
Semestre = ‘2008-I’
(CATALOGO)) (CATALOGO))
El equivalente en SQL es: SELECT distinct Cod_Asignatura, Cod_CP FROM CATALOGO WHERE Semestre = '2007-II' EXCEPT SELECT distinct Cod_Asignatura, Cod_CP FROM CATALOGO WHERE Semestre = '2008-I'
La operación DIFERENCIA se implementa en SQL mediante la cláusula EXCEPT. En este caso, hará la operación de Diferencia de conjuntos con los resultados de las dos tablas. Recordar que la operación DIFERENCIA también opera sobre tablas con la misma estructura Al ejecutar la sentencia SQL, se debe mostrar un resultado similar a:
4.5 INTERSECCIÓN 5 Determinar las asignaturas de la carrera profesional de Ingeniería Informática que se dictaron tanto en el semestre ‘2007 -II’ y el semestre ‘2008 -I’.
Solución: En álgebra relacional se tiene: πCod_Asignatura, πCod_Asignatura,
Cod_CP(σ Cod_CP = ‘IN’ and Semestre = ‘2007-II’ Cod_CP(σ Cod_CP = ‘IN’ and Semestre = ‘2008-I’
(CATALOGO)) (CATALOGO))
El equivalente en SQL es: SELECT distinct Cod_Asignatura, Cod_CP FROM CATALOGO WHERE Cod_CP='IN' and Semestre = '2007-II' INTERSECT SELECT distinct Cod_Asignatura, Cod_CP FROM CATALOGO WHERE Cod_CP='IN' and Semestre = '2008-I'
La operación INTERSECCIÓN se implementa en SQL mediante la cláusula INTERSECT . En este caso, hará la operación de Intersección de conjuntos con los resultados de las dos tablas. Recordar que la operación INTERSECCIÓN también opera sobre tablas con la misma estructura Al ejecutar la sentencia SQL, se debe mostrar un resultado similar a:
4.6 PRODUCTO CARTESIANO 6. Determinar la relación de alumnos con el nombre de sus respectivas carreras profesionales Solución: En álgebra relacional se tiene: πCod_Alumno,Paterno,Materno,Nombres,Nombre_CP (σAlumno.Cod_CP=CARRERA_PROFESIONAL.Cod_CP (ALUMNO X CARRERA_PROFESIONAL)
El equivalente en SQL es: SELECT Cod_Alumno, Paterno, Materno, Nombres, Nombre_CP FROM ALUMNO A, CARRERA_PROFESIONAL C WHERE A.Cod_CP = C.Cod_CP
La operación de Producto Cartesiano se implementa en SQL en la cláusula FROM. Si en esta cláusula se pone más de una tabla, entonces se efectúa el producto cartesiano de éstas; luego, es necesario agregar la cláusula WHERE (Selección) para seleccionar sólo las tuplas que tengan sentido. En SQL se puede utilizar “alias” asociado a las tablas, de modo que en el resto de las sentencias se pueda
utilizar estos alias en lugar del nombre de las tablas. El alias se coloca inmediatamente después del nombre de cada tabla. Al ejecutar la sentencia SQL, se debe mostrar un resultado similar a:
4.7 JOIN (Composición Interna) 7. Determinar la relación de alumnos con el nombre de sus respectivas carreras profesionales Solución: En álgebra relacional se tiene: πCod_Alumno,Paterno,Materno,Nombres,Nombre_CP(ALUMNO
Cod_CP
CARRERA_PROFESIONAL)
El equivalente en SQL es: SELECT Cod_Alumno, Paterno, Materno, Nombres, Nombre_CP FROM ALUMNO A inner join CARRERA_PROFESIONAL C ON A.Cod_CP = C.Cod_CP
La operación JOIN se implementa en SQL como composición interna mediante la cláusula inner join. Requiere adicionalmente la cláusula ON para especificar la condición en base a la que se debe juntar las tab las. Al ejecutar la sentencia SQL, se debe mostrar un resultado similar al del ejemplo anterior, ya que ambas sentencias son equivalentes. Nota.- La operación JOIN NATURAL del álgebra relacional no tiene su equivalente en el SQL Server.
4.8 FUNCIONES DE AGREGACIÓN
8. Determinar el número de alumnos de cada carrera profesional En álgebra relacional se tiene: COUNT(Cod_Alumno) as NroAlumnos(ALUMNO)
Cod_CP
El equivalente en SQL es: SELECT Cod_CP, Count(Cod_Alumno) as NroAlumnos FROM ALUMNO GROUP BY Cod_CP
El atributo o atributos que se utilizan como criterio de agrupación se escriben después de la cláusula GROUP BY. Al ejecutar la sentencia SQL, se debe mostrar un resultado similar a:
4.9 ASIGNACIÓN (TABLAS TEMPORALES)
9. Determinar el número de asignaturas en los que se matricularon los alumnos de Ingeniería Informática en el semestre ‘2008 -I’
En álgebra relacional se tiene: -- Determinar matrículas del semestre '2008-I' T1
πCod_Alumno,Cod_Asignatura(σCod_CP=’IN’
and Semestre=’ 2008-I ’(MATRICULA)
-- Contar el número de asignaturas por alumno Cod_Alumno COUNT(Cod_Asignatura) as NroAsignaturas (T1)
El equivalente en SQL es: -- Determinar matrículas del semestre '2008-I' SELECT Cod_Alumno, Cod_Asignatura INTO #T1 FROM MATRICULA WHERE Cod_CP = 'IN' and Semestre = '2008-I' -- Contar el número de asignaturas por alumno SELECT Cod_Alumno, Count(Cod_Asignatura) as NroAsignaturas FROM #T1 GROUP BY Cod_Alumno
La operación de asignación para almacenar resultados parciales en tablas temporales se implementa en SQL mediante la cláusula INTO.
Tablas temporales en Transact SQL Las tablas temporales son similares a las permanentes, salvo por el hecho de que las tablas temporales se almacenan en tempdb y se eliminan automáticamente cuando ya no se utilizan. Los dos tipos de tablas temporales, las locales y las globales, difieren en cuanto a sus nombres, visibilidad y vida útil. Las tablas temporales locales presentan un solo signo de número (#) como primer carácter del nombre; son visibles únicamente para la conexión actual del usuario y se eliminan cuando el usuario se desconecta de los equipos en los que se ejecuta SQL Server. Las tablas temporales globales presentan dos signos de número (##) antes del nombre, son visibles para cualquier usuario después de su creación y se eliminan cuando todos los usuarios que hacen referencia a la tabla se desconectan de SQL Server.
Al ejecutar la sentencia SQL, se debe mostrar un resultado similar a:
PARTE 2.- Ejercicios Propuestos Escribir las sentencias SELECT para obtener la siguiente información: 1.- Número de alumnos matriculados por semestre y por Carrera Profesional. 2.- Relación de alumnos con su respectivo número de créditos acumulados. 3.- Relación de alumnos que hayan aprobado todas sus asignaturas en el último semestre 4.- Relación de alumnos con su respectivo promedio aritmético en cada semestre 5.- Relación de docentes con el número de asignaturas dictadas en cada semestre.
5.- TRABAJO COMPLEMENTARIO. Realizar un trabajo monográfico sobre TRANSACT SQL
