Sunday, April 11, 2021

(TYFK) Relations, Tables, and Semantic Consistency

Note: Each "Test Your Foundation Knowledge" post presents one or more misconceptions about data fundamentals. To test your knowledge, first try to detect them, then proceed to read our debunking, reflecting the current understanding of the RDM, distinct from whatever has passed for it in the industry to date. If there isn't a match, you can review references -- reflecting the current understanding of the RDM, distinct from whatever has passed for it in the industry to date -- which explain and correct the misconceptions. You can acquire further knowledge by checking out our POSTS, BOOKS, PAPERS, LINKS (or, better, organize one of our on-site SEMINARS, which can be customized to specific needs).


“A relation, or table, in a relational database ... must have a set of columns or attributes, and it must have a set of rows to contain the data. A tuple (or row) can be a duplicate. In practice, a database might actually contain duplicate rows, but there should be practices in place to avoid this, such as the use of unique primary keys (next up). Given that a tuple cannot be a duplicate, it follows that a relation must contain at least one attribute (or column) that identifies each tuple (or row) uniquely. This is usually the primary key. This primary key cannot be duplicated.”

 

Misconceptions

 
  • A relation is not a table and, thus, has neither columns, nor rows (certainly not fields);
  • "Duplicate tuples" is a contradiction in terms -- a table with duplicate rows does not visualize a relation (i.e., is not a R-table) -- and a database with duplicated data is not relational;
  • Unlike a mathematical relation, there is no such thing as a database relation without a PK, which would be semantically inconsistent (i.e. it would not be a faithful representation of group of entities, which are distinguishable in the real world);
 

Fundamentals

 

  • A database relation:
- is a relationship among domains (sets of values) -- a subset of their cross-product -- a set of tuples (sets of values drawn from the domains) or, in other words, a set of sets from sets;
- has attributes, which are representations (1:1 mappings) of the domains in the relation;
- is semantically constrained to be consistent with (i.e., represent faithfully) the entity group in the conceptual model represented by the database), including by a PK constraint:
. domains represent properties;
. relations represent entity groups;
. attributes represent entity properties;
. tuples represent (facts about -- properties of) entities;
. some constraints represent relationships among properties, entities and groups (some properties are relationships);
  • Duplication would violate the RDM and mean semantic inconsistency with (inaccurate representation of) the group.
  • A R-table visualizes a relation on some physical medium -- it plays no part in the relational model.


Further Reading


What Relations Really Are and Why They Are Important

Understanding Relations series

What Is a Relational Database




Thursday, March 25, 2021

(OBG) Don't Confuse Levels of Representation Part 1

Note: To demonstrate the correctness and stability due to a sound theoretical foundation relative to the industry's fad-driven "cookbook" practices, I am re-publishing as "Oldies But Goodies" material from the old DBDebunk.com (2000-06), so that you can judge for yourself how well my arguments hold up and whether the industry has progressed beyond the misconceptions those arguments were intended to dispel. I may revise, break into parts, and/or add comments and/or references.

This is an email exchange with readers in response to my article Normalization and Performance: Never the Twain Shall Meet.

Friday, March 19, 2021

Data Sublanguages vs. Programming Languages

Revised 3/20/21

I recently came across a review of Edsger Dijkstra's work, where the following comment on a book he co-authored (referred to as D&S) raised my debunking antennae:

“... in general computer people seem to have a penchant for whipping up homebrew logics ... D&S is not the only example ... See E.F. Codd’s Relational Calculus, an obvious mess.”
--Maarten van Emden, A Bridge too Far: E.W. Dijkstra and Logic 

Having recently argued that "Codd was wrong" and "You're teaching [his] gospel" Betray Lack of Foundation Knowledge, my suspicion should hardly surprise. Besides, criticism of Dijkstra is a very tall order in itself, particularly in the context of disputes among logicians). As a reader asked, "What’s so obviously messy in Codd’s Relational Calculus?". Answer:

Friday, March 12, 2021

(TYFK) Tables Can Visualize -- But ARE NOT -- Relations!

