14
Scheme (R) = (A,B,C,D,E,F,G,H)

Function Dependencies (F) = {A->CGH, AD->C, DE->F, G->H}

How would I perform a lossless-join decomposition of the schema R into Third Normal Form (3NF)?

Any help will be appreciated.

Chandrahas Aroori
  • 955
  • 2
  • 14
  • 27
Chris
  • 141
  • 1
  • 1
  • 3
  • 1
    Shouldn't the study guide tell you the process of solving this sort of problem? Or at the least, give you an answer you can check against? – Anon. Dec 05 '10 at 23:26

2 Answers2

18

Since A→CGH and Ax→C for any letter x, we can ignore the second of the functional dependencies (AD→C) because it doesn't tell us anything that A→CGH doesn't also tell us.

There is nothing that determines B; there is nothing that determines D.

Since G determines H, and A determines both G and H, we can separate G→H into a relation (there is a transitive dependency A→G and G→H).

R1 = { G, H }       : PK = { G }

That leaves F' = { A→CG, DE→F } and R' = (A, B, C, D, E, F, G).

The two functional dependencies left can form two more relations:

R2 = { A, C, G }    : PK = { A }
R3 = { D, E, F }    : PK = { D, E }

That leaves R'' = { A, B, D, E }

R4 = { A, B, D, E } : PK = { A, B, D, E }

The join of R1, R2, R3, and R4 should leave you with the R you started with for any starting value of R (that satisfies the constraints of the given functional dependencies).

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
  • 1. why Since A→CGH and Ax→C for any letter x, we can ignore the second of the functional dependencies (AD→C) because it doesn't tell us anything that A→CGH doesn't also tell us? A determines a subset, does it specifically determines every singe atoms, what AD seems to do? – Revolucion for Monica May 09 '16 at 19:06
  • 2. So you removed the "new key" and the attributes you determined in `F'`? Is it the way determines a 3NF decompostion? But what is needed to start such an algorithm? At least two FD have to determine the same attribute? – Revolucion for Monica May 09 '16 at 19:07
  • @Marine1: I'm not sure quite what you are asking. In the answer, I said (and you quoted) _"Since A→CGH and Ax→C for any letter x, we can ignore the second of the functional dependencies (AD→C) because it doesn't tell us anything that A→CGH doesn't also tell us."_ Are you asking about the validity of that assertion? Check Wikipedia on [Armstrong's Axioms](http://en.wikipedia.org/wiki/Armstrong's_axioms) or [Functional Dependencies](http://en.wikipedia.org/wiki/Functional_dependency) and use decomposition, augmentation and decomposition again to obtain AD→C from A→CGH. – Jonathan Leffler May 10 '16 at 19:49
  • If the problem is something else, please can you explain what your concern is more clearly — it may even warrant a new question that cross-references this one but takes the information that's needed to make the question free-standing (dropping the x-ref shouldn't make the question incomprehensible). A general question about 'how do you take a general unnormalized table schema and a set of functional dependencies and create a normalized schema' is likely to be too broad, though. Tied to a specific unnormalized schema and set of functional dependencies, it might be OK, as here. – Jonathan Leffler May 10 '16 at 19:53
  • From: A→CGH by decomposition A→C; by augmentation AD→CD; by decomposition AD→C. – Jonathan Leffler May 10 '16 at 19:59
-1

Overall you should get the min cover first and then add the key ABDE

overloading
  • 145
  • 9