Re: Quote of the Week

From: --CELKO-- <jcelko212_at_earthlink.net>
Date: 13 May 2004 13:35:23 -0700
Message-ID: <18c7b3c2.0405131235.8157ea0_at_posting.google.com>

>> it is called a special value I guess, and perfectly legitimate. Would you care pointing to or posting the details, so we can judge better? <<

"On an Old Celko Puzzle" at:
http://www.dbdebunk.com/page/page/666711.htm

I don't mind a special value. Techniques for missing or special values go back to statistics before the RM existed. However, using
'1900-01-01' as a missing date and 0.00 for a missing salary is simply
false information.

>> I think it was clear enough SQL doesn't interest them much, except as a bad example. <<

That is brutally obvious at the start of Date's solution :)

>> Actually I am at his side in this. I don't see how your solution is less procedural than his, and his is certainly simpler. <<

Let's do a simple one; an adjacency list model and a nested set model for the same little organization. There should be more constraints to assure there are no cycles, etd., but let me skip them:

CREATE TABLE AdjOrgChart
(emp CHAR(10) NOT NULL PRIMARY KEY,
  boss CHAR(10) DEFAULT NULL REFERENCES OrgChart(emp));

OrgChart
emp boss

---

'Albert' NULL
'Bert' 'Albert'
'Chuck' 'Albert'
'Donna' 'Chuck'
'Eddie' 'Chuck'
'Fred' 'Chuck'

CREATE TABLE NestedOrgChart
(emp CHAR(10) NOT NULL PRIMARY KEY,
  lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),   rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),   CONSTRAINT order_okay CHECK (lft < rgt));

OrgChart
emp lft rgt

---

'Albert' 1 12
'Bert' 2 3
'Chuck' 4 11
'Donna' 5 6
'Eddie' 7 8
'Fred' 9 10

The organizational chart would look like this as a directed graph:

            Albert (1, 12)
            /        \
          /            \
    Bert (2, 3)    Chuck (4, 11)
                   /    |   \
                 /      |     \
               /        |       \
             /          |         \
        Donna (5, 6) Eddie (7, 8) Fred (9, 10) 

Now do a simple query, given an employee, find all their Supervisors, no matter how deep the tree.

SELECT O2.*
   FROM OrgChart AS O1, OrgChart AS O2
  WHERE O1.lft BETWEEN O2.lft AND O2.rgt     AND O1.emp = :myemployee;

This is a pure set operation: Given all the nodes in the tree, I can apply the test (characteristic function) for membership in the result in parallel.

In the adjacency list model, you have to loop or recurse from the employee's node up the tree procedurally. You can try to hide the cursor in syntax, like the Oracle CONNECT BY .. PRIOR, but the natural of the model requires a sequential or recursive solution. Given all the nodes in the tree, I can apply the characteristic function (E1.boss = B1.emp) for membership in the result in parallel, but it will only show me the immediate superiors, not the *entire set* of superiors in one step.

Here is the link on Amazon.com for my new book on "Trees & Hierarchies in SQL"

http://www.amazon.com/exec/obidos/tg/detail/-/1558609202/qid=1080772873/sr=1-1/ref=sr_1_1/102-7683601-6345721?v=glance&s=books#product-details

>> Being constantly in the move in the last year I haven't delved deep into this, as it seems the only good explanation of their model is in the book. So I can't evaluate your claims yet. <<

Temporal stuff is complicated; I'd recommend one of Snodgrass' book from Morgan-Kaufmann for a good overview.

>> SQL ain't relational, c'mon. <<

Sure; and FORTRAN is not Algebra; Maple and Mathematica are much better products for Algebra and they throws in Calculus, too! But for most programmers and most jobs, FORTRAN worked fine becuase it can handle the I/O, infterface to the rest of the world, etc. This is an engineer's argument :)

>> By implying so you give Fabian all the reason he could ever need. <<

True, very true. Received on Thu May 13 2004 - 22:35:23 CEST