Sunday, June 26, 2022


09/19/23: For the latest on this subject see: FIRST NORMAL FORM - A DEFINITIVE GUIDE


“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?” --Reader


DBDebunk was maintained and kept free with the proceeds from my @AllAnalitics column. The site was discontinued in 2018. The content here is not available anywhere else, so if you deem it useful, particularly if you are a regular reader, please help upkeep it by purchasing publications, or donating. On-site seminars and consulting are available.Thank you.


05/21 SMS: Order and Relational Databases

05/02 SMS: "Relation Proliferation"?

04/25 SMS: Relational Database and Set Theory

- 08/19 Logical Symmetric Access, Data Sub-language, Kinds of Relations, Database Redundancy and Consistency, paper #2 in the new UNDERSTANDING THE REAL RDM series.
- 02/18 The Key to Relational Keys: A New Understanding, a new edition of paper #4 in the PRACTICAL DATABASE FOUNDATIONS series.
- 04/17 Interpretation and Representation of Database Relations, paper #1 in the new UNDERSTANDING THE REAL RDM series.
- 10/16 THE DBDEBUNK GUIDE TO MISCONCEPTIONS ABOUT DATA FUNDAMENTALS, my latest book (reviewed by Craig Mullins, Todd Everett, Toon Koppelaars, Davide Mauri).

- To work around Blogger limitations, the labels are mostly abbreviations or acronyms of the terms listed on the
FUNDAMENTALS page. For detailed instructions on how to understand and use the labels in conjunction with that page, see the ABOUT page. The 2017 and 2016 posts, including earlier posts rewritten in 2017 were relabeled accordingly. As other older posts are rewritten, they will also be relabeled. For all other older posts use Blogger search.
- The links to my AllAnalytics columns no longer work. I re-published only the 2017 columns @dbdebunk, and within them links to sources external to AllAnalytics may or may not work.

I deleted my Facebook account. You can follow me @DBDdebunk on Twitter: will link to new posts to this site, as well as To Laugh or Cry? and What's Wrong with This Picture? posts, and my exchanges on LinkedIn.

Note: Then & Now (T&N) is a new version of what used to be the Oldies but Goodies (OBG) series. To demonstrate the superiority of a sound theoretical foundation relative to the industry's fad-driven "cookbook" practices, as well as the evolution of  relational knowledge and understanding, I am re-visiting 2000-06 debunkings,  “polishing” them with my knowledge and understanding of today. This will enable you to judge how well my arguments hold up and the gap between the evolution and progress of the RDM  and the industry’s stagnation  (if not outright regress). The further readings, POSTS, BOOKS, PAPERS, LINKS are recommended (or, even better, organize one of our on-site SEMINARS, which can be customized to specific needs).


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 dependencies among the attributes represented by the columns!!! It is impossible to determine that from just the table without knowing the rules. Thus, to determine whether your table is in 2NF I would need the whole table and the business rules, including dependencies.

Except that you're actually referring to 1NF, not 2NF. R-tables are in 1NF by definition because they do not have the table equivalent of repeating groups -- multivalued columns. Had your table had a CHILDREN multivalued column, it would not be in 1NF and, thus, it would not be a R-table.

The table in your example is poorly designed, but it is in 1NF. This is a common error induced even by industry "experts". Here’s, for example, Joe Celko in a very old issue of DBMS magazine, which I had debunked in an article at that time.

"The relational model states that in order to be in 1NF, 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
  ename CHAR(15) NOT NULL,
  child1 CHAR(15),
  child2 CHAR(15),
    :       :
  childN CHAR(15));”

& Now

You have been contaminated with a common misconception in the industry: the confusion of "repeating group" with "repeated attribute". Moreover, the question is really about 1NF, not 2NF (beyond 1NF the normal form of a relation can only be determined with reference to the attribute dependencies defined by the conceptual model that the database is intended to represent).

According to the current understanding of the RDM, database relations are by definition in both 1NF and 5NF (tables only visualize database relations, they play no role in the RDM). Adherence to three core design principles produces 1NF+5NF databases, obviating normalization (to 1NF) and further normalization (to 5NF), which come up only as repair of poor designs (2NF-4NF).

A relation is not in 1NF (and, thus, not a database relation) if it has relation-valued attributes (RVA) defined on relation-valued domains (RVD), or "nested relations" -- the relational equivalent to hierarchic "repeating groups" (i.e., groups of values that repeat).

Repeated attributes (i.e., an attribute of the same type that repeats, here CHILD) are poor design, but do not violate 1NF. A CHILDREN RVA with relation values would: each relation value a set of tuples (groups of values) representing the properties of children that repeat for each employee; in which case EMPLOYEES would not be a database relation.

Further Reading

First Normal Form in Theory and Practice series

Simple Domains and Value Atomicity





No comments:

Post a Comment

View My Stats