Sunday, May 22, 2016

This Week (& a Change in Format)



I am changing the format of the posts on DBDebunk.

"This Week" posts every other week will continue to be followed by posts on data and relational fundamentals, but with a twist: each of the latter posts will explain the preceding week's "What's wrong with this picture?"Here's the first post in the new format

1. What's wrong with this picture?

"Data is stored in two-dimensional tables consisting of columns (fields) and rows (records). Multi-dimensional data is represented by a system of relationships among two-dimensional tables. This usually leads to data storage becoming redundant, and also difficult to maintain on account of addition and deletion anomalies. This is only the case if we do not normalize the data. Keys are fields or combinations of fields used to identify records." --Nigel Peck, Denormalization Summary

Sunday, May 15, 2016

Weak Entities, Referential Constraints and the Conceptual-Logical Conflation



Note: This is a rewrite of a 09/07/14 post.

A LinkedIn exchange initiated by the question What is a weak entity? diversified into various aspects of data modeling and database design, some of which was contaminated by the conceptual-logical conflation.

"Peter Chen (E/R Modeling, 1976) used the term "weak" entity to describe one which could not meaningfully have an independent existence. An example might be an Order which requires a customer (and a Product or set of products). A weak entity need not be a composite, as in your OrderItem example. The central issue here is dependency of one entity [of one] type on another [of another type]. Furthermore, there could be more that one such dependency. In your example, the OrderItem would be dependent on BOTH Order and Item.

It is important to note that this dependency will NOT be enforced by a simple RDBMS unless you also define referential integrity on both parts of the composite key, notwithstanding that some RDBMS's (e.g., SQL Server) couple the definition of the relationship with a referential integrity constraint. In other words, you cannot have a defined relationship without enforcing referential integrity.

