-1

Given the follwing functional dependencies, it is a little bit confusing for me because third normal form says no non-prime attribute of R is transitively dependent on the primary key. So i removed the functional dependency C --> DE from table and placed it in new relation but all these attributes can also be determinded by the primary key of the relation. I think that i can't remove D and E from this table or should i remove because further BCNF also does not help in removing these attributes.Question is when i remove first functional dependency should i also remove D and E from the first table?enter image description here

Khan Saab
  • 434
  • 1
  • 8
  • 20
  • Yes, you should remove those attributes. Consider that from the relation ABC, given a certain value for AB, you have a (unique) value for C. With that value, in the second relation, you can find the values for D and E determined by AB. – Renzo May 25 '17 at 15:21
  • 1
    Please use text not images whenever you can. Parts of images can't be searched for or cut and pasted. – philipxy May 25 '17 at 19:49
  • Go to the *reference* you were given for the definition of "transitive FD" and the algorithms for decomposing to 3NF/BCNF. So what if "all these attributes can also be determinded by the primary key of the relation"? A PK *always* determines all attributes. Do "the relation" and "this relation" mean the original or the "new relation"? Also, PKs don't matter, CKs do. What does this have to do with transitive FDs & BCNF? Why BCNF if you want 3NF? Please edit your question to be clear. Illustrate all these things you are talking about and make sure that it is always clear what you referring to. – philipxy May 25 '17 at 20:03
  • 2
    *"no non-prime attribute of R is transitively dependent on the primary key"* No non-prime attribute of R is transitively dependent on *any candidate key*. – Mike Sherrill 'Cat Recall' May 26 '17 at 00:52

1 Answers1

0

To put a relation into a given NF (normal form) you should follow an algorithm that has been advised for that NF. (Eg given some FDs, there are lots of others that hold, per Armstrong's axioms; you need to deal with them too. Eg there are certain benefits to "preserving" FDs when possible, and a decomposition to 3NF components that preserves FDs is always possible; but if we decompose so that some FD's attributes are split between components, we can fail to preserve FDs.)

Note that these algorithms do not involve first normalizing to lower NFs. (That can stop "good" higher-NF designs from being the final result.)

When you do decompose to get rid of a FD X -> Y from a relation with attributes R, the decomposition will always be non-loss/non-additive if the components have attribute sets X U Y and R - Y. By repeated decompositions all your components will eventually be in the NF you want (if it is BCNF or below). But your overall decomposition won't necessarily be as "nice" as an advised algorithm would give you.

philipxy
  • 14,867
  • 6
  • 39
  • 83