Conceptual modeling

Re: To Null or not to Null. That is the question!

hughdarwen — Wed, 25 May 2011 17:48:23 GMT

In response to Clifford, in great haste because I'm about to be away and out of communication until next Tuesday:

(a) At the physical layer anything goes. If some kind of "sparse matrix" gives desired performance while a more direct representation of the logical schema doesn't, then go for it. (I have often made this point.) But the things representing the gaps wouldn't be called NULL because that has semantics in the language (SQL) in which it is defined. Nor would the storage and retrieval mechanism have to deal with 3-valued logic etc.

(b) Well, standard SQL has had NATURAL JOIN since 1992--what's more I was the one that put it there! (It also has JOIN ... USING ..., which ought to be natural join with protection from inadvertently joining on the wrong attributes but alas it isn't, quite.)

In Business System 12 and Tutorial D, when you're confident that the attribute names are as you want them, you can write JOIN{r1, r2, ... rn} (though in BS12 the braces were parens) in place of r1 JOIN r2 JOIN ... JOIN rn. (This thanks to JOIN's commutativity and associativity, of course.)

Regards,

Hugh

Re: To Null or not to Null. That is the question!

Clifford Heath — Wed, 25 May 2011 07:13:22 GMT

Dear Mr Darwen:

The only perjorative in my intent was against those who have come to think the SQL is a good example of the relational model! That is, I used the word "purist" to avoid confusion with the popular misunderstanding of "relational".

I bet you get very tired of being drawn into this kind of debate, with people who aren't equipped to understand your arguments. Nevertheless, that's not the case here, and I'd really like your comments on my previous discussion of the different requirements of conceptual vs physical layers, and the need for NULLs at the physical layer, hidden under a pure conceptual layer.

I'd also be delighted if you had time to comment on the conceptual difficulty (for ordinary mortals!) of expressing joins... I think SQL makes this stupidly difficult and have shown how it can be much easier in a more sensible query language. I think this is a mandatory requirement before we can expect people to normalise the NULLs out of their database design, whether or not we provide hidden conceptual->physical mapping intelligence.

Re: To Null or not to Null. That is the question!

Andy Carver — Tue, 24 May 2011 17:38:17 GMT

Well said! Thanks, Mr. Darwen!

Re: To Null or not to Null. That is the question!

hughdarwen — Tue, 24 May 2011 17:32:25 GMT

