08 diseño (algoritmos)

1 BD1 – Cátedra BD

Algoritmos de Diseño Relacional

2 BD1 – Cátedra BD

Normalización = Descomposión de

cualquier forma?

• La idea básica de normalización es el

reemplazo de un esquema de relación

por una descompoción en una forma

normal más alta.

• Sirve cualquier descomposición?

3 BD1 – Cátedra BD

Observaciones

• Cuando normalizamos no alcanza sólo

con las analizar las dependencias.

• Es necesario definir claramente los

criterios con que se van a construir las

descomposiciones, teniendo en

cuenta:

– La no generación de tuplas erróneas.

– La preservación o no de dependencias.

4 BD1 – Cátedra BD

Descomposición de Join Sin

Pérdida (JSP)

• Una descomposición D = {R1,…, Rn}

de un esquema de relación R es de

join sin pérdida, si para toda r

instancia de R se cumple que:

*(R1(r),…, Rn(r)) = r donde * es el join

natural.

• Esto no hace otra cosa que exigir que

no aparezcan tuplas erróneas.

5 BD1 – Cátedra BD

Test de JSP para

Descomposiciones Binarias

• Dada D={R1, R2}, descomposición de R, va

a tener join sin pérdida si y sólo si se

cumple al menos una de las siguientes

dependencias:

– R1R2 R1 – R2

– R1R2 R2 – R1

• En otras palabras, hay JSP si los atributos

comunes de los dos esquemas determinan

a todos los atributos de uno de ellos.

6 BD1 – Cátedra BD

Proyección de un Conjunto de

Dependencias

• Dados un conjunto de df’s F y un

esquema R, la proyección de F sobre

Ri subesquema de R es el conjunto de

las df’s XY de F+ tal que XY Ri

• Se escribe como: Ri(F).

• Es el conjunto de dependencias que

se cumple en Ri.

7 BD1 – Cátedra BD

Preservación de Dependencias

Funcionales

• Se dice que una descomposición

D={R1,…, Rn} preserva dependencias

si:

– (R1(F) … Rn(F))+ = F+

• Se preservan las dependencias si la

unión de las proyecciones es

equivalente a F.

8 BD1 – Cátedra BD

Test de Preservación de DFs:

Algoritmo ENTRADA: Esquema R y D= {R1,...,Rk} (descomposición de R)

SALIDA : TRUE si la descomposición preserva DFs

FOR EACH X-->Y en F

RESULTADO := X

WHILE (CAMBIOS EN RESULTADO) DO

FOR EACH Ri DE LA DESCOMPOSICIÓN

T = (RESULTADO Ri)+ Ri con i=1..k

RESULTADO := RESULTADO U T

ENDFOR

ENDWHILE

IF Y no es subconjunto de RESULTADO

RETORNAR (FALSE)

ENDIF

ENDFOR

RETORNAR (TRUE)

9 BD1 – Cátedra BD

Test de Preservación de DFs:

Algoritmo - Si resultado contiene todos los atributos de Y, se

conserva la dependencia funcional X →Y.

- La descomposición conserva las dependencias si

y sólo si se conservan todas las dependencias

de F.

10 BD1 – Cátedra BD

Calidad de las Descomposiciones

• Las descomposiciones ideales cumplen las

siguientes dos condiciones:

– Tienen JSP.

– Tienen Preservación de Dependencias.

• Se puede aceptar la pérdida de

dependencias, siempre y cuando se

documente cuáles son las que se pierden.

• Nunca se pueden aceptar esquemas que

tengan join con pérdida (no tengan JSP).

11 BD1 – Cátedra BD

Ejemplo de BCNF

Considérese la siguiente descomposición:

• Cliente (nombre-cliente, calle-cliente, ciudad-

cliente) con F (nombre-cliente → calle-cliente

ciudad-cliente)

• Sucursal (nombre-sucursal, activo, ciudad-

sucursal) F (nombre-sucursal → activo ciudad-

sucursal)

• Info-Prestamo (nombre-sucursal, nombre-

cliente, número-préstamo, importe) con F

