Note: In "Setting Matters Straight" posts I debunk online pronouncements that involve fundamentals which I first post on LinkedIn. The purpose is to induce practitioners to test their foundation knowledge against our debunking, where we explain what is correct and what is fallacious. For in-depth treatments check out the POSTS and our PAPERS, LINKS and BOOKS (or organize one of our on-site/online SEMINARS, which can be customized to specific needs). Questions and comments are welcome here and on LinkedIn.

What's right/wrong about this database picture?

“Other than constraints on cardinality, business rules are not generally represented on data models of either kind. Even in the case of business data models, the models are supposed to represent fundamental structures, while business rules represent variable constraints.”

                                                                    --TDan.com

Fallacies

• Logical models are erroneously referred to as "data models".
• Conceptual models are erroneously referred to as "business data models".
• Logical models designed using the true RDM express all constraints necessary to represent all business rules of conceptual models, not just cardinality constraints.
• Conceptual models do specify structure -- relationships among properties, entities and groups thereof -- constraints are their formal database representation (not "variable constraints", whatever that means).

Fundamentals

A data model is used to formalize conceptual models of reality as logical models for database representation.

A conceptual model is a collection of business rules that jointly define a multigroup -- a collection of related groups of entities of a single type (i.e., that share the same properties and relationships). We have identified several types of rules that define a multigroup:

• 1st order properties (1OP) of entities;
• 2nd order properties (2OP) relationships among 1OPs;
• 3rd order properties (3OP) -- intragroup relationships;
• 4th order properties (4OP) -- intergroup relationships

and, thus, comprise a conceptual model.

Properties, entities and relationships at the conceptual level -- be they of/among entities or groups -- formalize with/as (are represented by) constraints. The RDM supports all the types of constraints
necessary to enforcee those rules:

• Domain constraints (represent property rules);
• Attribute constraints (represent property-in-context rules);
• Tuple constraints (represent entity rules);
• Multi-tuple constraints (represent group rules);
• Database constraints (represent multigroup rules)

The relational algebra (RA) integrates the intension of (i.e.  constraints on) database relations for semantic consistency of the database with the corresponding conceptual model.

Practitioners are blinded to all this by poor foundation knowledge, practices and tools in the industry. We outline here only the constraint implications.

First, conventional wisdom -- to the extent that it is even aware of RDM -- relies on a version thereof based on incorrect understanding. Among a plethora of deficiencies it does not require database relations to be in 5NF by design and relational operations do not constitute a proper relational algebra (RA): they lack 5NF closure, fail to integrate the intension of database relations (i.e., constraints) and to support multi-relation results.

Second, database design is poor and combines with RA flaws to produce semantic loss (erroneously referred to as "update anomalies"). For example, non-5NF relations that merge entities of multiple types involve complex dependencies that are not declared and, thus, even a true RDBMS with full constraint support would not guarantee semantic consistency.

Note that even with proper conceptual modeling and relational database design, available SQL database systems would not be able to guarantee the soundness and advantages of the RDM, because they fail to implement even the flawed industry version of RDM and violate relational requirements left and right.

Setting Matters Straight.

We revise the above statement as follows:

“Even though the RDM -- properly understood -- can represent all the business rules comprising conceptual models, in industry practice they are not all represented in logical models due to poor knowledge and understanding of the RDM. Conceptual models do represent structure -- relationships among properties, entities and groups thereof.”