Just to say that I don't mind being called a relational purist. After all, I can hardly deny it. I claim, of course, that a "relationally pure" language, if well-designed by all other criteria of good language design, will be of more practical use than one that is impure (like SQL) and also well-designed by all other criteria of good language design (unlike SQL!). Those who disagree with me will say, "The trouble with you is, your a relational purist and that's why I don't like your proposals". So they attach some pejorative connotation to the term, but that doesn't make it ad hominem (unless of course it's meant to be taken that way). It's relational purity they don't like. I like it and my reasons for liking it are thoroughly practical as well as aesthetic. Some claim that NULL is thoroughly practical. I disagree. It's a judgment call.

Regards,

Hugh

Re: To Null or not to Null. That is the question!

Andy Carver — Tue, 24 May 2011 15:22:45 GMT

Hi Ken,

If it matters, I still think Clifford's terminology is less problematic: First, "purist" does not necessarily carry any negative connotation. I doubt that Mr. Date would object to the characterisation of himself as a relational purist; in fact, I think he'd happily agree with the characterisation.

Second, the assumption that we agree on the meaning of "relational conformance" is guaranteed to lead to confusion, in a context where not even Ted Codd and Chris Date (and Hugh Darwen) can agree on what conforms to the (true) "relational model". So I fail to see the usefulness of that term at all, in the kind of disagreement we're in which guarantees we don't agree on the meaning of that term!

Cheers,

Andy

Re: To Null or not to Null. That is the question!

Ken Evans — Tue, 24 May 2011 15:12:40 GMT

Andy Carver:
P.S. So I don't appear too biased for some people's tastes, I offer up an additional link, which at least gives both sides equal time -- in the persons of Messers Date and Codd:

Hi Andy,

Thanks for this. I will defer my response until I have read the 2010 book "Database Explorations" (by Chris Date and Hugh Darwen) which arrived by courier just one hour ago.

Ken

Re: To Null or not to Null. That is the question!

Ken Evans — Tue, 24 May 2011 15:09:27 GMT

Andy Carver:
You complained about Clifford's use of the term "relational purist", and preferred us to use the term "relational conformist". I demur. I think Clifford's term is somewhat more accurate.

Hi Andy,

Again I confess to a lack of rigour in my use of language.
For clarification, I think that it is better to discuss the truth or falsity of one or more conjectures than to resort to ad hominem reasoning.
This (I now realise) is what was behind my objection to the term "purist".

So it seems to me better to discuss the topic of "relational conformance" in terms of whether a particular solution "conforms" rather than talk about the properties of any particular person who might propose the solution.

Ken

Re: To Null or not to Null. That is the question!

Andy Carver — Mon, 23 May 2011 12:41:51 GMT

Ken,

P.S. So I don't appear too biased for some people's tastes, I offer up an additional link, which at least gives both sides equal time -- in the persons of Messers Date and Codd:

http://www.dbdebunk.com/page/page/1706814.htm

Re: To Null or not to Null. That is the question!

Andy Carver — Mon, 23 May 2011 07:39:19 GMT

Hi Ken,

You complained about Clifford's use of the term "relational purist", and preferred us to use the term "relational conformist". I demur. I think Clifford's term is somewhat more accurate.

The reason I say this is that I cannot get my head around the idea that E.F. Codd was not a "relational conformist" (seeing as how he came up with the whole approach in the first place:-). But if he was indeed a "conformist" to the relational model, then we can't use this term to refer to people who abhor the use of nulls. For he himself argued strongly against their position, insisting that nulls were an important part of the relational model! You might wish to have a read of the following 1986 paper by him:

http://148.247.202.2/~jtj/courses/dbs/relatedpapers/Missing%20information%20(applicable%20and%20inapplicable)%20in%20relational%20databases.pdf

Cheers,

Andy

Re: To Null or not to Null. That is the question!

Andy Carver — Mon, 23 May 2011 06:27:59 GMT

Hi Mr. Darwen,

Although my wad is shot, I do notice I omitted to respond to a few questions and comments from you. Please allow me to do so below, in-line like

>> this.

hughdarwen:

<snip>

Here I just want to respond further to this, from you:

"The reason why nulls are GOOD is precisely that the bag functions (e.g. AVG) which the system provides, are TRAINED to ignore rows containing nulls!"

You seem to be arguing that NULL is good because AVG (etc.) treat it this way. Surely you see that if NULL didn't exist, AVG (etc.) would still work just fine--I would say even finer--especially when used in conjunction with the SUMMARIZE ... PER ... relational operator proposed by myself and Date as an improvement on SQL's GROUP BY.

>> Well, if NULLS don't exist, then it's either because you're using some other, user-chosen default values to represent that "info is missing here", or else you don't have to worry about potential missing information because you've chose to put each elementary fact type into its own table (as in your "6NF"). But this latter alternative, I think Clifford has persuasively shown to be, at present anyway, too inefficient. Which leaves us with the former alternative, default values chosen by the user but which the system doesn't know are to represent "info is missing here". And in that scenario, I'd say No, AVG (etc.) do NOT "work just fine", in fact they become a royal pain to use-- which is my whole point here. Now, it may be that you and Mr. Date have come up with a substitute for GROUP BY that, to some extent anyway, relieves the painfulness involved. I'd certainly be interested in reading up on your "SUMMARIZE ... PER ..." Where would I find the best introductory discussion, if I may ask?

Of course I agree that if NULL exists, then SQL's treatment of it in aggregate functions is sensible. But then I would add, in that case (a) why isn't the sum of no values at all zero, as it should be (zero being the identity value under addition) , and (b) why isn't 1+NULL equal to 1, considering that SUM(x) = 1 when the bag represented by x here is {1,NULL}?

>> These examples can only make one chuckle, and indeed if there is any part of these SQL functions' behaviour that is NOT "sensible", then SUM's treatment of nulls, and addition's treatment of them, are prime candidates for that dishonour.

Not supporting NULL means the book doesn't have to say anything about its treatment (in every operation). William of Occam would have approved.

>> Well, I like Occam's Razor as much as the next philosopher. But honestly, what scares me more than teaching students about 3VL (which I last did about two weeks ago), is the thought of teaching them how to, and that they must, do all the aggregation operations without benefit of NULLs.

Regards,

Hugh

Re: To Null or not to Null. That is the question!

Clifford Heath — Mon, 23 May 2011 02:35:36 GMT

hughdarwen:
performance is a characteristic of the implementation, not the language

While it's all very well to claim that, real applications must be written with real DBMS.

hughdarwen:
some queries of course go faster after decomposition

Of course, and the column store proponents have shown some interesting results. I'm wary though of their promotional focus.

What does concern me is the attitude that the vast numbers of database designs containing NULLs are that way because the creators are somehow ignorant or lazy. That would be an extremely disrespectful attitude to take to the integrity and professionalism of the million DBAs whose daily work is to scrutinise schemas, transaction logs and system performance in order to create acceptable performance. When a schema can be manually manipulated to implement column-store behaviour or to remove NULLs through better normalization, these people will do it... if it helps instead of hindering. The simple fact is, as I justified in my previous message, in the vast majority of cases, a physical model with NULLs performs better then one without.

I'm not justifying the use of NULLs in a conceptual model. Far from it. but unless and until we have DBMS that reliably produce the most effective physical schema from a pure conceptual one, we aren't going to make progress.

The other requirement for using more highly-normalised schemas is a query language where joins don't make your eyes bleed. Recently I helped a New Zealander design a model for a trading (swapping) service. The schema was simple: Owneditem is where Person has Item, WantedItem is where Person wants/is wanted by Item, WantedItem is valued in/contains ValueRange (and OwnedItem-ValueRange too). Five tables; Person, Item, WantedItem, OwnedItem, and a static table for ValueCategory. The SQL view for possible trades, using the (less recommended, but more succinct implicit joins) follows:

CREATE VIEW PossibleTrade AS
SELECT  p1.person_id AS person1_id,
        o1.item_id AS item1_id
        p2.person_id AS person2_id,
        o2.item_id AS item2_id
FROM    Person AS p1,
        OwnedItem AS o1
        Item AS i1,
        WantedItem AS w1,
        Person AS p2,
        OwnedItem AS o2,
        Item AS i2,
        WantedItem AS w2,
        ValueCategory AS vc
WHERE   p1.person_id = o1.person_id             -- Person1 owns
  AND   o1.item_id = i1.item_id                 -- an Item1
  AND   o1.value_category = vc.id               -- valued in the range VC1
  AND   o1.item_id = w2.item_id                 -- which is wanted
  AND   w2.person_id = p2.person_id             -- by Person2
  AND   p2.person_id = o2.person_id             -- who owns
  AND   o2.item_id = i2.item_id                 -- an Item2
  AND   o2.value_category = vc.id               -- valued in the same range VC1
  AND   o2.item_id = w1.item_id                 -- which is wanted
  AND   w1.person_id = p1.person_id             -- by Person1

Using explicit joins, it's shorter but harder to read:

CREATE VIEW PossibleTrade AS
SELECT  p1.person_id AS person1_id,
        o1.item_id AS item1_id
        p2.person_id AS person2_id,
        o2.item_id AS item2_id
FROM    Person AS p1
        JOIN OwnedItem AS o1 ON p1.person_id = o1.person_id AND w1.person_id = p1.person_id
        JOIN Item AS i1 ON o1.item_id = i1.item_id
        JOIN ValueCategory AS vc ON o1.value_category = vc.id AND o2.value_category = vc.id
        JOIN WantedItem AS w2 on o1.item_id = w2.item_id
        JOIN Person AS p2 ON w2.person_id = p2.person_id
        JOIN OwnedItem AS o2 ON p2.person_id = o2.person_id
        JOIN Item AS i2 ON o2.item_id = i2.item_id
        JOIN WantedItem AS w1 ON o2.item_id = w1.item_id

In CQL, this ugliness vanishes. Here's the entire schema, and the PossibleTrades view (derived fact type):

Value Category is written as Integer restricted to {5, 15, 50, 150, 500, 1500};

Item is identified by its Id;
Item has one Name, Name is of Item;

Person is identified by its Id;
Person has one Name, Name is of Person;

WantedItem is where
    Person wants Item, Item is wanted by Person;
WantedItem is in one Value Category, Value Category contains WantedItem;

OwnedItem is where
    Person has Item, Item is property of Person;
OwnedItem is in one Value Category, Value Category contains OwnedItem;

/*
 *  Derived Fact Types (views)
 */
Person (as Owner) has Item wanted by Person where
    Owner has Item that is wanted by Person;

Possible Trade is where
    Person(1) can trade Item(2) to Person(3) receiving Item(4) in return where
        Person(1) has Item(2) that is wanted by Person(3) that has Item(4) that is wanted by Person(1)
        and Item(2) is in Value Category that contains Item(4);

The last 2 statements contain the same logic as the SQL views above. This schema translates to the same SQL, but is comprehensible to ordinary mortals. I realise that this example doesn't illustrate the use or avoidance of NULLs, but it does illustrate why the extra normalisation (required to eliminate NULLs) is a problem.

Re: To Null or not to Null. That is the question!

hughdarwen — Sun, 22 May 2011 14:53:07 GMT

Clifford wrote: "The problem with SQL is that for the implementations to get reasonable performance, facts should be clustered into tables that represent composite facts wherever that's possible."

That point about performance is frequently made. I always want to remark that performance is a characteristic of the implementation, not the language. I also always want to ask, "performance of what?", to which the answer tends to be "queries that would require extra joins if we decomposed vertically to avoid nulls". But some queries of course go faster after decomposition. Also, in recent years there has been much interest in the performance benefits claimed for "column-wise" stores, in some varieties of which vertical decomposition is taken to extremes, under the covers. If those claims are justified, then vertical decomposition at the logical level doesn't seem such a bad idea, performance-wise, after all

Regards,

Hugh

Re: To Null or not to Null. That is the question!

Ken Evans — Sun, 22 May 2011 11:15:59 GMT

Clifford,

Well, for a "non-expert" you have certainly made a very good articulation of the "null problem"!

One small quibble is your use of the term "purist" as you used it in "relational purists".

As I understand it, the term "purist" may be interpreted by many people to have a pejorative connotation.
I suspect that this was not your intention.

Would you agree that "relational conformist" would be better?

Here, I draw upon Philip Crosby's "Quality is Free" book in which he advocates defining quality as "conformance to requirements".
I feel that the term "relational conformist" eliminates the risk of a pejorative interpretation and also leads to useful questions such as:

Does this data model conform to the relational model?
Why should I bother to conform to the relational model?
What are the benefits of conforming to the relational model?

and so on.

Regarding nulls, we all seem to be agreed that in RDBMS implementations, nulls are a fact of life that have to be handled.
However, that is not the same thing as saying that nulls are OK.

My original interjection (http://www.ormfoundation.org/forums/t/839.aspx) was intended to bring out the point that it is good practice to design a data model in a way that minimises the appearance of nulls in the physical implementation. My aim was to show that Marc's problem could be solved by using a conceptual model that eliminated the need for nulls - in his particular scenario.

Ken

Re: To Null or not to Null. That is the question!

Andy Carver — Sun, 22 May 2011 06:02:50 GMT

Clifford

That's extremely well stated, and yes, you're getting down to at least one of the hubs of this whole issue. I agree that the awkwardness, which we have no choice to deal with, comes from the source that you indicate, and thus (I would add) not from any logical sloppiness or incompetence of the "nulls are OK" crowd. I wanted to bring up the issue of conceptual queries earlier, but I'm glad I waited because you've now covered it much more eloquently than I could.

Cheers

Andy

Re: To Null or not to Null. That is the question!

Clifford Heath — Sun, 22 May 2011 02:03:47 GMT

Well, I don't feel like an expert, but...

The demand to exclude NULLs is for exactly the same reason that fact-based modelling demands elementary form. Whenever you express things in terms of predicates containing composite facts, you have problem when one of those facts is missing; so you need a different kind of expression. When elementary form is used, no fact exists without all roles being fulfilled, so you have no need for NULLs. In this respect, I think relational purists and fact oriented modellers are entirely in agreement, though we use different language to express it.

The problem with SQL is that for the implementations to get reasonable performance, facts should be clustered into tables that represent composite facts wherever that's possible. This clustering makes the best use of the physical media's access characteristics. If the DBMS implementations had instead focussed on correct modeling in highly-normalised form (isomorphic with elementary form), and solved the problems of physical clustering without that affecting the conceptual models, there would be no need for NULLs... except for the NULLs hidden inside the physical model, of course.

As technology advances and allows data to move progressively into Flash storage, and the rotational latency of the disk drive disappears, there's less need for physical clustering, so the conceptual and physical models will converge. There's still some premium on physical clustering due to hardware data transfer protocols, but I predict this will progressively become irrelevant.

Until that happens, we need to deal with the explicit generation of composite physical models, probably using both SQL and NULL values, and I don't see that to be a problem... although it could have been hidden by better DBMS design. The great pity isn't that physical models must be explicitly generated, but that we haven't built query languages that operate purely at the conceptual level, and hide the mapping to the physical level. Although it's possible to use SQL views to project a pure conceptual schema from a composite physical schema, it's a lot more work to construct. Querying it also requires many more joins, and because the SQL join syntax is so awkward and ugly, people avoid that. The result is that physical schemata are hard-wired into database query code in almost every application in existence.

As it is, we need to solve the conceptual/physical mapping problem outside the SQL implementations. The goal is to never have to interact with the physical model directly. A number of people besides myself have proposed such solutions to this problem, including the estimable Messrs Darwen and Date. My offering is the Constellation Query Language, which is similar to Terry's FORML and Conquer. I honestly think that such solutions represent the future of data.