Monday, February 26, 2018

Relationships and the Relational Model

Note: This is a rewrite of several older posts (which now link here) to bring them in line with McGoveran's formalization and interpretation  of Codd's true RDM [1]. 

Revised  5/4/18.
"William Kent confesses (in my words) that he can not distinguish between "relationships" and "attributes" ... the later might be completely redundant ... the notion of an attribute presumes a relationship, so we must define that first ... All of this is handled explicitly and correctly in ORM -- we model objects (each one appears only once in a data model diagram) and relationships. There are no attributes ... an attribute is an object playing a role in a relationship with another object."
"... we are not modeling objects/entities/attribute ... at all in the relational model, [but] a bunch of relationships ... hence perhaps Codd was correct in calling it a "relation", a bunch of relationships ... Interesting that most people think of relationships as being the distinguishing characteristic of a relational model and it is not ... [it] has no relationships since Codd decreed that all relationships must be represented by foreign keys, which are exactly the same as "attributes ..."
"... isn't it funny, that the term relation is implicitly mapped (in our minds) to a table of a database? If (loosely speaking) a relationship in our conventional data modeling is represented by a foreign key in a table (and combining both points together) -- should a table (relation) consists only of foreign keys? ... What [other] type(s) of relationships can be explicitly and formally defined in a relational data model? Of course there are many other relationships which can be inferred, such as between an attribute and an entity identifier. Please give me a precise reference to where Codd spoke of relationships [differently than i]n his 1985 piece published in ComputerWorld, [where] he said that the only way to represent a relationship (between relations) was through explicitly stored values (i.e., attributes, foreign keys)."

The lack of foundation knowledge and inability to reason never ceases to amaze me. So, in the RDM "we model a bunch of relationships", but "it has no relationships" because "all relationships must be represented by foreign keys"?

It says something about the state of knowledge in the industry [2] that five decades since the RDM many data professionals still (1) do not know that relational derives not from "relationships between tables", but
from mathematical relations, which contributes to the misconception that (2) "the relational model does not have any other relationships".


I have been using the proceeds from my monthly blog @AllAnalytics to maintain DBDebunk and keep it free. Unfortunately, AllAnalytics has been discontinued. I appeal to my readers, particularly regular ones: If you deem this site worthy of continuing, please support its upkeep. A regular monthly contribution will ensure this unique material unavailable anywhere else will continue to be free. A generous reader has offered to match all contributions, so let's take advantage of his generosity. Purchasing my papers and books will also help. Thank you.



  • 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 the FUNDAMENTALS page, see the ABOUT page. The 2017 and 2016 posts, including earlier posts rewritten in 2017 are relabeled. As other older posts are rewritten, they will also be relabeled, but in the meantime, use Blogger search for them. 
  • Following the discontinuation of AllAnalytics, the links to my columns there no longer work. I moved the 2017 columns to dbdebunk and, time permitting, may gradually move all of them. Within the columns, only the links to sources external to AllAnalytics work.  
  • The Dystopia of Western Decadence, the Only Acceptable Racism, the Myth of the “Palestinian Nation" as pure Anti-semitism and the West’s funding of Terror”

Properties and Attributes: Conceptual-logical Conflation

Object Role Modeling (ORM) is an extended version of the Nissjen Information Analysis Method (NIAM), a conceptual modeling approach rooted in linguistics used to produce conceptual models of reality. Confusing it with the RDM, which is a formal data model used to formalize conceptual models for database representation is conceptual-logical conflation (CLC) [3]. If you want proof, consider the title of the NIAM book: CONCEPTUAL SCHEMA AND RELATIONAL DATABASE DESIGN  (a classic recommended on the BOOKS page ). It is to avoid such confusions that I recommend the three-fold terminology of conceptual modeling, logical design, and physical implementation [4].

At the conceptual level, ORM objects have properties. Relation attributes represent them formally in the database at the logical level. So, if there is any "difficulty", it can only be in distinguishing between relationships and properties, not attributes.

Note: When Kent's DATA AND REALITY (also a recommended classic) was published, the RDM had only domains. Attributes were introduced later, so he can't be faulted for using the term in the property sense. We prefer, with ORM, objects to entities, but not in the OO sense.

Conceptually, object groups, properties and relationships are not absolute and inherent, but "in the eyes of the users", so to speak: the modeler chooses them based on perceptions of user application needs. For example, for some applications budget is a property of department objects, for others an object with properties of its own; both perceptions may make sense. All that is required is that choices are explicit, clear and, once made, use is consistent. 

Relationships and Constraints

When we use the RDM to design a logical database corresponding to a conceptual model:
  • Object groups map to relations;
  • Individual object properties or names[5] map to attributes;
  • Relationships between properties and among objects map to constraints.

Constraints are formalizations of the informal business rules in the conceptual model that specify the relationships. They are expressions in a relational data language that a RDBMS can enforce on single and multiple relations to ensure they are consistent with the object groups they represent (i.e., the representation of the object groups, including all relationships, is accurate)[6].

One of the many common and entrenched misconceptions[2] is that the only relationships that the RDM supports are those represented by referential constraints, namely, the relationships between object groups. So much so, that relational is thought to come from "relationships between tables". But relations are all about the within-group relationships  (1) between two or more object properties and (2) among all objects, and are, in fact, are defined by them. And those relationships are represented by tuple and multituple constraints just like relationships between object groups are represented by referential multirelation constraints (known as database constraints).

For example, object uniqueness is a relationship among all objects that they must satisfy collectively to be group members. It is supported in the RDM by multituple primary key (PK) constraints. So is the "relationship between an attribute and an object identifier", known as a functional dependency (FD), supported in the RDM by multituple FD constraints (note the CLC: object identifier is conceptual, attribute is logical). FD relationships are not "inferred"; rather, because -- notwithstanding conventional wisdom -- a relation is by definition in 5NF (not just in 1NF)[7], a RDBMS expects 5NF relations (i.e., the only dependency that holds in them is a FD of the non-key attributes on the PK[8]) and does not require explicit declaration of FD constraints: by enforcing the PK constraints, the FD constraints are also enforced implicitly (why?).

Data professionals miss the relationships because they lack foundation knowledge and think tables rather than relations. But tables are only "pictures" of relations on some physical medium (screen, paper). They distract with the arrangement of columns and rows on the medium (which relations do not have) and do not display constraints (which relations do). Thus, they obscure relationship[9], which is how they are missed.

Note: While we can speak of relationships at the logical level, it is preferable to stick to constraints to avoid CLC. For the important distinction between names and properties, see[5].


[1] McGoveran, D., LOGIC FOR SERIOUS DATABASE FOLK, forthcoming.


[3] Pascal, F., The Conceptual-Logical Conflation and the Logical-Physical Confusion.

[4] Pascal, F., Conceptual Modeling, Logical Design, and Physical Implementation.

[5] Pascal, F., The Key to Relational Keys: A New Understanding.

[6] Pascal, F., What Meaning Means: Business Rules, Predicates, Integrity Constraints and Database Consistency.

[7] Pascal, F., What Relations Really Are and Why They Are Important

[8] Pascal, F., The Costly Illusion: Normalization, Integrity and Performance.

Note: I will not publish or respond to anonymous comments. If you have something to say, stand behind it. Otherwise don't bother, it'll be ignored.

Do you like this post? Please link back to this article by copying one of the codes below.

URL: HTML link code: BB (forum) link code:

No comments:

Post a Comment