MORE ON REPEATING GROUPS AND NORMALIZATION

From: GS

To: Editor

A commonly used example of a table that is not in 2-NF is one with repeated attributes i.e. child1, child2, child3. However, after examining the definition of 2NF in your book PRACTICAL ISSUES IN DATABASE MANAGEMENT, it seems to me that tables such as these do in fact satisfy 2NF. Am I missing something?

From: Fabian Pascal

To: GS

The level you are referring to is 1NF, not 2NF.

The commonly used example is wrong: such a table may be poorly designed, but it is in 1NF.

Very Important: The determination of the normalization level of any given table can be made only with respect to a set of business rules that specify the dependencies between the attributes that the columns represent!!! It is impossible to determine that from just the table [without knowing the rules].

Thus, whether the table you refer to is in 2NF I have no idea, I would have to know the pertinent business rules and the whole table to determine that.

No, you are not [missing anything], but you're still talking 1NF, not 2NF.

R-tables are in 1NF by definition, because they do not have the table equivalent of repeating groups, multivalued columns. If your table contained a column called CHILDREN that represented multiple child values, then it would not be in 1NF and, thus, it would not be a R-table.

The issue is actually trickier, but it will do for your purposes. I will write an article on this at some point.

Ed. Comment: Such errors are induced by some who, for no good reason, are considered experts in the industry. Here’s, for example, Joe Celko in a very old issue of DBMS magazine, which I had debunked in an article at that time (I ignore the issue as to why you would want to do that in the first place!)

"The relational model states that in order to be in 1NF [First Normal Form], a table can have no repeating groups. But since SQL does not support repeating groups, the only way] people can write a repeating group in SQL ... is:

CREATE TABLE employees

(emp# INTEGER PRIMARY KEY,

ename CHAR(15) NOT NULL,

child1 CHAR(15),

child2 CHAR(15),

: :

childN CHAR(15));”

Posted 11/15/02

[ABOUT] [QUOTES] [LINKS]