Monday, February 22, 2016

Database Normalization Solved Exercise 7

Set of solved exercises in Normalization / Normalization Solved Examples / How to find candidate keys, and primary keys in database? / Sets of examples to find the keys of a tables / Process of Key finding in a database – Examples


Question:



Consider the relation scheme R = (E, F, G, H, I, J, K, L, M, N) and the set of functional dependencies {{E, F} {G}, {F} {I, J}, {E, H} {K, L} {M}, {K} {M}, {L} {N}} on R. What is the key for R?


Solution:

To find the key of a relation, we need to find the closure of attributes. If any attribute’s or set of attributes’ closure gives all the attributes of the relation, then we would say that attribute/set of attributes as the key for that relation.

To simplify this task or to avoid finding closure of all attributes, let us do find the closure for left hand side (LHS) attributes of the functional dependencies.

For the given question, attributes F, (E, F), (E, H), K, and L are the LHS attributes.


  • The closure of F, i.e., (F)+ = FIJ EFGHIJKLMN. Hence F is not a candidate key. [Refer, How to find closure]

  • (EF)+ = EFGIJ EFGHIJKLMN. Hence EF is not a candidate key.

  • (EH)+ = EHKLMN EFGHIJKLMN. Hence EH is not a candidate key.

  • (K)+ = KM EFGHIJKLMN. Hence, K is not a candidate key.

We observed that the left side attributes of any FDs alone cannot form a key. Let us try with the combination of two FDs LHS.



  • (EFH)+ = EFGHIJKLMN  = R.

Hence, EFH is the key for this relation.




[
Derivation of EFH+:
Step 1: result = EFH
Step 2: result = EFGH from {E, F} {G}
Step 3: result = EFGHIJ from {F} {I, J}
Step 4: result = EFGHIJKLM from {E, H} {K, L} {M}
Step 5: result = EFGHIJKLMN from {L} {N}
We have reached that the result = R. hence, EFH is the key.
]
 





Related Posts:








How to find candidate keys using closure finding algorithms


3 comments:

  1. Plzz Solve These Questions
    Consider a relation schema R = {A, B, C, D, E, F, H} on which the following functional dependencies hold: : {A → B, BE → H, EF → C, D → A}. Identify the candidate keys of R?
    Consider a relation schema R = {E, F, G, H, I, J, K,L,M,N} on which the following functional dependencies EJ → G, F → I, EH → L, K → LM, L → N, G → N. Identify key for R. Also find its
    minimal set.

    ReplyDelete
  2. Plz Solve These Questions... I'm in trouble
    Q. Consider a relation schema R = {A, B, C, D, E, F, H} on which the following functional dependencies hold: : {A → B, BE → H, EF → C, D → A}. Identify the candidate keys of R?
    Q. Consider a relation schema R = {E, F, G, H, I, J, K,L,M,N} on which the following functional dependencies EJ → G, F → I, EH → L, K → LM, L → N, G → N}. Identify key for R. Also find its
    minimal set.

    ReplyDelete

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents

data recovery