práctica base de datos ii

Práctica Base de Datos II

Segundo cuatrimestre del 2015 1

Practica 1 – Teoría de Normalización 1. Dados las siguientes instancias de relación indique 2 dependencias funcionales verdaderas y 2

falsas sobre los mismos a) b) A B C D E A B C D E a1 b1 c1 d1 e1 a2 b2 c2 d1 e2 a2 b1 c2 d1 e1 a1 b2 c1 d2 e1 a3 b2 c1 d1 e2 a1 b2 c1 d2 e2 2. Dadas las siguientes instancias de relación y las siguientes dependencias funcionales indicar para cada una de ellas si son verdaderas o falsas. Justificar. a) b) A B C D E A B C D E a1 b1 c1 d2 e1 a1 b1 c1 d2 e1 a2 b1 c1 d2 e1 a1 b2 c2 d1 e1 a2 b2 c1 d1 e2 a2 b3 c3 d2 e2 DF Instancia a) Instancia b) A->B ABC->AC B->C C->B B->CD AB->E BE->C E->B E->C 3. Dado la siguiente relación R = {A, B, C, D, E, F, G} y el siguiente conjunto de dependencias funcionales F={ AB->CD, D->AB, D->E, E->FG }, determinar cuáles de las siguientes dependencias funcionales pertenecen a la clausura del mencionado conjunto de dependencias funcionales:



a) AB->D b) AB->F c) D->G d) D->C e) E->D f) ABC->E

4. Dado el F utilizado en el ejercicio anterior a) calcular la clausura de los siguientes conjuntos de atributos:

i. AB ii. D

iii. E b) reconoce alguna relación entre las respuestas del ejercicio anterior y los resultados del

cálculo de la clausura de los conjuntos del punto a). 5. ¿Cómo podría probar que una dependencia funcional es falsa? 6. Indique para cada uno de los conjuntos de atributos del punto a) del ejercicio anterior si los

mismos constituyen claves candidatas de R. Justifique su respuesta 7. Calcular todas las claves candidatas para el conjunto R del ejercicio 3. Indicar la clave primaria del

mismo 8. Dado los siguientes R y F, indicar para cada uno de los siguientes conjuntos de atributos si los

mismos representan una clave candidata, una superclave o ninguna de las dos. R = { A, B, C, D, E, F, G} ; F = {A->B, C->D, E->AB, E->FG, F-> E, F->BD} a) AB b) A c) AC d) E e) EA

f) FC g) DA h) G i) F j) C 9. Dadas las siguientes relaciones y los siguientes conjuntos de dependencias funcionales hallar el

cubrimiento minimal para cada uno de los mencionados conjuntos a) R = {A, B, C, D, E, F, G}; F = {A->B, C->D, E->AB, E->FG, F-> E, F->BD} b) R = {A, B, C, D, E, F, G, H}; F= {AB->CD, D->AB, D->E, E->FG, DE->F, ABC->E} c) R = {A, B, C, D, E, F, G, H}; F= {D->ABCFG, D->E, E->FG} d) R = {A, B, C, D, E, F, G, H, I}; F= {AB->CDH, DH->AB, DH->E, E->FG, DE->F, D->B} e) R = {A,B,C,D,E,F,G,H,I } Y F = {ABC->D; C->G; A->CEG; B->FH ;B->I; I->B; I->H, BI->F}

10. De los ejercicios anteriores reconoce alguna mejora “empírica” que se le pueda hacer al algoritmo que usualmente se utiliza para calcular el F minimo.

11. Indicar para cada uno de los R del ejercicio 9 en qué forma normal se encuentra



12. Dado el siguiente R = {A, B, C, D, E, F, G} y sabiendo que AB es una clave candidata

a) indicar un conjunto de dfs de tal forma que R no esté en segunda forma normal b) indicar un conjunto de dfs de tal forma que R no esté en tercera forma normal, aunque si en

segunda c) indicar un conjunto de dfs de tal forma que R esté en tercera forma normal

13. Dar un ejemplo de una relación que: a) esté en segunda forma normal y no en tercera b) esté en tercera forma normal y no en FNBC c) esté en FNBC

