Tarea MODALIDAD ABIERTA Y A DISTANCIA La Universidad Católica de Loja Base de Datos Avanzada 4 créditos Departamento d
Views 108 Downloads 5 File size 1MB
Tarea MODALIDAD ABIERTA Y A DISTANCIA La Universidad Católica de Loja
Base de Datos Avanzada 4 créditos
Departamento de Ciencias de la Computación y Electrónica Sección Tecnologías Avanzadas de la Web y SBC Carrera
Ciclo
V
Informática
Profesora autora: Audrey Elizabeth Romero Peláez Le recordamos que el envío de la tarea a través del EVA (Entorno Virtual de Aprendizaje) es obligatorio; y, el ingreso se lo realiza en las fechas establecidas en el calendario académico, que se encuentra en el siguiente enlace.
https://distancia.utpl.edu.ec/calendario-academico/ Abril - Agosto 2019 TUTORÍAS: El profesor asignado publicará en el Entorno Virtual de Aprendizaje (EVA) su número telefónico y horario de tutoría. Más información puede obtener llamando al Call Center 07 3701444, línea gratuita 1800 88758875 o al correo electrónico [email protected]
Asesoría virtual: www.utpl.edu.ec
Tarea: Base de Datos Avanzada
Tarea Primer bimestre
Estimado(a) estudiante. Esta primera tarea contiene 3 actividades que usted debe desarrollar: Actividad
Tema
Puntaje
Actividad 1
Diseño físico de bases de datos
2.8 puntos
Actividad 2
Seguridad de la base de datos
1.6 puntos
Actividad 3
Procesamiento y optimización de consultas
1.6 puntos
TOTAL -> 6.0 puntos
La entrega de la tarea se hará a través del EVA, mediante un cuestionario que contendrá las interrogantes planteadas en el punto 5 de cada actividad. NOTA: En este documento se omiten las tildes en el nombre de las tablas y entidades, y signos especiales debido a restricción en los motores de bases de datos. ACTIVIDAD 1 (2.8 puntos) 1.
Actividad de Aprendizaje Realice el diseño físico de base de datos para el caso de estudio “Biblioteca” (Ver ANEXO), y responda a las interrogantes planteadas.
2.
Tema de la actividad Diseño físico de bases de datos
3.
Competencia a la que aporta la actividad Diseñar las especificaciones físicas requeridas para implementar una base de datos.
4.
Orientaciones metodológicas (estrategia de trabajo) La actividad consiste en ejecutar el diseño físico de bases de datos aplicado al caso práctico “Biblioteca” (Ver ANEXO que se encuentra al final del documento).
2 MODALIDAD ABIERTA Y A DISTANCIA
Tarea: Base de Datos Avanzada
Es importante que avance progresivamente con el estudio tanto de las unidades 1 y 2 como de su tarea. Para el desarrollo de esta actividad, le sugerimos la siguiente estrategia: a.
Examine el caso de estudio “Biblioteca”.
b.
Realice el análisis transaccional para todas las transacciones descritas en el caso práctico.
c.
1.
Por cada transacción debe identificar las tablas a las que se acceden tanto para consulta como para actualización.
2.
Recuerde que debe considerar todas las acciones que implica ejecutar la transacción desde una aplicación de usuario final.
3.
Considere el siguiente ejemplo: «para consultar la ficha de un libro», primero se busca y selecciona el libro requerido, que le permitirá visualizar la ficha del libro seleccionado, que muestra toda su información (editorial, área de conocimiento, autores y ejemplares)
Construya el diseño físico utilizando el SGBD Oracle Express Edition. 1.
La base de datos se denominará bibliotecadb.
2.
Realice la traducción del modelo lógico a lenguaje de base de datos Oracle.
3.
Recuerde que se debe intentar que se implementen todas las especificaciones del modelo lógico, si existe alguna restricción que no se pueda implementar mediante DDL Oracle, es importante que lo registre, indicando desde su punto de vista, la(s) forma(s) de implementarla(s).
4.
Luego, partiendo del análisis transaccional, identifique y defina índices. Observe aquellas transacciones que involucran operaciones de consulta con el fin de seleccionar las columnas candidatas para crear índices. Defina las sentencias SQL para crear los índices identificados e incorpore estas sentencias al script de creación de la base de datos bibliotecadb.
3 La Universidad Católica de Loja
UTPL
Tarea: Base de Datos Avanzada
5.
6.
5.
Defina la organización física de los datos. En este caso tenemos dos vistas de usuario: «Gestión de libros» y «Gestión de préstamos». Por tanto: ♦
Crearemos dos tablespaces: (1) TSLIBROS: con todas las tablas vinculadas al inventario de libros, y (2) TSPRESTAMOS: con las tablas utilizadas para el registro de los préstamos.
♦
Cada tablespace, debe tener al menos dos datafiles. Usted decide la ubicación y tamaño de los tablespaces y datafiles con base a la proyección de espacio requerido en disco. Incorpore estas definiciones al script SQL.
Finalmente, debe tener un solo script DDL, con todas las definiciones anteriores.
d.
Implemente la base de datos en su motor Oracle Express Edition.
e.
Con base a la experiencia desarrollada y al diseño realizado, analice y responda a las preguntas del cuestionario.
Interrogantes de la actividad planteada Luego de realizar el análisis transaccional y haber contabilizado el total de accesos proyectados a cada tabla tanto por consultas como por actualizaciones: Seleccione la respuesta correcta. 1.
¿Cuál de las siguientes son las dos tablas que menos se consultan? a. b. c. d. e. f.
2.
libros y prestamos usuarios y prestamos libros y autores areas y editoriales usuarios y libros libros y ejemplares
¿A cuál de los siguientes se aproxima más el tamaño proyectado a 5 años del espacio requerido en disco para almacenar los datos? a. b.
1000 MB 5 GB
4 MODALIDAD ABIERTA Y A DISTANCIA
Tarea: Base de Datos Avanzada
c. d. e. 3.
De todas las restricciones planteadas en el diseño lógico, ¿cuál de las siguientes no se puede implementar mediante DDL en Oracle? a. b. c. d.
4.
15 GB 150 GB 1.5 TB
El año del libro debe ser mayor a 1850 Los campos obligatorios Cada libro debe tener al menos un autor No puede haber dos usuarios con el mismo email
De acuerdo al diseño planteado, la tabla EJEMPLARES debió quedar almacenada en el tablespace TSLIBROS. Una vez poblada con datos ¿qué pasaría si la intentamos mover hacia el tablespace TSPRESTAMOS utilizando «ALTER TABLE ejemplares MOVE TABLESPACE tsprestamos; »? a. b.
c. d. 5.
Es posible siempre y cuando no existan referencias en la tabla PRESTAMOS Se puede mover sin restricciones, pero todos los registros que ya existían se quedan en el tablespace TSLIBROS, solo los nuevos registros se almacenarán en TSPRESTAMOS Siempre que exista espacio en el nuevo tablespace, la tabla se mueve sin restricciones, con todos sus datos. No es posible puesto que ya tiene datos
Ordene los elementos de la sentencia SQL que permite implementar la restricción «la fecha de devolución no debe ser inferior a la fecha de préstamo» A. B. C. D. E. F. G. H. I. J.
“chk_fecha_dev” ALTER TABLE fechaDevolucion fechaPrestamo CHECK ); >= prestamos ADD CONSTRAINT (
5 La Universidad Católica de Loja
UTPL
Tarea: Base de Datos Avanzada
Seleccione el orden: a. b. c. d. e. 6.
B-H-I-A-E-J-D-G-C-F B-I-H-E-A-J-C-G-D-F A-B-D-C-E-F-G-H-I-J B-H-I-A-E-J-C-G-D-F B-H-I-E-A-J-C-G-D-F
La transacción de consulta “Listar libros”, suele derivar en consultas como por ejemplo la siguiente:
Con base a lo anterior, ¿cuál de las siguientes columnas serían candidatas para crear un índice de manera que la consulta sea mucho más eficiente? a. b. c. d. e. 7.
libros.idioma editoriales.nombreEdt autores.pais libros.isbn libros.anio
Suponga que tenemos la tabla USUARIOS poblada con 1000 filas. De esos 1000 usuarios 50 no tienen email. ¿Qué pasaría en este punto al implementar la restricción «no puede haber dos usuarios con el mismo email» mediante un constraint tipo UNIQUE para la columna EMAIL? a. b. c. d.
En este caso la unicidad se valida solo para los valores no nulos. Solo daría error si entre los valores no nulos hay duplicados Da error, puesto que hay 50 usuarios con el mismo valor de email Da error, puesto que ese constraint se lo debió implementar cuando la tabla estaba vacía Nunca daría error, puesto que la restricción de unicidad aplicaría solo para los nuevos registros que se agreguen a partir de la creación del constraint
6 MODALIDAD ABIERTA Y A DISTANCIA
Tarea: Base de Datos Avanzada
6.
Criterio de evaluación Cada acierto de las interrogantes planteadas tiene una valoración de 0.4 pts, en total la actividad suma 2.8 puntos.
ACTIVIDAD 2 (1.6 puntos) 1.
Actividad de Aprendizaje Con base en el caso de estudio “Biblioteca” analice y proponga medidas de seguridad apropiadas para problema planteado en las interrogantes de esta actividad.
2.
Tema de la actividad Seguridad de la base de datos
3.
Competencia a la que aporta la actividad Proponer mecanismos que permitan garantizar la confidencialidad, integridad y disponibilidad de la base de datos.
4.
Orientaciones metodológicas (estrategia de trabajo) La actividad se basa en el caso práctico “Biblioteca” (descrito en el VER ANEXO al final del documento). Es importante que vaya avanzando progresivamente tanto con el estudio de la unidad 3 como con la realización de su tarea. Para el desarrollo de esta actividad, le sugerimos la siguiente estrategia: a.
b.
Mire la película titulada “Firewall” a.
Los datos de la película mencionada los puede observar en la URL: https://www.imdb.com/title/tt0408345/
b.
Identifique escenas dónde la seguridad está presente o ausente.
Identifique problemas de seguridad en general a.
Preste especial atención a los problemas de seguridad de los datos que puedan ser originados en las aplicaciones de usuario final.
7 La Universidad Católica de Loja
UTPL
Tarea: Base de Datos Avanzada
b.
c.
5.
Investigue en internet para identificar como un hacker podría atacar a la base de datos desde aplicaciones Web. Tome en cuenta que los ataques no precisamente implican son accesos a la base de datos, un ejemplo de ataque puede ser sobre cargar el trabajo del SGBD generando muchos accesos desde una aplicación Web (robots).
Identifique problemas de seguridad en un escenario específico. a.
Lea el escenario que se plantea en las interrogantes de la pregunta.
b.
Identifique primero los tipos de ataque que podrían ocurrir y priorice los que más pueden impactar en la seguridad de los datos. Lo que más se debería cuidar es la integridad de los datos.
c.
Ante las amenazas más graves, evalúe cómo podrían evitarse, o disminuir su probabilidad de ocurrencia. Analice todas las posibles medidas que se plantean en el texto y en la guía y otras que usted considere pueden ser efectivas.
d.
Elija las tres medidas que usted crea serían las más efectivas.
e.
Exponga las medidas, identificadas por usted, en la respuesta a la pregunta con la respectiva justificación.
Interrogantes de la actividad planteada 8.
Desarrolle la siguiente pregunta abierta El caso de estudio “Biblioteca” tiene implementadas dos aplicaciones Web para los usuarios finales (bibliotecarios y usuarios de biblioteca): a. b.
Una aplicación privada para administrar libros y préstamos, que solo la usarán los bibliotecarios. Una aplicación pública que permitirá consultar a través de internet el catálogo de libros. A ella podrá acceder cualquier persona y permitirá buscar y visualizar la información de los libros disponibles en la biblioteca.
Evalúe los riesgos a los que se exponen las aplicaciones mencionadas respecto a la seguridad de la base de datos.
8 MODALIDAD ABIERTA Y A DISTANCIA
Tarea: Base de Datos Avanzada
Además, el Jefe de Seguridad Informática le indica a usted, el DBA, que realice el documento de recomendaciones que deben implementarse para prevenir un ataque por parte de un hacker o agente malicioso al servidor de base de datos, que pueda afectar el rendimiento u operación o incluso acceder a los datos y dañar la información. Con estos antecedentes: Seleccione las recomendaría.
tres
medidas
más
importantes
que
usted
Enuncie cada medida y argumente las razones que justifican su implementación. 6.
Criterio de evaluación La pregunta se calificará sobre 1.6 puntos, aplicando la siguiente rúbrica: Escala Indicador 0
1
2
3
Medida 1 (30%)
No plantea medida, o medida planteada no tiene ninguna relación con el problema
La medida planteada tiene poca relación con el problema o su argumentación es débil
La medida planteada es válida. Pero su argumentación puede mejorar
La medida planteada es válida, y su argumentación es sólida
Medida 2 (30%)
No plantea medida, o medida planteada no tiene ninguna relación con el problema
La medida planteada tiene poca relación con el problema o su argumentación es débil
La medida planteada es válida. Pero su argumentación puede mejorar
La medida planteada es válida, y su argumentación es sólida
Medida 3 (30%)
No plantea medida, o medida planteada no tiene ninguna relación con el problema
La medida planteada tiene poca relación con el problema o su argumentación es débil
La medida planteada es válida. Pero su argumentación puede mejorar
La medida planteada es válida, y su argumentación es sólida
Dominio (10%)
No demuestra entender el problema planteado
Entiende el problema, pero las soluciones planteadas tienen poco impacto en su prevención
Entiende el problema, y su solución, pero debe mejorar la argumentación
Tiene claro el problema y cómo afrontarlo
9 La Universidad Católica de Loja
UTPL
Tarea: Base de Datos Avanzada
ACTIVIDAD 3 (1.6 puntos) 1.
Actividad de Aprendizaje Analice las consultas SQL pertenecientes al caso de estudio “Biblioteca” y desarrolle las interrogantes de esta actividad.
2.
Tema de la actividad Procesamiento y optimización de consultas
3.
Competencia a la que aporta la actividad Analizar e identificar la mejor estrategia para el procesamiento y ejecución de consultas SQL.
4.
Orientaciones metodológicas (estrategias de trabajo) La actividad se basa en el caso de estudio “Biblioteca” (Ver ANEXO al final del documento). Es importante que vaya avanzando constantemente tanto el estudio de la unidad 4 como la realización de su tarea. Para el desarrollo de esta actividad, le sugerimos la siguiente estrategia: a.
Primero, realice lo siguiente: 1. 2. 3.
b.
Estudie la unidad 4 hasta la optimización basada en costos. Es imprescindible que tenga clara la forma de interpretar correctamente las operaciones en algebra relacional. Preste especial atención a las reglas lógicas de simplificación que pueden aplicarse para las condicionales del WHERE.
Evalúe los casos propuestos en las interrogantes planteadas 1.
Tome en cuenta la estructura y restricciones del modelo de datos del caso de estudio.
2.
Antes de decidir y analizar su respuesta se puede ayudar poblando con algunos datos la base de datos implementada en la actividad 1, y sobre esta probar las consultas SQL (donde aplique).
10 MODALIDAD ABIERTA Y A DISTANCIA
Tarea: Base de Datos Avanzada
c.
5.
3.
Recuerde que las expresiones en algebra relacional siempre se evalúan de adentro hacia afuera.
4.
Para determinar el coste de planes de ejecución, aplique el proceso explicado en la guía. Recuerde que debe ir ejecutando una operación a la vez
Analice los casos propuestos y conteste a las interrogantes planteadas.
Interrogantes de la actividad planteada Seleccione la respuesta correcta. 9.
Dada la siguiente expresión en álgebra relacional:
¿Cuál es la representación correcta en formal de árbol? Opción 1:
11 La Universidad Católica de Loja
UTPL
Tarea: Base de Datos Avanzada
Opción 2:
Opción 3:
Respuesta: a. b. c.
Opción 1 Opción 2 Opción 3
12 MODALIDAD ABIERTA Y A DISTANCIA
Tarea: Base de Datos Avanzada
10.
Dada la siguiente consulta SQL que permite obtener los libros con más de 500 páginas publicados en 2016 en Argentina.
Determine el costo en términos de operaciones I/O para los siguientes planes de ejecución asociados la consulta planteada. Para ello suponga las siguientes estadísticas: ♦ ♦ ♦ ♦
editoriales: 200 libros: 10000 libros del 2016 con más de 500 páginas: 250 libros publicados en Argentina el 2016 con más de 500 páginas: 20
Plan A:
Plan B:
Plan C:
¿Cuál de los planes es más óptimo en términos del costo en operaciones I/O? a. b. c. d.
Plan A Plan B Plan C El costo de ejecución de los tres planes es el mismo
13 La Universidad Católica de Loja
UTPL
Tarea: Base de Datos Avanzada
11.
Suponga que deseamos obtener la lista de los libros publicados tanto en español como en francés, por la editorial LIMUSA y por la editorial THOMSON, desde el año 2010. Para tal fin se propone la siguiente sentencia SQL:
Luego de analizar la estructura sintáctica y semántica de la consulta podemos concluir que: Opción 1: La consulta es semánticamente incorrecta, lo correcto debería ser
Opción 2: La consulta es semánticamente incorrecta, lo correcto debería ser
14 MODALIDAD ABIERTA Y A DISTANCIA
Tarea: Base de Datos Avanzada
Opción 3: La consulta es sintácticamente incorrecta, lo correcto debería ser
Opción 4: La consulta es correcta, no hay errores sintácticos ni semánticos ¿Cuál es el análisis correcto? a. b. c. d. 12.
Opción 1 Opción 2 Opción 3 Opción 4
Para la misma consulta planteada en la pregunta anterior:
Cómo quedaría la consulta SQL simplificada luego del análisis que realiza el SGBD?
15 La Universidad Católica de Loja
UTPL
Tarea: Base de Datos Avanzada
Opción 1:
Opción 2:
Opción 3:
Opción 4: La consulta está bien formulada, no se puede simplificar más su enunciado Seleccione la opción correcta a. b. c. d. 6.
Opción 1 Opción 2 Opción 3 Opción 4
Criterio de evaluación Cada acierto de las interrogantes planteadas tiene una valoración de 0.4 pts, en total la actividad suma 1.6 puntos.
16 MODALIDAD ABIERTA Y A DISTANCIA
Tarea: Base de Datos Avanzada
Estimado(a) estudiante, una vez resuelta su tarea en el documento impreso (borrador), acceda al Entorno Virtual de Aprendizaje (EVA) en www.utpl.edu.ec e ingrese las respuestas respectivas.
SEÑOR ESTUDIANTE: Le recordamos que para presentarse a rendir las evaluaciones presenciales no está permitido el uso de ningún material auxiliar (calculadora, diccionario, libros, Biblia, formularios, códigos, leyes, etc.) Las pruebas presenciales están diseñadas para desarrollarlas sin la utilización de estos materiales.
17 La Universidad Católica de Loja
UTPL
Tarea: Base de Datos Avanzada
Tarea Segundo bimestre
Estimado(a) estudiante. Esta segunda tarea contiene 3 actividades que usted debe desarrollar: Actividad
Tema
Puntaje
Actividad 1
Gestión de transacciones
2.0 puntos
Actividad 2
Afinación de bases de datos
1.2 puntos
Actividad 3
Almacenes de datos
2.8 puntos TOTAL -> 6.0 puntos
La entrega de la tarea se hará a través del EVA, mediante un cuestionario que contendrá las interrogantes planteadas en el punto 5 de cada actividad. NOTA: En este documento se omiten las tildes en el nombre de las tablas y entidades, y signos especiales debido a restricción en los motores de bases de datos. ACTIVIDAD 1 (2 puntos) 1.
Actividad de Aprendizaje Con base al siguiente conjunto de transacciones: T1
T2
T3
T4
escribir(X)
leer(X)
leer(X)
leer(X)
leer(Y)
escribir(X)
escribir(Y)
escribir(X)
escribir(Z)
escribir(Y) escribir(Z)
Evalúe la estrategia que el SGBD debería seguir para la ejecución de una planificación concurrente, de manera que se preserve la integridad de los datos. En base a dicho análisis responda a las interrogantes planteadas. 2.
Tema de la actividad Gestión de transacciones
18 MODALIDAD ABIERTA Y A DISTANCIA
Tarea: Base de Datos Avanzada
3.
Competencia a la que aporta la actividad Diseñar planificaciones de ejecución concurrente de transacciones que garanticen la integridad y la recuperabilidad de los datos.
4.
Orientaciones metodológicas (estrategia de trabajo) Es importante que vaya avanzando constantemente tanto el estudio de la unidad 4 como la realización de su tarea. Para el desarrollo de esta actividad, le sugerimos la siguiente estrategia: a.
Analice el conjunto de transacciones planteadas en el enunciado de la actividad.
b.
Desarrolle las planificaciones propuestas:
c.
1.
Seleccione una planificación secuencial de partida para la construcción de una planificación secuenciable, es decir, una planificación concurrente que garantice la consistencia de los datos.
2.
Primero obtenga una planificación secuenciable en términos de conflictos, y luego evalúe si se puede realizar alguna intercalación adicional para una planificación secuenciable en términos de vistas.
3.
Repita los dos pasos anteriores partiendo de una planificación secuencial diferente.
4.
Compare si en todos los casos se llega a la misma planificación secuenciable.
5.
Evalúe si para las planificaciones secuenciables obtenidas son además planificaciones recuperables. Prevea que la confirmación (commit) de cada transacción ocurre inmediatamente después de la última operación. En caso de que no sean recuperables haga los cambios necesarios para que lo sean.
En base la experiencia y práctica desarrollada, analice las planificaciones y problemas enunciados en el cuestionario y responda a las interrogantes planteadas.
19 La Universidad Católica de Loja
UTPL
Tarea: Base de Datos Avanzada
5.
Interrogantes de la actividad planteada Seleccione la respuesta correcta. 1.
No es posible que una transacción empiece escribiendo un elemento de datos, tal como ocurre en la transacción T1, siempre debe primero realizar una operación de lectura. a. b.
2.
Verdadero Falso
Suponga que las transacciones se ejecutan concurrentemente, de la siguiente forma:
Se ha comprobado que la planificación anterior es secuenciable respecto a la planificación secuencial T1 -> T2 -> T3 -> T4, pero no es recuperable, ¿por qué no lo es? a. b. c. d.
Porque T2 depende de T4 y termina antes que T4; T2 debería terminar después de T4 Porque T4 depende de T2 y termina después que T2; T4 debería terminar antes de T2 Porque T3 depende de T2 y termina antes de T2; T3 debería terminar después que T2 En realidad, si es recuperable, T4 depende de T2 y como corresponde primero se confirma T2 y luego T4
20 MODALIDAD ABIERTA Y A DISTANCIA
Tarea: Base de Datos Avanzada
3.
Suponga que las cuatro transacciones se ejecutan concurrentemente, de la siguiente forma:
Luego de analizar la planificación mediante el grafo de precedencias se concluye que la planificación SI es secuenciable en términos de conflictos a. b. 4.
Verdadero Falso
Considere la siguiente planificación que permite ejecutar concurrentemente las 4 transacciones, que es secuenciable respecto a T1 -> T2 -> T4 -> T3, y es recuperable:
Analice e identifique ¿qué ocurriría si durante la ejecución de la planificación, la transacción T1 falla justo cuando estaba a punto de escribir(Z)?
21 La Universidad Católica de Loja
UTPL
Tarea: Base de Datos Avanzada
a. b. c. d. e. f. g. 5.
El SGBD deberá deshacer las transacciones T1 y T2 El SGBD deberá deshacer las transacciones T1 y T3 El SGBD deberá deshacer las transacciones T1 y T4 El SGBD deberá deshacer las transacciones T1, T2 y T3 El SGBD deberá deshacer todas las cuatro transacciones El SGBD solo debe deshacer la transacción que falló No se debe deshacer ninguna transacción
Deseamos comprobar si en este caso la planificación secuenciable resultante de las 4 transacciones propuestas será siempre la misma sin importar cuál sea la planificación secuencial de partida. Construya dos planificaciones secuenciables, la primera partiendo de la planificación secuencial T1 -> T2 -> T3 -> T4, y la segunda partiendo de T1 -> T3 -> T2 -> T4. Compare ambas planificaciones y responda: a. b.
6.
Son equivalentes No son equivalentes
Criterio de evaluación Cada acierto de las interrogantes planteadas tiene una valoración de 0.4 pts, en total la actividad suma 2 puntos.
ACTIVIDAD 2 (1.2 puntos) 1.
Actividad de Aprendizaje Reflexione sobre la introducción controlada de redundancia (desnormalización) sobre el esquema de datos del caso de estudio “Biblioteca” con la finalidad optimizar el desempeño en esta base de datos, y responda a las interrogantes planteadas.
2.
Tema de la actividad Afinamiento de bases de datos
3.
Competencia a la que aporta la actividad Evaluar y proponer acciones para mejorar el desempeño de una base de datos.
22 MODALIDAD ABIERTA Y A DISTANCIA
Tarea: Base de Datos Avanzada
4.
Orientaciones metodológicas (estrategias de trabajo) La actividad se basa el caso de estudio “Biblioteca” (Ver ANEXO al final del documento). Es importante que vaya avanzando constantemente tanto el estudio de la unidad 6 como la realización de su tarea. Para el desarrollo de esta actividad, le sugerimos la siguiente estrategia: a.
b. 5.
Analice el caso de estudio “Biblioteca”. 1.
Identifique las transacciones donde se podrían aplicar las técnicas de desnormalización estudiadas.
2.
Por cada técnica que haya identificado, reflexione acerca de las repercusiones que la desnormalización podría conllevar respecto a la integridad de los datos
Con base al análisis previo desarrolle las interrogantes planteadas.
Interrogantes de la actividad planteada Seleccione la respuesta correcta. 6.
Analice la tabla USUARIOS de nuestro caso de estudio, suponga que a fin de optimizar el modelo dividimos esta tabla en las dos siguientes:
¿Cuál de las siguientes afirmaciones evalúa mejor el cambio realizado? a. b.
El cambio no es correcto, se perdería información e integridad de los datos. Desde el punto de vista de la normalización, el modelo se optimiza y flexibiliza, y además mejora el desempeño de consultas donde se muestran los teléfonos del usuario.
23 La Universidad Católica de Loja
UTPL
Tarea: Base de Datos Avanzada
c.
d.
7.
Desde el punto de vista de la normalización el modelo se optimiza y flexibiliza, pero puede afectar al desempeño de consultas donde se muestren los teléfonos del usuario. El cambio sería válido solo cuando se tenga la seguridad de que ningún usuario tiene más de tres teléfonos.
Suponga la siguiente consulta SQL que forma parte de la implementación de la transacción de datos «Listar autores», que muestra los datos de cada autor, incluyendo el número de libros disponibles en biblioteca de ese autor, en este caso solo los libros de idioma español:
Para efectos de simplificar la consulta y hacerla más eficiente decidimos hacer una modificación en las tablas, quedando así su estructura:
Y la consulta se simplifica a:
¿Cuál es la técnica de desnormalización que se aplicó en este caso? a. b. c. d. e.
Duplicidad de atributos no clave en asociaciones 1:N Inclusión de atributos derivados Atributos multivaluados Inclusión de atributos complementarios Combinación de tablas con asociación 1:1
24 MODALIDAD ABIERTA Y A DISTANCIA
Tarea: Base de Datos Avanzada
8.
Suponga la siguiente consulta que obtiene información de autores que han publicado libros en 2017.
A efectos de mejorar el rendimiento se decide desnormalizar aplicando la técnica de “duplicación de atributos no clave en relaciones M:N”. ¿Cómo quedan las tablas luego de desnormalizar? Opción 1:
Opción 2:
Opción 3:
Opción 4:
Elija la opción correcta a. b. c. d. e.
Opción 1 Opción 2 Opción 3 Opción 4 Ninguna de las anteriores es correcta
25 La Universidad Católica de Loja
UTPL
Tarea: Base de Datos Avanzada
6.
Criterio de evaluación Cada acierto de las interrogantes planteadas tiene una valoración de 0.4 pts, en total la actividad suma 1.2 puntos.
ACTIVIDAD 3 (2.8 puntos) 1.
Actividad de Aprendizaje Analice y diseñe cubos OLAP para el caso de estudio “Biblioteca” y conteste a las interrogantes de la actividad.
2.
Tema de la actividad Almacenes de datos
3.
Competencia a la que aporta la actividad Analizar, evaluar y justificar la implementación de un almacén de datos como solución estratégica para la toma de decisiones.
4.
Orientaciones metodológicas (estrategias de trabajo) La actividad se basa en el caso de estudio “Biblioteca” (Ver ANEXO al final del documento). Es importante que avance progresivamente con el estudio tanto de las 8 unidades como de su tarea. Para el desarrollo de esta actividad, le sugerimos la siguiente estrategia: a.
Primeros pasos: 1.
Asegúrese que ha comprendido la diferencia entre bases de datos transaccionales y los almacenes de datos, de manera que se facilite su análisis.
2.
Es importante que haya entendido la estructura y composición de un cubo OLAP, que sepa identificar y diferenciar entre hechos, dimensiones y métricas o indicadores.
26 MODALIDAD ABIERTA Y A DISTANCIA
Tarea: Base de Datos Avanzada
b.
c.
Proceda a responder las preguntas objetivas de opción múltiple. 1.
Identifique los componentes del cubo planteado.
2.
Luego analice la respuesta a las consultas planteadas.
Diseñe del cubo OLAP solicitado 1. 2. 3. 4. 5. 6.
d.
5.
El objetivo es diseñar un solo cubo OLAP que satisfaga los cuatro informes planteados. Identifique primero los hechos que se desea analizar en función del enunciado. Identifique luego los indicadores (medidas) requeridos para el análisis. Luego, determine las perspectivas para analizar los hechos (dimensiones). Identifique si existen dimensiones relacionadas jerárquicamente. Finalmente, construya el modelo multidimensional.
Realice la explotación de datos mediante consultas SQL 1.
Realice las consultas SQL para la base de datos transaccional.
2.
Realice las consultas SQL para el almacen de datos.
3.
Tenga presente que el primer objetivo de la actividad es que usted pueda evidenciar que muchos de los informes gerenciales se pueden obtener de una base transaccional, pero que se pueden obtener de forma más sencilla desde un almacén de datos. El segundo objetivo es contrastar ambas soluciones de cada transacción de consulta solicitada.
Interrogantes de la actividad planteada Seleccione la respuesta correcta 9.
Considere el siguiente cubo OLAP diseñado con el objetivo de analizar la información de los préstamos que se realizan en la biblioteca
27 La Universidad Católica de Loja
UTPL
Tarea: Base de Datos Avanzada
Identifique ¿Cuál de los siguientes informes NO se podría obtener? a. b. c. d. e. 10.
Promedio diario de préstamos realizados en 2017 Número de préstamos realizados desde 2016, clasificados por editorial e idioma Número de préstamos que registran devolución tardía, por cada mes en 2017, de libros sobre “Arquitectura” Número total de libros en los que es autor “Luis Joyanes Aguilar” Realmente, permite obtener todos los informes anteriores
Dado el cubo OLAP de la pregunta anterior. Suponga que el director de la biblioteca quiere analizar estadísticas del número de préstamos realizados en la biblioteca. ¿Con cuántos atributos de dimensión se podría jugar para generar dicha estadística? Dicho de otra manera, ¿desde cuantas perspectivas podría analizarse ese indicador de acuerdo al cubo planteado? a. b. c. d. e. f.
11.
1 2 3 4 9 11
Analice la tabla de dimensión fecha_prestamo, que forma parte del cubo OLAP propuesto en las preguntas anteriores.
28 MODALIDAD ABIERTA Y A DISTANCIA
Tarea: Base de Datos Avanzada
Si como es lógico, los campos anio, mes y trimestre podrían obtenerse a partir de la fecha, ¿por qué en este caso conviene incluirlos como atributos separados? a. b. c.
d. e.
Porque las herramientas OLAP no permiten calcular esos campos a partir de una fecha Porque una tabla dimensional debe tener al menos dos atributos de dimensión Porque el tenerlos separados mejora el rendimiento las operaciones de consulta de la información, pues evita demoras al realizar cálculos Porque es la norma en dimensiones temporales Porque realmente en este caso esos campos corresponden a otra información que no tiene que ver con la fecha
Pregunta abierta 12.
Para nuestro caso de estudio “biblioteca”, diseñe un cubo OLAP que permita obtener las siguientes estadísticas: a.
b.
Número total de ejemplares de libros existentes en inventario que han sido publicados por la editorial “MCGRAW-HILL” en la década del 2000. Número de ejemplares de libros sobre Biología que han sido dados de baja, clasificados por el país origen de la publicación
Para la presentación debe elaborar un solo documento en formato PDF donde se incluya ♦ ♦
El diagrama multidimensional (cubo OLAP) Las consultas SQL para cada informe planteado. Formule dos consultas SQL por cada informe: ♦ La primera que obtenga el informe desde la base de datos transaccional ♦ La segunda que obtenga el informe desde el almacén de datos (desde cubo OLAP).
El documento deberá adjuntarlo al enviar su tarea a través del EVA de acuerdo a las instrucciones que le dará su profesor tutor
29 La Universidad Católica de Loja
UTPL
Tarea: Base de Datos Avanzada
5.
Criterio de evaluación Cada acierto de las preguntas objetivas de opción múltiple tiene una valoración de 0.4 pts. La pregunta abierta se calificará sobre 1.6 puntos, aplicando la siguiente rúbrica: Escala Indicador 0
1
2
3
Diseño multidimensional (55%)
No presenta O diseño planteado no tiene nada que ver con lo que se solicita
El diseño presentado intenta dar solución al problema, pero la identificación de hechos y dimensiones es incorrecta
En el diseño presentado evidencia que tiene claro cuales los hechos y las dimensiones, pero hay errores jerarquización de las dimensiones y la identificación de las métricas
El diseño presentado es correcto. Identifica bien hechos, dimensiones y métricas
Obtención de informes a partir de base de datos transaccional y almacén de datos (35%)
No presenta. O en general las consultas planteadas no se corresponden en absoluto con lo que se pide
Presenta menos del 50% de las consultas o aún se evidencian muchos errores sintácticos y/o semánticos en su formulación
No presenta todas las consultas o aún hay que corregir ciertos errores semánticos en su formulación.
Presenta todas las consultas y en general están bien formuladas
Dominio de la problemática planteada (10%)
No demuestra entender el problema planteado
Entiende el problema, pero no la solución.
Entiende el problema, pero aún hay que mejorar la solución
Tiene claro el problema y a la solución que se debe aplicar
En total la actividad tiene una valoración de 2.8 puntos
30 MODALIDAD ABIERTA Y A DISTANCIA
Tarea: Base de Datos Avanzada
ANEXO Caso de estudio “Biblioteca” Descripción Se desea implementar una base de datos para una biblioteca, que permita llevar el inventario de los libros ingresados y el registro de los préstamos que realizan diariamente a los usuarios de la biblioteca. Después de realizar el estudio preliminar, donde se definieron los alcances, las vistas de usuario, los requerimientos de datos y las transacciones de datos; primero se procedió a realizar el diseño conceptual (modelo E-R), y luego el diseño lógico se trabaja mediante el modelo de base de datos relacional. La Fig. 1 muestra el modelo lógico resultante. Diseño lógico Diagrama relacional
P = Primary Key, F = Foreign Key, U=No admite valores duplicados, * = Campo obligatorio Fig. 1. Diagrama lógico mediante modelo relacional del caso de estudio “Biblioteca”
31 La Universidad Católica de Loja
UTPL
Tarea: Base de Datos Avanzada
Restricciones adicionales de dominio Tabla
Columna
Restricción de dominio adicional
prestamos
fechaDevolucion
No debe ser inferior a la fecha de préstamo
libros
numPaginas
Debe ser mayor a 0 y menor a 2000
libros
nroEdicion
Debe ser mayor a 0
ejemplares
status
Tres posibles valores: “D” (Disponible) “A” (Alquilado) “E” (Dado de baja)
libros
anio
debe ser mayor a 1850
libros
cantEjemplares
No puede ser negativo
usuarios
email
debe necesariamente incluir el carácter especial “@” y debe incluir al menos dos caracteres antes y después de la arroba
Otras restricciones de integridad 1.
Libros.cantEjemplares es un campo derivado, corresponde a la cantidad de ejemplares existentes por cada libro. Equivale a contar las filas asociadas a cada libro en la tabla ejemplares. Debe estar siempre actualizado.
2.
Cada libro debe tener al menos un autor.
3.
Un libro no puede tener más de ocho autores.
4.
No puede haber dos libros con el mismo ISBN.
5.
No puede haber dos usuarios con el mismo email.
6.
En la tabla autorias un mismo libro no puede tener dos o más autores con el mismo orden (el orden es la posición en la que aparece el autor en la lista de autores del libro).
7.
En una misma fecha a un mismo usuario no se le puede prestar dos ejemplares del mismo libro.
8.
A un usuario no se le puede registrar el préstamo de un libro, si tiene préstamos vencidos.
32 MODALIDAD ABIERTA Y A DISTANCIA
Tarea: Base de Datos Avanzada
Transacciones de datos Nro
Freqmensual
Tipo
Transacción
Descripción
1
200
Actualización
Dar de alta autor
2
30
Actualización
Modificar datos de autor
Modificar datos de autor (no autorías). Implica que primero se debe encontrar el autor en base su nombre
3
1
Actualización
Eliminar autor
Eliminar autor de la base de datos. Implica que primero se debe encontrar el autor en base su nombre
4
500
Actualización
Registrar libro
Agregar nuevo libro al inventario, incluida la información de autoría y ejemplares
5
50
Actualización
Actualizar información de libro
Modificar datos del libro, agregar o quitar autores, agregar ejemplares, etc. Implica que primero se deberá buscar el libro en base su título o su ISBN
6
5
Actualización
Dar de baja ejemplar de un libro
Registrar la baja del ejemplar de un libro. Implica que primero se debe encontrar el libro en base su título o su ISBN
7
5000
Actualización
Registrar préstamo*
Registrar préstamo de un libro a un usuario. Si el usuario lleva varios libros, se registran varios préstamos, Implica además actualizar el status del ejemplar prestado
8
5000
Actualización
Registrar devolución de préstamo*
Registro de cuando el usuario devuelve el libro, Implica además actualizar el status del ejemplar prestado. Implica que primero se debe encontrar el préstamo en base al nombre del usuario, o al nombre del libro
9
130
Actualización
Dar de alta usuario
10
30
Actualización
Modificar datos de usuario
Registrar ficha de nuevo autor
Crear un nuevo usuario.
Modificar datos del usuario. Implica que primero se debe encontrar el usuario en base su cédula o a sus apellidos
33 La Universidad Católica de Loja
UTPL
Tarea: Base de Datos Avanzada
Nro
Freqmensual
Tipo
Transacción
Descripción
11
50000
Consulta
Consultar ficha de un libro
Mostrar la información completa de un libro. Implica que primero se debe encontrar el libro en base su título o su ISBN. La ficha además de incluir información del libro, sus autores, editorial y área; Debe mostrar también la lista de ejemplares con su ubicación.
12
3000
Consulta
Consultar ficha de autor
Mostrar información de un autor. Implica que primero se debe encontrar el autor en base su nombre
13
500
Consulta
Consultar ficha de préstamo*
Mostrar detalle de un préstamo. Implica que primero se debe encontrar el préstamo en base al nombre del usuario, o al nombre del libro
14
20
Consulta
Consultar ficha de usuario
Mostrar información de un usuario. Implica que primero se debe encontrar el usuario en base su cédula o a sus apellidos
15
20000
Consulta
Listar libros disponibles*
16
200
Consulta
Listar autores
Listar autores en base a criterios de selección como país, editorial con la que ha publicado, área de conocimiento de sus obras
17
50
Consulta
Listar préstamos pendientes
Listar los préstamos que aún no tienen fecha de devolución
18
50
Consulta
Listar préstamos vencidos
Listar los préstamos que aún no tienen fecha de devolución y han superado el plazo
19
5
Consulta
Estadística libros por editorial
Número de libros por editorial.
20
5
Consulta
Estadística Número de libros por área de libros por área conocimiento. de conocimiento
21
3
Consulta
Estadísticas de préstamos por periodo
Promedio de préstamos diarios por mes
22
5000
Consulta
Libros más populares*
Listar los libros que son más solicitados en préstamo
Listar libros en base a criterios de selección como anio, idioma, área de conocimiento, editorial, autor
34 MODALIDAD ABIERTA Y A DISTANCIA
Tarea: Base de Datos Avanzada
Nro
Freqmensual
Tipo
Transacción
23
5
Consulta
Usuarios más activos
Descripción Listar los usuarios que en promedio realizan más préstamos por mes
*Siempre que se requiera visualizar la información general de un libro, debe mostrarse como mínimo: el ISBN, el título, el año, la editorial y el 1er autor. SGBD elegido ● ●
Oracle Express Edition Links descarga: ♦ Oracle Database Express Edition: https://rebrand.ly/taworadb ♦ Oracle SQL Developer: https://rebrand.ly/taworadev
Consideraciones para el cálculo de espacio en disco ●
Número de ítems que se proyecta almacenar a 5 años ♦ 15000 autores ♦ 40000 libros, con un promedio de 5 ejemplares por libro y un promedio de 3 autores por libro ♦ 200 editoriales ♦ 120 áreas de conocimiento ♦ 10000 usuarios ♦ 300000 préstamos
●
El tamaño de la imagen portada de libro no debe superar los 300KB
Estimado(a) estudiante, una vez resuelta su tarea en el documento impreso (borrador), acceda al Entorno Virtual de Aprendizaje (EVA) en www.utpl.edu.ec e ingrese las respuestas respectivas.
SEÑOR ESTUDIANTE: Le recordamos que para presentarse a rendir las evaluaciones presenciales no está permitido el uso de ningún material auxiliar (calculadora, diccionario, libros, Biblia, formularios, códigos, leyes, etc.) Las pruebas presenciales están diseñadas para desarrollarlas sin la utilización de estos materiales.
35 La Universidad Católica de Loja
UTPL