Monday, May 20, 2013

Object Orientation, Relational Database Design, Logical Validity and Semantic Correctness

This is a 8/24/17 rewrite of a 5/20/13 post to bring it in line with McGoveran's formal exposition of Codd's RDM[1] and its correct interpretation.

08/25/17: I have added formal definitions of logical validity and semantic correctness. 
09/01/17: Minor revisions. 
09/02/17: Added references.

Here's what's wrong with last week's picture, namely:
"In my experience, using an object model in both the application layer and in the database layer results in an inefficient system. This are my personal design goals:
- Use a relational data model for storage
- Design the database tables using relational rules including 3rd normal form
- Tables should mirror logical objects, but any object may encompass multiple tables
- Application objects, whether you are using an OO language or a traditional language using structured programming techniques should parallel application needs which most closely correspond to individual SQL statements than to tables or "objects". --LinkedIn.com


The RDM put database management on a formal, scientific foot. Consequently, tool experience and relational terminology are insufficient -- foundation knowledge is necessary. Unfortunately, most data professionals do not possess it, in part because they have been misled by the industry and in part because few go through an education -- as distinct from training -- program that teaches the RDM and teaches it correctly. Consequently, even those with the heart in the right place who defend the RDM do it without proper understanding, their views distorted by what passes for the RDM in the industry[2] (stay tuned for a debunking of such a recent example).
 

Levels of Representation and Types of Model


There are four distinct types of model in database management:
  • Conceptual
  • Logical
  • Physical
  • Data
Models of the first three types are of some portion of reality of interest at different levels of abstraction --i.e., are enterprise-specific. A data model is a general formal theory of data. The RDM is grounded in simple set theory and first order predicate logic (FOPL) and used to formalize informal conceptual models of reality as logical models for database representation.

The four types of model are routinely confused and used interchangeably (e.g., the first three are referred to as data models, which they are not[3] and there is conceptual-logical conflation (CLC) and logical-physical confusion (LPC))[4]. "Use a relational data model for storage" is an example of both:

  • Use  the [not "a"] RDM for logical database design, not "for storage";
  • Use a physical [not "data"] model for storage;

I have long advocated terminology that helps avoid such mistakes[5]:
  • Conceptual modeling;
  • Logical database design;
  • Physical implementation;

Relational Data Management, OO Application Development


A data model covers only data management DBMS functions -- data definition, integrity and manipulation. In the case of the RDM, they are expressed in some FOPL-based relational data sub-language. Application functions -- two of which are preparation and presentation of results -- are expressed in computationally complete host (e.g., programming) languages.

There is no formal "object [data] model" to use at the database layer -- object orientation (OO) is a set of informal programming guidelines which, if followed, yield programs with useful properties. It, therefore, belongs in the application layer: application developers can follow the guidelines to program application objects, but extending it to database management is unsound and loses the advantages of the RDM:

  • Simpler, declarative, decidable data sub-languages;
  • Physical and logical independence;
  • System-guaranteed logical validity and, with proper database design, semantic correctness;
Thus, a relation is an element of a logical model that represents a real world object group, where an object is an element (entity, if you will) of the conceptual model. An OO object is an element of an application that, if it accesses a relational database, encapsulates one or more relations, as well as relational operations on them. 


Normalization, Logical Validity and Semantic Correctness


1NF is necessary for logical validity (correctness): a 1NF database relation preserves the properties of a mathematical relation, such that the set operations of the relational algebra (RA) can be applied for provably valid query and update results. A query result is said to be logically valid if and only if it is derived by any sequence of RA operations on one or more relations.

1NF is insufficient, however, for mutual consistency of base relations, which requires database design joint adherence to
  • The Principle of Orthogonal Design (POOD)
  • The Principle of Representational Minimality (PORM)
  • The Principle of Expressive Completeness (POEC)
The three principles jointly imply the Principle of Full Normalization (POFN), but not vice-versa. Full normalization -- an individual property of relations -- requires knowledge of semantic relationships (dependencies) that the DBMS must enforce via constraints and is necessary for database consistency with the conceptual model of reality that the database represents, but is insufficient for semantic correctness -- a multi-relation property -- for which design adherence to the POOD, PORM and POEC is required. A query result is said to be semantically correct if an only if for every assignment of meaning to RA relation operands under which their tuples represent true facts, the tuples of the result relations also represent true facts[6].

