olsonea (7/28/2011)

---

When I was studying Relational Database Concepts in college, there was a catchy mnemonic for 3NF: "The key, the whole key, and nothing but the key, so help me Codd."

The difference between 2NF and 3NF is that 2NF does not permit any non-prime attribute to have a dependency on something which is a proper subset of a candidate key, while in 3NF a non-prime attribute may not depend on anything which is not a superkey

James Goodwin (7/28/2011)

---

My confusion is that the original table in the example is, then, not in 2NF because the phone number depends on something (Group) that is not the whole key, or in fact, even part of the key. So it seems that you took a table from 1NF to 2NF which was then trivially in 3NF (in the sense that no extra work was required to get it into 3NF).

What I would like to see is a table that is in 2NF converted into 3NF.
--
JimFive

There's nothing in 2NF to stop an attribute being dependent on some set of attributes that includes something that isn't part of a key. The rule is that if a non-prime attribute is dependent on something that is a subset of a key that subset must be the whole key. Or putting it another way, the whole key is not the only thing it can be dependent on, the rule is simply that it must never be dependent on a partial key.

john.rees-894283 (7/28/2011)

---

Hi Tom,

I was also confused about whether the 3NF example was in 2NF or not.

I had just submitted a really long question, but then saw your reply to James:


This is a great summary, and IMO would be a good addition to the 2NF article.

Thanks, John

dogramone (7/28/2011)

---

Thanks for these articles Tom. I have ensure the application developers here are reading all of them. Of course the DBA's already get it but like a lot of small shops the DBAs don't do development and although there are a lot of senior developers they are all building to a OLTP style and are always making these common mistakes.

I'm anxiously looking forward to the next in the series and the Fourth and Fifth are for me as I've only ever gotten to 3.5 or what I was taught as Boyce Codd Normal Form.

Tom.Thomson (7/28/2011)

---

Ah, a confusion about 2NF. You hit the crucial point when you said "or [not] in fact, even part of [the key]", because that is of course allowed by 2NF. There's nothing in 2NF to stop an attribute being dependent on some set of attributes that includes something that isn't part of a key. The rule is that if a non-prime attribute is dependent on something that is a subset of a key that subset must be the whole key. Or putting it another way, the whole key is not the only thing it can be dependent on, the rule is simply that it must never be dependent on a partial key. So the original table inthe 3NF article is indeed in 2NF.