(número-préstamo → importe nombre-sucursal)

12 BD1 – Cátedra BD

Ejemplo de BCNF

nombre-cliente es clave candidata de Cliente

nombre-sucursal es clave candidata de Sucursal

• numero-prestamo no es superclave de Info-

Prestamo

– Puede haber un par de tuplas pertenecientes a un

préstamo:

(Centro, Sr. Pinilla, P-44, 1.000)

(Centro, Sra. Pinilla, P-44, 1.000)

– Y numero-prestamo → importe nombre-sucursal no es

trivial, entonces este esquema no está en BCNF

– Este esquema sufre del problema de repetición de

información (nombre-sucursal e importe, por ejemplo)

13 BD1 – Cátedra BD

Ejemplo de BCNF

Considérese la descomposición de join sin pérdida

de Info-Prestamo:

Prestamo (numero-prestamo, nombre-sucursal,

importe) con F (número-préstamo → nombre-

sucursal importe)

Prestatario (nombre-cliente, número-préstamo)

• En la relación Prestamo hay exactamente una

tupla para cada préstamo, y una tupla para cada

cliente de cada préstamo en la relación de

Prestatario.

14 BD1 – Cátedra BD

Descomposición a BCNF y 3NF

• Analizaremos como descomponer a BCNF un

esquema de relación de forma tal que la

descomposición sea de join sin pérdida.

• Cualquier esquema puede descomponerse de

esta manera. Adicionalmente, cualquier esquema

se puede descomponer a 3NF de forma tal que la

descomposición sea de join sin pérdida y que

además preserve las dependencias.

• Sin embargo pueden haber descomposiciones a

BCNF tales que sean de join sin pérdida pero que

no preservan las dependencias.

15 BD1 – Cátedra BD

Algoritmo para Descomponer en 3NF

con JSP y Preservación de DF’s • Entrada: un esquema R y un conjunto de df’s F

sobre R.

• Salida: una descomposición de R en 3NF con JSP y que preserva DF’s.

1. Obtener G, cobertura minimal de F.

2. Para cada miembro izquierdo X de una DF que aparezca en G

1. Crear un esquema X {Aj1,…,Ajn} donde {Aj1,…,Ajn} es el conjunto de los lados derechos de las dependencias de G que tienen X a la izquierda.

3. Colocar los atributos restantes en una nueva relación.

4. Si no hay una clave de R incluida en los esquemas anteriores, se agrega un nuevo esquema cuyos atributos son los de la clave.

16 BD1 – Cátedra BD

Ejemplo de Descomposición en

3NF • Univ(CodM, CiE, IdGr, HrsS, Cred)

• F={CodMHrsS; CiEIdGr; HrsSCred}

1. Se calcula cubrimiento minimal.

– En particular, F ya es minimal.

2. Agregamos un esquema para cada lado izquierdo en F.

– R1(CodM,HrsS), R2(CiE,IdGr), R3(HrsS,Cred)

3. No hay atributos restantes.

4. La clave de Univ es {CodM,CiE}.

– Como no están en ninguno de los esquemas, se agrega R4(CodM, CiE)

17 BD1 – Cátedra BD

Algoritmo para Descomponer en

BCNF con JSP

• Entrada: un esquema R y un conjunto de df’s F sobre R.

• Salida: una descomposición de R en BCNF con JSP

1. Sea D={R} /* la descomposicion inicial contiene sólo el esquema original */

2. Mientras haya un esquema Q en D tal que no esté en BCNF.

1. Considerar XY sobre Q que viola BCNF.

2. Reemplazar Q en D por 2 esquemas Q1 = {Q-Y} y Q2={XY}.

18 BD1 – Cátedra BD

Ejemplo de Descomposición en

BCNF

• Univ(CodM, CiE, IdGr, HrsS, Cred)

• F={CodMHrsS;CiEIdGr; HrsSCred}

1. D={Univ(CodM, CiE, IdGr, HrsS, Cred)}

2. a. Tomamos CodMHrsS que viola