14. Dados los R y F del ejercicio 9 hallar, siempre que sea posible, una descomposición de R que este en FNBC y que sea SPDF y SPDI

15. Dados los siguientes F, R y sus descomposiciones, completar el siguiente cuadro R F Descomposición SPDF SPDI A,B,C,D,E,F AB->CDEF, E->F {ABCD};{EF} A,B,C,D,E,F AB->CDEF, E->F {ABCDE};{EF} A,B,C,D,E,F,G AB->CDEF, E->F,F->G {ABCDF};{EF};{FG} N, O, P ,Q,R,S,T,U,V NO->Q; N->PR; P->T; O->SU; O->V; V->O {NOQ},{PQRV},{NOST},{UV}

16. Supongamos que se desean modelar los datos de los empleados, así como también la información referida a las categorías a los que pertenecen los mismos. Los atributos a tomar en cuenta son: tipo y número de documento, legajo, cuil, apellido y nombre y dirección del empleado. Además se desea conocer a que categoría pertenecen, cuál es la descripción de la misma y cuál es el sueldo base que la mencionada categoría tiene asociado. Cada empleado trabaja en un único departamento. Los departamentos tienen una descripción y además se desea conocer el nombre del gerente del mismo. a) indicar el conjunto de dependencias funcionales del mismo b) calcular las claves candidatas c) descomponer el mismo en FNBC con SPDF y SPDI d) Coincide la solución encontrada con la que hubiera obtenido en forma intuitiva?



Practica 2 - Optimización

1. Dada la siguiente base de datos: Empleado(E) nro_e(4) (clave) nom_e(32) domicilio(24) ciudad(10) edad(2) dni(8) tel(12) sueldo(6) Trabaja_en(T) nro_e (clave) nro_depto (clave) desde(8) hasta(8) Departamento(D) nro_depto(4) (clave) nom_depto(20) ubicación(20) ciudad(10) Gerencia (G) nro_e (clave) nro_depto (clave) desde(8) hasta(8) Los valores entre paréntesis indican la longitud en bytes es de los atributos. El tamaño de un bloque es de 1024 bytes, B = 10 TE = 20.000 TT= 60.000 TD= 2.000 TG= 4.000 IE.ciudad = 400 ID.ciudad = 200 En todos los casos la altura de los árboles es 4. La cantidad máxima de bloques por bucket es 6. El 30% de los empleados es menor a 35 años. El 10% de los empleados gana más de $ 10.000 Cada empleado trabaja en promedio en 2 departamentos Sobre todas las claves se cuenta con índices clustered. Tanto en el caso de Trabaja_en como de Gerencia la clave es (nro_e, nro_depto) Además existen índices unclustered sobre I1 : E.ciudad I2: T.desde Y un índice Hash sobre I3 : T.hasta



Construir el árbol canónico, optimizarlo algebraicamente y proponer un plan de ejecución para cada una de las siguientes consultas SQL: a. Select e.nro_e From empleado E Where edad < 35 b. Select nro_depto From departamento Where ciudad = ‘Rosario’ c. Select nro_e From empleado Where sueldo > 10.000 And edad < 35 d. Select e.nro_e, t.nro_depto From empleado E, trabaja_en T Where edad < 35 and e.ciudad = ‘Rosario’ And e.nro_e = t.nro_e and hasta is null Aclaración: para saber en qué departamento trabaja un empleado es necesario tomar el que tiene la fecha hasta en NULL en la tabla trabaja_en e. Select e.nro_e, t.nro_depto, d.nom_depto From empleado E, trabaja_en T, departamento D Where edad < 35 and e.ciudad = ‘Rosario’ And e.nro_e = t.nro_e and hasta is null And t.nro_depto = d.nro_depto f. select e.nro_e e.nom_e, e1.nro_e, e1.nom_e from Empleado e, Trabaja_en t, Gerencia g, Departamento d, Empleado e1 where e.nro_e=t.nro_e and d.nro_depto=t.nro_depto and g.nro_depto=d.nro_depto and g.nro_e=e1.nro_e and T.desde > 1/1/2012 and t.hasta < 1/1/2013 Aclaración: En el caso de los select que involucran joins se deben proponer 2 métodos alternativos de resolver los mismos 2. Dada la relación R(A, B, C) y la consulta select * from R where A > 10 and B=10 and C=5; Asumir que: TR=10000, BR=1000 IR.A=90, IR.B=150, IR.C=150 El 10% de los registros satisface que A > 10



