Base Datos

Asignatura Administración de Bases de Datos - Ingeniería Técnica en Informatica de Gesyión/Sistemas Depto. Organización

Views 464 Downloads 0 File size 210KB

Report DMCA / Copyright

DOWNLOAD FILE

Recommend stories

Citation preview

Asignatura Administración de Bases de Datos - Ingeniería Técnica en Informatica de Gesyión/Sistemas Depto. Organización y Estructura de la Información - E.U. Informática – UPM

Pág. 1 Curso 2012/2013

PRÁCTICA DE ADMINISTRACIÓN DE BASES DE DATOS Una compañía de salud gestiona datos relativos a las consultas médicas a pacientes por parte de médicos asociados a la compañía. Un día se produce un incendio en la sala donde se encuentra la base de datos. Tras el incendio se pierde la base de datos y toda la documentación relativa a la base de datos y procesos que accedían a ella, excepto una versión del esquema de datos (Anexo 1). Afortunadamente el ABD almacenaba regularmente copia de los datos en otro lugar, si bien, las copias que realizaba consistían en una exportación de la base de datos a ficheros texto. Se desconoce si el esquema de datos es consistente respecto de los datos de los ficheros de texto. En caso de existir diferencias entre ellos, prevalecerá siempre el esquema derivado del contenido de los ficheros de texto, ya que corresponde a la última versión utilizada de la base de datos. Tras el incidente, la empresa solicita nuestros servicios para recuperar la base de datos y realizar otra serie de acciones. Se pide realizar una memoria que contemple los apartados que se detallan a continuación, justificando para cada apartado las soluciones planteadas. Para la realización de la práctica se utilizará el SGBDR Oracle 11g o bien Oracle 10g, que se encuentra disponible en la web de Oracle (http://www.oracle.com/es/index.html). Para crear la base de datos se nos proporcionan seis ficheros de texto con registros de longitud fija que contienen una pequeña cantidad del conjunto global de datos.

1.

CREACIÓN Y CARGA INICIAL DE LA BASE DE DATOS

Se pide realizar las siguientes acciones, creando los correspondientes ficheros script SQL cuando sea posible: 1. Creación de la base de datos, de nombre PracABD. 2. Creación de un nuevo usuario de nombre ABDDBA y contraseña “abddba”, con el que se realizarán todas las acciones relativas a esta base de datos. 3. Estimación del espacio físico necesario para almacenar cada una de las tablas de la BD. A continuación crear y asignar a la BD los espacios físicos (datafiles) y lógicos (tablespaces) que se estimen oportunos, justificando la estimación realizada. Crear un tablespace para cada tabla, algunos de ellos de tamaño fijo y de tamaño menor que el estimado para la tabla. Otros que permitan incrementos pero también de tamaño menor (añadir si es preciso más datafiles) y otros de tamaño mayor que el estimado. Los nombres de tablespaces y datafiles irán precedidos del nombre del usuario: ABDDBA_TBLS_nombretabla, ABDDBA_DF_nombretabla respectivamente. 4. Creación de las tablas de la BD en los espacios creados anteriormente. 5. Insertar los datos en las tablas a partir de los ficheros de texto proporcionados, mediante la utilidad SqlLoader de Oracle, creando los ficheros ctl necesarios (ver el fichero proporcionado de ejemplo de carga de datos con SqlLoader). 6. Definición de las claves principales y foráneas de las tablas, creando los siguientes cuatro scripts: a) Creación de las claves primarias de cada tabla (sentencia Alter Table Add Constraint...) b) Creación de las claves foráneas (sentencia Alter Table Add Constraint...) c) Borrado de las claves foráneas de las tablas d) Borrado de las claves primarias de las tablas Deberá justificarse si los índices se deben crear antes o después de hacer la carga de los datos, tendremos en cuenta solamente los índices creados automáticamente por Oracle con la definición de clave primaria y foránea. Para ello, se medirá el tiempo de ejecución en cada uno de los casos. Por tanto, se deberá obtener el tiempo de ejecución de las sentencias de los ficheros script, en la siguiente secuencia de los pasos definidos anteriormente: 1. Índices antes de la carga: 4, 6a, 6b, 5 2. Índices después de la carga: 4, 5, 6a, 6b Nota: La definición de clave principal o foránea lleva implícita la creación automática de un índice.

Asignatura Administración de Bases de Datos - Ingeniería Técnica en Informatica de Gesyión/Sistemas Depto. Organización y Estructura de la Información - E.U. Informática – UPM

