Question 3: (10 pts) Consider a relation R(A,B,C,D,E) with the following depende

Question

Question 3: (10 pts)
Consider a relation R(A,B,C,D,E) with the following dependencies:

{A, B} -> {C} {C, D} -> {E} {D, E} -> {B}

Is AB a candidate key of this relation? If not, is ABD? Explain your answer.

Question 4: (35 pts)

Consider the relation R = {A, B, C, D, E, F, G, H, I, J} and the set of functional dependencies S = { {A, B} -> {C}, {A} -> {D, E}, {B} -> {F}, {F} - >{G, H}, {D} -> {I, J} }.

? What is the key of R?

? Decompose R into 2NF, then 3NF relations.

Please explain how you achieve your answer by specifying dependencies at each step.

Question 5: (35 pts)

Consider the relation R = {A, B, C, D, E, F, G, H, I, J, K} and the set of functional dependencies S = { {A, B} -> {C}, {B, D} -> {E, F}, {A, D} -> {G, H}, {B} -> {I}, {G} -> {J} , {F} -> {K} }.

? What is the key of R?

? Decompose R (1) into 2NF, (2) then into 3NF relations, and (3) check

whether the 3NF relations you obtained are in BCNF or not.

Explanation / Answer

================================================================================
Question 3:
   Consider a relation R(A,B,C,D,E) with the following dependencies:
       {A, B} -> {C} {C, D} -> {E} {D, E} -> {B}
   Is AB a candidate key of this relation? If not, is ABD? Explain your answer.
Ans:
   A -> A
   B -> B
   C -> C
   D -> D
   E -> E
   AB -> ABC
   AC -> AC
   AD -> AD
   AE -> AE
   BC -> BC
   BD -> BD
   BE -> BE
   CD -> BCDE
   CE -> CE
   DE -> BDE

   ABD -> ABCDE

   Hence AB is not a candidate key, and ABD is a candidate key.
   No subset of its attributes is a key.
================================================================================
Question 4: (35 pts)
   Consider the relation R = {A, B, C, D, E, F, G, H, I, J} and the set of functional dependencies
   S = { {A, B} -> {C}, {A} -> {D, E}, {B} -> {F}, {F} - >{G, H}, {D} -> {I, J} }.
    What is the key of R?
   Decompose R into 2NF, then 3NF relations.
Please explain how you achieve your answer by specifying dependencies at each step.

Ans:
   A minimal set of attributes whose closure includes all the attributes in R is a key.

   Since the closure of {A, B},
       {A, B}+ = R,
       one key of R is {A, B} (in this case, it is the only key).

   To normalize R intuitively into 2NF then 3NF, we take the following steps

   First, identify partial dependencies that violate 2NF. These are attributes that are
functionally dependent on either parts of the key, {A} or {B}, alone.

   We can calculate the closures {A}+ and {B}+ to determine partially dependent attributes:

           {A}+ = {A, D, E, I, J}.   Hence {A} -> {D, E, I, J} ({A} -> {A} is a trivial dependency)
           {B}+ = {B, F, G, H}, hence {B} -> {F, G, H} ({B} -> {B} is a trivial dependency)
          
   To normalize into 2NF, we remove the attributes that are functionally dependent on part of the key (A or B)
   from R and place them in separate relations R1 and R2, along with the part of the key they depend on (A or B),
   which are copied into each of these relations but also remains in the original relation, which we call R3 below:

           R1 = {A, D, E, I, J},
           R2 = {B, F, G, H},
           R3 = {A, B, C}

   The new keys for R1, R2, R3 are underlined. Next, we look for transitive
dependencies in R1, R2, R3. The relation R1 has the transitive dependency
       {A} -> {D} -> {I, J}, so we remove the transitively dependent attributes {I, J} from R1 into a
relation R11 and copy the attribute D they are dependent on into R11. The remaining
attributes are kept in a relation R12.
  
   Hence, R1 is decomposed into R11 and R12 as follows:
      
       R11 = {D, I, J},
       R12 = {A, D, E}

   The relation R2 is similarly decomposed into R21 and R22 based on the transitive dependency
       {B} -> {F} -> {G, H}:

       R21 = {F, G, H}, R22 = {B, F}
  
   The final set of relations in 3NF are {R11, R12, R21, R22, R3}

================================================================================
Question 5: (35 pts)
   Consider the relation R = {A, B, C, D, E, F, G, H, I, J, K} and the set of functional dependencies
   S = { {A, B} -> {C}, {B, D} -> {E, F}, {A, D} -> {G, H}, {B} -> {I}, {G} -> {J} , {F} -> {K} }.
       What is the key of R?
       Decompose R (1) into 2NF, (2) then into 3NF relations, and (3) check
whether the 3NF relations you obtained are in BCNF or not.

Ans:
       A minimal set of attributes whose closure includes all the attributes in R is a key.

   Since the closure of {A, B},
       {A, B}+ = R,
       one key of R is {A, B} (in this case, it is the only key).

   To normalize R intuitively into 2NF then 3NF, we take the following steps

   First, identify partial dependencies that violate 2NF. These are attributes that are
functionally dependent on either parts of the key, {A} or {B}, alone.

   We can calculate the closures {B}+ to determine partially dependent attributes:

           {B}+ = {B}, hence {B} -> {B,I} ({B} -> {B} is a trivial dependency)
          
   Since all attributes are functionally dependent on each other.
      
   only R1= B->I is in 2NF and 3NF,
      
   The final set of relations in 3NF are {R1}
===================================================================================

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.