Sunday, August 13, 2017

Relational Fidelity, Cursors and ORDER BY

Here's what's wrong with last database picture, namely:
"In a book I am reading (QUERYING SQL SERVER 2012) the author talks about theory of how databases work. He mentions relations, attributes and tuples etc. He frequently stresses the fact that some aspect of T-SQL is not relational. Like in the following excerpt:
"T-SQL also supports an object called a cursor that is defined based on a result of a query, and that allows fetching rows one at a time in a specified order. You might care about returning the result of a query in a specific order for presentation purposes or if the caller needs to consume the result in that manner through some cursor mechanism that fetches the rows one at a time. But remember that such processing isn’t relational. If you need to process the query result in a relational manner--for example, define a table expression like a view based on the query--the result will need to be relational. Also, sorting data can add cost to the query processing. If you don’t care about the order in which the result rows are returned, you can avoid this unnecessary cost by not adding an ORDER BY clause."
I would like to know, since every implementation of SQL pretty much has an ORDER BY clause which makes it non-relational, why does it even matter that (the set after ORDER BY is used) its not relational anymore since its like that everywhere? I can understand if he said it was non-standard, for example using != instead of <> for inequality because that affects portability etc., but I do not understand why something is better being relational. Please enlighten."

Saturday, August 5, 2017

This Week

1. Database Truth of the Week

"Semantic correctness: every interpretation of the symbols (meaning assignment and truth value assignment) that makes the axioms true, makes the theorems true. When we extend a logical data model with semantics (specific to the subject matter and its "business" rules) via constraints, those constraints become axioms that must be true." --David McGoveran

Tuesday, August 1, 2017

Structure, Integrity, Manipulation: How to Compare Data Models

My August blog post @All Analytics.

"The IT industry operates like the fashion industry: every few years -- and the number keeps getting smaller -- a "new" data technology pops up, with vendors, the trade media and various "experts" all stepping over each other to claim that it'll "revolutionize your business" and unless you jump on the bandwagon, you'll be "left behind." But time and again these prove to be fads lacking a sound foundation. Huge resources are invested in migrations from fad to fad, rather than in productive work (Don't believe the hype about Hadoop usage, Basta, Big Data It's Time to Say Arrivederci)."

Read it all.


Sunday, July 30, 2017

Integrity Is Not Only Referential

Many years ago I wrote an article with this title that carried a dual meaning. The first alluded to vendors claiming relational features--in that case Borland's Paradox referred to a form--based (i.e., application-based) feature as referential integrity. Vendors get away with this because of the second, literal meaning: data professionals may be familiar with uniqueness (PK) and referential (FK) constraints only because they are the only two types of constraints with shorthands enforced by SQL DBMSs that are "out of the box", but are unaware of the several other types of relational constraints and their lack of support in SQL. They are either unsupported declaratively, supported via stored procedures outside the RDM, or not at all, with loss of relational advantages. Even if they were supported, they require proficiency in formalization of conceptual business rules as logical constraints and validation--that most practitioners don't possess.

Read it all

Sunday, July 23, 2017

This Week

1. Database Truth of the Week

"And [AI] weaknesses there are. Watson requires many months of laborious training, as experts must feed vast quantities of well-organized data into the platform for it to be able to draw any useful conclusions. And then it can only draw conclusions based upon the body of data, or ‘corpus’ (plural: ‘corpora’) that it has been trained on. The ‘well-organized’ requirement is especially challenging for Watson, as unprepared data sets are typically insufficient. As a result, Watson customers must hire teams of expert consultants to prepare the data sets, a time-consuming and extraordinarily expensive process." --Is IBM's Watson a Joke?

Sunday, July 16, 2017

Relations and Relationships Part II

This is a 6/21/17 rewrite of a 4/21/13 post, to bring it in line with McGoveran's interpretation of the real RDM envisioned by Codd. It is the second part of a debunking of a LinkedIn thread (the rewrite of the first part of which was posted two weeks ago).

The misconception that the RDM represents relationships of only one type--referential--most likely originates with the E/R conceptual modeling approach. It assumes an "absolute" distinction between entities (objects) and relationships. The distinction, however, is in the "eye of the modeler": objects, properties and object groups are all, in fact, relationships labeled differently as a matter of subjective, pragmatic convenience.  All those relationships expressed as business rules comprising a conceptual model are expressible in a relationally complete FOPL-based data language as integrity constraints enforcible by a RDBMS for consistency with the rules. That neither SQL, nor any other current data languages can express--nor can the DBMSs based on it enforce--all of them is the deficiency of their implementation, not a RDM weakness.  
Read it all.

Due to a glitch an earlier revision of an older post seems to have gotten lost. If you have not seen it, read it it here: To Really Understand Integrity, Don't Start with SQL.

Sunday, July 9, 2017

This Week

1. Database Truth of the Week

"For the operations of a formal system to have inverses within some specific use of that system (like a specific application):
  • The basic elements must be orthogonal (independent), hence the Principle of Orthogonal Design;
  • The combination of basis elements and operations must be expressive enough to represent every aspect of the subject matter, hence the Principle of Expressively Complete Design;
  • And, at the same time, not so expressive that there is more than one way to express each aspect of the subject matter, hence the Principle of Representation Minimality Design.
The basic elements of a relational database is the relation. Adherence to these principles ensure thatthere is a unique relational expression for every aspect of the subject matter--either a base relation or a derived relation--and if there are two ways to derive a derived relation, then those two expressions are provably equivalent (i.e., the differences are merely syntax and never meaningful)." --David McGoveran