Suponer que existe un índice clustered para A e índices non-clustered para B y C . a. Armar el árbol canónico. b. Proponer 2 árboles diferentes y armar su plan de ejecución. 3. Definir la mejor estrategia de evaluación y calcular su costo sobre la base de datos del ejercicio 1 para las siguientes consultas y las siguientes situaciones: Asumir que: t empleados = 100.000, tamaño de bloque = 4.000 bytes I(Empleado.ciudad)=100, I(Empleado.edad)=35, I(Empleado.sueldo)=50 1. select * from Empleado where ciudad = "Córdoba" and edad = 45; Suponer que existe: a. Índice clustered sobre ciudad, índice non-clustered sobre edad. b. Índice non-clustered sobre edad. c. Índice clustered sobre edad. 2. select * from Empleado where ciudad = "Córdoba" or edad = 45; Suponer que existe: a. Índice clustered sobre ciudad, índice non-clustered sobre edad. b. Índice non-clustered sobre edad. c. Índice clustered sobre edad. 6. Sean R y S dos relaciones tales que TR=2.000.000 y TS=1.500.000. En un bloque se pueden almacenar 40 tuplas de R o de S. Se pide seleccionar la mejor estrategia de junta entre todas las que conozca, y estimar la cantidad de accesos al disco para cada uno de los casos siguientes de disponibilidad de memoria volátil: a. 2.501 bloques b. 501 bloques c. 301 bloques d. 101 bloques



Práctica 3 Laboratorio de Optimización A)Para el presente trabajo practico, se solicita: a) ejecutar todas consultas sin los índices abajo detallados b) imprimir el plan de ejecución de cada query ejecutado c) Crear los índices que se consideren necesarios (pueden estar entre los sugeridos o no) y ejecutar los mismos select, comparando los distintos planes de ejecución para todas las posibilidades consideradas d) Analizar las diferencias que surjan entre los distintos planes de ejecución para el mismo query e) Elegir la mejor combinación consulta-índice Indices sugeridos

CREATE NONCLUSTERED INDEX fname ON member(firstname) CREATE NONCLUSTERED INDEX corp_no ON member(corp_no) CREATE NONCLUSTERED INDEX mem_no ON member(member_no) CREATE UNIQUE nonclustered INDEX mbr_mem_no ON member(member_no) CREATE clustered INDEX mbr_corp_no_CL ON member(corp_no) CREATE UNIQUE nonclustered INDEX mbr_name ON member(lastname, firstname) CREATE nonclustered INDEX chg_mem_no ON charge(member_no) CREATE nonclustered INDEX mbr_name ON member(member_no, lastname) CREATE nonclustered INDEX chg_charge_no ON charge(member_no, charge_no) Nota : Se utilizara la base de datos Credit., la tabla member contiene 10000 registros 1)SELECT * FROM member WHERE firstname LIKE 'Q%' AND corp_no > 450 AND member_no > 6000 2)SELECT * FROM member WHERE firstname LIKE 'Q%' AND corp_no > 450 AND member_no > 9500 3) SELECT * FROM member WHERE member_no IN (100,101,102,103,104,105,106,107,108,109,200,201, 202,203,204,205,206,207,208,209,210,211,212,213, 214,215,216,217,218,219,220,221,222,223,224,225, 226,227,228,229,230,231,232) 4) SELECT * FROM member WHERE member_no IN (100,101,102,103,104,105,106,107,108,109,200,201, 202,203,204,205,206,207,208,209,210,211,212,213, 214,215,216,217,218,219,220,221,222,223,224,225, 226,227,228,229,230,231,232, 233)