Note: Each "Test Your Foundation Knowledge" post presents one or more misconceptions about data fundamentals. To test your knowledge, first try to detect them, then proceed to read our debunking, reflecting the current understanding of the RDM, distinct from whatever has passed for it in the industry to date. If there isn't a match, you can review references -- reflecting the current understanding of the RDM, distinct from whatever has passed for it in the industry to date -- which explain and correct the misconceptions. You can acquire further knowledge by checking out our POSTS, BOOKS, PAPERS, LINKS (or, better, organize one of our on-site SEMINARS, which can be customized to specific needs).


“A relation, or table, in a relational database ... must have a set of columns or attributes, and it must have a set of rows to contain the data. A tuple (or row) can be a duplicate. In practice, a database might actually contain duplicate rows, but there should be practices in place to avoid this, such as the use of unique primary keys (next up). Given that a tuple cannot be a duplicate, it follows that a relation must contain at least one attribute (or column) that identifies each tuple (or row) uniquely. This is usually the primary key. This primary key cannot be duplicated.”

Misconceptions


  • A relation is not a table and, thus, has neither fields, nor columns (which are not attributes) and rows;
  • "Duplicate tuples" is a contradiction in terms -- a table with duplicate rows does not visualize a relation (i.e., is not a R-table) -- and a database with duplicated data is not relational;
  • Without PKs a relation is not semantically consistent (i.e. it does not faithfully represent group of entities, which are distinguishable in the real world);


Fundamentals


  • A database relation

- is a relationship among domains (sets of values) -- a subset of their cross-product -- a set of tuples (sets of values drawn from the domains) or, in other words, a set of sets from sets;
- has attributes, which are representations (1:1 mappings) of the domains in the relation;
- is semantically constrained to be consistent with (i.e., represent faithfully) the entity group in the conceptual model it represents in the database.

  • A R-table visualizes a relation on some physical medium -- it plays no part in the relational model.
  • Absence of PKs is semantic inconsistency with (inaccurate representation of) reality and a violation of the RDM.



Further Reading

What Relations Really Are and Why They Are Important

Understanding Relations series

What Is a Relational Database

Duplicates: Stating the Same Fact More Than Once Does Not Make it Truer, Only Redundant





Wednesday, February 24, 2021

(OBG) Third Order Properties and Multi-Tuple Constraints: An Example

Note: To demonstrate the correctness and stability due to a sound theoretical foundation relative to the industry's fad-driven "cookbook" practices, I am re-publishing as "Oldies But Goodies" material from the old DBDebunk.com (2000-06), Judge for yourself how well my arguments hold up and whether the industry has progressed beyond the misconceptions those arguments were intended to dispel. I may revise, break into parts, and/or add comments and/or references. You can acquire foundation knowledge by checking out our POSTS, BOOKS, PAPERS, LINKS (or, even better, organize one of our on-site SEMINARS, which can be customized to specific needs).


As part of the new understanding of the RDM we posted articles -- one last week -- about the types of properties and relationships at the conceptual level that are enforced via semantic constraints at the logical database level. One category of relationships exist among all members of an entity group, which are collective third order properties (3OP) of the group, enforced via multi-tuple constraints. There are at least two kinds of 3OP relationships: entity uniqueness, enforced via PK constraints and aggregate restriction, enforced via aggregation constraints. Practitioners are familiar with -- even if they do not necessarily have a full understanding of -- the former, but not so much with the latter. It so happens that they were the subject of an exchange between a reader of the old dbdebunk and C.J. Date. It is worth re-visiting as an example and, with the benefit of hindsight, to add some comments on re-publication.

Friday, February 19, 2021

(TYFK) Semantics, Relations and the Missed Link: Constraints

Note: Each "Test Your Foundation Knowledge" post presents one or more misconceptions about data fundamentals. To test your knowledge, first try to detect them, then proceed to our debunking thereof. If there isn't a match, you can review references -- which reflect the current understanding of the RDM, distinct from whatever has passed for it in the industry to date -- which explain and correct the misconceptions. You can acquire further knowledge by checking out our POSTS, BOOKS, PAPERS and LINKS (or, better, organize one of our on-site SEMINARS, which can be customized to specific needs).

“[As a] set [a database relation] is a collection of similar or related things.”

--ArtfulSoftware.com


Can you tell what's wrong with this statement (hint: one word is wrong)? If not, it is because it is impossible without the old industry interpretation of the RDM.

Friday, February 12, 2021

