Sunday, April 16, 2023



Note: This is a multi-part complete re-write of a previous series which, when completed, is intended to replace it.

In Part 1 we attributed the fallacy that the RDM can express only one type of relationship -- between relations, using FKs -- to practitioners being unaware of the adaptation of math relations to database management and missing the additional features of database relations. We documented the differences in features between math and database relations (see the table in Part 1) and intimated that some of the additional features express relationships other than those between relations using FKs (which we leave out in this discussion).

In this Part 2 we identify the c-relationships and use a simple conceptual model (CM) of six entity groups:

Customers (cID, cname, FICO, discount)
Products (pID, pname, price)
Salesmen (sID, sname, sales, salary, commission)
Orders (oID, pID, cID, sID, date, amount)
Order Items (oID, iID, pID, quantity)

to illustrate them (to recall, we prepend 'relationship' with c- and l- when we use the term at the conceptual and logical levels, respectively).


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.



03/31 I Left Ceausescu's Romania for AI Algorithms??? At Least Him I Understood!!



04/03 Added First OrderLogic to LINKS page

04/03 Added Mathematical Logic - Reasoning in First Order Logic to LINKS page

03/26 Added Modeling of Integrity Constraints Dependencies to LINKS page

03/14 Added Russell’s On Denoting to LINKS page

03/14 Added Russell’s Paradox to LINKS page.


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.

Conceptual Modeling

