Sunday, May 20, 2018

Relationships and the RDM Part 2: Integrity Constraints





Note: This is the second of a three-part rewrite of two earlier posts, to bring them into line with McGoveran's formalization and re-interpretation of Codd's true RDM[1]. For the in-depth treatment see [2,3].


Revised: 5/28/18.

(Continued from Part 1)

In Part 1 I referred to our approach to conceptual modeling[2] inspired by McGoveran's recent work[1] whereby a conceptual model includes the identified types of objects -- entities, groups, subgroups, and the multigroup -- of user interest, and the business rules (BR) that specify their defining properties, some of which are relationships that are missing from the conventional entity-relationship modeling (E/RM)[4], namely:
  • Relationships within groups:
- among properties and entities;
- among properties.
among entity members:
- uniqueness;
- aggregate;
- among group and subgroup members (ESS).
  • Relationships across-groups:
- many-to-many (M:N);
- many-to-one (M:1);
- one-to-one (1:1).

------------------------------------------------------------------------------------------------------------------

SUPPORT THIS SITE

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.

NEW PUBLICATIONS


NEW: SOCIAL MEDIA
 
The social media pages are now as follows. Please make a note of it and disseminate.

Twitter:


Facebook:


The DBDebunk pages will contain links to updates to this site, as well as To Laugh or Cry? and What's Wrong with This Picture, which I am bringing back.

The PostWest pages will contain links to evidence for, and my take on Evidence on Dystopian Western Decadence, The Only Acceptable Racism Left, and The Weaponized Myth of a "Palestinian Nation".

HOUSEKEEPING

  • 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 objects and BRs must be formalized symbolically, using a formal data model, for database representation. If the RDM is used for formalization:
  • Properties map to domains;
  • Entity groups map to relations:
  • Entity properties and names map to attributes;
  • (Facts about) entity members map to tuples;
  • Relationships map to constraints.
(for the difference between properties and names, see [5]).

There are three categories of BRs that specify defining properties that arise from relationships and map to constraints:

  • Entity rules;
  • Multi-entity rules;
  • Multigroup rules.

For BR formulation see [2]. 


Integrity Constraints


Using a formal data language based on first order predicate logic (FOPL), informal BRs are formalized as symbolic predicates that are enforceable by a RDBMS as constraints on relations and the database. Constraints constrain the relations and the database as a whole to be consistent with the BRs (i.e., to represent the model, including relationships, accurately) and, thus, ensure data integrity. The BRs that specify defining properties arising from all the relationships above are expressible in FOPL and, therefore, enforcible as constraints by a true RDBMS (though not necessarily by SQL implementations):

  • Properties-entity relationships formalize as general dependency (GD) constraints;
  • Uniqueness relationships formalize as uniqueness (PK) constraints;
  • M:N, M:1 and 1:1 relationships formalize as relations and referential (FK) constraints;
  • ESS relationships by database design and FK constraints;
  • The constraints corresponding to property relationships and aggregate constraints do not have specific names.
(for details and BR formalization as constraints see [3].)

Note: We mentioned in Part 1 that the conjunction of PK and GD constraints are equivalent to a functional dependency (FD) constraint. Relations are in fifth normal form (5NF) by definition, a formal way of saying that that they represent entity groups, which have members of a single type by definition. Consequently, there is no need to declare and enforce FD constraints -- they are implicitly enforced via the PK and GD constraints. This is not the case with denormalized tables in SQL, which are not relations and require explicit declaration and enforcement of FD constraints. This is seldom done, which puts database consistency at risk[6].

We started with the quotes in Part 1 that show that among the many misconceptions about the RDM[7], one of the most common and entrenched is that the only relationships it supports, if any, are among entity members of different groups (i.e., those represented in relational databases by referential constraints). We have shown this to be false, but in the absence of foundation knowledge, practitioners are unfamiliar with the various types of relationships, and unaware of the constraints' correspondence to them. SQL implementations are confused with RDBMSs, but have weak support of relational constraints, reinforcing the misconception.

We are now in a position to debunk the comments presented in Part 1.

(Continued in Part 3)

 

Follow: @DBDEBUNK @THEPOSTWEST



References

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

[2] Pascal, F., Conceptual Modeling for Database Design: A New Perspective, forthcoming.

[3] Pascal, F., Logical Database Design: Formalizing the Informal, forthcoming.

[4] Pascal, F., Data Model: The RDM Is, the E/RM Isn't.

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

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

[7] Pascal, F., THE DBDEBUNK GUIDE TO MISCONCEPTIONS ABOUT DATA FUNDAMENTALS.


No comments:

Post a Comment