2.

Pág. 2 Curso 2012/2013

ESTUDIO DE INDICES

En este apartado se realizará un estudio de índices para la base de datos, reflejando en la memoria el proceso seguido y los resultados obtenidos en la ejecución de las sentencias que se acompañan en el Anexo 2: a) Eliminar las claves foráneas y principales de las tablas. Medir los tiempos de ejecución de cada sentencia del Anexo 2. b) Crear las claves principales y foráneas de las tablas. Medir los tiempos de ejecución de cada sentencia del Anexo 2. c) Con las claves primarias y foráneas creadas, determinar el conjunto de índices candidatos para cada sentencia del Anexo 2: 1. Crear índices para los atributos candidatos (salvo para los que sean clave o figuren únicamente en la cláusula Select) 2. Determinar el tiempo después de la creación de cada índice, de forma individual, y en combinación con otros índices. Deberá indicarse el tiempo de ejecución y los índices que están activos en ese momento. Para indicar los tiempos de ejecución utilizar una plantilla similar a la siguiente tabla: Clave Primaria

√ √ √

Claves Foráneas

√ √

fecha_nac

ciudad



apellido1

Fecha_nac ciudad apellido1

Tiempo

……. ……. ……. …….

d) En función de los tiempos obtenidos, debe especificarse qué índices se consideran más adecuados para que cada sentencia se ejecute en el menor tiempo posible. Notas:

1. Para establecer correctamente los tiempos de ejecución de las sentencias, debe tenerse en cuenta que tras la ejecución de una consulta, todos o parte de los datos se encuentran en memoria principal. 2. La medición de tiempos se realizará por sentencias individuales, no debe medirse la ejecución de varias sentencias a la vez. 3. Para cada sentencia del Anexo 2 se definirá un máximo de cinco índices distintos, además de los correspondientes a claves primarias y foráneas.

3.

REORGANIZACIONES FÍSICAS DE LA BD

Analizar y documentar el estado actual de los tablespaces creados en la BD, tanto para datos de tablas como de índices. Indicar el tanto por ciento de llenado y el número de extensiones que tiene cada tablespace. Analizar el espacio asignado inicialmente y el utilizado finalmente para cada tabla de la BD, justificando razonadamente las conclusiones de dicho análisis. Realizar una reorganización física de la base de datos, comprobando si se producen mejoras de rendimiento en relación a las sentencias SQL del Anexo 2. Indicar los valores de almacenamiento para los espacios de la base de datos antes y después de las reorganizaciones. Organizar primeramente solo algunos de los objetos tabla y/o índice, y después el esquema o algunos tablespaces completos.

4.

REORGANIZACIÓN LÓGICA DE LA BD

En este apartado se pide realizar reorganizaciones lógicas de la base de datos (cambios en el esquema lógico) que permitan mejorar el rendimiento de la base de datos reduciendo el espacio de almacenamiento y/o los tiempos de respuesta. Para ello se deberá crear una nueva base de datos, o modificar la estructura de la existente. Ejecutar las sentencias SQL que se estimen necesarias (incluidas o no en el Anexo 2) para establecer y justificar el aumento o pérdida de rendimiento de la reorganización efectuada respecto del diseño de partida.

Asignatura Administración de Bases de Datos - Ingeniería Técnica en Informatica de Gesyión/Sistemas Depto. Organización y Estructura de la Información - E.U. Informática – UPM

5.

Pág. 3 Curso 2012/2013

AREA DE MEMORIA

Modificar el tamaño del área SGA, al mínimo que se permita y al máximo (o un valor alto). En ambos casos, indicar en qué medida varía el rendimiento del gestor en las consultas definidas, respecto del valor actual y del valor que aconseja Oracle. ¿Y el tamaño de PGA influye en la ejecución de las consultas? Los parámetros de SGA y PGA pueden modificarse desde el Enterprise Manager, en la ventana de administración, opción “Parámetros de memoria”. ¡Ojo! al bajar el valor de SGA la consulta puede pasar a tardar bastantes minutos. Si al intentar restaurar los valores Oracle no lo permite, iniciar sesión como usuario SYS con rol SYSDBA, y realizar la modificación. Para analizar el rendimiento al variar los parámetros de memoria utilizar únicamente una de las consultas del Anexo 2 ya ejecutada en los apartados anteriores, comparando los resultados de las ejecuciones.

6.

CATÁLOGO DE LA BASE DE DATOS

