Follow @DBDebunk Follow @ThePostWest

 
Per Part 1, meaning is captured during conceptual modeling as information about objects of interest, specifically their properties (some of which are relationships), specified in business rules (BR). Because they are expressed informally in natural language, objects and BRs must be formalized into computable form. Data modeling (we prefer logical database design) uses a formal data model to formalize informal conceptual models as formal logical models for database representation: it assigns the meaning in the former to symbols and expressions in the latter[2]. Using the RDM:

• Objects -- entities, entity groups, and multigroups -- formalize as tuples, relations, and databases, respectively;
• Properties formalize as domains, and when associated with entities of specific types, as attributes;
• Group and multigroup properties -- relationships among entities, and among groups[3] -- formalize as constraints on and among relations enforceable by the DBMS.

------------------------------------------------------------------------------------------------------------------ 
SUPPORT THIS SITE

Up to 2018, DBDebunk was maintained and kept free with the proceeds from my @AllAnalitics column. In 2018 that website was discontinued. If you deem this this content, not available anywhere else, useful, particularly if you are a regular reader, please ensure its continuation and free availability by supporting it with as much as you can afford via purchases of publications, or donations. Thank you.

LATEST PUBLICATIONS

 
SOCIAL MEDIA 

I deleted my Facebook account. You can follow me on Twitter:

• @dbdebunk: will contain links to new posts to this site, as well as To Laugh or Cry? and What's Wrong with This Picture, which I am bringing back.  
• The PostWest: will contain evidence for, and my take on the spike in Anti-semitism that usually accompanies existential crises. The current one is due to the decadent decline of the West and the corresponding breakdown of the world order.

 
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. 

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

Practitioners may be familiar with two types of BRs (see below), in part because SQL provides syntactic shorthands for their corresponding primary (PK) and foreign key (FK) constraints. SQL has CHECK syntax for "arbitrary" constraints, but SQL DBMSs are not true RDBMSs, and few practitioners are familiar with  other BRs that formalize as relational constraints and are expressible in SQL, and do not know to formulate them! Consequently, in practice, conceptual models are incomplete, SQL databases poorly designed, constrained, and interpreted, with loss of relational advantages, foremost among them system-guaranteed logical validity and semantic correctness[4], and all deficiencies absurdly blamed on the RDM[5].