“... singular names, property terms and class terms correspond to common sense concepts of individual beings, traits or activities of individual beings and sets of beings who share some common traits or activities … This three-fold distinction is an important part of our Western intellectual heritage and is deeply embedded not only in our thought habits, but also in our language. The distinction between properties and classes, however, is somewhat artificial and tenuous, since every property defines a class -- namely, the set of individuals possessing that property—whereas every class is a class simply by virtue of the fact that its members have common defining properties.” --Olson, R., MEANING AND ARGUMENT: ELEMENTS OF LOGIC
Humans are culturally and linguistically conditioned to perceive and organize reality as entities (individual beings), properties (traits and activities) and groups of entities (sets of beings). In our temporarily mixed approach to conceptual modeling, a conceptual model (CM) organizes reality as a multigroup -- a group of related groups of entities, each group having entities of a single type (i.e., that share common properties):

  • A collection of individual properties defines an entity type; entities sharing those defining properties -- to which refer as first order properties (1OP) -- are instances of that type (i.e., collections of property values).
  • Entities are primary objects, 1OPa are consequential and have an existence (have values) only in association with an entity of some type.
  • Entities are distinguishable (otherwise we would be unable to tell them apart) by one or more of the defining 1OPs; for convenience, entities are assigned a unique name as a shorthand for the identifying 1OPs (properties describe entities, names denote them, for which reason the name can stand-in for the entity it names.

For example, the cname, FICO score and discount 1OPs define a customer entity type, cID being the unique name assigned to customer entities (instances) of that type (entities are primitive, groups are compound objects).

Note: Conceptual modeling makes an ontological commitment. The industry has adopted an Object-properties Modeling (OpM) approach that assumes the Ontological Commitment to Objects (OCO) and the RDM and Codd grounded the RDM in it. We adopt a Properties-object Modeling (PoM) that assumes an Ontological Commitment to Properties (OCP), which has major implications for the RDM and database management. Because McGoveran's work is still in progress, we "cheat" in the first version of this paper and mix some features of the PoM with assuming the OCO: even though entities are defined by properties (PoM), they are primary objects and properties are consequential (for the differences see Understanding Conceptual vs. Data Modeling series). We will update this paper when McGoveran's work is completed.

In this conceptualization, in which entities are primitive and groups and the multigroup are compound objects,  c-relationships can exist:

  • Intra-group between:

- properties and entities;
- entities;
- properties;

  • Inter-group between:

- entities
- groups

Note: We exclude inter-group c-relationships from this discussion, as their expression in RDM is acknowledged (see:  Foreign Keys series) and we focus on intra-group c-relationships.

Whether a c-relationship is deemed a relationship or a property is contextual: a c-relationship in one context may be deemed a property in another:

  • A c-relationship between 1OPs is an (indirect) individual property of entities, to which  we refer as a second order properties (2OP);
  • A c-relationship between entities within a group is a collective property of the group, to which we refer as a third order property (3OP);
  • A relationship between groups is a collective property of the multigroup, to which we refer as fourth order properties (4OP).

Note: We suspect this contextuality is a source of confusion wrt to properties and relationships.

“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.”

Properties-Entities Relationships


Under OCO properties exist (have values) only in association with an entity of some type -- defining 1OPs are dependent on the entities. Because the name denotes an entity, we can say c-relationships of dependency exist between every 1OP and the name. For the purposes of this version of the paper this is just another way of saying that entities have those properties.

Dependencies of 1OPs on the entities are c-relationships within each group between every 1OP and name of each entity it is associated with. As a relationship between all group members, this is a collective 2OP of the group.

For example, the 1OPs sname, sales, salary and commission are dependent on salesmen entities.

Properties Relationships

C-relationships may exist between 1OPs.

For example, a policy of granting heavier discounts to customers with higher FICO scores establishes a relationship between the two customer 1OPs; or paying higher salaries and commissions to salesmen with higher sales establishes two such 1OP relationships.

C-relationships between 1OPs are indirect individual 2OPs of entities.

Entities Relationships

Entities Uniqueness

Every entity is unique by virtue of its identifying 1OP(s) and name.

Entity uniqueness is a c-relationship between all entities in a group and, thus, a collective 3OP of the group.

For example, the 1OPs pID, cID, sID and date uniquely identify every entity within the orders group and oID is a name assigned as a shorthand for these identifying 1OPs.

Functional Dependencies

If a name is properly assigned to entities of the same type, there are functional dependency (FD) c-relationships between every identifying 1OP and the name: for every name value there is exactly one value of every identifying 1OP.

A FD is a c-relationship between all entities within a group and, thus, is a collective 3OP of the group.

In the orders example, the pID, cID, sID and date 1OPs are functionally dependent on oID.

Note very carefully the following (which the reader can try to ascertain by her/himself:

In a group of entities of a single type the conjunction of name uniqueness and simple dependencies of every 1OP on the name is equivalent to the FDs of every defining 1OP on the name.

As we shall see, this is very advantageous for relational database management.

Aggregation Relationships

Consider policies to limit the total customer balance, or the total number of salesmen employed to certain maximums. They establish c-relationships between all customers or salesmen, respectively, that they must satisfy collectively as a group.

Aggregation c-relationships between all entities in a group are collective 3OPs of the group.

Entity Supertype-subtypes Relationships

A meaning criterion (MC) is a property that only some, but not all group members have. An MC partitions a group into subgroups -- one whose members have the MC and one whose members don't (a group that is not partitionable is primitive). An entity supertype-subtypes (ESS) c-relationship exists between group and subgroup members, the latter being a subtype of the former. The subgroup is defined by the conjunction of the group defining 1OPs and the MC (see Meaning Criteria and Entity Supertype-Subtypes Relationships.) 

For example, suppose some, but not all salesmen earn commissions. An ESS c-relationships exists between the salesmen who do earn both (supertype) and those who don't (subtype).

It is useful to view intra-group c-relationships/properties as a hierarchy of criteria for group membership that entities must satisfy to qualify. For example, to qualify as customers, entities must share individually the 1OPs and 2OPs and collectively the 3OPs. Similarly, for a group to qualify for multigroup membership it must share in the inter-group relationships (i.e., share in the 40Ps of the multigroup.)

To formalize conceptual models as logical models for database representation, a data model must express all the c-relationships, but as we have seen, without being able to even specify them, many in the industry claim it is unable to express the intra-group c-relationships.

Of that, in Part 3.





No comments:

Post a Comment

View My Stats