Crear las siguientes vistas con información exclusivamente de las tablas creadas de la base de datos PRACABD (es decir, excluyendo los objetos propios del catálogo), y efectuar después una consulta sobre ellas mostrando el resultado obtenido: a) Por cada índice: nombre del índice, nombre de la tabla a la que indexa, y por cada atributo que componga el índice (si el índice tiene más de un atributo se visualizaran varias filas) el nombre, la posición que ocupa en la clave del índice, el tipo de datos y la longitud en bytes del atributo. b) Por cada índice: nombre de la tabla a la que indexa, nombre del índice, si está activo o no, y la longitud total de la clave de índice (para las tablas de la BD, excluyendo las del catálogo). c) Información sobre estadísticas de cada tabla de la base de datos, incluyendo el nombre de la tabla, el número de filas, longitud media de las filas, el número de bloques ocupados, número de bloques vacíos, y fecha de realización del último análisis. La información del catálogo de una base de datos de Oracle puede obtenerse a partir de una serie de vistas que Oracle define al crear la base de datos. Entre ellas, algunas de las que nos pueden interesar para resolver este apartado, son: • • • •

dba_indexes dba_ind_columns dba_tab_columns dba_tab_statistics

Para que el usuario ABDDBA pueda consultar estas vistas del catálogo será necesario asignarle privilegios de consulta sobre ellas. Por otra parte, para actualizar las estadísticas de una tabla concreta se debe ejecutar el comando: ANALYZE TABLE nombre_tabla COMPUTE STATISTICS FOR TABLE;

7.

SEGURIDAD

En este apartado comprobaremos la asignación de privilegios a usuarios. Realizar lo siguientes pasos, especificando en la memoria las sentencias ejecutadas y los resultados obtenidos: a) Crear dos usuarios de nombre “usuclinica” y “usugestor” respectivamente. b) Asignar a ususucursal todos los privilegios sobre las tablas paciente, medico, consulta y atencion_medica. c) Asignar a usugestor: 1. Todos los privilegios sobre las tablas clinica y personal_servicio. 2. Privilegio de consulta sobre las tablas medico y consulta. 3. Privilegio de modificación del atributo salario de la tabla medico. d) Acceder a la BD como usuario usuclinica, y ejecutar al menos dos sentencias sobre las que tenga privilegios y otras dos sobre las que no lo tenga. e) Acceder a la BD como usuario usugestor, y ejecutar al menos dos sentencias sobre las que tenga privilegios y otras dos sobre las que no lo tenga. f) Intentar crear con uno de estos usuarios, cualquier nuevo objeto sobre la BD, por ejemplo un índice sobre alguna tabla. Explicar el resultado

Asignatura Administración de Bases de Datos - Ingeniería Técnica en Informatica de Gesyión/Sistemas Depto. Organización y Estructura de la Información - E.U. Informática – UPM

8.

Pág. 4 Curso 2012/2013

EJECUCIÓN CONCURRENTE DE TRANSACCIONES Realizar un estudio detallado de la ejecución de transacciones mediante dos sesiones de isqlplus y explicar el resultado obtenido, con todas las opciones de bloqueo y modos de operación permitidos. Ejecutar concurrentemente dos transacciones utilizando la sentencia SQL SET TRANSACTION, cubriendo los siguientes casos: 1. lectura-lectura 2. lectura-escritura 3. escritura-escritura Cuando sea posible deberá forzarse una situación de interbloqueo entre las transacciones participantes en la ejecución.

ANEXO 1. ESQUEMA DE LA BASE DE DATOS PACIENTE: Atributo CodPaciente Dni Nombre Apellido1 Apellido2 Direccion Ciudad Telefono Fecha_Nac Medicamentos Operaciones Habitos

Tipo Integer Integer Varchar (30) Varchar (20) Varchar (20) Varchar (60) Varchar (30) Varchar (9) Date Varchar (100) Varchar (100) Varchar (50)

Restricciones Clave primaria No nulo No nulo No nulo No nulo No nulo No nulo

Tipo Smallint Integer Varchar (30) Varchar (20) Varchar (20) Varchar (60) Varchar (5) Varchar (9) Varchar (9) Varchar (9) Decimal (8,2) Varchar(6) Date Smallint

Restricciones Clave Primaria No nulo No nulo No nulo

Tipo Smallint Varchar(60) Varchar(30) Varchar (9)

Tipo Integer Varchar(10) Varchar(2) Varchar (20) Smallint

