BD 2006/2007
Dependencias funcionales y normalización
EJERCICIOS RESUELTOS
1. Considere la siguiente si guiente relación R e indique si, para el conjunto c onjunto de tuplas almacenadas en este momento, R satisface o no las dependencias funcionales BEÆD, DÆB, ADÆE, CÆAB y EÆB A a3 a2 a1 a4 a3
B b2 b1 b2 b2 b2
C c2 c4 c5 c3 c3
D d4 d2 d1 d1 d1
E e1 e1 e3 e2 e3
Solución Se satisfacen BE Æ DÆ B y ADÆ E pero no C Æ (para las dos últimas tuplas Æ D, Æ AB c3 está asociada con a4, b2 y con a3, b2) ni E Æ B (para las dos primeras tuplas t uplas e1 está asociada con b2 y b1).
2. Sea la relación R(A, B, C, D, E, G, H) y F={E ÆGH, CÆD, DÆA, HÆC}. Supongamos que la relación R tiene ya almacenadas las tuplas: A a1 a1 a1 a1
B b1 b2 b1 b2
C c1 c2 c2 c3
D d2 d2 d2 d1
E e1 e2 e2 e3
G g1 g1 g1 g2
H h1 h2 h2 h3
Decidir si cada una de las siguientes tuplas podría estar almacenada en R: 1. (a1, b1, c1, d1, e2, g1, h2) 3. (a1, b3, c2, d2, e1, g1, h1)
2. (a1, b2, c3, d1, e4, g2, h3) 4. (a1, b1, c2, d2 , e2, g1, h2)
Solución 1) 2) 3) 4)
No, no cumple C Æ Æ D según los valores de la primera tupla almacenada Sí No, no cumple H Æ C ÆC, , según los valores de la primera tupla almacenada No, satisface F pero es una tupla repetida (es la misma que la tercera tupla almacenada)
3. Sea R(A, B, C, D, E, G) y F={AD ÆE, CÆG, GEÆC, AÆC, BCÆA, BÆD}. Demostrar que las dependencias AÆG, BCÆE, ABÆE y ADG ÆC pertenecen a F+ aplicando a) los axiomas de Armstrong y b) el concepto de cierre de un atributo. Solución +
2.1)AÆ G A F =ACG AÆ C y C Æ G G. ÆG, , por transitividad A Æ G. + 2.2)BC Æ (BC) F =BCGADE Æ E BÆ D, por aumentación, BC Æ Æ D BC Æ Æ D y BC Æ Æ A, por aditividad, BC Æ Æ AD BC Æ y ADÆ E, por transitividad, BC Æ Æ AD Æ E
1
BD 2006/2007
Dependencias funcionales y normalización
2.3)ABÆ E (AB)+F =ABCDEG BÆ D y ADÆ E, por pseudotransitividad, ABÆ E + 2.4)ADGÆ C (ADG) F =ADGCE AÆ C, por aumentación, ADG Æ C +
4. Sea R(A, B, C, D, E, G, H) y F={AÆD, ABÆDE, CEÆG, EÆH}. Obtener (AB) . Solución
( AB)+F =ABDEH 5. Sea R(A, B, C, D, E, G, H) y F={AB ÆE, AGÆD, BEÆC, EÆG, CGÆH} el conjunto de d.f. que satisface R. Decidir si R satisface además ABÆGH. Solución
( AB)+F =ABECGHD, sí se satisface ya que con AB podemos obtener GH
6. Sea R(A, B, C, D, E, G, H) y F={AB ÆD, AÆE, DHÆG, BÆD, CÆH, BCÆG, EÆD}. Obtener un conjunto de d.f. equivalente a F que no sea redundante, eliminando de F las dependencias redundantes. Solución ABÆ D, ( AB)+F-{AB D}=ABED, luego es redundante F’= {AÆ E, DH ÆG D, C Æ H, , BÆ BC Æ G, E Æ D} + AÆ E, ( A) F’-{A E}=A, no es redundante + DH ÆG , (DH) F’-{DH G}=DH, no es redundante + BÆ D, (B) F’-{B D}=B, no es redundante + C Æ H, (C) F’-{C H}=C, no es redundante + BC ÆG , (BC) F’-{BC G}=BCDHG, luego es redundante F’’= {AÆ E, DH ÆG D, C Æ H, , BÆ E Æ D} + E Æ D, (E) F’’-{E D}=E, no es redundante La solución es F” = {AÆ E, DH Æ G, BÆ D, C Æ H, E Æ D}. •
•
→
→
•
•
•
→
→
→
•
•
→
→
7. Sea R(A, B, C, D, E), F={AB ÆC, A ÆE, B ÆD, C ÆE, D ÆE, D ÆC} y G={BÆD, DÆC, CÆE, AÆE}. Comprobar si F y G son equivalentes. Solución +
Hay que comprobar si ABÆ C y DÆ E ∈ G , ya que el resto de dependencias son las mismas. + + ABÆ C, ( AB) G=ABDCE, luego AB Æ C ∈ G , + + DÆ E, (D) G=DCE, luego D Æ E ∈ G , •
•
8. Sea F={ABÆEG, BÆC, EÆH, HÆC, DEGÆA, DHÆA, BCDÆG}. Obtener una cobertura canónica de F. Solución Dependencias simples: F={ABÆ E, ABÆ G, BÆ C, E Æ H, A, DH Æ A, H ÆC , DEGÆ BCDÆ G} •
2
BD 2006/2007
Dependencias funcionales y normalización
Dependencias completas: + + ABÆ E, (B) F =BC y (A) F =A; es completa + + ABÆ G, (B) F =BC y (A) F =A; es completa + + + DEGÆ A, (EG) F =EGHC, (DG) F =DG, (DE) F =DEHCA; sobra G, DE Æ A F’=F-{DEGÆ A}∪ {DE Æ A}={AB E, ABÆ G, BÆ C, E Æ H, H ÆC Æ , DE Æ A, DH Æ A, BCDÆ G} Hay que examinar la dependencia que ha quedado después de eliminar G + + DE Æ A, (E) F’=EHC, (D) F’=D; es completa + + DH Æ A, (H) F’=HC, (D) F’=D; es completa + + BCDÆ G, (CD) F’=CD, (BD) F’=BDCG; sobra C, luego la dependencia queda BDÆ G F’’= {ABÆ E, ABÆ G, BÆ C, E Æ H, H ÆC , DE Æ A, DH Æ A, BDÆ G} + + BDÆ G, (D) F’=D, (B) F’=BC; luego ya es completa. •
Eliminar dependencias redundantes: Partimos de F’’= {AB Æ E, ABÆ G, BÆ C, E Æ H, H ÆC , DE Æ A, DH Æ A, BDÆ G} + ABÆ E (AB) F”-{AB E}=ABCG; no es redundante + ABÆ G (AB) F”-{AB G}=ABECH; no es redundante BÆ C (B)+F”-{B C}=B; no es redundante + E Æ H (E) F”-{E H}=E; no es redundante + H ÆC (H) F”-{H C}=H; no es redundante + DE Æ A (DE) F”-{DE A}=DEHCA; es redundante F’’’={ABÆ E, ABÆ G, BÆ C, E Æ H, H ÆC , DH Æ A, BDÆ G} + DH Æ A (DH) F’’’-{DH A}=DHC; no es redundante + BDÆ G (BD) F’’’-{BD G}=BDC; no es redundante •
→
→
→
→
→
→
→
→
9. Calcular las claves candidatas de R(A, B, C, D, E, G, H) con Fc={A ÆB, AÆC, DÆG, AÆD, BÆH, HÆE, BÆA}, que es una cobertura canónica. Solución Todos los atributos participan en las dependencias y todos aparecen en los consecuentes. Como C, E y G no están en ningún antecedente no podrán formar parte de ninguna clave. Empezamos probando con los antecedentes de las d.f. (A)+Fc=ABCDGHE, luego A es clave candidata + (B) Fc=ABCDGHE, luego B es clave candidata + (D) Fc=DG, habría que combinar D con H (A y B ya son clave y C y E se han descartado previamente). + (DH) Fc=DGHE, por aquí no se puede seguir (H)+Fc=HE, sólo se podría combinar con D, pero ya se ha hecho antes (A y B ya son clave y C y G se han descartado previamente). Las claves candidatas son por lo tanto A y B.
10. Dada R(A, B, C, D, E, G) y Fc={CD ÆA, BCÆD, CEÆD, AÆB, AEÆG}, conjunto de d.f . canónico. Determinar en qué forma normal se encuentra R. Solución Claves candidatas + No están en los consecuentes CE. (CE) F =CEDABG, luego CE es la única clave candidata. •
3
BD 2006/2007
Dependencias funcionales y normalización
Forma normal No está en FNBC porque, por ejemplo, en CD Æ A, CD no es superclave No está en 3FN porque, por ejemplo, en CD Æ A, CD no es superclave y A no es primo + + Para ver si está en 2FN, calculamos (C) F =C y (E) F =E. Está en 2FN. •
11. Sea R(A, B, C, D, E, G, H, I, J) y F={B ÆH, G ÆI, A ÆE, I ÆJ, CDHÆA, CAÆD, AIÆC}. Obtener las claves candidatas de R y determinar la forma normal en que se encuentra la relación R. Solución Debe comprobarse que F es una cobertura canónica, que lo es. Para calcular la claves candidatas: + Los atributos que no están en el consecuente son BG. (BG) F =BGHIJ; como no son todos los atributos de R, hay que combinar BG con A, C, D o E. De ellos E puede descartarse porque está en los consecuentes pero no está en ningún antecedente. (BGA)+F =BGAHIJECD; BGA es clave candidata + (BGC) F =BGCHIJ; hay que combinar con D (con A no, porque BGCA sería superclave) + (BGD) F =BGDHIJ; hay que combinar con C (con A no, porque BGDA sería superclave) (BGCD)+F =BGCDHIJAE; BGCD es clave candidata Las claves candidatas son BGA y BGCD. Forma normal: No está en FNBC porque, por ejemplo, en B Æ H, B no es superclave No está en 3FN porque, por ejemplo, en B Æ H, B no es superclave y H no es primo No está en 2FN porque, por ejemplo, en B Æ H, H depende solamente de uno de los atributos de una clave; es decir la d.f. BGA Æ H no sería completa. Está en 1FN •
•
•
12. Sea R(A, B, C, D, E, G) y F={A ÆBCG, BÆD, BEÆC, DEÆA}. Estudiar la forma normal en que se encuentra la relación R y si no está en 3FN, obtener una descomposición sin pérdida de información ni de dependencias que esté al menos en 3FN. Solución Debe comprobarse si F es una cobertura canónica, que no lo es ya que BE Æ C es redundante. Fc={AÆ B, AÆ C, AÆ G, BÆ D, DE Æ A} Claves candidatas + El único atributo que no está en el consecuente es E. (E) Fc =E; como no son todos los atributos de R, hay que combinar con el resto, excepto con C que está en los consecuentes pero no está en ningún antecedente. + (EA) Fc =EABCGD; EA es clave candidata + (EB) Fc =EBDACG; EB es clave candidata + (ED) Fc =EDABCG; ED es clave candidata + (EG) Fc =EG; ya no se puede combinar con más, pues serían superclaves de las anteriores Las claves candidatas son EA, EB y ED. Forma normal No está en FNBC porque, por ejemplo, en A Æ B, A no es superclave •
•
•
4
BD 2006/2007
Dependencias funcionales y normalización
No está en 3FN porque, por ejemplo, en A Æ C, A no es superclave y C no es primo No está en 2FN porque, por ejemplo, en A Æ C, puede observarse que C es un atributo no primo que depende parcialmente de una clave (la d.f. EA Æ C no es completa). Está en 1FN Descomposición en 3FN Partimos de F={A Æ BCG, BÆ D, DE Æ A}. - Todos los atributos están en F. - No hay ninguna d.f. que involucre a todos los atributos de R. - Así que simplemente descomponemos en R1(A, B, C, G) F1= {A Æ BCG} R2(B, D) F2={BÆ D} R3(D, E, A) F3={DE Æ A} - Como una de las claves candidatas ED esta completamente contenida en R3 no hace falta añadir una relación más. - Tampoco hay ningún esquema contenido en otro. La descomposición es, por lo tanto, R1, R2, R3. Además todas las relaciones resultantes están también en FNBC, porque en cada una de ellas el antecedente de su única dependencia es la clave de la relación. •
13. Sea la relación R(A, B, C, D, E, G, H) y F={E ÆGH, CÆD, DÆA, HÆC}. Estudiar en qué forma normal está R y si no está en 3FN, realizar una descomposición en un conjunto de relaciones que satisfagan la 3FN. Solución Debe transformarse F en una cobertura canónica: convertimos sus d.f. en simples,son completas y no hay ninguna redundante F={E ÆG A, H Æ C} , E Æ H, C Æ D, DÆ Claves candidatas + No están en los consecuentes BE. (BE) F =BEGHCDA, luego BE es la única clave candidata. Forma normal No está en FNBC porque, por ejemplo, en E ÆG , A no es superclave No está en 3FN porque, por ejemplo, en E ÆG , E no es superclave y G no es primo No está en 2FN porque, por ejemplo, en E ÆG , puede observarse que G, que es un atributo no primo que depende de una parte de la clave. Está en 1FN Descomposición en 3FN usando el conjunto de d.f. agrupado R(A, B, C, D, E, G, H) y F={E ÆG A, H ÆC H, C Æ D, DÆ } - El atributo B no está en las d.f.; creamos R1(B) F1={} - No hay ninguna d.f. que involucre a todos los atributos - Descomponemos en: R2(E, G, H) F2={E Æ GH} R3(C, D) F3={C Æ D} R4(D, A) F4={DÆ A} R5(H ,C) F5={H ÆC } - Como la clave BE no está incluida en ninguna, añadimos R6(BE) F6={} - Como ahora el esquema de R1 está incluido en el de R6, eliminamos R1 La descomposición es R2, R3, R4, R5 y R6. Además puede comprobarse que cada relación está en FNBC. •
•
•
•
5
BD 2006/2007
Dependencias funcionales y normalización
14. Tenemos un esquema R = {C(curso), P(profesor), H(hora), A(aula), E(estudiante), G(grado)}. Las dependencias asociadas a este esquema tienen que representar las siguientes restricciones : cada curso es impartido por un sólo profesor a una hora y en un determinado aula se imparte un sólo curso a una hora un profesor está en una sola aula a una hora un estudiante está en una sola aula cada estudiante por cada curso que sigue tiene un grado Representar el conjunto canónico de dependencias funcionales, calcular las claves candidatas y estudiar en qué forma normal está la relación R. •
• •
•
•
Solución •
•
•
•
•
•
R(C, P, H, A, E, G) y F={C ÆP A, HE Æ A, , HAÆ C, HPÆ EC ÆG } Puede comprobarse que F es una cobertura canónica. La única clave candidata es HE, ya que H y E no están en los consecuentes de las d.f. y (HE)+F =HEAPCG No está en FNBC ya que en C Æ P, por ejemplo, C no es superclave No está en 3FN ya que en C ÆP , por ejemplo, C no es superclave y P no es principal o primo (es decir, P no forma parte de ninguna clave) Está en 2FN. A simple vista no se observa ninguna dependencia sólo de H o de E, pero debemos verificar que esto también ocurre en F + , calculando + + (H) F =H y (E) F =E. Como no obtenemos ningún atributo no principal, sabemos que estará en 2FN.
15. Una red hotelera mantiene un sistema centralizado de reservas de acuerdo a las especificaciones : Cada habitación (H) tiene asignado un código único que indica el hotel (O) y el tipo de habitación (T) El precio del hotel (P) depende del hotel y del tipo de habitación. Un cliente (C) puede efectuar distintas reservas (R) estando una reserva determinada por la habitación y la fecha (F). Cada reserva tiene un número único que determina la información del cliente, la habitación y la fecha. Se pide : a) Definir el esquema R y el cjto de dependencias funcionales para representar el enunciado. Calcular la cobertura canónica Fc y todas las claves. b) Realizar un diseño en 3FN de Codd que conserve las dependencias funcionales y la información. c) Analice luego si los subesquemas obtenidos están en FNBC. Si alguno no lo está llévelo a dicha forma normal y analice si se pierde o no alguna dependencia en este proceso. d) Escribir en álgebra relacional una consulta que recupere el precio de una habitación dado su código. •
• •
•
Solución a) R(H, O, T, P, F, R, C) y DF={H ÆO T, OT ÆP , HF Æ R, RÆ CHF} Hallamos la cobertura canónica: transformamos las d.f. en simples,todas las d.f. son completas y no hay d.f. redundantes Fc={ H ÆO H, RÆ F} , H ÆT , OT ÆP , HF Æ R, RÆ C, RÆ Claves candidatas R y HF, ya que:
6
BD 2006/2007
Dependencias funcionales y normalización
Todos los atributos están en los consecuentes. Se descartan P y C que no están en los antecedentes. Probamos con los antecedentes. + (H) Fc =HOTP, habría que combinar con F ó R + (OT) Fc =OTP, podría combinarse con H, F ó R + (HF) Fc =HOTPFRC, es clave candidata (R)+Fc =RHFCOTP, es clave candidata Son las dos únicas ya que H ya no se podrá combinar más (con F es clave candidata y R sólo también es clave candidata) y OT combinado sólo con H o sólo con F no nos aporta nada más (y con HF o R ya no tiene sentido combinarlos, pues éstos son clave candidata) b) La relación no está en 3FN, de hecho el máximo nivel de normalización es 1FN ya que ni siquiera está en 2FN (por ejemplo, O depende parcialmente de HF, ya que sólo depende de H, al existir la d.f . H ÆO ) Descomposición en 3FN usando Fc agrupada que es el DF original: R(H, O, T, P, F, R, C) DF={H ÆO T, OT ÆP , HF Æ R, RÆ CHF} No existen atributos que no estén en Fc No hay ninguna dependencia que incluya todos los atributos del esquema Descomponemos en R1(H, O, T) F1={H ÆO T} K1=H R2(O, T, P) F2={OT ÆP } K2=OT R3(H, F, R) F3={HF Æ R} K3=HF R4(R, C, H, F) F4={ RÆ CHF} K4=R Alguna de las claves candidatas iniciales está incluida ya en alguna de las relaciones Eliminamos la relación R3, porque su esquema está completamente contenido en R4 y llevamos la d.f. HF Æ R a F4 Descomposición final: R1(H, O, T) F1={H ÆO T} K1=H R2(O, T, P) F2={OT ÆP } K2=OT R4(R, C, H, F) F4={ RÆ CHF, HF Æ R } K4={HF, R} •
•
•
•
•
c) R1, R2 y R4 están además en FNBC, ya que en todas ellas el antecedente es superclave d)π P(σ H=’código_habitación’(R1)*R2)
16. Una empresa fabrica diferentes productos y mantiene una red de puntos de venta para su comercialización. Los puntos de venta (V) se agrupan en zonas (Z). En cada punto de venta hay agentes (A). Cada agente opera en un único punto de venta, de modo que dos agentes del mismo punto de venta no pueden comercializar el mismo producto (P). Por cada producto que vende un agente se le asigna a éste una calificación (Q), que depende del producto y de la cantidad (C) vendida. Representar este enunciado mediante un esquema relacional (atributos y dependencias funcionales) llevándolo a 3FN con preservación de dependencias y sin pérdida de información. En el esquema resultante analice si las relaciones están en FNBC. Solución •
R(V, Z, A, P, C, Q) y F={V Æ Z, A, PAC ÆQ AÆ V, VPÆ }
7
BD 2006/2007
Dependencias funcionales y normalización
Puede comprobarse que F es una cobertura canónica. Claves candidatas + No están en el consecuente P y C. (PC) F =PC. Hay que combinar con el resto, excepto con Z y Q que no están en los antecedentes, o sea, basta con combinar con A y V. (PCA)+F =PCAVQZ y (PCV) +F =PCVZAQ. Claves candidatas: PCA y PCV. Forma normal No está en FNBC porque, por ejemplo, en V Æ Z, V no es superclave. No está en 3FN, porque, por ejemplo, en V Æ Z, V no es superclave y Z no es primo. No está en 2FN porque, por ejemplo, en V Æ Z, Z depende de un subconjunto de la clave PCV. Está en 1FN Descomposición - No hay ningún atributo que no esté en las d.f. - No hay ninguna d.f. tal que involucre a todos los atributos del esquema - Descomponemos en: R1(V, Z) F1={V Æ Z} R2(A, V) F2={AÆ V} R3(V, P, A) F3={VPÆ A} R4(P, A, C, Q) F4={PAC ÆQ } - Una de las claves candidates PCA está incluida ya en R4. - El esquema de R2 está incluido en el de R3. Eliminamos R2 y la d.f. AÆ V pasa a F3. En resumen, la descomposición es: R1(V, Z) F1={V Æ Z} R3(V, P, A) F3={VPÆ A, AÆ V}} R4(P, A, C, Q) F4={PAC ÆQ } Análisis de la descomposición R1(V, Z) F1={V Æ Z} K1=V; está en FNBC R3(V, P, A) F3={VPÆ A, A Æ V} K3={PV y PA}; no está en FNBC porque en AÆ V A no es superclave (sin embargo, si estaba en 3FN porque V era primo}. R4(P, A, C, Q) F4={PAC ÆQ K4=PAC; está en FNBC } Si quisiésemos llevar R3 a FNBC, tendríamos que elegir la d.f. AÆ V que es la que no cumple que el antecedente es superclave. La descomposición quedaría: R31(P, A) F31={} R32(A, V) F32={AÆ V} Como podemos observar la d.f. VP Æ A se ha perdido, por lo que la descomposición de R3 en R31, R32, ambas en FNBC, preserva la información pero no las d.f. •
•
•
•
•
8