(TYFK) What Is a Relational Database and Why Is It Important?

Note: Each "Test Your Foundation Knowledge" post presents one or more misconceptions about data fundamentals. To test your knowledge, first try to detect them, then proceed to read our debunking, which is based on the current understanding of the RDM, distinct from whatever has passed for it in the industry to date. If there isn't a match, you can acquire the knowledge by checking out our POSTS, BOOKS, PAPERS, LINKS (or, better, organize one of our on-site SEMINARS, which can be customized to specific needs).

“The most popular data model in DBMS is the Relational Model. It is more scientific a model than others. This model is based on first-order predicate logic and defines a table as an n-ary relation. The main highlights of this model are:
  • Data is stored in tables called relations.
  • Relations can be normalized.
  • In normalized relations, values saved are atomic values.
  • Each row in a relation contains a unique value.
  • Each column in a relation contains values from a same [sic] domain.”
--What is a relational database and why is it important, Quora.com

Saturday, February 6, 2021

(OBG) Cookbooks and Skyscrapers with Shack Foundations

Note: To demonstrate the correctness and stability due to a sound theoretical foundation relative to the industry's fad-driven "cookbook" practices, I am re-publishing as "Oldies But Goodies" material from the old DBDebunk.com (2000-06), so that you can judge for yourself how well my arguments hold up and whether the industry has progressed beyond the misconceptions those arguments were intended to dispel. I may revise, break into parts, and/or add comments and/or references.

 

Skyscrapers with Shack Foundations

(originally posted 06/04/2000)

 “Well, it's really a judgment call and I think a lot of experience comes into it. It's a little bit like building a shack. Say you want to build a skyscraper, and you started out building a shack and you just keep trying to add onto it. After a while you have this severe structural problem ... So there is a fallacy to the build-upon-a-simple structure approach. Sometimes you get up to three stories and you have to do some major structural changes, and I just accept that.”
--Wayne Ratliffe, developer of dBase
“Client Servers were a tremendous mistake. And we are sorry that we sold it to you. Instead of applications running on the desktop and data sitting on the server, everything will be Internet based. The only things running on the desktop will be a browser and a word processor. What people want is simple, inexpensive hardware that functions as a window on to the Net. The PC was ludicrously complex with stacks of manuals, helplines and IT support needed to make it function. Client server was supposed to alleviate this problem, but it was a step in the wrong direction. We are paying through the nose to be ignorant.”
--Larry Ellison, CEO, Oracle Corp.

Monday, February 1, 2021

Normalization -- Will They Ever Learn?

“To Normalize or not to Normalize? that really isn't a question. few things to consider:
Normalization is supposed to protect from data anomalies, but not prevent us from using data encapsulation is the magic trick that allows you to do what you want without breaking rules.what are your experiences with normalization?”
                                                                --LinkedIn


This is a question that at this time need -- and should -- not be asked anymore, and the fact that it still is is one confirmation -- among many -- that there is no progress in data management. According to the current understanding of the RDM:

  • Database relations are both normalized (in 1NF) and fully normalized (in 5NF) by definition, otherwise they are not relations and the relational algebra (RA) does not work;
  • Adherence to three database design principles produces 1NF and 5NF relational databases;
  • Consequently, there should not be such a thing as "doing" normalization (to 1NF) and further normalization (to 5NF) except to repair databases that are non-relational due to failure to adhere to the principles.


Note: The three design principles are fundamental to SST/FOPL foundation of the RDM, but were never understood even by relational proponents. I do not know what encapsulation has to do with this.

Saturday, January 23, 2021

"Codd was wrong" and "You're teaching the gospel" Betray Lack of Foundation Knowledge

Note: I have documented and debunked these misconceptions so many times that I will no longer reference them -- the reader motivated to gain genuine understanding should use the (1) blog labels (2) Blogger search (3) POSTS page to locate the relevant posts.

I have long claimed that a core problem in the industry is the vast majority of practitioners who use relational terminology, do not know/understand what it means, yet are convinced they do -- the less the understanding, the greater the convinction. A recent LinkedIn exchange provided -- as if it were needed -- yet another example. It was triggered by my comment:

