Re: Getting Normal Forms *wrong*. Is there actual disagreement on what 1NF > 3NF means or is this sloppiness or ignorance on authors' parts?

On Aug 7, 7:56 am, dana <dana_at_w..._at_yahoo.com> wrote:
> Is there "genuine" disagreement on what 1NF, 2NF and 3NF mean? Or is
> the variety of contradictory in-print definitions of Normal Forms due
> to sloppy thinking, slopping writing, or plain old ignorance from
> authors? Also, where can I find an accurate, concise (less than 3
> sentences for each NF), and easy-for-any-IT-person-to-understand,
> definition of Normal Forms 1 through 3?
>
> My understanding of Normal Forms 1 through 3 is as follows. I look
> forward to being corrected--but hopefully in a polite, constructive,
> professional manner. I'm leaving out any mention of relations and
> relvars here, because I don't expect the typical IT person to know
> what those are (although it can be argued, convincingly I think, that
> they should):
>
> 1NF:
>            1) No multivalued attributes (e.g. every attribute in every
> tuple should be atomic)
>            2) No repeating groups of attributes are allowed (e.g.
> SKILL_1, SKILL_2, SKILL_n are out)
>
> Many common definitions I've read in books on database design miss
> point #1 completely. Is that because #1 is not part of 1NF, because
> the authors are "protecting" the reader, or because of author
> ignorance on what it is for something to be in 1NF?

1NF: Each component of each tuple in each relation has exactly one value drawn from a domain of similar values.

There is some disagreement about whether domains must be simple or can be complex.

A repeating groups of attributes does not violate 1NF; a repeating group of values in a component does.

>
> 2NF:
>            1) Must already be in 1NF
>            2) Every non-key attribute must rely exclusively on the
> key, e.g. no Functional Dependencies.
>
> Here's something I really need clarification on. Is it the case, or is
> it *not* the case, that 2NF "only applies" in cases where there is a
> composite key? This is my understanding, but it may be dead wrong. If
> so, I need to know sooner or later. I have seen this point glossed
> over or missed by various authors, some even using the words "fully
> dependent" when perhaps they should have used "Functionally
> Dependent"; meaning they either don't know what a functional
> dependency is, or they're trying to "protect" the reader (and that may
> or may not be legit given the context and one's proclivities).

2NF: No attribute is functionally determined by any subset of any candidate key.

> 3NF:
>
>         1) Must already be in 2NF
>         2) No transitive dependencies. All non-key attributes must
> rely exclusively on the key.
>
> I would like some concise and clear definitions of functional and
> transitive dependencies, because 2NF and 3NF sound similar to me.

3NF: No attribute is functionally determined by any set of attributes that is not a superkey.

A functional dependency, X --> Y, (read X functionally determines Y (or just X determines Y), or Y depends on X), is a constraint of the form "whenever two tuples agree on values for X, they must agree on values for Y, where X and Y are sets of attributes."

A transitive dependency is a consequence of the inference rules for functional dependencies: If X --> Y and Y --> Z, then X --> Z. The goal of 3NF is to eliminate all instances in which Y is not also a superkey. If both X and Y are superkeys, then X --> Y and Y --> X, so whenever there is more than one key, every attribute will be both transitively and nontransitively dependent upon all candidate keys.

Note: a superkey is a not necessarily proper superset of a candidate key.

> Pet peeve:
>
> I dislike the mantra, "the key, the whole key, and nothing but the
> key" to characterize Normal Forms 1 through 3. What is evidently
> intended as a helpful mantra seems to give a false sense of
> understanding. I'm all about some mnemonics, but understanding must
> preceed the mnemonic, right?
>
> Finally, what are some good books out there on database design for
> neophytes and experienced folks alike that gets the theory correct
> while not getting mired in what relational databases *could* be. I
> read a really great book by Fabian Pascal, the title of which escapes
> me, which I need to go and buy--believe it had the word Practitioner
> in it.
>
> I want to be faithful to relational theory as much as possible while
> getting work done with products currently on the market. I believe
> Fabian Pascal stated that theory was immensely important for practice;
> that it was not about a bunch of curmudgeonly academics bickering
> about how many angels can stand on the head of a pin. Is he correct?
> While it's essential that methodologists and theorists are out there
> visioning what databases can or should be, practitioners have to get
> projects completed with existing resources.
>
> Thanks very much.
>
> Dana
Received on Fri Aug 07 2009 - 15:41:19 CEST