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?

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).

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.

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

On Aug 7, 7:56*am, dana <dana_at_w... (AT) yahoo (DOT) com> wrote:
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: No attribute is functionally determined by any subset of any
candidate key.

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.

"dana" <dana_at_work (AT) yahoo (DOT) com> wrote

Many of the differences arise out of sloppy thinking, sloppy writing, or
oversimplification for the sake of ease of understanding.

However, there are at least two other sources of differences.

The first is that some writers phrase normal forms as pertaining to tables,
whether it's relational tables or SQL tables. The most formal definitions
pertain to relations and relvars.

The second is the difference between first normal form as defined by EF Codd
and as defined by CJ Date.




See the difference between EF Codd and CJ Date on point #1. Point #2
deserves more elaboration, which I'll come back to later.

Note your use of "the key". There are some who would call this "sloppy
writing". I am not one of them. A formal definition would refer back to
candidate keys and not back to "the key". However, your phrasing is the way
I first learned it some 25 years ago.

Point 2 above is misworded. In 2 NF non key attirubtes must depend on the
entire key, and not depend on part of the key.

For example: if the key is Student ID and Course ID, and there is an
attribute Course Name which is functionally dependent of Course ID, but not
on Student ID, then putting these three in one table violates 2NF. In the
case of a simple KEY and a relation (or table) in 1NF, conformance to 2NF is
automatic.






think the examples that go with most explanations should make this clear.
The following wording is sloppy, but I hope it clarifies.

A table is not in third normal form if at least one non key column depends
on another non key column (or columns).
A table is not in second normal form is at least one column does not depend
on part of the the key. (If the key has only one part, this can't happen).





While I understand your qualms about mantras, I do not share them in this
case. "the key, the whole key, and nothing but the key (so help me Codd)"
is a neat summary of the rules for the first three forms.



One of the mantras that carries a great deal of weight with me is "Theory IS
practical" from CJ Date. Having said that, I think that Date's
introduction to database systems is more geared to the engineer who proposes
to build a new DBMS than to the person who just wants to learn how to design
decent databases and develop decent database applications on Oracle, DB2,
SQL Server or even (God help me) MS Access. A would be database designer
does need to learn the basic concepts, and learning those concepts by trial
and error is way too expensive. Far too many seasoned programmers just dive
in, only to discover their design flaws after a mountain of code already
depends on their naive design.

I'm not going to recommend any of my books to you, because they are way out
of date. And i'm not going to recommend any books I have not read.
Hopefully, somebody else will help out here.


Without meaning to berate you at all, I think you still have a great deal to
learn. I could be worng.

One thing you might want to learn over the next year or so is when to
knowingly violate one of the normal forms in your design, and how to come
up with a good design even when you don't follow normaliztion all the way.
Another thing you might want to learn is how to design an SQL database that
conforms to the relational model, and how to write queries and updates to
that database whose results can be forecasted by relational think, in spite
of the differences between SQL and "truly relational"..

A third thing you might want to learn is how to keep performance
considerations in proper perspective. High performance is one measure of
goodness. But there are other measures such as ease of programming,
integrity of the data. flexibility for adaping the data to an unforeseen
use, etc. etc. Far too many neophytes focus in on performance to the
exclusion of everything else. The sad thing is that a lot of those
neophytes end up retreating to record-at-a-time processing, and paying a
huge performance hit compared to the performance hit they would have
suffered with a simple and sound design.

dana wrote:

Yes.


http://filedb.experts-exchange.com/i...tionPoster.pdf


A repeating group is basically a synonym for a multivalued attribute.
Whether SKILL_1, SKILL_2 etc. comprise a repeating group depends on what
SKILL_1, SKILL_2 etc. are.

If SKILL_1 is always "reading" and SKILL_2 is always "horseback riding",
for example, then they do not violate 1NF. If one is trying, on the
other hand, to construct an array type where none exists such that
sometimes SKILL_1 is "reading" and sometimes it is "horseback riding",
then that is a problem. Some might argue it still does not violate 1NF
and is just an incoherent design.


I suspect it is just a little misunderstanding regarding the terms used.


Statement 2) makes no sense to me. 1NF requires every non-key attribute
is functionally dependent on each candidate key.


