3NF decomposition + database normalization

normalization

I am studying for a test and would like to know if I am on the right track to get the following into 3NF with minimal number of keys for R:

Given relation R(A,B,C,D,E,F,G) and the functional dependencies
f = {A->DE, E->BF, AB->FG, G->D, F->D}

  1. how can I derive the minimal key(s) of R ->
    I thought of this f+={A→BDEFG, E→BF, G→D, F→D} but it is wrong I don;t know how

  2. Is R in 3NF? If not decompose it into 3NF.

Best Answer

Every textbook I've ever seen gives you one or more algorithms you can use to determine every possible candidate key for R{ABCDEFG}. (It can be a fairly laborious and time-consuming job.) Based on the FDs you provided, I think R{ABCDEFG} has only one candidate key, {AC}.

Loosely speaking, for a relation to be in 3NF, it must be in 2NF and have no transitive dependencies. For a relation to be in 2NF, it must be in 1NF and have no partial key dependencies.

  • The only candidate key here is {AC}.
  • From A->DE we know that DE is dependent on A, which is only part of the candidate key.

Therefore, R{ABCDEFG} is not in 2NF (or 3NF) based on the FDs you provided.