The 2NF states that all non-key attributes must be fully functionally dependent on the candidate key. I was wondering would the relation be in 2NF if "each" individual candidate key determines all other attributes including other candidate keys not presented on the left-hand side?

What if a dependency only determines "all" the non-keys rather than all the non-keys + other candidate keys? Let's assume we have the following relation: R (PK, CCK1, CCK2, CCK3, CCK4, CCK5, NK1, NK2, NK3). This relation contains three candidate keys: PK (which is the primary key of the relation), {CCK1, CCK2} <-- The second candidate key, and {CCK3, CCK4, CCK5} as the third candidate key.

If there is a dependency that states {CCK1, CCK2} -> {PK, CCK3, CCK4, CCK5, NK1, NK2, NK3, is this FD in 2NF? How about {CCK1, CCK2} -> {CCK3, CCK4, CCK5, NK1, NK2, NK3}? What about partial dependency of one candidate key unto another candidate key, for instance, {CCK1, CCk2} -> {PK, CCK3, CCK4, NK1, NK2, NK3}? Notice CCK2 is missing from the dependency.

The first example perhaps is indeed in 2NF but what about the latter FD? The reason I am confused is that the definition of 2NF does not indicate any fully functionally dependency on the candidate keys and only talks about non-key attributes.


Any insight would be greatly appreciated.

the best insight into attribute and key dependencies is with real examples

instead of PK, CCK, etc., how about coming up with actual columns, like employeeno, employeename, deptno, etc.

can you show us a table of values where these relationships exist? if so, we can give you more insights

i've never seen any real examples of {CCK1, CCK2} -> {PK, CCK3, CCK4, CCK5, NK1, NK2, NK3} and i would really like to

r937,

My question was not meant to introduce a real-world situation. I wish I could come up with such sample, but I am sure through some extremely complex scenario, this could very well be the design -- right or wrong, does not matter. My question is more rhetorical as in "if" there is such a relation with all these dependencies, what would it take to identify the problem and how I can utilize normalization to remedy the issue.

I used the abbreviated attributes to make the relation simple to follow. So again this meant to illustrate a situation where the design of the relation has gone terribly wrong and now we have to sort of fix it. Anyway, if the example sounds confusing, I believe my initial set of questions should convey the point I am seeking help on.

Thank you

It looks like you are getting snarled up in abstraction. The case where you have several composite candidate keys as well as a primary key is very rare. Also once you have chosen your primary keys the candidacy of the other attributes is irrelevant and they are demoted to alternate keys or non-key attributes. Alternate keys will have a unique index. When the primary key is chosen the rules of 2nd normal form apply to that primary key and only that primary key all the other attributes are effectively non-key.