-1

I am working on a normalization problem and am hung up on whether the given relation is in 2NF form. The relation is given as R(A,B,C,D,E,F,G) and the set of FD's is given as:

{ A->B, B->AC, F->ACDE, ADE->FG }.

The candidate keys that I have found are {F, ADE, BDE, ACDE}. However I am a bit confused if ACDE is allowed to be a candidate key since from my understanding, in order for an attribute to appear in a key, it must either appear on the LHS or on BOTH the LHS and RHS of a functional dependency. If ACDE is a legal key, then would it violate 2NF since ADE is a subset of ACDE and can still generate the non-prime attribute G?

philipxy
  • 14,867
  • 6
  • 39
  • 83
WRK
  • 1
  • 5
  • So what is a *definition* of CK & is it or is it not satisfied? It does not make sense to talk about a CK violating a NF. Certain *FDs* violate a NF per some particular definition. What do you mean for a CK to "generate" an attribute? You are not using terms properly. Please use enough words & sentences & references to parts of examples to clearly say what you mean. PS See my comment on another recent question re "the set of FD's" not being clear. PS Why do hold this "understanding"? PS "appear on the LHS or on BOTH the LHS and RHS of a FD" means the same thing as "appear on the LHS of a FD". – philipxy Nov 25 '18 at 10:51

1 Answers1

0

ACDE is not a candidate key, it is just a superkey. This can be easily seen if you consider that ACDE contains ADE, which is a candidate key.

In fact C cannot be a prime attribute, since any attribute appearing only in the RHS of functional dependencies cannot be part of any key.

So in this case there are only three candidate keys: ADE, BDE and F, and the relation is not in 2NF, since C, which is a not prime attribute, is determined from a proper subset of a candidate key. For instance, it is determined from B.

Renzo
  • 26,848
  • 5
  • 49
  • 61
  • Ah, that makes sense. But would this be in 2NF? I'm thinking not since B -> AC so BDE and ADE violate 2NF. Am I correct in this? – WRK Nov 25 '18 at 06:56
  • @WRK, you are correct, the relation is not 2NF. I have updated the answer. – Renzo Nov 25 '18 at 06:59
  • @WRK & Renzo Re the comment that this violates 2NF: See definitions of partial FD & 2NF. *FDs* violate, although a violating FD gives an associated CK & attribute. A violating partial FD is one with a CK as determinant, not the FD(s) that must also exist with a proper subset of the CK as determinant. When B -> AC holds with those 3 CKs, BDE -> C is a violating partial FD of a non-prime attribute on a CK. Also: One must examine *all* proper subsets of all CKs with each non-prime attribute to find all violators, you can't just look at some FDs in a cover. So WRK is not "correct in this". – philipxy Nov 25 '18 at 14:57
  • @philipxy So the correct answer would be to say that A,B are vilating partial FD's of a non-prime attribute on a CK. I've check for G and they all hold. So the two problematic CK's are ADE and BDE for C? – WRK Nov 25 '18 at 15:58
  • @WRK Again: An FD, not attribute, is partial or not. It is FDs, not CKs or attributes, that violate. Look at the definition you use--something like, 2NF is when there is no FD that is .... So we say that those FDs "violate" 2NF (per that definition). (It's not that attributes or CKs violate FDs.) PS Maybe the definition says "... & the RHS is a non-prime attribute". Maybe "... & the RHS contains a non-prime attribute". Those describe the same condition but have different notions of violating FDs. A certain common definition has yet a different notion. – philipxy Nov 25 '18 at 22:29
  • @philipxy The definition that I am using comes from Database System Concepts 6th edition - Silberschatz. The definition of 2NF in that book says that a Relation R is in 2NF if "Every non-prime attribute in R is fully dependent on every CK of R" and if it is also in 1NF, which for this problem is implied. Sorry for the missuses in terminology (ie "generates") I will amend this question, and the other, later. If I find that I can not make helpful to others then I will delete it. – WRK Nov 26 '18 at 01:38
  • @WRK Hi. Per your definition 2NF is violated when there is a non-prime attribute that is partally dependent on some CK. So we can reasonably speak of a *set* of FDs as violating. We usually speak of the partial dependence of an attribute on a CK as violating; since FD CK -> attribute expresses that, we say the FD violates. In general various CKs might partially determine various non-prime attributes. Yes we could figure out that talk of a violating CK or attribute is about a CK or attribute from one or more violating FDs. (As I said, "a violating FD gives an associated CK & attribute".) – philipxy Nov 26 '18 at 03:56
  • @WRK PS Exercise 8.17 in Silberschatz et al 6th: "A relation schema R is in [2NF] if each attribute A in R meets one of the following criteria: • It appears in a [CK]. • It is not partially dependent on a [CK]." There we would talk re *attributes* that violate 2NF & it would be unclear to talk re FDs that violate. (We ought to explicitly say something like, "call a violating FD one whose RHS is a violating attribute & LHS is a CK it's partially dependent on".) (This exemplifies why when we use an informal word like "violate" and/or ask re definitions that we *give the definitions*.) – philipxy Nov 26 '18 at 04:41