Thus,
  • According to the common understanding of the RDM, database relations are in 1NF by definition, which ensures only logical validity; 
  • According to a correct understanding of Codd's RDM, relations are in 5NF by definition, which, together with adherence to the three principles ensures both logical validity and semantic correctness;

Note: This is a huge topic and the basis of McGoveran's forthcoming proposed semantic improvements to RDM[1]. The POOD is just the tip of the iceberg -- it was never (1) intended to be considered alone and in isolation of the PORM and POEC (2) motivated strictly by view updating, which is just one aspect of semantic correctness -- avoiding loss of information by data manipulation.
 

5NF means a 1:1 relationship between real world object groups and relations: one relation per object group and one object group per relation. While an OO application object can and usually does encompass multiple relations, an object in conceptual models is represented by a tuple of one relation.

Note: An object group in a conceptual model is roughly akin to a “class” in OO programming.


OO Classes and Relations


"Application objects ... should parallel application needs which most closely correspond to individual SQL statements than to tables or "objects"" argues against considering relations in the logical model as the analog of the application's object classes.
"I’ve preached this idea since the mid-1980s. Forcing each application object class to be represented as one or more tables is the biggest single mistake the ODBMS vendors and proponents made. On the other hand, the object-relational mapping (ORM) advocates had no discipline that guaranteed the information in the "object model" would map correctly to the relational logical model. They most often mapped it directly to the relational physical model, ignoring requirements of the logical model altogether." --David McGoveran

Here's a correct reformulation of the recommendations:

  • Use the RDM to formalize conceptual models of reality to logical database models;
  • Design databases that
  • consist of relations;
  • are constrained to be consistent with the object groups they represent;
  • satisfy the POOD, PORM and POEC (and, thus, with POFN); application objects may encompass multiple relations;
  • R-tables should mirror (visualize) relations;
  • Application object classes should not be forced to be represented by one or more (stored) relations;
Given the above, can you figure out what's wrong with the following coments?
"When you design a relationally modeled database (better?) there are certain rules that you follow. Among these are rules describing at least six levels of normalization. I design to 3NF as a design goal, yes. Why not 4NF or BNF [sic] or others? Sometimes I will go further, however, as a general rule, 3NF is the minimal level required for efficient storage and processing. Sometimes higher normal forms add unnecessary overhead to a system without adding data security or other benefits. This results in application data structures that are efficient for the application and efficient for communication with the database. If the queries that are modeled are designed for efficiency in the database as well, then all layers are optimized."
"Some of the objects we model as database designers are physical, some are logical,and some are some combination of both. The models, I agree are all 'logical'."
"OO is both [a programming and data paradigm]. I do agree that it is a set of guidelines ... The problem I have with OO databases and data modeling and applications designed around the traditional OO model is that they all assume that you will always fetch and update entire objects which is incredibly inefficient for most of the operations that an application performs. Example: A natural object in a banking application is to model an account as an object. Such an object will naturally have hundreds of elements in it many with a one to many relationship to the account. Most applications will be involved in updating the account's balance which is only a single element. Getting the account information you need to process that update entails examining a few dozen elements at most. To fetch the entire account object into the application in this case multiplies the communications and IO loads by one or two orders of magnitude which I find completely unnecessary."
The upshot of that ill-fated and ill-conceived idea of an object store was to reverse this idea and design applications to pull the stored data objects into the application whole and push them to disk whole when the stored objects represented problem space objects beyond the scope of the application's needs of the moment."

References 

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

[2] Pascal, F., THE DBDEBUNK GUIDE TO MISCONCEPTIONS ABOUT DATA FUNDAMENTALS - A DESK REFERENCE FOR THE THINKING DATA PROFESSIONAL AND USER.

[3] Pascal, F., Data Model: Neither Conceptual, Nor Logical, Nor Physical.

[4] Pascal, F.,  The Conceptual-Logical Conflation and the Logical-Physical Confusion

[5] Pascal, F., Levels of Representation: Conceptual Modeling, Logical Design and Physical Implementation

[6] Pascal, F., The Principle of Orthogonal Database Design Part I 



Do you like this post? Please link back to this article by copying one of the codes below.

URL: HTML link code: BB (forum) link code:

1 comment:

  1. "Most applications will be involved in updating the account's balance which is only a single element."

    I would say that in a properly designed accounting system there would be no account balance attribute to be updated.

    ReplyDelete