5)SELECT * FROM member WHERE member_no=1234 OR region_no=5 6)SELECT * FROM member WHERE member_no=1234 OR corp_no=410 7) SELECT * FROM member WHERE region_no = 5 OR corp_no = 410 8)SELECT member.member_no, lastname, charge_no FROM member JOIN charge ON member.member_no = charge.member_no WHERE member.lastname = 'HAHN' AND firstname = 'CVVD' 9) SELECT m.member_no, lastname, charge_no FROM member m JOIN charge c ON m.member_no = c.member_no WHERE m.lastname = 'HAHN' AND firstname = 'CVVD' 10) SELECT corp_no, count(*) FROM member GROUP BY corp_no 11) SELECT region_no, count(*) FROM member GROUP BY region_no 12)SELECT corp_no, count(*) FROM member WHERE member.lastname = 'HAHN' GROUP BY corp_no 13)SELECT region_no, corp_no, count(*) FROM member WHERE member.lastname = 'HAHN' GROUP BY region_no, corp_no 14)SELECT corp_no, region_no, count(*) FROM member WHERE member.lastname = 'HAHN' GROUP BY corp_no, region_no 15)SELECT corp_no, region_no, count(*) FROM member WHERE member.lastname = 'HAHN'



GROUP BY corp_no, region_no HAVING COUNT(*) > 4 B) a) Crear el índice corp_no sobre la tabla member y borrar todos los demás b) Revisar el plan de ejecución de la siguiente consulta SELECT * FROM member WHERE corp_no = 450 c) Que cantidad de filas estima que va a devolver? d) Ejecutar el comando DBCC SHOW_STATISTICS ('member', corp_no) e) Ese comando devuelve un resultado que puede dividirse en 3 partes, en la última parte hay un cuadro que tiene una primera columna “RANGE-HI_KEY”, buscar en esa tabla la fila correspondiente al número 450 (que es el valor de la clave del índice), cual es la columna cuyo valor coincide con el número estimado de filas? f) Revisar el plan de ejecución de la siguiente consulta: SELECT * FROM member WHERE corp_no = 448 g) Cual es ahora el número de filas estimadas? Ejecutar nuevamente el comando dbcc show_statistics, existe el valor 448 entre los valores de la “range_hi_key”? Buscar en la documentación el significado de las columnas ‘range_row” and “distinct_range_row”. En el siguiente link http://www.sqlservercentral.com/blogs/aschenbrenner/2014/01/28/inside-the-statistics-histogram-density-vector/ pueden encontrar un artículo muy interesante sobre el tema C) El siguiente ejemplo fue tomado de “A Hazard of Using the SQL Update Statement” (http://www.sqlservercentral.com/articles/T-SQL/101464/) a) Ejecutar las siguientes instrucciones SQL CREATE TABLE #Test1 ( ID INT IDENTITY(1,1) PRIMARY KEY ,Value VARCHAR(10) ); CREATE TABLE #Test2 ( ID INT ,Row_No INT ,Value VARCHAR(10) ,PRIMARY KEY (ID, Row_No) ); INSERT INTO #Test1 SELECT 'Dwain' UNION ALL SELECT 'Brandie'; INSERT INTO #Test2



SELECT 1, 1, 'Tom' UNION ALL SELECT 1, 2, 'Chris' UNION ALL SELECT 2, 1, 'Jeff' UNION ALL SELECT 2, 2, 'Lynn'; SELECT a.ID, b.Row_No, a.Value, b.Value FROM #Test1 a JOIN #Test2 b ON a.ID = b.ID; b) Que devuelve el join? c) Que hace la siguiente instrucción? UPDATE a SET Value = b.Value FROM #Test1 a JOIN #Test2 b ON a.ID = b.ID; d) Cuantos registros modifica esta instrucción? Puede predecirse el resultado? Por que? e) Usando las vistas “updated” y “deleted” compruebe su respuesta al punto d) f) Analice el plan de ejecución de la consulta. Que puede concluir?



