0

I'm looking at a specific example of a relation with a composite primary key. Based on its functional dependencies, I know it is in 1NF. While normalizing it to 3NF I came across a situation I have not yet encountered. I followed the steps for all partial dependencies and transitive dependencies, but the last step of normalizing to 3NF requires you to create a relation that contains the primary key and all non-prime attributes dependent on it.

In my specific case, I have the primary key, but no full functional dependencies on it. Do I make a table containing only my composite primary key? Or do I not make one at all?

I have no confusion of composite and primary keys. See my comment below to see why I believe my question is different from that one

Gary
  • 67
  • 1
  • 8
  • Possible duplicate of [Normal forms - 2nd vs 3rd - is the difference just composite keys? non trivial dependency?](http://stackoverflow.com/questions/27474203/normal-forms-2nd-vs-3rd-is-the-difference-just-composite-keys-non-trivial-d) – philipxy Feb 10 '17 at 04:00
  • I wouldn't say my question is a duplicate of that one. I do understand the difference between PKs and CKs. When following the steps I've been taught in class to normalize to 3NF, I first create a relation schema for each partial and transitive dependency. The last step I take is to create a schema that contains the PK of the original relation AND any attributes that are fully dependent on that PK – Gary Feb 10 '17 at 04:05
  • See also [this](http://stackoverflow.com/a/35965468/3404097), [this](http://stackoverflow.com/questions/40234799/definition-of-3nf/40314371#40314371), [this](http://stackoverflow.com/a/34191604/3404097) and other answers of mine involving 3NF / third normal form, or full* partial* functional* dependen*. Also [this](http://stackoverflow.com/a/24038895/3404097) and others on atom* and 1NF / first normal form. – philipxy Feb 10 '17 at 04:16
  • I use PK vs CK as an example of many misconptions in your post. Eg we don't go through 2NF to get to 3NF or BCNF, we use an algorithm directly. Find one. Your post doesn't seem to be referring to one, but to going to 2NF then 3NF. But to get to 2NF we get rid of all partial dependencies on CKs, so only full ones are left. Your *whole post* is odd like that. And I didn't pick that as duplicate because of PK vs CK. I picked it for the *answer*. Please edit your answer to say what algorithm you (think you) are following & show steps of your example. Also google 'stackoverflow.com 3NF algorithm'. – philipxy Feb 10 '17 at 04:28

2 Answers2

1

FDs (functional dependencies) have nothing to do with 1NF, no matter which of the various meanings for "1NF" you are using. So it's not clear what you're trying to say about 1NF. A relation by definition has a value for each attribute of each tuple. A thing like a relation with something like a "list of values" for some part like an attribute of some part like a tuple is not a relation so CKs (candidate keys) & FDs do not apply. If you define a "1NF relation" as one without certain data types (because of some fuzzy application-dependent received wisdom about "atomicity", or in Codd's sense of having no relation-valued attributes) then satisfaction does not depend on whether FDs hold on the design with that data type. (Moreover if the "normalized" "atomic"-attributed version of such a "non-1NF" "non-atomic"-attributed design satisfies a FD then the original has a certain constraint, but it's not a FD constraint.)

FDs that aren't partial are full. The only partial FDs that matter on the way to 2NF & 3NF are partial FDs of non-prime attributes on CKs. When these are gone you have 2NF. (From "followed the steps for all partial dependencies and transitive dependencies" it sounds like your plan is to decompose to 2NF then to 3NF.) Partial FDs just aren't mentioned in a definition of 3NF that requires 2NF. Also, definitions for 3NF and the common algorithm for putting a relation into 3NF just don't make use of partial FDs.

There can also be other partial FDs. They just don't matter. In particular, all the FDs of attributes on proper superkeys are partial. Just follow the definitions for determining what normal form(s) a relation is and follow the algorithms for putting a relation into a normal form. This goes for all definitions and algorithms. There is no point in worrying about every property you notice that it might be "bad".

PS You shouldn't put a relation into 3NF by first putting it into 2NF. That can exclude some good 3NF decompositions of the original from being found. Use an algorithm for 3NF. (The usual one for 3NF actually generates decompositions in the slightly stronger EKNF (Elementary Key Normal Form)).

philipxy
  • 14,867
  • 6
  • 39
  • 83
  • I disagree. If there is a value in the table that is NOT functionally dependent on the key, then the table is not in 1NF. This is hard to do,in real life. However, there are plenty of designers that are faced with a situation where the primary key of a table determines not a single value but a list of values. For example, if the key is studentId and the list is a list of courses the student is signed up for. Some people want to put the course list in a single column, with comma separated values. This is actually a violation of 1NF, even though it doesn't look that way. – Walter Mitty Feb 10 '17 at 16:53
  • "FD" is a technical term, and you are abusing (ie not using) it when you say "determines a list of values". A relation by definition has a value for each attribute of each tuple. A thing with a "list of values" for some thing like an attribute of some thing like a tuple is not a relation so CKs & FDs do not apply. If you define "a 1NF relation" as a relation without certain data types because of some fuzzy received wisdom about "atomicity", satisfaction does not depend on whether FDs hold on the design with that data type. (Please do define "1NF".) (See my edited answer.) – philipxy Feb 10 '17 at 20:08
  • PS The CKs are *a consequence of the FDs*. Every relation has a CK. "A value in the table that is NOT functionally dependent on the key" cannot happen. Anyway a "key" is something in a *relation*, as is a FK. You might have a design that is not a relation, so (by whatever definition you choose for "1NF") it is not "a 1NF relation", but then it wasn't CKs & FDs that are the problem because CKs & FDs are in *relations*. You might have things *like* a CK or FD in mind, involving things *like* relations (but not). That's not relations, CKs or FDs. – philipxy Feb 10 '17 at 20:10
  • @philpxy I am not convinced that you are correct. In the interest of clarifying concepts for the OP, it will be best if we take this disagreement off line. – Walter Mitty Feb 10 '17 at 20:35
1

It is perfectly legitimate to have a relation that consists of a composite key and no other attributes. It's not only theoretically valid, but also it happens in the real world.

In such situation, that relation is merely asserting the existence of something identified by the composite key. And it would be used by the user of the data to test for existence and not for the same kind of lookups that a relation with non key attributes is typically used for.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58