BCNF en Univ y Calculamos el nuevo

D={Univ1(CodM,CiE,IdGr,Cred),

Univ2(CodM,HrsS)}

19 BD1 – Cátedra BD

Ejemplo de Descomposición en

BCNF (cont)

2. b. Proyectamos las dependencias para

ver en que FN quedó la descomposición:

– Univ1(CodM,CiE,IdGr,Cred) : {CiEIdGr;

CodMCred}

– Univ2(CodM,HrsS): {CodMHrsS}

• c. Como CiEIdGr viola BCNF,

cambiamos Univ1 por:

– Univ11(CodM,CiE,Cred),

– Univ12(CiE,IdGr)

20 BD1 – Cátedra BD

Ejemplo de Descomposición en

BCNF (cont)

2. d. Proyectamos para ver si estamos en BCNF:

– Univ11(CodM,CiE,Cred):{CodM Cred}

– Univ12(CiE,IdGr): {CiEIdGr}

• e. CodMCred viola BCNF en Univ11, por lo que cambiamos Univ11por:

– Univ111(CodM,CiE)

– Univ112(CodM,Cred)

21 BD1 – Cátedra BD

Ejemplo de Descomposición en

BCNF (cont)

2. f. Proyectamos para verificar la Forma

normal y obtenemos:

– Univ111(CodM,CiE): {}

– Univ112(CodM,Cred): {CodMCred}

• Los dos últimos esquemas están en

BCNF.

• El resultado es D={Univ111(CodM,CiE),

Univ112(CodM,Cred), Univ12(CiE,IdGr),

Univ2(CodM,HrsS)}

22 BD1 – Cátedra BD

Ejemplo de Descomposición en

BCNF (Comentarios 1) • Este proceso se puede representar con un árbol de

descomposición, en donde el resultado está dado por los esquemas de las hojas:

Univ(CodM, CiE, IdGr, HrsS, Cred) :

{CodMHrsS;CiEIdGr; HrsSCred}

Univ1(CodM,CiE,IdGr,Cred):

{CiEIdGr; CodMCred}

Univ2(CodM,HrsS):

{CodMHrsS}

Univ11(CodM,CiE,Cred):

{CodM Cred}

Univ12(CiE,IdGr):

{CiEIdGr}

Univ111(CodM,CiE): {} Univ112(CodM,Cred):

{CodMCred}

23 BD1 – Cátedra BD

Ejemplo de Descomposición en

BCNF (Comentarios 2)

• Hay que chequear si se preservaron todas las dependencias. Para eso hay que calcular la clausura del lado izquierdo de las dependencias que no quedaron directamente con respecto a la union de las proyecciones de las dependencias.

• G= univ111(F) univ112(F) univ12(F) univ2(F)

• G= { } {CodMCred} {CiEIdGr} {CodMHrsS}

• G= {CodMCred; CiEIdGr; CodMHrsS}

• {HrsS}G+={HrS}

CONCLUSION: No se preservó la dependencia HrsCred.

24 BD1 – Cátedra BD

Ejemplo de Descomposición en

BCNF (Comentarios 2)

• No es posible garantizar cuando este

algoritmo va a perder dependencias o

no lo va a hacer.

• Esto puede depender de muchas

cosas, entre ellas, el orden en que se

eligen las dependencias.

– Ejercicio: aplicar el algoritmo tomando

como primera dependencia HrsSCred.

25 BD1 – Cátedra BD

Otros Cálculos.

• Para determinar en qué forma normal está un determinado esquema, es necesario conocer todas las claves.

• Para determinar si se perdieron dependencias, es necesario proyectar las dependencias.

• Se debería determinar cuando hay JSP, incluso en descomposiciones no binarias.

• Se van a presentar ideas para llevar adelante estas tareas.

26 BD1 – Cátedra BD

Ideas para el Cálculo de Todas las

Claves

• Para calcular todas las claves, habría que calcular las clausuras de todos los subconjuntos de atributos de la relación, lo que sería calcular 2n clausuras.