It is the case.


Each non-key attribute has to be functionally dependent on the entire
key -- not just part of a composite key. "Fully dependent" seems like a
shorthand for that--perhaps an ill-advised shorthand.


I suggest you stop and review what functional dependency means.
Normalization is all about functional dependencies, and I suspect you
would understand normalization better if you understood functional
dependency better.


Functional dependency is closely related to the logical operation called
"implication", and the same symbol is generally used for both. ie. the
arrow.

B is functionally dependent on A if for every instance of the same value
of A, one has the same value of B. Thus, any given value of A implies a
specific value of B.

Note that two different values of A might imply the same value of B so
the relation is not symmetric. B does not necessarily imply A when A
implies B.

While not symmetric, implication and functional dependency are
transitive. If B implies C and A implies B, then A implies C too.

A->B->C means A->C

In the latter case, the functional dependency is transitive. In a sense,
A only implies C indirectly.


It's not so much a mantra as a very accurate description of what the
normal forms mean. 1NF means every non-key attribute depends on the key
(and not some array index instead.) 2NF means every non-key attribute
depends on the whole key (not just part of a composite key). 3NF means
every non-key attribute depends on the key but not on any non-key
attribute(s).

The only part of the mnemonic that is shaky is the last part. If there
are multiple candidate keys, of course, the non-key attributes depend on
each of the candidate keys-not just a single key.


The one you are thinking of is Fabian's /Practical Issues in Database
Management: A Reference for the Thinking Practitioner/

His 2nd book is good too. I have never read his 1st book.

Every text I have found has some annoying errors, but many are still
useful for learning 1st principles. I originally learned from one of
Teorey's books.


He is correct. Of course, he's correct. I don't know why that would
surprize anyone or why anyone would have to ask.

Would you want to fly in an airplane designed by someone who was
ignorant of the theories of fluid dynamics, statics or newton's laws?
You might end up in an airplane with no usable control surfaces, with
wings that fall off in mid-flight, or with no way on landing to stop
before the end of the runway.


How will they use the resources if they don't know how to use them?

"Bob Badour" <bbadour (AT) pei (DOT) sympatico.ca> wrote

Here's a relation:

Triangles {Id*, Area, SumOfAngles}

* = key. The following dependency holds because the sum of the angles of any
triangle is a constant:

{}->{SumOfAngles}

{} (the empty set) is a proper subset of the key {Id} and {}->{SumOfAngles}
is a dependency on only part of the key. Triangles is a relation without a
composite key and it violates 2NF. I don't see why 2NF shouldn't apply to
this case, even though it's unusual and not especially interesting.

--
David Portas

David Portas wrote:

I sit corrected. Thank you!

David Portas wrote:
I think this a very nice example of something that I don't know the name
for! (a kind of tautology maybe?) I guess this example is not
applicable to trapezoids. What if nobody in the enterprise knows
geometry? The example I like is {name*, state, zipcode}.; For
convenience, I might choose to ignore the fact that state determines
zipcode (or does it? - I think I remember reading that a few obscure
zipcodes span states, or if they don't, that they might in the future!).
My attitude is that there is always somebody who knows more about a
subject than I do, but they might not be available when I need them.
This reminds me how upset I get when people want a db to record every
possible fact, not just the ones that the present application needs.
But thanks for posting it, it's a good one to remember..

paul c wrote:
Oops, meant to say zipcode determines state, (or maybe it does!).

On Aug 8, 8:48 am, paul c <toledobythe... (AT) oohay (DOT) ac> wrote:

Every trapezoid is a quadrilateral. The internal angles of a
quadrilateral always sum to 360 degrees.

David BL wrote:
It seems you are a subject expert who is not always available to
somevbody who is tasked witn comng up with a db desiign. Good for you,
but so what? I think anybody who has an app to make, shouldn't have to
spend forever trying to figure out what they don't knowl, nor be told
that by a kibbizer (no offence intgended, but that could well be the
circumstance here)f. What they already know should be the premise(s) of
their app, no matter how much more you or a different expert migjht know/.