1

I know that for a Relation to be 3NF It has to be 2NF and no transitive dependencies should exist but I couldn't answer the following question:

For a relationship to be 3NF :
A) All Attributes should depend on the primary key.
B) The relationship should only have one Foreign Key.
C) The relationship should only have one Primary Key.
D) The Relationship's Table should only have atomic values

D applies on a 3NF relationship because it's one of the conditions of 1NF and for a relationship to be 3NF it has to be 2NF and 1NF. C is too general and doesn't apply just on 3NF but my book has chosen it as the answer! B is not related to Normalization and A may be considered as 2NF but they didn't say all non-attributes so I don't know actually, what is the right answer here?

Miriam Arbaji
  • 319
  • 1
  • 3
  • 17
  • See my answer. We might be able to help more if you give your textbook name & edition. Also when you are justifying things, to be sound you must appeal to definitions and/or accepted facts/theorms via clear steps. "A may be considered as 2NF" is not clear. – philipxy Dec 07 '19 at 22:41
  • I Live in Syria and What we study in College is not available online that's why I didn't mention the textbook name – Miriam Arbaji Dec 07 '19 at 22:51
  • It's definitely not a translation of a book available in English? Anyway, see my answer & see if you can find claims that clarify the questions I raise & edit them into your post. Eg what does your textbook say about whether you have to have a PK? If the English in your quote is your own, try to phrase it more precisely; consider the ambiguities in my post. – philipxy Dec 07 '19 at 22:57
  • Yes it's my translation from Arabic and I tried to translated precisely but honestly the question doesn't say that much, and no English Translation of the book is available – Miriam Arbaji Dec 07 '19 at 22:59
  • 1
    I actually hadn't seen your 1st try at 3NF. Both have problems. I give a correct definition in my edited answer. **Exact details matter.** We need to memorize definitions exactly & the definition of the terms they use & so on. Then only use terms in a way that we could replace them by their definitions. – philipxy Dec 07 '19 at 23:38

1 Answers1

3

By definition of "superkey", all attributes depend on a superkey. By definition of "CK" (candidate key) as a superkey containing no smaller superkey, all attributes depend on a CK. By definition of "PK" (primary key) as a distinguished CK, all attributes depend on a PK. So A is an answer.

FKs (foreign keys) are irrelevant to normalization. So B is not an answer.

By definition of "PK", a relation/schema can have at most one, which we pick from among the CKs. There can always be a PK, because there is always at least one CK. Whether you must pick a PK depends on your textbook--PKs per se have no role in normalization theory. Unfortunately "should only have one" is not clear, because it might mean exacly one & it might mean at most one. So if it agrees with your textbook, C is an answer; otherwise not. Go with your textbook.

Presentations that talk about "atomic" values require them in either the definition of "relation" or the definition of "1NF" & higher NFs. So for your textbook presumably D is an answer. But actually the notion of atomic values, although ubiquitous, is confused & also "1NF" has no single meaning. Go with your textbook.

(None of the options guarantee 3NF.)

PS Your characterization of 3NF is not correct. Only certain transitive FDs (functional dependencies) matter--3NF is when/iff 2NF & no non-CK attribute is transitively dependent on a CK. (If one's "is in 1NF" is just "is a relation" then one can drop the "2NF &".) And be sure you get the correct definition of "transitive FD"--for sets X & Y, X->Y is transitive when/iff there exists set S where X->S & S->Y & not S->X & not S=Y. Get correct definitions from a good textbook.

philipxy
  • 14,867
  • 6
  • 39
  • 83
  • I know that transitive FD Means A->B and B->C so A->C, But I will look more into it thank you for your help – Miriam Arbaji Dec 07 '19 at 22:57
  • 1
    The definition of "transitive FD" is that X->Y is transitive when/iff there exists A where X->A & A->Y & not A->X & not A=Y. Sadly most textbooks don't make this clear. Sadly most textbooks regurgitate other textbooks, including their mistakes. PS "A->B and B->C so A->C" is not a clear definition of an expression involving A & B. Use enough words, phrases & sentences, including references to parts of things. to say what you mean. – philipxy Dec 07 '19 at 23:03
  • Okay I will, Thank you for clarifying a lot of things to me – Miriam Arbaji Dec 07 '19 at 23:08