Re: Bill of materials / Groups in Groups

joe_celko_at_my-deja.com wrote in message <86id54$5v$1_at_nnrp1.deja.com>...
> CREATE TABLE OrgChart
> (emp CHAR(10) PRIMARY KEY,
> lft INTEGER NOT NULL UNIQUE CHECK (lft > 0), -- key
> rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1), -- key
> CONSTRAINT subset_definition
> CHECK (lft < rgt), -- domain constraint
> CONSTRAINT subordination -- domain constraint
> CHECK (NOT EXISTS
> (SELECT *
> FROM OrgChart AS C1
> WHERE C1.emp <> OrgChart.emp
> AND C1.lft BETWEEN OrgChart.lft AND OrgChart.rgt
> AND Orgchart.rgt BETWEEN C1.lft AND C1.rgt)),
> CONSTRAINT compound_key UNIQUE (lft, rgt) -- key
> );

According to Atzeni & DeAntonellis, a domain constraint simply expresses the fact that the values of a certain attribute must always come from a certain set (the "domain" of the attribute). For instance, the requirement that lft is an integer and must be greater than zero is a domain constraint: it declares that values of lft come from the set of positive integers. But a constraint like "subset_definition" is not a domain constraint. It enforces a relationship that must hold between two attributes of each tuple (lft < rgt), which is much stronger than simply requiring an attribute to take on values from a pre-specified set. The constraint "subordination" is even more complicated, referring to several attributes of a tuple and also to the other tuples in the relation. It is certainly not a domain constraint. Since you've specified two complex constraints that are neither key constraints nor domain constraints, I have to conclude once again that the relation is not in DKNF.

Do you have a different definition of the term "domain constraint"? (I'd wager that if you can define "domain constraint" so that your two constraints above qualify, then I can show that my ridiculous one-table employee + department example from 1/13 is in DKNF by that definition!)

>>> The nested set model works ONLY if you write complex stored
>procedures to do all of your updates, and you allow the procedures to
>temporarily violate the constraints of the relation.  This is fine from
>a practical standpoint, and can be done effectively with today's
>DBMSs.  However, it does NOT satisfy the definition of DKNF. <<
>
>No, I can get by with just an intentional definition of the domain of
>valid (lft,rgt) pairs in a compound key.  But if I do not close up the
>gaps, I lose a lot of power that is handy for doing queries based on
>simple algebra on the (lft,rgt) pairs.

Well, in the version you've presented here, where you allow gaps, you have indeed gotten rid of the deletion anomalies: you can delete any row from the database without putting it in an invalid state. But you still have insertion anomalies. If you start with a consistent set of data and add a row that satisfies all the key constraints and domain constraints, it will very often put the database in an inconsistent state. Therefore you need a stored procedure to do your inserts for you.

>>> I still don't believe that there's any definition of normalization
>according to which the nested set model is normalized while the
>adjacency model is not. <<
>
>Again, delete Chuck and you lose subordination in the adjacency list
>model, but not in the nested sets.  Update Chuck in in the adjacency
>list model and he has be changed in his other roles, but not in the
>nested sets.

I really don't see why you say this. If you delete Chuck from the adjacency model using the appropriate stored procedure, you don't lose any subordination. Why are you happy to use complex triggers and stored procedures to help you maintain integrity in the nested set model, but unwilling to let me use a much simpler stored procedure to satisfy a business rule in the adjacency model?

As for "updating Chuck" and changing his "other roles", I'm not sure what you mean. Are you simply talking about updating the references to "Chuck" throughout the database if Chuck changes his name to Charles? In your 1/13 message you showed how to manage this practically using SQL clauses like "ON UPDATE CASCADE". Is there some reason why it's impossible or inappropriate to use this sort of clause in the adjacency model?

In any case, this whole issue goes quite far afield from the discussion of DKNF, and you haven't suggested any other notion of "normalization" that we should be using, so I must assume that you agree with me that the differences between our models are best described in terms of practical utility and not in terms of which model is "more normalized". Received on Mon Jan 24 2000 - 00:00:00 CET