“How many know today that a relation is by definition in 5NF, otherwise it's not a relation, the relational algebra has "anomalies" and all bets are off? IMO, none! If you need to "do" normalization, you did not design correctly, which means you don't understand the RDM.”
that prompted the following reaction:
“Is that really true? You construct a table and fill it full of garbage. It may not even be in 1NF, but is it not still a "relation" of columns, even if it's not a relation of rows or attributes? Codd had no real conception of syntax as separate from semantics, I don't think relational theory has a clear position on this. This is where Kimball and dimensional systems differ from Codd's relational, it made some effort (not a lot) to distinguish syntactic and semantic elements.”
--Joshua Stern

Saturday, January 16, 2021

(OBG) Missing Data Part 5: Many-valued Logics and NULL Part 2

Note: To demonstrate the correctness and stability due to a sound theoretical foundation relative to the industry's fad-driven "cookbook" practices, I am re-publishing as "Oldies But Goodies" material from the old DBDebunk.com (2000-06), so that you can judge for yourself how well my arguments hold up and whether the industry has progressed beyond the misconceptions those arguments were intended to dispel. I may revise, break into parts, and/or add comments and/or references.

In response to a LinkedIn thread where interest was expressed in a "NULL research" project, we re-publish as "Oldies But Goodies" a series of old exchanges with readers reacting to our The Final NULL in the Coffin: A Relational Solution to Missing Data: in Parts 1, 2, 3  we re-published a past exchange between myself and Hugh Darwen with a reader on the pros and cons of our solution vs. Hugh Darwen's "horizontal decomposition" NULLless solution, and in Part 4 my debunking of reactions to an article of mine on the subject which exhibit the confusion inevitably evoked by NULL.

We complete this series with an exchange with a reader who claimed that our criticism applies to SQL's specific implementations of NULL, not to the NULL concept itself. I and David McGoveran explained that the implementation flaws in SQL derive directly from the deeper problems endemic to many-valued logics

Saturday, January 9, 2021

(OBG) Missing Data Part 4: Many-valued Logics and NULL Part 1

Note: To demonstrate the correctness and stability of a sound theoretical foundation relative to the industry's fad-driven "cookbook" practices, I am re-publishing as "Oldies But Goodies" material from the old DBDebunk.com (2000-06), so that you can judge for yourself how well my arguments hold up and whether the industry has progressed beyond the misconceptions those arguments were intended to dispel. I may break long pieces into multiple posts, revise, and/or add comments and references.

In response to a LinkedIn exchange we continue the series about missing data, NULL and the RDM. In Parts 1,2 and 3 we re-published a past exchange between myself and Hugh Darwen on the pros and cons of our relational solution to missing data vs. Hugh's "horizontal decomposition".

Here we re-publish my debunking of reactions to an article of mine exhibiting the common confusions evoked by NULL.

Friday, January 1, 2021

(OBG) Database Design and Guaranteed Correctness Part 2

Note: This is a re-write of an earlier post (which now links here), to bring it into line with the current understanding of the RDM derived from McGoveran formalization and interpretation of Codd's work[1]. Reference [9] is also an important re-write and is recommended pre-requisite for this post.

Continued from Part 1

 “The term database design can be used to describe many different parts of the design of an overall database system. Principally, and most correctly, it can be thought of as the logical design of the base data structures used to store the data. In the relational model these are the tables and views ... However, the term database design could also be used to apply to the overall process of designing, not just the base data structures, but also the forms and queries used as part of the overall database application within the database management system(DBMS). The process of doing database design generally consists of a number of steps which will be carried out by the database designer. Usually, the designer must:

  • Determine the data to be stored in the database.
  • Determine the relationships between the different data elements.
  • Superimpose a logical structure upon the data on the basis of these relationships.
Within the relational model the final step above can generally be broken down into two further steps, that of determining the grouping of information within the system, generally determining what are the basic objects about which information is being stored, and then determining the relationships between these groups of information, or objects.”
--What is a Relational Database, Quora.com
There is, typically, much vagueness and confusion here and instead of debunking it makes more sense to provide a rigorous description of what database design really is: formalization of a conceptual model -- expressed as business rules -- as a logical model for representation in the database using a formal data model. If the data model is the RDM, the logical model consists of relations constrained for semantic consistency with the conceptual mode, the constraints being formalizations of the business rules.

View My Stats