This is not always desirable. Consider the task of bulk loading some records which have a relationship with some other entity type(s). The only practical way to do this is to first turn off referential integrity enforcement which means deleting the definition of the relationship entirely. After completing the loading of data, if you reinstate the defined relationship, will the system automatically ensure that referential integrity is not violated? Good question."
--Gordon Everest
Relationships in the E/RM are at the conceptual level. Dependencies of entities of one class/type on those of another are one kind of relationships defined as business rules in a conceptual model. Referential constraints are their formal representation at the logical level of a relational database. So it's not that "some" SQL DBMS's (true RDBMS's do not exist) "couple the definition of the relationship with a referential integrity constraint", the constraints are precisely how a RDBMS (what exactly is a "simple RDBMS"?) enforces business rules: a defined relationship is enforced as a referential constraint in a relational database. How does Everest propose to enforce business rules without constraints?

There are no circumstances in which FK constraints "are not desirable" and deactivating them is not the the solution to bulk loads, staging tables are. For example:

"In Oracle you can instruct the DBMS to log violating rows into an 'exceptions' table while enabling a constraint (be it, a primary key, unique key, foreign key, or plain check constraint). This fits the bill better, particularly if it's possible to INSERT...SELECT them after corrections." --Toon Koppelaars
"SQL Server will check on re-enabling the constraint that it is not violated by the new state of the database. If it is, it only reports that an error occurred. It has a real time violation catalog to easily map a constraint name to a specific violation and transaction id. In other words, doing bulk batch constraint validation is part of the product." --William Sisson and Racim Boudjakdji
Much of the criticism of the RDM is rooted in ignorance of data and relational fundamentals and the history of database management.
"How to implement“existence dependence” ... between two entities ...is a design decision and is dependent upon the architecture of the implementation technology. [Chen's E/R models] ... were completely technology agnostic and had no “relational” flavor at all. Why? With the rapid emergence and popularity of the NoSQL, NewSQL, Graph Databases, etc. no longer can relational implementation and FK be assumed and we again need to separate the “conceptual data model” from the “logical data model”. Whether this is evolution of de-evolution is a whole other subject (for those of us that remember WHY relational technology emerged in the 1980s--and as a warning to those who would forget their history--IDMS, IMS, etc.)"

The relational model has been so dominant for so long, we have forgotten our roots and the reason that there were originally three layers of data model, not just two. The rise of the post-relational technologies has again required their creation and reminds us of their utility.
--David Tryon
Data model agnosticism has nothing to do with the “emergence/popularity of non-relational technologies” (NoSQL, NewSQL did not exist when Chen introduced the E/RM), it is  is inherent in conceptual modeling--which is what E/RM is used for--by definition. Conceptual models are expressed in real world terms--facts about classes of property-sharing entities. To be represented in a database, a conceptual model must be formalized as a logical model expressed in database terms. That's when a data model comes into play, to provide abstract data structure, integrity and manipulation (relation, constraints and relational algebra in the relational case). The conceptual and  logical were always separate, but as Everest comments show, levels of representation are constantly confused. 

Incidentally
  • A logical model is implemented in specific hardware and DBMS software, a conceptual model is formalized as a logical model by means of a data model (e.g., the RDM). Using 'implementation' for both induces and reinforces the confusion.
  • Emergence, or popularity are not grounds for technology adoption: a superior generality-to-simplicity ratio (G2SR), soundness and flexibility are. 
If only there were relational dominance. Relational technology was treated by the industry as just any other "hot" fad and was never understood and implemented.  Instead we got SQL DBMS's, whose even poor relational fidelity proved superior to the hierarchic and network products that they replaced, but did not confer all the advantages of true RDBMS's.

Do away with the RDM and constraints and you're back to relying on application developers for database functions, particularly integrity enforcement and optimization, without guaranteed logical and semantic correctness--clearly de-evolution.







Monday, May 2, 2016

This Week



1. What's wrong with this picture?
The query framework, as it exists in modern day MongoDB, supports the following functionality:
  • Filtering. The rough equivalent of the WHERE clause in SQL.
  • Paging. The rough equivalent of LIMIT and OFFSET in SQL.
  • Sorting. The rough equivalent of ORDER BY in SQL.
--John De Goes, MongoDB: The Frankenstein Monster of NoSQL Databases, LinkedIn.com

Tuesday, April 26, 2016

Re-write



My April column @All Analytics.

A R-table with attributes defined only on simple domains takes a less convoluted form -- a normal form -- devoid of nesting. If R-tables are in the preferred normal form i.e., components meaningful to applications (here, employee attributes) are simple domains in their own right and a true RDBMS enforces value atomicity -- first order logic is sufficient. This imposes some limitations on the expressive power of data languages, but they are declarative and PDI and simplicity are preserved. A true RDBMS enforces atomicity via a data language that does not allow applications to access attribute components not explicitly defined on their own domains.

Read it all. (Please comment there, not here)

FYI: I have revised all three parts of the series on 1NF -- mainly refinements and clarifications.









Tuesday, April 19, 2016

First Normal Form in Theory and Practice Part 3



09/19/23: For the latest on this subject see: FIRST NORMAL FORM - A DEFINITIVE GUIDE


Note: This is a 11/23/17 revision of Part 3 of a three-part series that replaced all of my previous posts on the subject (pages of which redirect here), in order to further tighten integration with the formalization and interpretation [1] of McGoveran's formalization and interpretation [1] of Codd's true RDM.

(Continued from Part 2)

 
"Is this table in 1NF?" is a common question in database practice. On the other hand, "What problems are solved by splitting street addresses into individual columns?", or  
What's the best way to store an array in a relational database does not seem to evoke associations with 1NF. This reveals poor foundation knowledge.


Part 1 introduced the poor understanding of 1NF and Part 2 provided a correct definition and explanation. Part 3 explains how 1NF can be enforced by the data sublanguage, which SQL does not.

Sunday, April 17, 2016

This Week



1. What's wrong with this picture?

NoSQL database management systems give us the opportunity to store our data according to more than one data storage model, but our entity-relationship data modeling notations are stuck in SQL land. Is there any need to model schema-less databases, and is it even possible? --Theodore Hills, The Hybrid Data Model, Dataversity.net

Sunday, April 10, 2016

First Normal Form in Theory and Practice Part 2



09/19/23: For the latest on this subject see: FIRST NORMAL FORM - A DEFINITIVE GUIDE


Note: This is a 11/23/17 revision of Part 2 of a three-part series that replaced all of my previous posts on the subject (pages of which redirect here), in order to further tighten integration with the McGoveran formalization and interpretation [1] of Codd's true RDM.
(Cont'd from Part 1)
 

Part 1 raised the issue of poor understanding of Codd's concept of a simple domain with atomic values underlying 1NF. In Part 2 I clarify Codd's definition of 1NF and its correct interpretation.

View My Stats