Restricciones Clave Primaria No nulo No nulo No nulo No nulo

PERSONAL_SERVICIOS:

No nulo No nulo

Atributo Cod_Personal Dni Nombre Apellido1 Apellido2 Direccion Ciudad Telefono Movil Salario Fecha_incorp Puesto Cod_Clinica

Tipo Integer Integer Varchar (15) Varchar (15) Varchar (15) Varchar (60) Varchar (5) Varchar (9) Varchar (9) Decimal (8,2) Date Varchar (10) Smallint

No nulo

ATENCION_MEDICA:

Restricciones Clave Primaria No nulo No nulo No nulo

Atributo CodPaciente Cod_Medico Cod_Consulta Fecha Hora Motivo Observaciones

CLINICA: Atributo Cod_Clinica Direccion Ciudad Telefono

Atributo Cod_consulta Planta Sala Especialidad Cod_Clinica

No nulo

MEDICO: Atributo Cod_Medico Dni Nombre Apellido1 Apellido2 Direccion Ciudad Telefono Movil Num_Busca Salario Turno Fecha_incorp Cod_Clinica

CONSULTA:

Tipo Smallint Smallint Smallint Date Varchar(5) Varchar(12) Varchar (100)

Restricciones Clave Primaria No nulo No nulo No nulo No nulo No nulo No nulo No nulo No nulo No nulo No nulo No nulo

Restricciones Clave Primaria Clave Primaria Clave Primaria Clave Primaria Clave Primaria No nulo

Asignatura Administración de Bases de Datos - Ingeniería Técnica en Informatica de Gesyión/Sistemas Depto. Organización y Estructura de la Información - E.U. Informática – UPM

Pág. 5 Curso 2012/2013

ANEXO 2. SENTENCIAS SQL Consulta 1. SELECT nombre, apellido1, apellido2, p.ciudad FROM paciente p, atencion_medica a, consulta c WHERE fecha_nac BETWEEN ‘01/01/1970’ AND ‘31/12/1989’ AND especialidad = “Especialidad 5” AND p.cod_paciente=a.cod_paciente AND a.cod_consulta=c.cod_consulta ORDER BY apellido1, apellido2, nombre

Consulta 2. SELECT dni, apellido1, apellido2, nombre, telefono, fecha_nac FROM paciente p, atencion_medica a WHERE fecha_nac < '01/01/1940' AND operaciones IS NULL AND p.cod_paciente =a.cod_paciente ORDER BY fecha_nac, ciudad, apellido1 Comprobar que solo se muestran filas que tengan un valor nulo en el atributo operaciones.

Consulta 3: SELECT cod_clinica, especialidad, count(*) FROM atencion_medica a, consulta c, medico m WHERE motivo 'Motivo 6' AND turno = 'Mañana' AND a.cod_consulta = c.cod_consulta AND a.cod_medico = m.cod_medico GROUP BY cod_clinica, especialidad ORDER BY 3, especialidad

Consulta 4. Definir una actualización (comando UPDATE), de manera que creando los índices necesarios se reduzca o se aumente el tiempo de ejecución, indicando cuál puede ser el motivo de la reducción o el aumento de tiempo.

NORMAS Y PLAZO DE ENTREGA La práctica se realizará preferiblemente en grupo de dos personas. Para aprobar la práctica se deberá: A. Entregar y aprobar la memoria de la práctica, que incluirá obligatoriamente: 1. Portada, especificando el nombre de la asignatura, el del autor y la fecha de entrega. 2. Índice con numeración de los apartados, y el número de página donde se encuentren en la memoria. 3. Solución a cada uno de los apartados solicitados. En ellos aparecerá obligatoriamente el planteamiento seguido para solucionarlo, las sentencias SQL utilizadas, así como una descripción de las opciones del gestor que se hayan ejecutado para la resolución. 4. Conclusiones La no inclusión de alguno de estos apartados supondrá el suspenso automático de la práctica. La memoria se enviará por correo electrónico al profesor de la asignatura, antes del día 30 de mayo de 2013 para la convocatoria de junio, y del 4 de julio para la convocatoria extraordinaria de julio. B. Aprobar el examen específico de la práctica. El examen de prácticas tendrá lugar el mismo día que el examen de teoría (3 de junio / 10 de julio). El examen de prácticas podrá realizarse siempre y cuando se haya entregado previamente la memoria de la práctica de la asignatura. Se recuerda que para aprobar la asignatura es requisito indispensable tener aprobada la parte práctica.