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.
------------------------------------------------------------------------------------------------------------------
SUPPORT THIS SITE
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.
LATEST UPDATES
-12/24/20: Added 2021 to the POSTS page
-12/26/20: Added “Mathematics, machine learning and Wittgenstein to LINKS page
LATEST PUBLICATIONS (order from PAPERS and BOOKS pages)
- 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).
USING THIS SITE
- 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.
SOCIAL MEDIA
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:Pace reader's email, there's no redundancy here.CONSTRAINT xyz COUNT (SUP_COURT_REL) = 9; (My own syntax, not SQL.)
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).
References
Relationships and the RDM series
Levels of Representation: Relationships, Rules, Relations and Constraints
(TYFK) What Is A Database Relationship
No comments:
Post a Comment