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 (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.


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.

-12/24/20: Added 2021 to the
POSTS page

-12/26/20: Added “Mathematics, machine learning and Wittgenstein 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 the 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 columns there no longer work. I moved only the 2017 columns to dbdebunk, within which only links to sources external to AllAnalytics may work or not.

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.
- The PostWest blog for monthly samples of global Antisemitism – the only universally acceptable hatred left – as the (traditional) response to the existential crisis of decadence and decline of Western  civilization (including the US).
- @ThePostWest on Twitter where I comment on global #Antisemitism/#AntiZionism and the Arab-Israeli conflict.


On Table Cardinality Constraints

(originally posted on 06/18/2001)

“I recently came across an interesting question posted on an Oracle server. The poster asked for a way to limit the number of rows that could be inserted into a table. He did not explain intended usage of this constraint. My first reaction was that such requirement violates E. F. Codd rules of the relational model, by storing information outside of the tuple of a relation. A potential solution to this problem would be to store the count of records in another relation and create a constraint on the acceptable range of the values. This of course leads to redundancy. The solution proposed by the respondents was to implement a trigger that would raise an exception when the maximum number of rows was exceeded. While this solves his immediate problem, it has the "feel" of a workaround. For my taste, this solution takes part of the application and codes it within the DBMS, but it's still application-level integrity constraint, rather than an integrity constraint enforced by the model. I would be very interested in your thoughts on this problem.”

In fact, I had encountered this very constraint in the real world. The mayor of DC was allowed up to 100 political appointees serving "at his pleasure". Enforcing this business rule required a cardinality constraint in the logical database and guess what: because none was enforced, there were often more than 100 tuples in the relation representing the group of appointees!”

C.J. Date responded:

“This email asks for a way to limit "the number of rows that could be inserted into a table". First of all, I take the requirement to be not a limit on the number of rows that can be inserted in any given INSERT operation, but rather a limit on the cardinality of the table at all times. This latter is a perfectly reasonable requirement, by the way: For example, imagine we have a table representing the justices on the US Supreme Court. There's no violation of the Information Principle (IP) involved. (There have certainly been gross violations of a variety of other principles recently in connection with the body I mention, but this isn't the time or place for that discussion.)

The reader suggests one thing we could do is "store the count of records [sic] in another relation and create a constraint [involving that count]". Well, yes, you could do that; in fact, it's probably a good idea, because then you could change the constraint by simply updating that other relation. Alternatively, you could embed the count directly in the constraint, thus:
CONSTRAINT xyz COUNT (SUP_COURT_REL) = 9; (My own syntax, not SQL.)
Pace reader's email, there's no redundancy here.

Aside: Please don't use a trigger solution. Triggers are a cop-out, at least for the purpose at hand; they basically mean you're writing detailed code that the DBMS vendor really ought to have written for you in the first place. There are numerous other problems with triggers too -- "I have a truly marvelous demonstration of this proposition which this margin is too narrow to contain." JT says the trigger solution "has the feel of a workaround." Quite right. But he also says it "takes part of the application and codes it within the DBMS." I think the situation is almost one hundred percent the opposite! [Ed. Note: What Chris means is that users must write code for triggers that should otherwise be written by the vendor and built into the DBMS]. And he says it's an "application-level integrity constraint" ... I don't understand this one, either; I would have said we were talking about what should be a " database-level" constraint, not an "application-level" one.”

Comments on re-publication

  • We no longer tolerate the table-columns-rows terminology -- as we explained so many times, it inhibits understanding -- and urge relation-attributes-tuples.
  • Not only don't they violate relational principles, but constraints -- domain, relation and database (i.e., multi-relation) -- comprise one of the three integral components of the RDM -- integrity --  that guarantees semantic consistency (i.e., faithful database representation of the intended conceptual model).
  • Relation constraints -- attribute, tuple and multi-tuple -- constrain relations to represent accurately the entity groups they are supposed to represent).
  • A cardinality constraint is a multi-tuple constraint that ensures consistency with a relationship among all group members that is a collective 3OP of the group (e.g., constrains an APPOINTEES relation to not more than 100 tuples).
  • Constraints are not recorded as user relations, but in the system relations of the database catalog (which is why a truly relational database is self-documenting upon user request).


Relationships and the RDM series

Levels of Representation: Relationships, Rules, Relations and Constraints

(TYFK) What Is A Database Relationship

No comments:

Post a Comment

View My Stats