Re: Bill of materials / Groups in Groups

>> OK, I've refreshed my memory on Domain-Key Normal Form, as presented
in Atzeni & DeAntonellis ... definition is that you can always delete any tuple from the relation without making it inconsistent ... <<

Bingo! Let's do the nested set table out in its full glory:

CREATE TABLE OrgChart
 (emp CHAR(10) PRIMARY KEY,
  lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),   rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),   CONSTRAINT subset_definition
  CHECK (lft < rgt),
  CONSTRAINT subordination
  CHECK ((rgt-lft +1)/2

• (SELECT COUNT(*) FROM OrgChart AS C1 WHERE C1.lft BETWEEN OrgChart.lft AND OrgChart.rgt)) );

We could also add a minimal numbering constraint and enforce it with a trigger, but it is not vital:

  CONSTRAINT no-gaps
  CHECK (2 * (SELECT COUNT(*) FROM OrgChart)

• (SELECT MAX(rgt) FROM OrgChart))

Now use the old data again:

 OrgChart
 emp lft rgt

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

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

Delete Chuck in the nested set model and Donna, Eddie and Fred are still subordinates of Albert. Delete Chuck in the adjacency list model and Donna, Eddie and Fred are separate trees in a forest, with no relationship to Albert!

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jan 21 2000 - 00:00:00 CET