Note: In "Setting Matters Straight" posts I debunk online pronouncements that involve fundamentals which I first post on LinkedIn. The purpose is to induce practitioners to test their foundation knowledge against our debunking, where we explain what is correct and what is fallacious. For in-depth treatments check out the POSTS and our PAPERS, LINKS and BOOKS (or organize one of our on-site/online SEMINARS, which can be customized to specific needs). Questions and comments are welcome here and on LinkedIn.

(Continued from Part 1)

What's right/wrong about this database picture?

“So, what is this theory of normal forms? It deals with the mathematical construct of relations (which are a little bit different from relational database tables). The normalization process consists of modifying the design through different stages, going from an unnormalized set of relations (tables), to the first normal form, then to the second normal form, and then to the third normal form.”

--Vertabelo.com

Misconceptions

• All database relations are, mathematically, relations, but not all mathematical relations are database relations.
• The tabular structure play practically no role in RDM.
• In practice there is no normalization (to 1NF) and there should not be further normalization (to 5NF).
• Further normalization does not go from 2NF sequentially through 3NF and 4NF to 5NF.
  

Fundamentals

Mathematical relations are a special kind of sets -- abstractions that have no meaning (i.e., they represent nothing in the real world). Database relations are intended to represent entity groups defined by shared entity properties (some of which are relationships) and, thus, are semantically constrained: semantic constraints ensure that database relations are consistent with (i.e., faithfully represent) the properties and relationships defining the entity groups they correspond to.

According to a McGoveran conjecture, if database design adheres to three core principles (i.e., each database relation represents a group of entities of a single type), then relations are in both 1NF and 5NF and, thus, neither normalization (to 1NF), nor further normalization (to 5NF) are necessary.

In a 5NF relation the only dependencies that hold are functional dependencies (FD) of each of the non-key attributes on the primary key (PK) (i.e., for each PK value there is exactly one value of every non-key attribute). Then the only constraint to be declared to and enforced by the DBMS is the uniqueness constraint on the PK attribute.

If, on the other hand, the design principles are violated (i.e., multiple entity groups  are "bundled" into single relations), additional dependencies on multiple keys hold and, thus, additional additional constraints must be declared to and enforced by the DBMS, unnecessarily increasing the complexity of the design, structure and understanding of the database, as well as of query expressions and their results. This is why in RDM database relations are in 5NF by definition (i.e., they must be so designed), otherwise they are mathematically relations, but not database relations and all bets are off.

There are three ways to violate design principles (i.e., to bundle groups) that yield relations in 2NF, 3NF and 4NF (we ignore the 1NF because nobody designs non-1NF relations anymore, which obviates normalization). Note very carefully that, industry fallacy notwithstanding, it is not the case that database design starts with 2NF relations and proceeds with further normalization stepwise -- through 3NF and 4NF -- to 5NF! Rather, you design 5NF relations and no further normalization is necessary. If and only if you encounter poorly designed relations in one of the lower NFs, you further normalize them from either 2NF, 3NF, or 4NF directly to 5NF in one step.

Note: This stepwise fallacy is one of the main reason practitioners fail to understand  further normalization and get stuck trying to perform it (example: DATABASE DESIGN: THE STATE OF KNOWLEDGE IN THE INDUSTRY). My paper makes this clear: it does not take a 2NF example through all the "intermedia ry" NFs, but rather three 2NF, 3NF, and 4NF examples to 5NF.

As a set, a database relation that represents a group of entities of a single type, has an intension, the definition of the type of object the entities are (the shared properties that qualify them as set members); and an extension, the member entities that satisfy the definition (i.e., share those properties). The extension, but not the intension, can be visualized on physical media as the body of a table. Without the intension -- the constraints -- a table is an incomplete description of a database relation: RA operations, correctly defined, are not applicable to tables.

Setting Matters Straight

We revise the above comment as follows:

The theory of normal forms deals with the mathematical construct of relations (different from tables, the body of which only visualizes the extension of relations) as applied to database management. Database relations are in 5NF by definition. Normalization to 1NF and further normalization repair relations in any of the four different states of poor design (1NF-4NF) directly to 5NF.

(Continued in Part 3)