Note: In "relationally formalizable" we include BRs for which the corresponding constraints are not definable in FOPL (e.g., group 3OPs below) , and so are not implementable in relational algebra (RA) (e.g., They are primitives implemented in a computationally complete language (CCL), and invoked in a relational data language[3].
 
We use a simple example to acquaint you with the types of BRs that formalize as relational constraints enforceable by a true RDBMS. Their formulation in natural language is beyond the scope of this discussion, but  test your foundation knowledge: try your hand at it, figure out the corresponding constraints, check if they are expressible in the SQL dialect of your DBMS.

Note: In fact, few of the supported constraints are RDM inspired (e.g., Protect Your Data with PostgreSQL Constraints).

Objects, Properties, and BRs

An entity group is a collection of entities that:

• Are of the same type (i.e., share required entity properties);
• Collectively have required group properties; and,
• Share a designation property (see below).

A multigroup is a collection of groups that jointly have required multigroup properties.

Required properties defining properties.

Otherwise put, the criterion for membership of entities in a group is (1) sharing the entity PICs, and (2) collectively having group properties, and (3) sharing the designation property that define the group; the criterion for membership of groups in a multigroup is jointly having the multigroup defining properties.

Accordingly, there are four categories of BRs.

A Simple Example

An entity type is a named collection of properties associated with, and shared by entities of that type.

For example: 

• Customer is a name assigned to the collection of properties <FICO score, customer balance, state, gender, tax status> associated with, and shared by entities of type customer.
• Order is a name assigned to the collection of properties <order date, order amount> associated with, and shared by entities of type customer.

Every entity of a type is a named collection of specific values of the shared properties.

• Customer name and customer ID are names assigned to entities of type customer;
• Order is a name assigned to entities of type order.

Note: We distinguish names (which reference objects), from properties (which describe them)[6]. For the relationship between entities of distinct types see multigroup 4OPs rules below.

Property Rules

We distinguish between independently existing properties, which are applicable in multiple contexts (i.e., associable with entities of multiple types), and properties in context, which are properties applied in a context (i.e., associated with, and shared by entities of a type).

For example, amount{US$0.00-10,000,000.00} is a property applicable in the customer and order contexts; customer balance and order amount are PiCs derived from amount.

PiCs have the same value range as, or a range narrower than the property from which they derive.

A property rule specifies a property, and a PiC rule specifies a PiC, of entities of a type. 

Entity Type Rules

There are two types of entity PiCs:

• First order PiCs (1OP) are "primitive" (i.e., not derived from other PiCs). For example, entities of type customer share 1OPs FICO score, customer balance, state, gender and tax status 1OPs;
• Second order PiCs (2OP) arise from relationships among (i.e., derived from) 1OPs[3]. For example, the relationship:

         FICO score   Customer Balance
    ------------+------------------
       0- 579                 0.00 
     580- 669           ≤   500.00    
     670- 739           ≤ 1,000.00
     740- 799           ≤ 1,500.00 
          800+          ≤ 2,000.00
    -------------------------------

is imposed on the two 1OPs by the enterprise limiting customer balance as a function of FICO score, and gives rise to a customer 2OP.

2OPs are indirectly associated with entities via the directly associated 1OPs from which they derive.

An entity type rule specifies the conjunction of 1OPs and 2OPs associated with, and shared by entities of a type. 

Note: An entity type rule denotes the meaning, but not the full meaning of a relation -- the conjunction of the entity type and group rules (see next).

Group Rules

Third order properties (3OP) are group (i.e., collective) properties that arise from relationships among all entity members of a group. There are are three types of 3OPs:

• Entity uniqueness: Entities of a type are distinguishable by a combination of one or more shared PICs, otherwise we could not tell them apart. For convenience, however, they are assigned names (e.g., customer IDs and order#s) that are unique within the group);
• Single-type entities (for lack of a better name): PiCs of entity members of a group are associated with entities of a single type, so for every member name there is exactly one value of each of their shared PICs (why?) (e.g., there is exactly one FICO score, customer balance, state, gender, and tax status value for every specific customer ID) -- knowledge of a name determines the value of each of the PiCs;
• Aggregate relationships imposed by a restriction on an aggregate of a combination of one or more 1OP(s)[3] (e.g., if the total balance owed by customers is restricted to 10,000,000).

A group rule specifies a group 3OP.

Note: Externally assigned names (e.g., customer names) may not be unique within a group, one reason for internally assigned names (e.g., customer IDs)[6]. Relations are by definition in 5NF, which is a formal way of saying that they represent (facts about) entities of a single type[7].

Designation Property Rules

At any given time there may be more than one collection of entities that (1) individually share the entity 1OPs and 2OPs, and (2) collectively have the 3OPs required for group membership -- they are potential group members. When authorized users insert tuples in the database, they designate the corresponding entities actual group members, and they acquire the designation property, which distinguishes them from the entities of that type that were not so designated and remained potential members at that time[7].

A designation property rule specifies a designation property.

Multigroup Rules

Fourth order properties (4OP) of a multigroup arise from relationships among groups. They may be due to

• Relationships among members of the groups[3]:

- Many-to-one (m:1): If, for example, a customer can issue multiple orders, and an order is issued by a single customer, the relationship of many orders to one customer gives rise to a multigroup 4OP;
- Many-to-many (m:n): If a customer issues multiple orders, and an order can be issued by multiple customers, the relationship of many orders to many customers gives rise to a multigroup 4OP;

• Group-level relationships: If the total order amount is designated a function of the total customer balance, the imposed group-level relationship gives rise to a multigroup 4OP.

A multigroup rule specifies a multigroup 4OP.

Note: One-to-one (1:1) relationships are a special case of m:1 relationships, which are a special case of m:n relationships.

Conclusion

The BRs that specify properties (some of which are relationships) and formalize as relational constraints are:

• Property and PiC rules;

• Entity type rules;
• Designation property rules.

• Group rules (3OP):

- Entity uniqueness rules;
- Single-type entities rules;
- Aggregate rules.

• Multigroup rules (4OP):

- M:1 rules;
- M:n rules; 
- Group-level rules.

SQL has shorthands only for:

• PK constraints that correspond to entity uniqueness rules;
• FK constraints that correspond to to m:1 and m:n rules.

It is not a-priori knowledgeable whether and which of the other rules are expressible with the CHECK syntax in a given SQL dialect, and how.

If and only if a database consists exclusively of fully normalized relations that are constrained to be consistent with the full meaning intended by the modeler in the BRs (and the DBMS is relational) are query results guaranteed to be logically valid and semantically correct[4]. 

Continued in Part 3.


Note: I will not publish or respond to anonymous comments. If you have something to say, stand behind it. Otherwise don't bother, it'll be ignored.


References

[1]  Pascal, F., What Is a Data Model, and What It Is Not.

[2] Pascal, F., Understanding Conceptual vs. Data Modeling Parts 1-4.

[3] McGoveran, D., LOGIC FOR SERIOUS DATABASE FOLK, forthcoming (chapter drafts). 

[4] Pascal, F., Logical Validity and Semantic Correctness.

[5] Pascal, F., THE DBDEBUNK GUIDE TO MISCONCEPTIONS ABOUT DATA FUNDAMENTALS - A DESK REFERENCE FOR THE THINKING DATA PROFESSIONAL AND USER. [6] Pascal, F., The Key to Relational Keys: A New Understanding.

[7] Pascal, F., Understanding Relations Parts 1-3.

[8] Pascal, F., Designation Property and Assertion Predicate.