• Se pueden seleccionar los conjuntos sobre los que se deben calcular las clausuras tomando en cuenta las siguientes ideas: – Los atributos que aparecen solamente del lado

derecho de algunas dependencias, no estarán en ninguna clave.

– Los atributos que no aparecen del lado derecho de ninguna dependencia, estarán en todas las claves.

– Los atributos que no están en ninguna de las categorías anteriores, pueden estar o no en una clave.

27 BD1 – Cátedra BD

Ideas para el Cálculo de Todas las

Claves

• Por lo tanto:

– Si los atributos que están en todas las claves, conforman entre ellos una clave, entonces esa es la única clave de la relación.

– Si ya se encontró una clave, no se debe probar con un conjunto con más atributos, dado que ese sería una superclave.

• Hay que tener en cuenta que pueden existir simultáneamente varias claves de tamaños distintos.

28 BD1 – Cátedra BD

Ejemplo: Determinación de

Todas las Claves • R(A,B,C,D,E,G) y F={ABDG, AE, GEA}

• Atributos en todas las claves: – B y C

• Atributos en ninguna clave: – D

• Clausuras: – BC+={B,C} No es clave

– BCA+={B,C,A,D,G,E} Es clave

– BCG+={B,C,G}

– BCE+={B,C,E}

– BCGE+={B,C,G,E,A,D} Es clave

Tenemos garantía

de que son todas

porque no podemos

formar más

conjuntos de cada

tamaño y que sean

claves.

29 BD1 – Cátedra BD

Obtención de clave K para R

Algoritmo. sea R = {ATR} y F = {DF’s}

1.Hacer K:= R

2.Para cada atributo A en K

calcular (K-A)+ respecto al conjunto F

si (K-A)+ contiene todos los atributos de

R, hacer K:= K – A;

30 BD1 – Cátedra BD

Test de JSP en Descomposiciones

No Binarias

• Entrada: Una relación R, una descomposición D de R y un conjunto de dependencias F.

• Salida: True si la D tiene JSP respecto a R y F, False en caso contrario.

1. Crear una matriz S con la fila i para cada relación Ri en D y la columna j para cada atributo Aj de R.

2. Inicializar la matriz según el siguiente criterio:

– Si el atributo Ai está en Ri, entonces S(i,j)=aj, sino S(i,j)=bi,j

3. Repetir hasta que no haya cambios en la matriz

– Para cada df XY F, si hay dos filas que tengan iguales valores en X, se igualan los valores de Y.

4. Si se obtiene un fila completa con a’s, entonces D tiene JSP, en caso contrario D no tiene JSP.

31 BD1 – Cátedra BD

Ejemplo Test JSP

• R(CiE, NomE, NumP, NomP, LugP, Hrs)

• R1(CiE,NomE), R2(CiE,NumP,Hrs), R3(NumP,NomP,LugP)

• F={CiENomE;NumPNomP,LugP; CiE,NumPHrs }

CiE NomE NumP NomP LugP Hrs

R1 a1 a2 b1,3 b1,4 b1,5 b1,6

R2 a1 b2,2 a3 b2,4 b2,5 a6

R3 b3,1 b3,2 a3 a4 a5 b3,6

Inicialización

bfil,col en

todos los

demás

acol Si

Acol esta

en Rfil

32 BD1 – Cátedra BD

Ejemplo Test JSP (cont)

• R(CiE, NomE, NumP, NomP, LugP, Hrs)

• R1(CiE,NomE), R2(CiE,NumP,Hrs), R3(NumP,NomP,LugP)

• F={CiENomE; NumPNomP,LugP; CiE,NumPHrs }

CiE NomE NumP NomP LugP Hrs

R1 a1 a2 b1,3 b1,4 b1,5 b1,6

R2 a1 b2,2 a3 b2,4 b2,5 a6

R3 b3,1 b3,2 a3 a4 a5 b3,6

CiENomE

a2

NumPNomP,LugP

a4 a5

Al igualar

a con b,

se deja

a.

Hay una

fila de a’s

JSP