Práctica 4 Schedules 1. Dado el siguiente conjunto de transacciones T1 = { R1(x), R1(y), w1(x), w1(y),c1} T2= { R2(z), R2(w), w2(w), w2(z),w2(x),c2} T3= { R3(x), R3(z), w3(x), w3(z),c3} T4= { R4(y), R4(z), w4(y), w4(z),c4} Escribir a) La historia serial b) Una historia no recuperable c) Una historia recuperable, pero que no evite abort en cascada d) Una historia que evite abort en cascada e) Una historia estricta 2. Ídem ejercicio anterior para el siguiente conjunto de transacciones T1 = { R1(x), R1(y), w1(x), w1(y),a1} T2= { R2(z), R2(w), w2(w), w2(z),c2} T3= { R3(x), R3(z), w3(x), w3(z),a3} T4= { R4(y), R4(z), w4(y), w4(z),c4} 3. Dada las siguientes historias, construir para cada una el grafo de seriabilidad e indicar cuales de las siguientes características satisface : RC, ACA, ST, SR a) H = { R1(x), R1(y), R2(z), R2(w), R4(y), R4(z), w1(x), w1(y),c1, w2(w), w2(z), R3(x), R3(z), w3(x), w4(y), w4(z),c4, w3(z),c2, c3,} b) H = { R1(x), R1(y), R2(z), w1(x), w1(y),c1, R2(w), R4(y), R4(z), w2(w), w2(z), R3(x), R3(z), w3(x), w4(y), w4(z),c4, w3(z),c2, c3,} c) H = { R1(x), R1(y), R2(z), R2(w), w1(x), w1(y),R4(y), R4(z), c1,w2(w), w2(z), R3(x), R3(z), w3(x), w4(y), w4(z),c4, w3(z),c2, c3,} d) H = { R1(x), R1(y), R2(z), R2(w), R4(y), R4(z), w1(x), w1(y),c1, w2(w), w2(z), R3(x), R3(z), w3(x), w4(y), w3(z),c2, w4(z),c4,c3}



Práctica 5 Control de Concurrencia 1. Dado el siguiente conjunto de transacciones T1 = { R1(x), R1(y), w1(x), w1(y),c1} T2= { R2(z), R2(w), w2(w), w2(z),c2} T3= { R3(x), R3(z), w3(x), w3(z),w3(x),c3} T4= { R4(y), R4(z), w4(y), w4(z),c4} a) Agregarlas operaciones de lock y unlock necesarias para cada operación b) Construir una historia que cumpla con las reglas establecidas para el two-phase locking, que sea serializable y no sea serial. c) Construir una historia que no cumpla con las reglas establecidas para el two-phase locking , que sea serializable y no sea serial d) Construir, de ser posible, una historia no serial que sea 2PL, serializable y provoque un deadlock e) Construya una posible historia si se utilizara un control de concurrencia por timestamp. f) Construya una posible historia, si se utilizara un control de concurrencia por timestamp y que implique abortar la transacción 3. g) Construya una historia que utilice control de concurrencia por timestamp y se beneficie de la “thomas write rule” h) Construya una historia que pueda corresponder a un control de concurrencia por multiversion. Recuerde establecer para cada operación sobre que versión del ítem está operando. 2. Dadas las siguientes historias indicar si cumplen con las reglas establecidas en el 2PL, en caso de que no cumplan indicar que regla no cumplen y en que operaciones y proponer las modificaciones necesarias para que si cumplan con las reglas del 2PL a) rl1(a), rl1(b), rl2(a), rl2(c), r1(a), r1(b), r2(a), r2(c), ru1(a), ru1(b), ru2(a), ru2(c) b) wl1(a), rl1(b), rl2(a), rl2(c), w1(a), r1(b), r2(a), r2(c), wu1(a), ru1(b), ru2(a), ru2(c) c) rl1(a), wl1(b), rl2(a), rl2(c), r1(a), w1(b), r2(a), r2(c), ru1(a), wu1(b), ru2(a), ru2(c) d) rl1(a), rl1(b), wl2(a), rl2(c), r1(a), r1(b), w2(a), r2(c), ru1(a), ru1(b), wu2(a), ru2(c) e) rl1(a), rl1(b), ru1(a), rl2(a), rl2(c), r1(a), r1(b), r2(a), r2(c), ru1(b), ru2(a), ru2(c) f) rl1(a), ru1(a), rl1(b), rl2(a), rl2(c), r1(a), r1(b), r2(a), r2(c), ru1(b), ru2(a), ru2(c) 3. Dadas las siguientes historias indicar si son ejecuciones correctas de control de concurrencia por timestamp, en caso de que no lo sean proponga modificaciones que las transformen en historias adecuadas

a) st1 , st2, r1 (A), r2 (B), w2 (A), w1(B) b) st1 , st2, r1 (A), w1(B) ,r2 (B), w2 (A), c) st1, st2, r1(A), w2(B),w1(b) d) st1 , r1 (A), st2, r2 (B), r2 (A), w1(B) e) st1 , st2, st3, r1(A), r2(B), w1(C), r3(B), r3(C), w2(B),w3(B) f) st1 , st2, st3, r1(A), r2(B), w1(C), r3(C), r3(B), w2(B),w3(B) g) st1, st2, r1(A), r2(B), w2(A),r1(B) 4. Modificar las historias del punto anterior indicando en cada caso la versión que corresponde leer/escribir



5. Indicar para cada una de las historias modificadas en el punto anterior si son correctas para un control multiversion, el resultado coincide con el del punto 3?



Práctica 6 Recuperación 1. Dadas la siguiente historia escribir las entradas del log correspondiente a la misma, asumiendo que el log es de la forma <t,ítem, valor anterior> , que todos los ítems tienen inicialmente el valor 5 y que cada operación de write los incrementa en 1. a. H = { R1(x), R1(y), R2(z), R2(w), R4(y), R4(z), w1(x), w1(y),c1, w2(w), w2(z), R3(x), R3(z), w3(x), w4(y), w3(z),c2, w4(z),c3,c4} 2. Ídem ejercicio anterior, pero asumiendo que el log es de la forma <t,ítem, nuevo valor> 3. Ídem ejercicio anterior, pero asumiendo que el log es de la forma <t,ítem, valor anterior, nuevo valor> 4. Modificar el log anterior de tal forma de que ocurra un checkpoint quiescente luego del c1. 5. Modificar el log del punto 3 de tal forma que comience un checkpoint non-quiescent luego del c1, cuando debería finalizar el mismo? 6. Tomando el log del ejercicio 4 supongamos que se produce una caída del equipo luego del c2, que valores debería escribir el proceso de restart en la base de datos? 7. Tomando el log del ejercicio 5 supongamos que se produce una caída del equipo luego de finalizar el c3, que valores debería escribir el proceso de restart en la base de datos? 8. Dada las siguientes entradas en el log < START S>; <S, A, 60, 61>;<COMMIT S>;< START T>; <T,A,61,62>; <START U>; <U,B,20,21>; <T,C,30,31>; <START V>; <U,D,40,41>; <V,F,40,41>; <COMMIT U>; <T,E,50,51>; <COMMIT T>; <V,B,21,22>; <COMMIT V> Supongamos que se escribe el comienzo de un checkpoint non quiescence inmediantemente despues uno de los siguientes registros fue escrito en el log I.<S, A, 60, 61> II.<T,A,61,62> III.<U,B,20,21> IV.<U,D,40,41> V. <T,E,50,51> Para cada caso indicar a) en qué momento debería escribirse el <END CKPT> b) que valores tendría la base de datos y cuales debería tener si el crash se produce antes del checkpoint c) que valores tendría la base de datos y cuales debería tener si el crash se produce después del checkpoint d) para los casos b) y c) que acciones debería realizar el proceso de restart



Práctica 7 Seguridad 1- Usando la base de datos utilizada para resolver las consultas SQL definir: a) un grupo para aquellos usuarios que van a efectuar sólo consultas sobre las tablas b) un grupo para los usuarios que cargan los pedidos c) un grupo para el sector de recursos humanos que es el que da de alta empleados. Para realizar este ejercicio primero crear una matriz donde se indique para cada grupo que permisos se le asignan sobre cada tabla. 2- Definir la seguridad en el tema de auditoría de tal forma que ningún usuario de los que da alta de facturas y pagos pueda modificar las tablas de auditoria. Probarlo haciendo un insert sobre cualquiera de dichas tablas.

práctica base de datos ii

Documents